/ View Ticket
Login
Ticket Hash: 7fa8049685b50b5aeb0c211a3d97bdc34db3d4f6
Title: Incorrect result on a LEFT JOIN when using an index
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2018-04-24 14:42:54
Version Found In: 3.23.1
User Comments:
drh added on 2018-04-24 12:48:46: (text/x-fossil-wiki)
The query below gets a different result with and without the index.  The
result without the index is correct.

<blockquote><verbatim>
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(1);
CREATE TABLE t2(y, z);
SELECT ifnull(z, '!!!') FROM t1 LEFT JOIN t2 ON (x=y);
CREATE INDEX t2i ON t2(y, ifnull(z, '!!!'));
SELECT ifnull(z, '!!!') FROM t1 LEFT JOIN t2 ON (x=y);
</verbatim></blockquote>

This problem appears to have been introduced by check-in
[a47efb7c8520a01110ce3] on 2017-04-11, and first released with
version 3.19.0.  The problem was discovered during internal testing
and has not been observed in the wild.