Hi List, By coincidence we discovered that SQLite allows to use the same tables aliases multiple times in one query at the same level. As long as all referred columns are unambiguously named SQLite does not complain about duplicate table aliases. Although I cannot find any documentation that duplicate table aliases are not allowed, it is not what the average user would expect.
Is this behaviour by design or should SQLite report an error? On the internet I found that (at least some) other database engines do not allow duplicate table aliases. Examples: CREATE TABLE aaa(a INTEGER, b INTEGER); CREATE TABLE bbb(a INTEGER, c INTEGER); INSERT INTO aaa VALUES(1,2); INSERT INTO bbb VALUES(2,3); -- Example 1: -- No error is reported since duplicate.c is not ambiguous -- Expected an error reporting duplicate table aliases SELECT duplicate.c FROM aaa duplicate, bbb duplicate; -- output: -- 3 -- Example 2: -- Error is reported since duplicate.a is ambiguous -- Expected an error reporting duplicate table aliases SELECT duplicate.a FROM aaa duplicate, bbb duplicate; -- output: -- Error: ambiguous column name: duplicate.a -- Example 3: -- Error is reported since the implicit duplicate.a is ambiguous -- Expected an error reporting duplicate table aliases SELECT * FROM aaa duplicate, bbb duplicate; -- output: -- Error: ambiguous column name: main.duplicate.a -- Example 4: -- No error, since local alias (for table bbb) shadows global alias (for table aaa), which is allowed. -- Behaviour as expected SELECT duplicate.a FROM aaa duplicate WHERE duplicate.b IN (SELECT duplicate.a FROM bbb duplicate); -- output: -- 1 Example 4 shows the expected behaviour (duplicate in the subquery refers to table bbb, in the main query to table aaa); For Examples 1-3 I would expect an error for duplicate table aliases. Regards, Rob Golsteijn _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
