On 2015-10-12 04:05 PM, Richard Hipp wrote: > On 10/12/15, R.Smith <rsmith at rsweb.co.za> wrote: >> Two questions - >> 1 - would referencing a table alias formed from a table in the same FROM >> clause be valid in future? Or is this just for JSON purposes? >> >> 2 - would this be allowed to reference ANY aliased table, or only one >> that can be simplified/factored to an actual table - What rules would >> govern such things? >> > SQLite has always allowed both. Right? Do you have a counter-example? >
I think I do... in fact the example I sent in said post doesn't work currently on 3.8.11 Example: SELECT sqlite_version(); -- sqlite_ -- version() -- ---------- -- 3.8.11.1 CREATE TABLE Numbers(IsPrime, Val, Descr); INSERT INTO Numbers VALUES (1, 1, 'One'), (1, 2, 'Two'), (1, 3, 'Three'), (0, 4, 'Four'), (1, 5, 'Five'), (0, 6, 'Six'), (1, 7, 'Seven'), (0, 8, 'Eight'), (0, 9, 'Nine'), (0,10, 'Ten'); -- This bit works well: SELECT A.* FROM (SELECT Val, Descr FROM Numbers WHERE IsPrime) AS A; -- Val | Descr -- ------------ | ------- -- 1 | One -- 2 | Two -- 3 | Three -- 5 | Five -- 7 | Seven -- When I try to use an Alias in a FROM clause of something defined in the same FROM clause - it never works: SELECT A.*, B.* FROM (SELECT Val, Descr FROM Numbers WHERE IsPrime) AS A, (SELECT Descr FROM A) AS B; -- 2015-10-12 17:18:50.039 | ERROR (1) : no such table: A -- 2015-10-12 17:18:50.040 | [Info] Script failed - Rolling back... -- Even when the Alias CAN be reduced/factored to a Table, it still doesn't work in 3.8.11: SELECT B.* FROM (SELECT * FROM Numbers) AS A, (SELECT Descr FROM A) AS B; -- 2015-10-12 17:26:09.305 | ERROR (1) : no such table: A -- 2015-10-12 17:26:09.305 | [Info] Script failed - Rolling back... -- However, this would work according to the JSON doc for the table-valued function (if Descr held JSON values): SELECT B.* FROM (SELECT * FROM Numbers) AS A, JSON_EACH(A.Descr) AS B; Running this same script through sqlite 3.8.8 CLI, this output happens: D:\Documents>sqlite3 testdb.db SQLite version 3.8.8 2015-01-16 12:08:06 Enter ".help" for usage hints. sqlite> .echo on sqlite> .read alias.sql .read alias.sql SELECT sqlite_version(); 3.8.8 CREATE TABLE Numbers(IsPrime, Val, Descr); INSERT INTO Numbers VALUES (1, 1, 'One'), (1, 2, 'Two'), (1, 3, 'Three'), (0, 4, 'Four'), (1, 5, 'Five'), (0, 6, 'Six'), (1, 7, 'Seven'), (0, 8, 'Eight'), (0, 9, 'Nine'), (0,10, 'Ten'); SELECT A.* FROM (SELECT Val, Descr FROM Numbers WHERE IsPrime) AS A; 1|One 2|Two 3|Three 5|Five 7|Seven Error: near line 20: no such table: A sqlite> To re-state the two questions more clearly: 1 - would referencing a table alias in a FROM clause, where it was itself formed in the same FROM clause, be valid in future? 2 - would this be allowed to reference ANY aliased table, or only one that can be simplified/factored to an actual table - What rules would govern such things? Or is this just a Table-valued thing? I hope this is more clear, thanks. Ryan
