SQLite

View Ticket
Login
Ticket Hash: ccbe5759fbd63b11c158da7f2ac1430c5b1142df
Title: ANALYZE causes DISTINCT to malfunction in CROSS JOIN
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-07-29 15:54:51
Version Found In: 3.29.0
User Comments:
mrigger added on 2019-07-29 09:28:50:

In the test case below, only one row is fetched, rather than two:

CREATE TABLE t0 (c0, c1, c2, PRIMARY KEY (c0, c1));
CREATE TABLE t1 (c2);
INSERT INTO t0(c2) VALUES (0), (1), (3), (4), (5), (6), (7), (8), (9), (10), (11);
INSERT INTO t0(c1) VALUES ('a');
INSERT INTO t1(c2) VALUES (0);
ANALYZE;
SELECT DISTINCT t0.c0, t1._rowid_, t0.c1 FROM t1 CROSS JOIN t0 ON TRUE ORDER BY t0.c0; -- expected: |1|, |1|a, actual: |1|

When removing the ANALYZE, the query works as expected. It also works when using JOIN or FROM t1, t0 rather than a CROSS JOIN.