/ View Ticket
19:16 Fixed ticket [f484b65f]: Incorrect result from row-value comparison in WHERE clause plus 6 other changes (artifact: 79ae33f7 user: drh)
Improved test cases for the [f484b65f3d6230593c34f] bug. (check-in: 1f300514 user: drh tags: trunk)
Fix an incorrect table lookup used to find the appropriate search operator for a WHERE clause on a row-value inequality. The incorrect table lookup was causing an incorrect answer for the less-than operator. Fix for ticket [f484b65f3d6230593c34f11] (check-in: f3112e67 user: drh tags: trunk)
18:05 New ticket [f484b65f] Incorrect result from row-value comparison in WHERE clause. (artifact: e19d6906 user: drh)

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:

The following SQL returns no rows. It ought to return four rows:

INSERT INTO t1(a,b) VALUES(1,11),(2,22),(3,33),(4,44);
SELECT * FROM t1 WHERE (a,b)>(0,0);

Changing the comparison operator from > to >= gives the correct result. The correct result also results from < and <= 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:

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.