SQLite Forum

Rows are Unexpectedly Filtered Out by DISTINCT in RIGHT JOIN
Login

Rows are Unexpectedly Filtered Out by DISTINCT in RIGHT JOIN

(1) By Jinsheng Ba (bajinsheng) on 2022-05-14 07:18:43 [source]

In a specific scenario of RIGHT JOIN, the DISTINCT unexpectedly filters out all rows.

CREATE TABLE t1 (c0 INTEGER UNIQUE);
CREATE TABLE t2 (c0);
CREATE TABLE t3 (c0);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (3);

SELECT t1.c0, t3.c0 FROM t2 NATURAL JOIN t1 RIGHT OUTER JOIN t3 ON t1.c0;
-- |3
SELECT DISTINCT t1.c0, t3.c0 FROM t2 NATURAL JOIN t1 RIGHT OUTER JOIN t3 ON t1.c0;
-- Empty set

The first SELECT returns one row, while no row is returned if the DISTINCT is added, as shown in the second SELECT.

As a reference for bug positioning, the issue will not happen if we either 1) remove the UNIQUE for c0.t1 or 2) change RIGHT JOIN to LEFT JOIN.

Environment:

Version: 3.39.0

Commit ID: 778e57a5

OS: Ubuntu 20.04

Configuration Options: ./configure --enable-all

Compiler: gcc-9

Client: CLI

(2) By Richard Hipp (drh) on 2022-05-14 20:47:09 in reply to 1 [link] [source]

Thanks for the bug report. This should now be fixed on trunk.

(3) By Jinsheng Ba (bajinsheng) on 2022-05-15 09:14:11 in reply to 2 [link] [source]

Thanks! It has been fixed.