/ View Ticket
Login
Ticket Hash: f484b65f3d6230593c34f117861ba8f7225740d8
Title: Incorrect result from row-value comparison in WHERE clause
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2018-02-13 19:16:00
Version Found In: 3.22.0
User Comments:
drh added on 2018-02-13 18:05:40: (text/x-fossil-wiki)
The following SQL returns no rows.  It ought to return four rows:

<blockquote><verbatim>
CREATE TABLE t1(a INTEGER PRIMARY KEY,b);
INSERT INTO t1(a,b) VALUES(1,11),(2,22),(3,33),(4,44);
SELECT * FROM t1 WHERE (a,b)>(0,0);
</verbatim></blockquote>

Changing the comparison operator from &gt; to &gt;= gives the correct result.
The correct result also results from &lt; and &lt;= operators assuming the values
on the RHS are adjusted accordingly.

The problem appears to have been in the code ever since row-values were
introduced in version 3.15.0 on 2016-10-14.  The problem was reported
on the sqlite-users mailing list by an anonymous users.

drh added on 2018-02-13 19:16:00: (text/x-fossil-wiki)
Root cause was an incorrect table lookup when translating the TK_GT expression
node operator from the parse tree into the OP_SeekGE opcode for the virtual
machine.  This was probably caused by insufficient caffeine.  The problem
should have been detected by subsequent assert() statement, except those
assert()s were omitted.