QDB Virtual Machine Opcodes

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, and some use none.

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 indexes 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, which stores a list of integers. This list is used to hold the row IDs 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 row ID 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.

Viewing programs generated by QDB

Every SQL statement that QDB interprets results in a program for the virtual machine. However, if you precede the SQL statement with the keyword EXPLAIN, the virtual machine doesn't execute the program. Instead, the instructions of the program are returned like a query result. This feature is useful for debugging and for learning how the virtual machine operates, and for profiling an SQL statement. The following is an example of the output from the statement 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 qdb first, it will set up the output mode to make the program more easily viewable.

You can put the QDB virtual machine in a mode where it will trace its execution by writing messages to standard output; and you can use the non-standard SQL PRAGMA, comments 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.

The opcodes

There are currently 125 opcodes defined by the virtual machine. All currently defined opcodes are described in the list below.

AbsValue
Treat the top of the stack as a numeric quantity. Replace it with its absolute value. If the top of the stack is NULL, its value is unchanged.
Add
Pop the top two elements from the stack, add them together, and push the result back onto the stack. If either element is a string, then it is converted to a double using the atof() function before the addition. If either operand is NULL, the result is NULL.
AddImm
Add the value P1 to whatever is on top of the stack. The result is always an integer.

To force the top of the stack to be an integer, just add 0.

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 P3 is a pointer to the FuncDef for this function. The P2 argument is not used by this opcode. It is there only to disambiguate functions that can take varying numbers of arguments. The P3 argument is needed only for the degenerate case where the step function was not previously called.

AggStep
Execute the step function for an aggregate. The function has P2 arguments. P3 is a pointer to the FuncDef structure that specifies the function. Use memory location P1 as the accumulator.

The P2 arguments are popped from the stack.

And
Pop two values off the stack. Take the logical AND of the two values and push the resulting boolean value back onto the stack.
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
Pop the top two elements from the stack. Convert both elements to integers. Push back onto the stack the bitwise AND of the two elements. If either operand is NULL, the result is NULL.
BitNot
Interpret the top of the stack as an value. Replace it with its ones-complement. If the top of the stack is NULL, its value is unchanged.
BitOr
Pop the top two elements from the stack. Convert both elements to integers. Push back onto the stack the bitwise OR of the two elements. If either operand is NULL, the result is NULL.
Blob
P3 points to a Binary Large OBject (BLOB) that is P1 bytes long. Push this value onto the stack. This instruction is not coded directly by the compiler. Instead, the compiler layer specifies an OP_HexBlob opcode, with the hexadecimal string representation of the BLOB as P3. This opcode is transformed to an OP_Blob the first time it is executed.
Callback
Pop P1 values off the stack and form them into an array. Then invoke the callback function using the newly formed array as the third parameter.
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 cleared is in the main database file if P2 is 0. If P2 is 1, then the table to be cleared 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
P3 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.
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.) Push onto the stack the value of the P2th column contained in the data. If there are fewer than P2+1 values in the record, push a NULL onto the stack.

If the KeyAsData opcode has previously executed on this cursor, then the field might be extracted from the key rather than the data.

If P1 is negative, then the record is stored on the stack rather than in a table. If P1 is -1, the top of the stack is used, if P1 is -2, the next on the stack is used, and so forth. The value pushed is always just a pointer into the record that is stored further down on the stack. The column value is not copied. The number of columns in the record is stored on the stack just above the record itself.

If the column contains fewer than P2 fields, then push a NULL. Or if P3 is of type P3_MEM, then push the P3 value. The P3 value will be the default value for a column that has been added using the ALTER TABLE ADD COLUMN command. If P3 is an ordinary string, just push a NULL. When P3 is a string, it is really just a comment describing the value to be pushed, not a default value.

Concat
Look at the first P1+2 elements of the stack. Append them all together with the lowest element first. The original P1+2 elements are popped from the stack if P2 is 0 and retained if P2 is 1. If any element of the stack is NULL, then the result is NULL.

When P1 is 1, this routine makes a copy of the top stack element into memory obtained from sqliteMalloc().

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, so 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.
CreateIndex
Allocate a new index in the main database file if P2 is 0 or in the auxiliary database file if P2 is 1. Push the page number of the root page of the new index onto the stack.
CreateTable
Allocate a new table in the main database file if P2 is 0 or in the auxiliary database file if P2 is 1. Push the page number for the root page of the new table onto the stack.

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 a Next loop.

If the OPFLAG_NCHANGE flag of P2 is set, then the row change count is incremented (otherwise not).

If P1 is a pseudo-table, then this instruction is a no-op.

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 P2 is 0. If P2 is 1 then the table to be cleared 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 pushed onto the stack. If no page movement was required (because the table being dropped was already the last one in the database), then a zero is pushed onto the stack. If AUTOVACUUM is disabled, then a zero is pushed onto the stack.

See also: Clear

Distinct
Use the top of the stack as a record created using MakeRecord. P1 is a cursor on a table that declared as an index. If that table contains an entry that matches the top of the stack, then fall through. If the top of the stack matches no entry in P1, then jump to P2.

The cursor is left pointing at the matching entry if it exists. The record on the top of the stack is not popped.

This instruction is similar to NotFound except that this operation does not pop the key from the stack.

The instruction is used to implement the DISTINCT operator on SELECT statements. The P1 table is not a true index but rather a record of all results that have been produced so far.

See also: Found, NotFound, IsUnique, NotExists

Divide
Pop the top two elements from the stack, divide the first element (what was on top of the stack) from the second element (the next on stack), and push the result back onto the stack. If either element is a string, then it is converted to a double using the atof() function before the division. Division by zero returns NULL. If either operand is NULL, the result is NULL.
DropIndex
Remove the internal (in-memory) data structures that describe the index named P3 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 P3 in database P1. This opcode 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 P3 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.
Dup
Make a copy of the P1th element of the stack and push it to the top of the stack. The top of the stack is element 0, so the instruction Dup 0 0 0 will make a copy of the top of the stack.

If the content of the P1th element is a dynamically allocated string, then a new copy of that string is made if P2 is 0. If P2 is note 0, then just a pointer to the string is copied.

Also see the Pull instruction.

Eq
Pop the top two elements from the stack. If they are equal, then jump to instruction P2. Otherwise, continue to the next instruction.

If the 0x100 bit of P1 is true and either operand is NULL, then take the jump. If the 0x100 bit of P1 is clear, then fall through if either operand is NULL.

If the 0x200 bit of P1 is set and either operand is NULL, then both operands are converted to integers prior to comparison. NULL operands are converted to zero and non-NULL operands are converted to 1. Thus, for example, with 0x200 set, NULL==NULL is true, whereas it would normally be NULL. Similarly, NULL==123 is false when 0x200 is set, but is NULL when the 0x200 bit of P1 is clear.

The least significant byte of P1 (mask 0xff) must be an affinity character - 'n', 't', 'i' or 'o' - or 0x00. An attempt is made to coerce both values according to the affinity before the comparison is made. If the byte is 0x00, then numeric affinity is used.

Once any conversions have taken place, and neither value is NULL, the values are compared. If both values are BLOBs, or both are text, then memcmp() is used to determine the results of the comparison. If both values are numeric, then a numeric comparison is used. If the two values are of different types, then they are unequal.

If P2 is zero, do not jump. Instead, push an integer 1 onto the stack if the jump would have been taken, or a 0 if not. Push a NULL if either operand was NULL.

If P3 is not NULL, it is a pointer to a collating sequence (a CollSeq structure) that defines how to compare text.

Expire
Cause precompiled statements to expire. An expired statement fails with an error code of QDB_SCHEMA if it is ever executed (via sqlite3_step()).

If P1 is 0, then all SQL statements expire. If P1 is non-zero, then only the currently executing statement is affected.

FifoRead
Attempt to read a single integer from the FIFO and push it onto the stack. If the FIFO is empty push nothing but instead jump to P2.
FifoWrite
Write the integer on the top of the stack into the FIFO.
ForceInt
Convert the top of the stack into an integer. If the current top of the stack is not numeric (meaning that is a NULL or a string that does not look like an integer or floating-point number), then pop the stack and jump to P2. If the top of the stack is numeric, then convert it into the least integer that is greater than or equal to its current value if P1 is 0, or to the least integer that is strictly greater than its current value if P1 is 1.
Found
The top of the stack holds a BLOB constructed by MakeRecord. P1 is an index. If an entry that matches the top of the stack exists in P1, then jump to P2. If the top of the stack does not match any entry in P1 then fall through. The P1 cursor is left pointing at the matching entry if it exists. The BLOB is popped off the top of the stack.

This instruction is used to implement the IN operator where the left-hand side is a SELECT statement. P1 is not a true index but is instead a temporary index that holds the results of the SELECT statement. This instruction just checks to see if the left-hand side of the IN operator (stored on the top of the stack) exists in the result of the SELECT statement.

See also: Distinct, NotFound, IsUnique, NotExists

Function
Invoke a user function (P3 is a pointer to a Function structure that defines the function) with P2 arguments taken from the stack. Pop all arguments from the stack and push back the result.

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 metadata 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 opcode works just like the Eq opcode except that the jump is taken if the second element down on the stack is greater than or equal to the top of the stack. See the Eq opcode for additional information.
Gosub
Push the current address plus 1 onto the return address stack, 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 Eq opcode except that the jump is taken if the second element down on the stack is greater than the top of the stack. See the Eq 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 QDB_OK (0). For errors, it can be some other value. If P1 is non-zero, then P2 will determine whether or not to rollback the current transaction. Do not roll back if P2 is OE_Fail. Do the rollback if P2 is OE_Rollback. If P2 is OE_Abort, then back out all changes that have occurred during this execution of the VDBE, but do not rollback the transaction.

If P3 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.

HexBlob
P3 is an UTF-8 SQL hex encoding of a Binary Large OBject (BLOB). The BLOB is pushed onto the VDBE stack.

The first time this instruction executes, in transforms itself into a Blob opcode with a binary BLOB as P3.

IdxDelete
The top of the stack is an index key built using the MakeIdxKey opcode. This opcode removes that entry from the index.
IdxGE
The top of the stack is an index entry that omits the row ID. Compare the top of stack against the index that P1 is currently pointing to. Ignore the row ID on the P1 index.

If the P1 index entry is greater than or equal to the top of the stack then jump to P2. Otherwise fall through to the next instruction. In either case, the stack is popped once.

If P3 is the "+" string (or any other non-NULL string), then the index taken from the top of the stack is temporarily increased by an epsilon prior to the comparison. This makes the opcode work like IdxGT except that if the key from the stack is a prefix of the key in the cursor, the result is false whereas it would be true with IdxGT.

IdxGT
The top of the stack is an index entry that omits the ROWID. Compare the top of stack against the index that P1 is currently pointing to. Ignore the ROWID on the P1 index.

The top of the stack might have fewer columns than P1.

If the P1 index entry is greater than the top of the stack then jump to P2. Otherwise fall through to the next instruction. In either case, the stack is popped once.

IdxInsert
The top of the stack holds an SQL index key made using the MakeIdxKey instruction. This opcode writes that key into the index P1. Data for the entry is nil.

This instruction works only for indexes. The equivalent instruction for tables is OP_Insert.

IdxIsNull
The top of the stack contains an index entry such as might be generated by the MakeIdxKey opcode. This routine looks at the first P1 fields of that key. If any of the first P1 fields are NULL, then a jump is made to address P2. Otherwise it falls straight through.

The index entry is always popped from the stack.

IdxLT
The top of the stack is an index entry that omits the ROWID. Compare the top of stack against the index that P1 is currently pointing to. Ignore the ROWID on the P1 index.

If the P1 index entry is less than the top of the stack then jump to P2. Otherwise fall through to the next instruction. In either case, the stack is popped once.

If P3 is the "+" string (or any other non-NULL string), then the index taken from the top of the stack is temporarily increased by an epsilon prior to the comparison. This makes the opcode work like IdxLE.

IdxRowid
Push onto the stack 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 row ID of the table entry to which this index entry points.

See also: Rowid.

If
Pop a single boolean from the stack. If the boolean popped is true, then jump to p2. Otherwise continue to the next instruction. An integer is false if zero, and true otherwise. A string is false if it has zero length, and true otherwise.

If the value popped of the stack is NULL, then take the jump if P1 is true, and fall through if P1 is false.

IfMemPos
If the value of memory cell P1 is 1 or greater, jump to P2. This opcode assumes that memory cell P1 holds an integer value.
IfNot
Pop a single boolean from the stack. If the boolean popped is false, then jump to P2. Otherwise continue to the next instruction. An integer is false if zero, and true otherwise. A string is false if it has zero length, and true otherwise.

If the value popped of the stack is NULL, then take the jump if P1 is true and fall through if P1 is false.

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 on the top of the stack. The key is the next value down on the stack. The key must be an integer. The stack is popped twice by this instruction.

If the OPFLAG_NCHANGE flag of P2 is set, then the row change count is incremented (otherwise not). If the OPFLAG_LASTROWID flag of P2 is set, then row ID is stored for subsequent return by the sqlite3_last_insert_row ID() function (otherwise it's unmodified).

This instruction works only on tables. The equivalent instruction for indexes is OP_IdxInsert.

Int64
P3 is a string representation of an integer. Convert that integer to a 64-bit value and push it onto the stack.
Integer
Push the 32-bit integer value P1 onto the stack.
IntegrityCk
Do an analysis of the currently open database. Push onto the stack the text of an error message describing any problems. If there are no errors, push a ok onto the stack.

The root page numbers of all tables in the database are integer values on the stack. This opcode pulls as many integers as it can off of the stack and uses those numbers as the root pages.

If P2 is not zero, the check is done on the auxiliary database file, not the main database file.

This opcode is used for testing purposes only.

IsNull
If any of the top abs(P1) values on the stack are NULL, then jump to P2. Pop the stack P1 times if P1 is greater than 0. If P1 is less than 0, leave the stack unchanged.
IsUnique
The top of the stack is an integer record number. Call this record number R. The next on the stack is an index key created using MakeIdxKey. Call it K. This instruction pops R from the stack but it leaves K unchanged.

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 row ID of the entry that the index refers to.

This instruction asks if there is an entry in P1 where the field matches K but the row ID 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 pushed onto the stack and control falls through to the next instruction.

See also: Distinct, NotFound, NotExists, Found

Last
The next use of the Rowid, 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 is greater than 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 Eq opcode, except that the jump is taken if the second element down on the stack is less than or equal to the top of the stack. See the Eq 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
This works just like the Eq opcode, except that the jump is taken if the second element down on the stack is less than the top of the stack. See the Eq opcode for additional information.
MakeRecord
Convert the top abs(P1) entries of the stack 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 irrelevant as long as the OP_Column opcode can decode the record later and as long as the sqlite3VdbeRecordCompare() function correctly compares two encoded records. Refer to source code comments for the details of the record format.

The original stack entries are popped from the stack if P1 is greater than 0 but remain on the stack if P1 is less than 0.

If P2 is not zero and one or more of the entries are NULL, then jump to the address given by P2. This feature can be used to skip a uniqueness test on indexes.

P3 may be a string that is P1 characters long. The nth character of the string indicates the column affinity that should be used for the nth field of the index key (i.e. the first character of P3 corresponds to the lowest element on the stack).

The mapping from character to affinity is as follows:

If P3 is NULL, then all index fields have the affinity NONE.

MakeRecordI
This opcode works just OP_MakeRecord except that it reads an extra integer from the stack (thus reading a total of abs(P1+1) entries) and appends that extra integer to the end of the record as a variant. This results in an index key.
MemIncr
Increment the integer valued memory cell P1 by 1. If P2 is not zero and the result after the increment is exactly 1, then jump to P2.

This instruction throws an error if the memory cell is not initially an integer.

MemInt
Store the integer value P1 in memory cell P2.
MemLoad
Push a copy of the value in memory location P1 onto the stack.

If the value is a string, then the value pushed is a pointer to the string that is stored in the memory location. If the memory location is subsequently changed (using OP_MemStore), then the value pushed onto the stack will change too.

MemMax
Set the value of memory cell P1 to the maximum of its current value and the value on the top of the stack. The stack is unchanged.

This instruction throws an error if the memory cell is not initially an integer.

MemMove
Move the content of memory cell P2 to memory cell P1. Any prior content of P1 is erased. Memory cell P2 is left containing a NULL.
MemNull
Store a NULL in memory cell P1.
MemStore
Write the top of the stack into memory location P1. P1 should be a small integer, since space is allocated for all memory locations between 0 and P1 inclusive.

After the data is stored in the memory location, the stack is popped once if P2 is 1. If P2 is zero, then the original data remains on the stack.

MoveGe
Pop the top of the stack and use its value as a key. Reposition cursor P1 so that it points to the smallest entry that is greater than or equal to the key that was popped from the stack. If there are no records greater than or equal to the key, and P2 is not zero, then jump to P2.

See also: Found, NotFound, Distinct, MoveLt, MoveGt, MoveLe.

MoveGt
Pop the top of the stack and use its value as a key. Reposition cursor P1 so that it points to the smallest entry that is greater than the key from the stack. 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
Pop the top of the stack and use its value as a key. Reposition cursor P1 so that it points to the largest entry that is less than or equal to the key that was popped from the stack. 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
Pop the top of the stack and use its value as a key. Reposition cursor P1 so that it points to the largest entry that is less than the key from the stack. 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
Pop the top two elements from the stack, multiply them together, and push the result back onto the stack. If either element is a string, then it is converted to a double using the atof() function before the multiplication. If either operand is NULL, the result is NULL.
MustBeInt
Force the top of the stack to be an integer. If the top of the stack is not an integer and cannot be converted into an integer with out data loss, then jump immediately to P2, or if P2 is 0, raise a QDB_MISMATCH exception.

If the top of the stack is not an integer and P2 is not zero and P1 is 1, then the stack is popped. In all other cases, the depth of the stack is unchanged.

Ne
This works just like the Eq opcode, except that the jump is taken if the operands from the stack are not equal. See the Eq opcode for additional information.
Negative
Treat the top of the stack as a numeric quantity. Replace it with its additive inverse. If the top of the stack is NULL, its value is unchanged.
NewRowid
Get a new integer record number (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 pushed onto the stack.

If P2 is greater than 0, then P2 is a memory cell 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 QDB_FULL error is generated. The P2 memory cell is updated with the generated record number. This P2 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/data pairs, then fall through to the following instruction; if the cursor advance was successful, jump immediately to P2.

See also: Prev

Noop
Do nothing. This instruction is often useful as a jump destination.
Not
Interpret the top of the stack as a boolean value, and replace it with its complement. If the top of the stack is NULL, its value is unchanged.
NotExists
Use the top of the stack 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 through. The cursor is left pointing to the record if it exists. The integer key is popped from the stack.

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: Distinct, Found, NotFound, IsUnique.

NotFound
The top of the stack holds a BLOB constructed by MakeRecord. P1 is an index. If no entry exists in P1 that matches the BLOB, then jump to P1. If an entry does existing, fall through. The cursor is left pointing to the entry that matches. The BLOB is popped from the stack.

The difference between this operation and Distinct is that Distinct does not pop the key from the stack.

See also: Distinct, Found, NotExists, IsUnique.

NotNull
Jump to P2 if the top P1 values on the stack are all not NULL. Pop the stack if P1 times if P1 is greater than zero. If P1 is less than zero, then leave the stack unchanged.
Null
Push a NULL onto the stack.
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 push a NULL onto the stack.
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.

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 an integer from the top of the stack. A 0 means the main database and a 1 means the database used for temporary tables. 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 P2 is 0, then take the root page number from the next of the stack.

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 a EBUSY error code.

The P3 value is a pointer to a KeyInfo structure that defines the content and collating sequence of indexes. P3 is NULL for cursors that are not pointing to indexes.

See also OpenWrite.

OpenVirtual
Open a new cursor P1 to a transient or virtual table. The cursor is always opened for reading and writing, 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 P3 is 0, and to a BTree index if P3 is not 0. If P3 is not NULL, it points to a KeyInfo structure that defines the format of keys in the index.

OpenWrite
Open a read/write cursor named P1 on the table or index whose root page is P2. If P2 is 0, then take the root page number from the stack.

The P3 value is a pointer to a KeyInfo structure that defines the content and collating sequence of indexes. P3 is NULL for cursors that are not pointing to indexes.

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
Pop two values off the stack. Take the logical OR of the two values and push the resulting boolean value back onto the stack.
ParseSchema
Read and parse all entries from the QDB_MASTER table of database P1 that match the WHERE clause P3.

This opcode invokes the parser to create a new virtual machine, then runs the new virtual machine. It is thus a reentrant opcode.

Pop
Pop P1 elements off the top of the stack and discarded.
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 pair, then fall through to the following instruction. If the cursor backup was successful, then jump immediately to P2.
Pull
Remove the P1th element from its current location on the stack and push it back on top of the stack. The top of the stack is element 0, so Pull 0 0 0 is a no-op. Pull 1 0 0 swaps the top two elements of the stack.

See also the Dup instruction.

Push
Overwrite the value of the P1th element down on the stack (P1 is 0 is the top of the stack) with the value of the top of the stack. Then pop the top of the stack.
ReadCookie
Read cookie number P2 from database P1 and push it onto the stack. A value of P2==0 is the schema version, while 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.

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
The string value P3 is converted to a real and pushed on to the stack.
Remainder
Pop the top two elements from the stack, divide the first (the element that was on top of the stack) from the second (the element that was next on the stack) and push the remainder after division onto the stack. If either element is a string, then it is converted to a double using the atof() function before the division. Division by zero returns NULL. If either operand is NULL, the result is NULL.
ResetCount
This opcode resets the VM's 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.
Return
Jump immediately to the next instruction after the last unreturned OP_Gosub. If an OP_Return has occurred for all OP_Gosub, then processing aborts with a fatal error.
Rewind
The next use of the Rowid, 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
Push onto the stack the complete row data for cursor P1. There is no interpretation of the data. It is just copied onto the stack exactly as it is found in the database file.

If the cursor is not pointing to a valid row, a NULL is pushed onto the stack.

Rowid
Push onto the stack an integer which is the key of the table entry that P1 is currently pointing to.
RowKey
Push onto the stack the complete row key for cursor P1. There is no interpretation of the key. It is just copied onto the stack exactly as it is found in the database file.

If the cursor is not pointing to a valid row, a NULL is pushed onto the stack.

Sequence
Push onto the stack an integer which is the next available sequence number for cursor P1. The sequence number on the cursor is incremented after the push.
SetCookie
Write the top of the stack into cookie number P2 of database P1. A value of P2==0 indicates the schema version, while a value of P2==1 indicates 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
Before the OP_Column opcode can be executed on a cursor, this opcode must be called to set the number of fields in the table.

This opcode sets the number of columns for cursor P1 to P2.

If OP_KeyAsData is to be applied to cursor P1, it must be executed before this op-code.

ShiftLeft
Pop the top two elements from the stack, convert both elements to integers, and push back onto the stack the second element shifted left by N bits, where N is the top element on the stack. If either operand is NULL, the result is NULL.
ShiftRight
Pop the top two elements from the stack, convert both elements to integers, andush back onto the stack the second element shifted right by N bits, where N is the top element on the stack. If either operand 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 BEGIN..COMMIT transaction. This opcode 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.

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 P3 is pushed onto the stack. If P3 is 0, then a NULL is pushed onto the stack. P3 is assumed to be a null-terminated string encoded with the database native encoding.
String8
P3 points to a null-terminated UTF-8 string. This opcode is transformed into an OP_String before it is executed for the first time.
Subtract
Pop the top two elements from the stack, subtract the first (the element that was on top of the stack) from the second (the element that was next on the stack) and push the result back onto the stack. If either element is a string, then it is converted to a double using the atof() function before the subtraction. If either operand is NULL, the result is NULL.
ToBlob
Force the value on the top of the stack 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 on the top of the stack to 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 on the top of the stack 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.

ToText
Force the value on the top of the stack to be text. If the value is numeric, convert it to an 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.

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.

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
Push the value of variable P1 onto the stack. A variable is an unknown in the original SQL string as handed to sqlite3_compile(). Any occurrence 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.
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, 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 the cookie has changed and 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.