This opcode is a prefix opcode to SeekGE. In other words, this opcode must be immediately followed by SeekGE. This constraint is checked by assert() statements.

This opcode uses the P1 through P4 operands of the subsequent SeekGE. In the text that follows, the operands of the subsequent SeekGE opcode are denoted as SeekOP.P1 through SeekOP.P4. Only the P1, P2 and P5 operands of this opcode are also used, and are called This.P1, This.P2 and This.P5.

This opcode helps to optimize IN operators on a multi-column index where the IN operator is on the later terms of the index by avoiding unnecessary seeks on the btree, substituting steps to the next row of the b-tree instead. A correct answer is obtained if this opcode is omitted or is a no-op.

The SeekGE.P3 and SeekGE.P4 operands identify an unpacked key which is the desired entry that we want the cursor SeekGE.P1 to be pointing to. Call this SeekGE.P3/P4 row the "target".

If the SeekGE.P1 cursor is not currently pointing to a valid row, then this opcode is a no-op and control passes through into the SeekGE.

If the SeekGE.P1 cursor is pointing to a valid row, then that row might be the target row, or it might be near and slightly before the target row, or it might be after the target row. If the cursor is currently before the target row, then this opcode attempts to position the cursor on or after the target row by invoking sqlite3BtreeStep() on the cursor between 1 and This.P1 times.

The This.P5 parameter is a flag that indicates what to do if the cursor ends up pointing at a valid row that is past the target row. If This.P5 is false (0) then a jump is made to SeekGE.P2. If This.P5 is true (non-zero) then a jump is made to This.P2. The P5==0 case occurs when there are no inequality constraints to the right of the IN constraint. The jump to SeekGE.P2 ends the loop. The P5!=0 case occurs when there are inequality constraints to the right of the IN operator. In that case, the This.P2 will point either directly to or to setup code prior to the IdxGT or IdxGE opcode that checks for loop terminate.

Possible outcomes from this opcode:

If the cursor is initially not pointed to any valid row, then fall through into the subsequent SeekGE opcode.
If the cursor is left pointing to a row that is before the target row, even after making as many as This.P1 calls to sqlite3BtreeNext(), then also fall through into SeekGE.
If the cursor is left pointing at the target row, either because it was at the target row to begin with or because one or more sqlite3BtreeNext() calls moved the cursor to the target row, then jump to This.P2..,
If the cursor started out before the target row and a call to to sqlite3BtreeNext() moved the cursor off the end of the index (indicating that the target row definitely does not exist in the btree) then jump to SeekGE.P2, ending the loop.
If the cursor ends up on a valid row that is past the target row (indicating that the target row does not exist in the btree) then jump to SeekOP.P2 if This.P5==0 or to This.P2 if This.P5>0.

Sqlite Opcode Explanation

Sqlite Query Plan