In order to execute an SQL statement, the SQLite library first parses the SQL, analyzes the statement, then generates a short program to execute the statement. The program is generated for a "virtual machine" implemented by the SQLite library. This document describes the operation of that virtual machine.
This document is intended as a reference, not a tutorial. A separate Virtual Machine Tutorial is available. If you are looking for a narrative description of how the virtual machine works, you should read the tutorial and not this document. Once you have a basic idea of what the virtual machine does, you can refer back to this document for the details on a particular opcode. Unfortunately, the virtual machine tutorial was written for SQLite version 1.0. There are substantial changes in the virtual machine for version 2.0 and the document has not been updated.
The source code to the virtual machine is in the vdbe.c source file. All of the opcode definitions further down in this document are contained in comments in the source file. In fact, the opcode table in this document was generated by scanning the vdbe.c source file and extracting the necessary information from comments. So the source code comments are really the canonical source of information about the virtual machine. When in doubt, refer to the source code.
Each instruction in the virtual machine consists of an opcode and up to three operands named P1, P2 and P3. P1 may be an arbitrary integer. P2 must be a non-negative integer. P2 is always the jump destination in any operation that might cause a jump. P3 is a null-terminated string or NULL. Some operators use all three operands. Some use one or two. Some operators use none of the operands.
The virtual machine begins execution on instruction number 0. Execution continues until (1) a Halt instruction is seen, or (2) the program counter becomes one greater than the address of last instruction, or (3) there is an execution error. When the virtual machine halts, all memory that it allocated is released and all database cursors it may have had open are closed. If the execution stopped due to an error, any pending transactions are terminated and changes made to the database are rolled back.
The virtual machine also contains an operand stack of unlimited depth. Many of the opcodes use operands from the stack. See the individual opcode descriptions for details.
The virtual machine can have zero or more cursors. Each cursor is a pointer into a single table or index within the database. There can be multiple cursors pointing at the same index or table. All cursors operate independently, even cursors pointing to the same indices or tables. The only way for the virtual machine to interact with a database file is through a cursor. Instructions in the virtual machine can create a new cursor (Open), read data from a cursor (Column), advance the cursor to the next entry in the table (Next) or index (NextIdx), and many other operations. All cursors are automatically closed when the virtual machine terminates.
The virtual machine contains an arbitrary number of fixed memory locations with addresses beginning at zero and growing upward. Each memory location can hold an arbitrary string. The memory cells are typically used to hold the result of a scalar SELECT that is part of a larger expression.
The virtual machine contains a single sorter. The sorter is able to accumulate records, sort those records, then play the records back in sorted order. The sorter is used to implement the ORDER BY clause of a SELECT statement.
The virtual machine contains a single "List". The list stores a list of integers. The list is used to hold the rowids for records of a database table that needs to be modified. The WHERE clause of an UPDATE or DELETE statement scans through the table and writes the rowid of every record to be modified into the list. Then the list is played back and the table is modified in a separate step.
The virtual machine can contain an arbitrary number of "Sets". Each set holds an arbitrary number of strings. Sets are used to implement the IN operator with a constant right-hand side.
The virtual machine can open a single external file for reading. This external read file is used to implement the COPY command.
Finally, the virtual machine can have a single set of aggregators. An aggregator is a device used to implement the GROUP BY clause of a SELECT. An aggregator has one or more slots that can hold values being extracted by the select. The number of slots is the same for all aggregators and is defined by the AggReset operation. At any point in time a single aggregator is current or "has focus". There are operations to read or write to memory slots of the aggregator in focus. There are also operations to change the focus aggregator and to scan through all aggregators.
Every SQL statement that SQLite interprets results in a program for the virtual machine. But if you precede the SQL statement with the keyword "EXPLAIN" the virtual machine will not execute the program. Instead, the instructions of the program will be returned like a query result. This feature is useful for debugging and for learning how the virtual machine operates.
You can use the sqlite command-line tool to see the instructions generated by an SQL statement. The following is an example:
$ sqlite ex1
sqlite> .explain
sqlite> explain delete from tbl1 where two<20;
addr opcode p1 p2 p3
---- ------------ ----- ----- ----------------------------------------
0 Transaction 0 0
1 VerifyCookie 219 0
2 ListOpen 0 0
3 Open 0 3 tbl1
4 Rewind 0 0
5 Next 0 12
6 Column 0 1
7 Integer 20 0
8 Ge 0 5
9 Recno 0 0
10 ListWrite 0 0
11 Goto 0 5
12 Close 0 0
13 ListRewind 0 0
14 OpenWrite 0 3
15 ListRead 0 19
16 MoveTo 0 0
17 Delete 0 0
18 Goto 0 15
19 ListClose 0 0
20 Commit 0 0
All you have to do is add the "EXPLAIN" keyword to the front of the SQL statement. But if you use the ".explain" command to sqlite first, it will set up the output mode to make the program more easily viewable.
If sqlite has been compiled without the "-DNDEBUG=1" option (that is, with the NDEBUG preprocessor macro not defined) then you can put the SQLite virtual machine in a mode where it will trace its execution by writing messages to standard output. The non-standard SQL "PRAGMA" comments can be used to turn tracing on and off. To turn tracing on, enter:
PRAGMA vdbe_trace=on;
You can turn tracing back off by entering a similar statement but changing the value "on" to "off".
There are currently 129 opcodes defined by the virtual machine. All currently defined opcodes are described in the table below. This table was generated automatically by scanning the source code from the file vdbe.c.
Opcode Name | Description |
---|---|
Add | Add the value in register P1 to the value in register P2 and store the result in regiser P3. If either input is NULL, the result is NULL. |
AddImm | Add the constant P2 the value in register P1. The result is always an integer. To force any register to be an integer, just add 0. |
Affinity | Apply affinities to a range of P2 registers starting with P1. P4 is a string that is P2 characters long. The nth character of the string indicates the column affinity that should be used for the nth memory cell in the range. |
AggFinal | Execute the finalizer function for an aggregate. P1 is the memory location that is the accumulator for the aggregate. P2 is the number of arguments that the step function takes and P4 is a pointer to the FuncDef for this function. The P2 argument is not used by this opcode. It is only there to disambiguate functions that can take varying numbers of arguments. The P4 argument is only needed for the degenerate case where the step function was not previously called. |
AggStep | Execute the step function for an aggregate. The function has P5 arguments. P4 is a pointer to the FuncDef structure that specifies the function. Use register P3 as the accumulator. The P5 arguments are taken from register P2 and its successors. |
And | Take the logical AND of the values in registers P1 and P2 and write the result into register P3. If either P1 or P2 is 0 (false) then the result is 0 even if the other input is NULL. A NULL and true or two NULLs give a NULL output. |
AutoCommit | Set the database auto-commit flag to P1 (1 or 0). If P2 is true, roll back any currently active btree transactions. If there are any active VMs (apart from this one), then the COMMIT or ROLLBACK statement fails. This instruction causes the VM to halt. |
BitAnd | Take the bit-wise AND of the values in register P1 and P2 and store the result in register P3. If either input is NULL, the result is NULL. |
BitNot | Interpret the content of register P1 as an integer. Replace it with its ones-complement. If the value is originally NULL, leave it unchanged. |
BitOr | Take the bit-wise OR of the values in register P1 and P2 and store the result in register P3. If either input is NULL, the result is NULL. |
Blob | P4 points to a blob of data P1 bytes long. Store this blob in register P2. This instruction is not coded directly by the compiler. Instead, the compiler layer specifies an OP_HexBlob opcode, with the hex string representation of the blob as P4. This opcode is transformed to an OP_Blob the first time it is executed. |
Clear | Delete all contents of the database table or index whose root page in the database file is given by P1. But, unlike Destroy, do not remove the table or index from the database file. The table being clear is in the main database file if P2==0. If P2==1 then the table to be clear is in the auxiliary database file that is used to store tables create using CREATE TEMPORARY TABLE. See also: Destroy |
Close | Close a cursor previously opened as P1. If P1 is not currently open, this instruction is a no-op. |
CollSeq | P4 is a pointer to a CollSeq struct. If the next call to a user function or aggregate calls sqlite3GetFuncCollSeq(), this collation sequence will be returned. This is used by the built-in min(), max() and nullif() functions. The interface used by the implementation of the aforementioned functions to retrieve the collation sequence set by this opcode is not available publicly, only to user functions defined in func.c. |
Column | Interpret the data that cursor P1 points to as a structure built using the MakeRecord instruction. (See the MakeRecord opcode for additional information about the format of the data.) Extract the P2-th column from this record. If there are less that (P2+1) values in the record, extract a NULL. The value extracted is stored in register P3. If the KeyAsData opcode has previously executed on this cursor, then the field might be extracted from the key rather than the data. If the column contains fewer than P2 fields, then extract a NULL. Or, if the P4 argument is a P4_MEM use the value of the P4 argument as the result. |
Concat | Add the text in register P1 onto the end of the text in register P2 and store the result in register P3. If either the P1 or P2 text are NULL then store NULL in P3. P3 = P2 || P1 It is illegal for P1 and P3 to be the same register. Sometimes, if P3 is the same register as P2, the implementation is able to avoid a memcpy(). |
ContextPop | Restore the Vdbe context to the state it was in when contextPush was last executed. The context stores the last insert row id, the last statement change count, and the current statement change count. |
ContextPush | Save the current Vdbe context such that it can be restored by a ContextPop opcode. The context stores the last insert row id, the last statement change count, and the current statement change count. |
Copy | Make a copy of register P1 into register P2. This instruction makes a deep copy of the value. A duplicate is made of any string or blob constant. See also OP_SCopy. |
CreateIndex | Allocate a new index in the main database file if P1==0 or in the auxiliary database file if P1==1 or in an attached database if P1>1. Write the root page number of the new table into register P2. See documentation on OP_CreateTable for additional information. |
CreateTable | Allocate a new table in the main database file if P1==0 or in the auxiliary database file if P1==1 or in an attached database if P1>1. Write the root page number of the new table into register P2 The difference between a table and an index is this: A table must have a 4-byte integer key and can have arbitrary data. An index has an arbitrary key but no data. See also: CreateIndex |
Delete | Delete the record at which the P1 cursor is currently pointing. The cursor will be left pointing at either the next or the previous record in the table. If it is left pointing at the next record, then the next Next instruction will be a no-op. Hence it is OK to delete a record from within an Next loop. If the OPFLAG_NCHANGE flag of P2 is set, then the row change count is incremented (otherwise not). P1 must not be pseudo-table. It has to be a real table with multiple rows. If P4 is not NULL, then it is the name of the table that P1 is pointing to. The update hook will be invoked, if it exists. If P4 is not NULL then the P1 cursor must have been positioned using OP_NotFound prior to invoking this opcode. |
Destroy | Delete an entire database table or index whose root page in the database file is given by P1. The table being destroyed is in the main database file if P3==0. If P3==1 then the table to be clear is in the auxiliary database file that is used to store tables create using CREATE TEMPORARY TABLE. If AUTOVACUUM is enabled then it is possible that another root page might be moved into the newly deleted root page in order to keep all root pages contiguous at the beginning of the database. The former value of the root page that moved - its value before the move occurred - is stored in register P2. If no page movement was required (because the table being dropped was already the last one in the database) then a zero is stored in register P2. If AUTOVACUUM is disabled then a zero is stored in register P2. See also: Clear |
Divide | Divide the value in register P1 by the value in register P2 and store the result in register P3. If the value in register P2 is zero, then the result is NULL. If either input is NULL, the result is NULL. |
DropIndex | Remove the internal (in-memory) data structures that describe the index named P4 in database P1. This is called after an index is dropped in order to keep the internal representation of the schema consistent with what is on disk. |
DropTable | Remove the internal (in-memory) data structures that describe the table named P4 in database P1. This is called after a table is dropped in order to keep the internal representation of the schema consistent with what is on disk. |
DropTrigger | Remove the internal (in-memory) data structures that describe the trigger named P4 in database P1. This is called after a trigger is dropped in order to keep the internal representation of the schema consistent with what is on disk. |
Eq | This works just like the Lt opcode except that the jump is taken if the operands in registers P1 and P3 are equal. See the Lt opcode for additional information. |
Expire | Cause precompiled statements to become expired. An expired statement fails with an error code of SQLITE_SCHEMA if it is ever executed (via sqlite3_step()). If P1 is 0, then all SQL statements become expired. If P1 is non-zero, then only the currently executing statement is affected. |
FifoRead | Attempt to read a single integer from the Fifo. Store that integer in register P1. If the Fifo is empty jump to P2. |
FifoWrite | Write the integer from register P1 into the Fifo. |
ForceInt | Convert value in register P1 into an integer. If the value in P1 is not numeric (meaning that is is a NULL or a string that does not look like an integer or floating point number) then jump to P2. If the value in P1 is numeric then convert it into the least integer that is greater than or equal to its current value if P3==0, or to the least integer that is strictly greater than its current value if P3==1. |
Found | Register P3 holds a blob constructed by MakeRecord. P1 is an index. If an entry that matches the value in register p3 exists in P1 then jump to P2. If the P3 value does not match any entry in P1 then fall thru. The P1 cursor is left pointing at the matching entry if it exists. This instruction is used to implement the IN operator where the left-hand side is a SELECT statement. P1 may be a true index, or it may be a temporary index that holds the results of the SELECT statement. This instruction is also used to implement the DISTINCT keyword in SELECT statements. This instruction checks if index P1 contains a record for which the first N serialised values exactly match the N serialised values in the record in register P3, where N is the total number of values in the P3 record (the P3 record is a prefix of the P1 record). See also: NotFound, MoveTo, IsUnique, NotExists |
Function | Invoke a user function (P4 is a pointer to a Function structure that defines the function) with P5 arguments taken from register P2 and successors. The result of the function is stored in register P3. Register P3 must not be one of the function inputs. P1 is a 32-bit bitmask indicating whether or not each argument to the function was determined to be constant at compile time. If the first argument was constant then bit 0 of P1 is set. This is used to determine whether meta data associated with a user function argument using the sqlite3_set_auxdata() API may be safely retained until the next invocation of this opcode. See also: AggStep and AggFinal |
Ge | This works just like the Lt opcode except that the jump is taken if the content of register P3 is greater than or equal to the content of register P1. See the Lt opcode for additional information. |
Gosub | Push the current address plus 1 onto the return address stack and then jump to address P2. The return address stack is of limited depth. If too many OP_Gosub operations occur without intervening OP_Returns, then the return address stack will fill up and processing will abort with a fatal error. |
Goto | An unconditional jump to address P2. The next instruction executed will be the one at index P2 from the beginning of the program. |
Gt | This works just like the Lt opcode except that the jump is taken if the content of register P3 is greater than the content of register P1. See the Lt opcode for additional information. |
Halt | Exit immediately. All open cursors, Fifos, etc are closed automatically. P1 is the result code returned by sqlite3_exec(), sqlite3_reset(), or sqlite3_finalize(). For a normal halt, this should be SQLITE_OK (0). For errors, it can be some other value. If P1!=0 then P2 will determine whether or not to rollback the current transaction. Do not rollback if P2==OE_Fail. Do the rollback if P2==OE_Rollback. If P2==OE_Abort, then back out all changes that have occurred during this execution of the VDBE, but do not rollback the transaction. If P4 is not null then it is an error message string. There is an implied "Halt 0 0 0" instruction inserted at the very end of every program. So a jump past the last instruction of the program is the same as executing Halt. |
IdxDeleteM | The content of P3 registers starting at register P2 form an unpacked index key. This opcode removes that entry from the index opened by cursor P1. |
IdxGE | The P4 register values beginning with P3 form an unpacked index key that omits the ROWID. Compare this key value against the index that P1 is currently pointing to, ignoring the ROWID on the P1 index. If the P1 index entry is greater than or equal to the key value then jump to P2. Otherwise fall through to the next instruction. If P5 is non-zero then the key value is increased by an epsilon prior to the comparison. This make the opcode work like IdxGT except that if the key from register P3 is a prefix of the key in the cursor, the result is false whereas it would be true with IdxGT. |
IdxInsert | Register P2 holds a SQL index key made using the MakeIdxRec instructions. This opcode writes that key into the index P1. Data for the entry is nil. P3 is a flag that provides a hint to the b-tree layer that this insert is likely to be an append. This instruction only works for indices. The equivalent instruction for tables is OP_Insert. |
IdxLT | The P4 register values beginning with P3 form an unpacked index key that omits the ROWID. Compare this key value against the index that P1 is currently pointing to, ignoring the ROWID on the P1 index. If the P1 index entry is less than the key value then jump to P2. Otherwise fall through to the next instruction. If P5 is non-zero then the key value is increased by an epsilon prior to the comparison. This makes the opcode work like IdxLE. |
IdxRowid | Write into register P2 an integer which is the last entry in the record at the end of the index key pointed to by cursor P1. This integer should be the rowid of the table entry to which this index entry points. See also: Rowid, MakeIdxRec. |
If | Jump to P2 if the value in register P1 is true. The value is is considered true if it is numeric and non-zero. If the value in P1 is NULL then take the jump if P3 is true. |
IfNeg | If the value of register P1 is less than zero, jump to P2. It is illegal to use this instruction on a register that does not contain an integer. An assertion fault will result if you try. |
IfNot | Jump to P2 if the value in register P1 is False. The value is is considered true if it has a numeric value of zero. If the value in P1 is NULL then take the jump if P3 is true. |
IfPos | If the value of register P1 is 1 or greater, jump to P2. It is illegal to use this instruction on a register that does not contain an integer. An assertion fault will result if you try. |
IfZero | If the value of register P1 is exactly 0, jump to P2. It is illegal to use this instruction on a register that does not contain an integer. An assertion fault will result if you try. |
IncrVacuum | Perform a single step of the incremental vacuum procedure on the P1 database. If the vacuum has finished, jump to instruction P2. Otherwise, fall through to the next instruction. |
Insert | Write an entry into the table of cursor P1. A new entry is created if it doesn't already exist or the data for an existing entry is overwritten. The data is the value stored register number P2. The key is stored in register P3. The key must be an integer. If the OPFLAG_NCHANGE flag of P5 is set, then the row change count is incremented (otherwise not). If the OPFLAG_LASTROWID flag of P5 is set, then rowid is stored for subsequent return by the sqlite3_last_insert_rowid() function (otherwise it is unmodified). Parameter P4 may point to a string containing the table-name, or may be NULL. If it is not NULL, then the update-hook (sqlite3.xUpdateCallback) is invoked following a successful insert. (WARNING/TODO: If P1 is a pseudo-cursor and P2 is dynamically allocated, then ownership of P2 is transferred to the pseudo-cursor and register P2 becomes ephemeral. If the cursor is changed, the value of register P2 will then change. Make sure this does not cause any problems.) This instruction only works on tables. The equivalent instruction for indices is OP_IdxInsert. |
Int64 | P4 is a pointer to a 64-bit integer value. Write that value into register P2. |
Integer | The 32-bit integer value P1 is written into register P2. |
IntegrityCk | Do an analysis of the currently open database. Store in register P1 the text of an error message describing any problems. If no problems are found, store a NULL in register P1. The register P3 contains the maximum number of allowed errors. At most reg(P3) errors will be reported. In other words, the analysis stops as soon as reg(P1) errors are seen. Reg(P1) is updated with the number of errors remaining. The root page numbers of all tables in the database are integer stored in reg(P1), reg(P1+1), reg(P1+2), .... There are P2 tables total. If P5 is not zero, the check is done on the auxiliary database file, not the main database file. This opcode is used to implement the integrity_check pragma. |
IsNull | Jump to P2 if the value in register P1 is NULL. If P3 is greater than zero, then check all values reg(P1), reg(P1+1), reg(P1+2), ..., reg(P1+P3-1). |
IsUnique | The P3 register contains an integer record number. Call this record number R. The P4 register contains an index key created using MakeIdxRec. Call it K. P1 is an index. So it has no data and its key consists of a record generated by OP_MakeRecord where the last field is the rowid of the entry that the index refers to. This instruction asks if there is an entry in P1 where the fields matches K but the rowid is different from R. If there is no such entry, then there is an immediate jump to P2. If any entry does exist where the index string matches K but the record number is not R, then the record number for that entry is written into P3 and control falls through to the next instruction. See also: NotFound, NotExists, Found |
Last | The next use of the Rowid or Column or Next instruction for P1 will refer to the last entry in the database table or index. If the table or index is empty and P2>0, then jump immediately to P2. If P2 is 0 or if the table or index is not empty, fall through to the following instruction. |
Le | This works just like the Lt opcode except that the jump is taken if the content of register P3 is less than or equal to the content of register P1. See the Lt opcode for additional information. |
LoadAnalysis | Read the sqlite_stat1 table for database P1 and load the content of that table into the internal index hash table. This will cause the analysis to be used when preparing all subsequent queries. |
Lt | Compare the values in register P1 and P3. If reg(P3)<reg(P1) then jump to address P2. If the SQLITE_JUMPIFNULL bit of P5 is set and either reg(P1) or reg(P3) is NULL then take the jump. If the SQLITE_JUMPIFNULL bit is clear then fall thru if either operand is NULL. If the SQLITE_NULLEQUAL bit of P5 is set then treat NULL operands as being equal to one another. Normally NULLs are not equal to anything including other NULLs. The SQLITE_AFF_MASK portion of P5 must be an affinity character - SQLITE_AFF_TEXT, SQLITE_AFF_INTEGER, and so forth. An attempt is made to coerce both inputs according to this affinity before the comparison is made. If the SQLITE_AFF_MASK is 0x00, then numeric affinity is used. Note that the affinity conversions are stored back into the input registers P1 and P3. So this opcode can cause persistent changes to registers P1 and P3. Once any conversions have taken place, and neither value is NULL, the values are compared. If both values are blobs then memcmp() is used to determine the results of the comparison. If both values are text, then the appropriate collating function specified in P4 is used to do the comparison. If P4 is not specified then memcmp() is used to compare text string. If both values are numeric, then a numeric comparison is used. If the two values are of different types, then numbers are considered less than strings and strings are considered less than blobs. If the SQLITE_STOREP2 bit of P5 is set, then do not jump. Instead, store a boolean result (either 0, or 1, or NULL) in register P2. |
MakeRecord | Convert P2 registers beginning with P1 into a single entry suitable for use as a data record in a database table or as a key in an index. The details of the format are irrelavant as long as the OP_Column opcode can decode the record later. Refer to source code comments for the details of the record format. P4 may be a string that is P2 characters long. The nth character of the string indicates the column affinity that should be used for the nth field of the index key. The mapping from character to affinity is given by the SQLITE_AFF_ macros defined in sqliteInt.h. If P4 is NULL then all index fields have the affinity NONE. |
MemMax | Set the value of register P1 to the maximum of its current value and the value in register P2. This instruction throws an error if the memory cell is not initially an integer. |
Move | Move the value in register P1 over into register P2. Register P1 is left holding a NULL. It is an error for P1 and P2 to be the same register. |
MoveGe | If cursor P1 refers to an SQL table (B-Tree that uses integer keys), use the integer value in register P3 as a key. If cursor P1 refers to an SQL index, then P3 is the first in an array of P4 registers that are used as an unpacked index key. Reposition cursor P1 so that it points to the smallest entry that is greater than or equal to the key value. If there are no records greater than or equal to the key and P2 is not zero, then jump to P2. A special feature of this opcode (and different from the related OP_MoveGt, OP_MoveLt, and OP_MoveLe) is that if P2 is zero and P1 is an SQL table (a b-tree with integer keys) then the seek is deferred until it is actually needed. It might be the case that the cursor is never accessed. By deferring the seek, we avoid unnecessary seeks. See also: Found, NotFound, Distinct, MoveLt, MoveGt, MoveLe |
MoveGt | If cursor P1 refers to an SQL table (B-Tree that uses integer keys), use the integer value in register P3 as a key. If cursor P1 refers to an SQL index, then P3 is the first in an array of P4 registers that are used as an unpacked index key. Reposition cursor P1 so that it points to the smallest entry that is greater than the key value. If there are no records greater than the key and P2 is not zero, then jump to P2. See also: Found, NotFound, Distinct, MoveLt, MoveGe, MoveLe |
MoveLe | If cursor P1 refers to an SQL table (B-Tree that uses integer keys), use the integer value in register P3 as a key. If cursor P1 refers to an SQL index, then P3 is the first in an array of P4 registers that are used as an unpacked index key. Reposition cursor P1 so that it points to the largest entry that is less than or equal to the key value. If there are no records less than or equal to the key and P2 is not zero, then jump to P2. See also: Found, NotFound, Distinct, MoveGt, MoveGe, MoveLt |
MoveLt | If cursor P1 refers to an SQL table (B-Tree that uses integer keys), use the integer value in register P3 as a key. If cursor P1 refers to an SQL index, then P3 is the first in an array of P4 registers that are used as an unpacked index key. Reposition cursor P1 so that it points to the largest entry that is less than the key value. If there are no records less than the key and P2 is not zero, then jump to P2. See also: Found, NotFound, Distinct, MoveGt, MoveGe, MoveLe |
Multiply |
Multiply the value in regiser P1 by the value in regiser P2 and store the result in register P3. If either input is NULL, the result is NULL. |
MustBeInt | Force the value in register P1 to be an integer. If the value in P1 is not an integer and cannot be converted into an integer without data loss, then jump immediately to P2, or if P2==0 raise an SQLITE_MISMATCH exception. |
Ne | This works just like the Lt opcode except that the jump is taken if the operands in registers P1 and P3 are not equal. See the Lt opcode for additional information. |
NewRowid | Get a new integer record number (a.k.a "rowid") used as the key to a table. The record number is not previously used as a key in the database table that cursor P1 points to. The new record number is written written to register P2. If P3>0 then P3 is a register that holds the largest previously generated record number. No new record numbers are allowed to be less than this value. When this value reaches its maximum, a SQLITE_FULL error is generated. The P3 register is updated with the generated record number. This P3 mechanism is used to help implement the AUTOINCREMENT feature. |
Next | Advance cursor P1 so that it points to the next key/data pair in its table or index. If there are no more key/value pairs then fall through to the following instruction. But if the cursor advance was successful, jump immediately to P2. The P1 cursor must be for a real table, not a pseudo-table. See also: Prev |
Noop | Do nothing. This instruction is often useful as a jump destination. |
Not | Interpret the value in register P1 as a boolean value. Replace it with its complement. If the value in register P1 is NULL its value is unchanged. |
NotExists | Use the content of register P3 as a integer key. If a record with that key does not exist in table of P1, then jump to P2. If the record does exist, then fall thru. The cursor is left pointing to the record if it exists. The difference between this operation and NotFound is that this operation assumes the key is an integer and that P1 is a table whereas NotFound assumes key is a blob constructed from MakeRecord and P1 is an index. See also: Found, MoveTo, NotFound, IsUnique |
NotFound | Register P3 holds a blob constructed by MakeRecord. P1 is an index. If no entry exists in P1 that matches the blob then jump to P2. If an entry does existing, fall through. The cursor is left pointing to the entry that matches. See also: Found, MoveTo, NotExists, IsUnique |
NotNull | Jump to P2 if the value in register P1 is not NULL. |
Null | Write a NULL into register P2. |
NullRow | Move the cursor P1 to a null row. Any OP_Column operations that occur while the cursor is on the null row will always write a NULL. |
OpenEphemeral | Open a new cursor P1 to a transient table. The cursor is always opened read/write even if the main database is read-only. The transient or virtual table is deleted automatically when the cursor is closed. P2 is the number of columns in the virtual table. The cursor points to a BTree table if P4==0 and to a BTree index if P4 is not 0. If P4 is not NULL, it points to a KeyInfo structure that defines the format of keys in the index. This opcode was once called OpenTemp. But that created confusion because the term "temp table", might refer either to a TEMP table at the SQL level, or to a table opened by this opcode. Then this opcode was call OpenVirtual. But that created confusion with the whole virtual-table idea. |
OpenPseudo | Open a new cursor that points to a fake table that contains a single row of data. Any attempt to write a second row of data causes the first row to be deleted. All data is deleted when the cursor is closed. A pseudo-table created by this opcode is useful for holding the NEW or OLD tables in a trigger. Also used to hold the a single row output from the sorter so that the row can be decomposed into individual columns using the OP_Column opcode. When OP_Insert is executed to insert a row in to the pseudo table, the pseudo-table cursor may or may not make it's own copy of the original row data. If P2 is 0, then the pseudo-table will copy the original row data. Otherwise, a pointer to the original memory cell is stored. In this case, the vdbe program must ensure that the memory cell containing the row data is not overwritten until the pseudo table is closed (or a new row is inserted into it). |
OpenRead | Open a read-only cursor for the database table whose root page is P2 in a database file. The database file is determined by P3. P3==0 means the main database, P3==1 means the database used for temporary tables, and P3>1 means used the corresponding attached database. Give the new cursor an identifier of P1. The P1 values need not be contiguous but all P1 values should be small integers. It is an error for P1 to be negative. If P5!=0 then use the content of register P2 as the root page, not the value of P2 itself. There will be a read lock on the database whenever there is an open cursor. If the database was unlocked prior to this instruction then a read lock is acquired as part of this instruction. A read lock allows other processes to read the database but prohibits any other process from modifying the database. The read lock is released when all cursors are closed. If this instruction attempts to get a read lock but fails, the script terminates with an SQLITE_BUSY error code. The P4 value is a pointer to a KeyInfo structure that defines the content and collating sequence of indices. P4 is NULL for cursors that are not pointing to indices. See also OpenWrite. |
OpenWrite | Open a read/write cursor named P1 on the table or index whose root page is P2. Or if P5!=0 use the content of register P2 to find the root page. The P4 value is a pointer to a KeyInfo structure that defines the content and collating sequence of indices. P4 is NULL for cursors that are not pointing to indices. This instruction works just like OpenRead except that it opens the cursor in read/write mode. For a given table, there can be one or more read-only cursors or a single read/write cursor but not both. See also OpenRead. |
Or | Take the logical OR of the values in register P1 and P2 and store the answer in register P3. If either P1 or P2 is nonzero (true) then the result is 1 (true) even if the other input is NULL. A NULL and false or two NULLs give a NULL output. |
ParseSchema | Read and parse all entries from the SQLITE_MASTER table of database P1 that match the WHERE clause P4. P2 is the "force" flag. Always do the parsing if P2 is true. If P2 is false, then this routine is a no-op if the schema is not currently loaded. In other words, if P2 is false, the SQLITE_MASTER table is only parsed if the rest of the schema is already loaded into the symbol table. This opcode invokes the parser to create a new virtual machine, then runs the new virtual machine. It is thus a reentrant opcode. |
Prev | Back up cursor P1 so that it points to the previous key/data pair in its table or index. If there is no previous key/value pairs then fall through to the following instruction. But if the cursor backup was successful, jump immediately to P2. The P1 cursor must be for a real table, not a pseudo-table. |
ReadCookie | Read cookie number P3 from database P1 and write it into register P2. P3==0 is the schema version. P3==1 is the database format. P3==2 is the recommended pager cache size, and so forth. P1==0 is the main database file and P1==1 is the database file used to store temporary tables. If P1 is negative, then this is a request to read the size of a databases free-list. P3 must be set to 1 in this case. The actual database accessed is ((P1+1)*-1). For example, a P1 parameter of -1 corresponds to database 0 ("main"), a P1 of -2 is database 1 ("temp"). There must be a read-lock on the database (either a transaction must be started or there must be an open cursor) before executing this instruction. |
Real | P4 is a pointer to a 64-bit floating point value. Write that value into register P2. |
RealAffinity | If register P1 holds an integer convert it to a real value. This opcode is used when extracting information from a column that has REAL affinity. Such column values may still be stored as integers, for space efficiency, but after extraction we want them to have only a real value. |
Remainder | Compute the remainder after integer division of the value in register P1 by the value in register P2 and store the result in P3. If the value in register P2 is zero the result is NULL. If either operand is NULL, the result is NULL. |
ResetCount | This opcode resets the VMs internal change counter to 0. If P1 is true, then the value of the change counter is copied to the database handle change counter (returned by subsequent calls to sqlite3_changes()) before it is reset. This is used by trigger programs. |
ResultRow | The registers P1 throught P1+P2-1 contain a single row of results. This opcode causes the sqlite3_step() call to terminate with an SQLITE_ROW return code and it sets up the sqlite3_stmt structure to provide access to the top P1 values as the result row. |
Return | Jump immediately to the next instruction after the last unreturned OP_Gosub. If an OP_Return has occurred for all OP_Gosubs, then processing aborts with a fatal error. |
Rewind | The next use of the Rowid or Column or Next instruction for P1 will refer to the first entry in the database table or index. If the table or index is empty and P2>0, then jump immediately to P2. If P2 is 0 or if the table or index is not empty, fall through to the following instruction. |
RowData | Write into register P2 the complete row data for cursor P1. There is no interpretation of the data. It is just copied onto the P2 register exactly as it is found in the database file. If the P1 cursor must be pointing to a valid row (not a NULL row) of a real table, not a pseudo-table. |
Rowid | Store in register P2 an integer which is the key of the table entry that P1 is currently point to. If p2==0 then push the integer. |
RowKey | Write into register P2 the complete row key for cursor P1. There is no interpretation of the data. The key is copied onto the P3 register exactly as it is found in the database file. If the P1 cursor must be pointing to a valid row (not a NULL row) of a real table, not a pseudo-table. |
SCopy | Make a shallow copy of register P1 into register P2. This instruction makes a shallow copy of the value. If the value is a string or blob, then the copy is only a pointer to the original and hence if the original changes so will the copy. Worse, if the original is deallocated, the copy becomes invalid. Thus the program must guarantee that the original will not change during the lifetime of the copy. Use OP_Copy to make a complete copy. |
Sequence | Find the next available sequence number for cursor P1. Write the sequence number into register P2. The sequence number on the cursor is incremented after this instruction. |
SetCookie | Write the content of register P3 (interpreted as an integer) into cookie number P2 of database P1. P2==0 is the schema version. P2==1 is the database format. P2==2 is the recommended pager cache size, and so forth. P1==0 is the main database file and P1==1 is the database file used to store temporary tables. A transaction must be started before executing this opcode. |
SetNumColumns | This opcode sets the number of columns for the cursor opened by the following instruction to P2. An OP_SetNumColumns is only useful if it occurs immediately before one of the following opcodes: OpenRead OpenWrite OpenPseudo If the OP_Column opcode is to be executed on a cursor, then this opcode must be present immediately before the opcode that opens the cursor. |
ShiftLeft | Shift the integer value in register P2 to the left by the number of bits specified by the integer in regiser P1. Store the result in register P3. If either input is NULL, the result is NULL. |
ShiftRight | Shift the integer value in register P2 to the right by the number of bits specified by the integer in register P1. Store the result in register P3. If either input is NULL, the result is NULL. |
Sort | This opcode does exactly the same thing as OP_Rewind except that it increments an undocumented global variable used for testing. Sorting is accomplished by writing records into a sorting index, then rewinding that index and playing it back from beginning to end. We use the OP_Sort opcode instead of OP_Rewind to do the rewinding so that the global variable will be incremented and regression tests can determine whether or not the optimizer is correctly optimizing out sorts. |
Statement | Begin an individual statement transaction which is part of a larger transaction. This is needed so that the statement can be rolled back after an error without having to roll back the entire transaction. The statement transaction will automatically commit when the VDBE halts. If the database connection is currently in autocommit mode (that is to say, if it is in between BEGIN and COMMIT) and if there are no other active statements on the same database connection, then this operation is a no-op. No statement transaction is needed since any error can use the normal ROLLBACK process to undo changes. If a statement transaction is started, then a statement journal file will be allocated and initialized. The statement is begun on the database file with index P1. The main database file has an index of 0 and the file used for temporary tables has an index of 1. |
String | The string value P4 of length P1 (bytes) is stored in register P2. |
String8 | P4 points to a nul terminated UTF-8 string. This opcode is transformed into an OP_String before it is executed for the first time. |
Subtract | Subtract the value in register P1 from the value in register P2 and store the result in register P3. If either input is NULL, the result is NULL. |
TableLock | Obtain a lock on a particular table. This instruction is only used when the shared-cache feature is enabled. If P1 is the index of the database in sqlite3.aDb[] of the database on which the lock is acquired. A readlock is obtained if P3==0 or a write lock if P3==1. P2 contains the root-page of the table to lock. P4 contains a pointer to the name of the table being locked. This is only used to generate an error message if the lock cannot be obtained. |
ToBlob | Force the value in register P1 to be a BLOB. If the value is numeric, convert it to a string first. Strings are simply reinterpreted as blobs with no change to the underlying data. A NULL value is not changed by this routine. It remains NULL. |
ToInt | Force the value in register P1 be an integer. If The value is currently a real number, drop its fractional part. If the value is text or blob, try to convert it to an integer using the equivalent of atoi() and store 0 if no such conversion is possible. A NULL value is not changed by this routine. It remains NULL. |
ToNumeric | Force the value in register P1 to be numeric (either an integer or a floating-point number.) If the value is text or blob, try to convert it to an using the equivalent of atoi() or atof() and store 0 if no such conversion is possible. A NULL value is not changed by this routine. It remains NULL. |
ToReal | Force the value in register P1 to be a floating point number. If The value is currently an integer, convert it. If the value is text or blob, try to convert it to an integer using the equivalent of atoi() and store 0.0 if no such conversion is possible. A NULL value is not changed by this routine. It remains NULL. |
ToText | Force the value in register P1 to be text. If the value is numeric, convert it to a string using the equivalent of printf(). Blob values are unchanged and are afterwards simply interpreted as text. A NULL value is not changed by this routine. It remains NULL. |
Trace | If tracing is enabled (by the sqlite3_trace()) interface, then the UTF-8 string contained in P4 is emitted on the trace callback. |
Transaction | Begin a transaction. The transaction ends when a Commit or Rollback opcode is encountered. Depending on the ON CONFLICT setting, the transaction might also be rolled back if an error is encountered. P1 is the index of the database file on which the transaction is started. Index 0 is the main database file and index 1 is the file used for temporary tables. Indices of 2 or more are used for attached databases. If P2 is non-zero, then a write-transaction is started. A RESERVED lock is obtained on the database file when a write-transaction is started. No other process can start another write transaction while this transaction is underway. Starting a write transaction also creates a rollback journal. A write transaction must be started before any changes can be made to the database. If P2 is 2 or greater then an EXCLUSIVE lock is also obtained on the file. If P2 is zero, then a read-lock is obtained on the database file. |
Vacuum | Vacuum the entire database. This opcode will cause other virtual machines to be created and run. It may not be called from within a transaction. |
Variable | The value of variable P1 is written into register P2. A variable is an unknown in the original SQL string as handed to sqlite3_compile(). Any occurance of the '?' character in the original SQL is considered a variable. Variables in the SQL string are number from left to right beginning with 1. The values of variables are set using the sqlite3_bind() API. |
VBegin | P4 a pointer to an sqlite3_vtab structure. Call the xBegin method for that table. |
VColumn | Store the value of the P2-th column of the row of the virtual-table that the P1 cursor is pointing to into register P3. |
VCreate | P4 is the name of a virtual table in database P1. Call the xCreate method for that table. |
VDestroy | P4 is the name of a virtual table in database P1. Call the xDestroy method of that table. |
VerifyCookie | Check the value of global database parameter number 0 (the schema version) and make sure it is equal to P2. P1 is the database number which is 0 for the main database file and 1 for the file holding temporary tables and some higher number for auxiliary databases. The cookie changes its value whenever the database schema changes. This operation is used to detect when that the cookie has changed and that the current process needs to reread the schema. Either a transaction needs to have been started or an OP_Open needs to be executed (to establish a read lock) before this opcode is invoked. |
VFilter | P1 is a cursor opened using VOpen. P2 is an address to jump to if the filtered result set is empty. P4 is either NULL or a string that was generated by the xBestIndex method of the module. The interpretation of the P4 string is left to the module implementation. This opcode invokes the xFilter method on the virtual table specified by P1. The integer query plan parameter to xFilter is stored in register P3. Register P3+1 stores the argc parameter to be passed to the xFilter method. Registers P3+2..P3+1+argc are the argc additional parametersneath additional parameters which are passed to xFilter as argv. Register P3+2 becomes argv[0] when passed to xFilter. A jump is made to P2 if the result set after filtering would be empty. |
VNext | Advance virtual table P1 to the next row in its result set and jump to instruction P2. Or, if the virtual table has reached the end of its result set, then fall through to the next instruction. |
VOpen | P4 is a pointer to a virtual table object, an sqlite3_vtab structure. P1 is a cursor number. This opcode opens a cursor to the virtual table and stores that cursor in P1. |
VRename | P4 is a pointer to a virtual table object, an sqlite3_vtab structure. This opcode invokes the corresponding xRename method. The value in register P1 is passed as the zName argument to the xRename method. |
VRowid | Store into register P2 the rowid of the virtual-table that the P1 cursor is pointing to. |
VUpdate | P4 is a pointer to a virtual table object, an sqlite3_vtab structure. This opcode invokes the corresponding xUpdate method. P2 values are contiguous memory cells starting at P3 to pass to the xUpdate invocation. The value in register (P3+P2-1) corresponds to the p2th element of the argv array passed to xUpdate. The xUpdate method will do a DELETE or an INSERT or both. The argv[0] element (which corresponds to memory cell P3) is the rowid of a row to delete. If argv[0] is NULL then no deletion occurs. The argv[1] element is the rowid of the new row. This can be NULL to have the virtual table select the new rowid for itself. The subsequent elements in the array are the values of columns in the new row. If P2==1 then no insert is performed. argv[0] is the rowid of a row to delete. P1 is a boolean flag. If it is set to true and the xUpdate call is successful, then the value returned by sqlite3_last_insert_rowid() is set to the value of the rowid for the row just inserted. |