SQLite

View Ticket
Login
Ticket Hash: 9cdc5c4662d4063771d45d2893b294df0e868001
Title: Incorrect result from second execution of correlated scalar sub-query that uses a partial sort
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-06-27 02:23:49
6.03 years ago
Created: 2019-06-26 20:00:00
6.03 years ago
Version Found In:
User Comments:
dan added on 2019-06-26 20:00:00:

CREATE TABLE t1(x);
INSERT INTO t1 VALUES('ALFKI');
INSERT INTO t1 VALUES('ANATR');

CREATE TABLE t2(y, z);
CREATE INDEX t2y ON t2 (y);
INSERT INTO t2 VALUES('ANATR', '1997-08-08 00:00:00');
INSERT INTO t2 VALUES('ALFKI', '1997-08-25 00:00:00');

SELECT (
  SELECT y FROM t2 WHERE x = y ORDER BY y, z
)
FROM t1;

The script above should return two rows - one with 'ANATR' and a second with 'ALFKI'. Instead it returns 'ANATR' and then NULL.

Problem introduced by commit [4678cb1044f0b].