Unexpected result of `JOIN`

(1) By ChiZhang on 2023-05-01 10:16:06 [source]

Consider the following program:

CREATE TABLE vt0(c2);
CREATE TABLE t1 (c0 TEXT );

INSERT INTO t1(c0) VALUES (1);
INSERT INTO vt0(c2) VALUES (-1);

CREATE VIEW v0(c0) AS SELECT 0 FROM t1;


SELECT vt0.c2 AS c1 FROM t1 CROSS JOIN v0 ON (0) FULL OUTER JOIN vt0 ON 1; -- empty

SELECT vt0.c2 AS c1 FROM t1 CROSS JOIN v0 ON ( EXISTS (SELECT v0.c0 FROM v0 WHERE false)) FULL OUTER JOIN vt0 ON 1; -- -1

These two queries should be equivalent, because in the EXISTS clause of the second query, the condition is false, so this EXISTS clause equals to 0. But these two queries have different results. The first one returns an empty result, while the second one returns -1.

I can reproduce this on the latest trunk version 2e85b0e3

(2) By Mark Brand (mabrand) on 2023-05-01 11:53:55 in reply to 1 [link] [source]

This brings up a basic question about the expected behavior of 'RIGHT JOIN" and "FULL JOIN" in situations like:

SELECT *
FROM (SELECT 'a' a) a
JOIN (SELECT 'b' b) b ON 0=1
RIGHT JOIN (SELECT 'c' c) c ON 0=0

SQLite returns no row.
MSSQL and Postgresql return one row: <NULL, NULL, 'c'>, which is what I would expect.

(3) By ChiZhang on 2023-05-02 01:31:48 in reply to 2 [link] [source]

Hi Mark Brand,

Thank you for your reply!

I just tried it and found the example you gave me both return one row <NULL, NULL, 'c'> in sqlite3 and Postgresql.

And I tried the following program and it also returned row <NULL, NULL, 'c'> in sqlite3 and Postgresql:

SELECT *
FROM (SELECT 'a' a) a
JOIN (SELECT 'b' b) b ON CAST(0 AS boolean)
RIGHT JOIN (SELECT 'c' c) c ON CAST(1 AS boolean);

But if there is no CAST, Postgresql do not support this grammar, and Sqlite3 returns an empty result.

SELECT *
FROM (SELECT 'a' a) a
JOIN (SELECT 'b' b) b ON 0 
RIGHT JOIN (SELECT 'c' c) c ON 1;

But as the document of Boolean Datatype, SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). So why there are difference, the CAST should have no effect for this example.

(4.1) By Harald Hanche-Olsen (hanche) on 2023-05-02 06:32:50 edited from 4.0 in reply to 3 [link] [source]

Indeed, asking for CAST(x as boolean) to be implemented and always returning either 0 or 1 (or NULL if x is null) seems like a reasonable feature request, if only for compatibility with other implementations.

(5.1) By Mark Brand (mabrand) on 2023-05-02 08:27:05 edited from 5.0 in reply to 3 [link] [source]

Hi ChiZhang,

Are you sure about that? For me, SQLite 3.41.2, unlike MSSQL and Postgresql, returns no rows for the query in my previous post, repeated here:

SELECT *
FROM (SELECT 'a' a) a
JOIN (SELECT 'b' b) b ON 0=1
RIGHT JOIN (SELECT 'c' c) c ON 0=0;

For compatibility with MSSQL and Postgresql, "0=1" is used instead of "false" or "0" as the condition for the first JOIN. Similarly, "0=0" is used as the obligatory condition on the RIGHT JOIN, but this one doesn't matter. It could also be "0=1".

I would describe this situation as RIGHT JOIN (and FULL JOIN) misbehaving when the left side is another JOIN yielding an empty set of rows.

Just to clarify, it seems to me that your first case is essentially the same:
    SELECT vt0.c2 AS c1 FROM t1 CROSS JOIN v0 ON (0) FULL OUTER JOIN vt0 ON 1; -- empty
This query should return 1 row, but does not.

Why the behavior changes when you replace the condition "(0)" by EXISTS.. is a second question.

(6) By ChiZhang on 2023-05-02 08:32:32 in reply to 5.0 [link] [source]

Hi Mark Brand,

I build sqlite3 from source code and I can confirm that the latest trunk version ad6aae76 produce NULL|NULL|c on this test case. I reproduce this on Ubuntu 22.04.

I also tried the latest release version for Linux, indeed return an empty result as you said. But as I am trying a new test method, so I focus on the latest trunk version.

I also tried the latest release version for Mac OS X, but get an error message Error: near line 1: RIGHT and FULL OUTER JOINs are not currently supported.

(7) By Mark Brand (mabrand) on 2023-05-02 09:08:55 in reply to 6 [link] [source]

With the latest trunk your first case  now returns 1 row (correctly):

    SELECT vt0.c2 AS c1 FROM t1 CROSS JOIN v0 ON (0) FULL OUTER JOIN vt0 ON 1; -- empty

See https://sqlite.org/src/info/d095da0e7a24e3bc

(8) By ChiZhang on 2023-05-02 10:02:58 in reply to 7 [link] [source]

Hi Mark Brand,

Thanks for your work!

(9.1) By Mark Brand (mabrand) on 2023-05-02 10:34:28 edited from 9.0 in reply to 8 [link] [source]

The thanks go to DRH!