Ambiguous Reference Error for Right Join
(1) By Jinsheng Ba (bajinsheng) on 2022-05-23 11:15:41 [source]
An error bubbles up when using right join SELECT, and will not happen for the semantic equivalent left join SELECT.
CREATE TABLE t2 (c0);
CREATE TABLE t0 (c0);
CREATE TABLE t1 (c0);
SELECT * FROM t2 RIGHT OUTER JOIN t1 ON t1.c0 NATURAL JOIN t0;
-- Parse error: ambiguous reference to c0 in USING()
SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.c0 NATURAL JOIN t0;
-- No error
Environment:
Version: 3.39.0
Commit ID: 392e8482
OS: Ubuntu 20.04
Configuration Options: ./configure --enable-all
Compiler: gcc-9
Client: CLI
(2) By Bill Wade (billwade) on 2022-05-23 12:09:04 in reply to 1 [link] [source]
https://sqlite.org/omitted.html
(3) By Ryan Smith (cuz) on 2022-05-23 12:12:28 in reply to 2 [link] [source]
I suppose that document will have to be amended soon.
(4) By Richard Hipp (drh) on 2022-05-23 12:15:48 in reply to 2 [link] [source]
That document is valid for 3.38.0. Jinsheng Ba is testing the (still unreleased) 3.39.0. See https://sqlite.org/draft/omitted.html for the equivalent document for the version that is being tested.
(5) By Richard Hipp (drh) on 2022-05-23 12:36:51 in reply to 1 [link] [source]
The c0 column is ambiguous in the NATURAL JOIN. So both queries should arguably return an error. PostgreSQL-14 returns an error for both queries.
However, historical versions of SQLite have allowed the LEFT JOIN version to work. The historical name resolution algorithm found the first match and stopped looking. This is wrong. But it is what SQLite has done for many, many years. Changing the behavior now risks breaking legacy applications. So we deliberately continue to allow the older behavior.
But the older behavior is only allowed for LEFT JOIN. Because RIGHT JOIN is a new feature, we know that no legacy applications are using RIGHT JOIN. Hence, strict column matching rules can be applied to queries that use RIGHT JOIN without the risk of breaking legacy applications.
The permissive name matching for LEFT JOIN is an unfortunate legacy of SQLite. It dates from a time, nearly two decades ago, when gadgets had severe memory constraints and it was considered better to bypass some obscure and harmless error checks if it could save a few bytes in the size of the library. These days, developers have more memory at hand and prefer stricter enforcement. But we don't want to break legacy applications by suddenly starting to do strict enforcement of niggling name-matching rules. Allowing some ambiguous name matches on a NATURAL LEFT JOIN is harmless, and is not something that is likely to cause confusion of grief for application developers. Better to allow the quirky behavior to continue.
(6) By Domingo (mingodad) on 2022-05-23 12:54:39 in reply to 5 [link] [source]
Could you have the LEFT JOIN also fixed under a macro like "SQLITE_STRICT" or "SQLITE_NOLEGACY" or any other name implying that sqlite will work with less or no bad legacy behavior ?
(7) By Richard Hipp (drh) on 2022-05-23 13:01:39 in reply to 6 [link] [source]
I could, in theory. But I have too many build combinations to test already without adding yet another one.
(8) By Domingo (mingodad) on 2022-05-23 13:06:33 in reply to 7 [link] [source]
What about having a LINT mode for sqlite3 that would emit warnings about those known legacy quirks ? ./sqlite3 --lint my.db < mysql-tocheck.sql