SQLite User Forum

SQLite doesn’t complain about ambiguous column names in a `USING` clause
Login
Hierarchical

SQLite doesn't complain about ambiguous column names in a `USING` clause

(1) By anonymous on 2022-05-24 09:36:54 [source]

Hi,

SQLite doesn't complain about ambiguous column names when they're used in a USING clause.

Example:

sqlite> CREATE TABLE tblA(a, blah_id);
sqlite> CREATE TABLE tblB(b, blah_id);
sqlite> CREATE TABLE tblC(c, blah_id);
sqlite> SELECT * FROM tblA JOIN tblB ON a = b JOIN tblC USING (blah_id);

Postgres will reject this query and complain about the ambiguous column name, which seems fair from a safety point of view.

I looked at the EXPLAIN output and came to the conclusion that this is equivalent to ON tblA.blah_id = tblC.blah_id (it uses the first table).

(Version 3.37.2 here)

I was wondering a couple of things:

  1. Is this intentional behaviour? I don't see this edge case documented on the SELECT page.
  2. Is this (use of first table) guaranteed behaviour? (Not that I want to rely on it, but curious...)
  3. (I didn't see one after looking, so I don't think so but best be sure) Is there an 'opt-in' flag to make SQLite complain about this ambiguous query?

Thank you.

(2) By Richard Hipp (drh) on 2022-05-24 11:25:09 in reply to 1 [link] [source]

This is intentional behavior. There are test cases that verify that SQLite does indeed continue to work this way.

PostgreSQL does cause blah_id to be flagged as ambiguous. SQLite just takes the first instance of blah_id that it finds and moves on. This was down in SQLite about two decades ago, back in an era when typical users of SQLite were running on devices with very limited memory and we were under pressure to keep the SQLite library footprint as small as possible. The extra logic to detect the ambiguity was considered unnecessary, and hence omitted.

Since then, many applications have come to depend on this non-standard behavior of SQLite and so we cannot change it now without causing those applications to break when they upgrade to the latest SQLite. We continue to provide this "flexible and forgiving" name resolution behavior in SQLite.

Except, in a query that uses a RIGHT or FULL JOIN, then SQLite does uses the full disambiguation rules of PostgreSQL. We can be stricter with enforcing rules for RIGHT and FULL JOIN since we know that there are no legacy applications using RIGHT and FULL JOIN (since those constructs were not supported prior to version 3.39.0). So if you change one of the joins in your example (either one) to a RIGHT JOIN, you should get the ambiguity error, just as you do in PostgreSQL.

(3) By anonymous on 2022-05-24 16:29:27 in reply to 2 [link] [source]

Thank you for the thorough answer!