SQLite Forum

DISTINCT does not work in CTE
Login

DISTINCT does not work in CTE

(1) By Dingyuan Wang (gumblex) on 2020-09-29 08:04:40 [link] [source]

The following SQL:

CREATE TABLE t1 (a);
INSERT INTO t1 VALUES (1), (1), (1), (2);

WITH RECURSIVE r1 AS (
SELECT DISTINCT a, a b FROM t1
UNION ALL
SELECT a, b+1 b FROM r1 WHERE b < 3
) SELECT * FROM r1;

Other databases return:

a|b
1|1
2|2
1|2
2|3
1|3

SQLite returns:

a|b
1|1
1|1
1|1
2|2
1|2
1|2
1|2
2|3
1|3
1|3
1|3

The EXPLAIN output of the two SQLs are the same.

(2.1) By Dingyuan Wang (gumblex) on 2020-09-29 09:24:57 edited from 2.0 in reply to 1 [link] [source]

The EXPLAIN output is the same as:

WITH r1 AS (
SELECT a, a b FROM t1
UNION ALL
SELECT a, b+1 b FROM r1 WHERE b < 3
) SELECT * FROM r1;

(without the DISTINCT)

(3) By TripeHound on 2020-09-29 09:17:20 in reply to 2.0 [link] [source]

I don't the what either the SQL spec(s), SQLite, or other database engines say on the matter, but it strikes me that aliasing an expression involving column b as b – and then adding a WHERE clause involving b – is likely to lead to the equivalent of C's "undefined behaviour"... is the constraint meant to act on the original value of the column, b, or the aliased version, b+1? Even if it is defined how this should work, it feels like something "you should not do", at least for clarity's sake.

From a quick experiment, it looks like SQLite is using the original column b in the where clause, whereas your other SQL engine is using the alias. If you change the penultimate line to:

SELECT a, b+1 c FROM r1 WHERE c < 3

then SQLite gives the same as your other database.

(4) By Dingyuan Wang (gumblex) on 2020-09-29 09:26:53 in reply to 3 [link] [source]

This problem is not the WHERE clause, but a heavily reduced version about the DISTINCT. The b in WHERE is r1.b.

(5) By Richard Hipp (drh) on 2020-09-29 11:57:55 in reply to 1 [source]

This problem was previously reported by Dario from Italy and has been fixed on trunk.

Curious that this problem would exist in the wild for 6.5 years without anybody encountering it, then all of a sudden we have two reports within two weeks of each other.