.expert Error: ON clause references tables to its right
(1) By jose isaias cabrera (jicman) on 2021-08-12 15:20:34 [link]
Greetings. This SQLite code works as it should on v3.36.0 on the sqlite3 tool (or, at least, it does what I want :-)): ``` WITH LastEntries (ProjID, pl_insert, pe_insert) AS ( SELECT projid, max(InsertDate), (SELECT max(insertdate) FROM project_extras where projid = e.projid) FROM project_list e GROUP BY projid ) SELECT c.CreatedDate AS Aug, a.Project_Type, a.ProjID FROM Project_List AS a LEFT JOIN Project_Extras AS b ON b.ProjID == a.ProjID AND a.Project_Delivered != 'Yes' AND a.PMO_Board_Report != 'No' AND a.Status == 'Acknowledged' AND a.InsertDate = f.pl_insert AND b.FinCarryOver != 'y' AND b.MonthlyRpt = 'y' AND b.BudgetYear = '2021' LEFT JOIN Project_Highlights AS c ON c.ProjID = b.ProjID AND c.CreatedDate LIKE '2021-%' LEFT JOIN LastEntries AS f WHERE a.ProjID = f.projid AND a.InsertDate = f.pl_insert AND b.InsertDate = f.pe_insert ORDER BY a.Manager, a.ProjID ; ``` But, .expert errors out with this: ``` SQLite version 3.36.0 2021-06-18 18:36:39 Enter ".help" for usage hints. sqlite> .expert sqlite> WITH LastEntries (ProjID, pl_insert, pe_insert) ...> AS ...> ( ...> SELECT projid, ...> max(InsertDate), ...> (SELECT max(insertdate) FROM project_extras where projid = e.projid) ...> FROM project_list e ...> GROUP BY projid ...> ) ...> SELECT c.CreatedDate AS Aug, a.Project_Type, a.ProjID ...> FROM Project_List AS a ...> LEFT JOIN Project_Extras AS b ON b.ProjID == a.ProjID ...> AND a.Project_Delivered != 'Yes' ...> AND a.PMO_Board_Report != 'No' ...> AND a.Status == 'Acknowledged' ...> AND a.InsertDate = f.pl_insert ...> AND b.FinCarryOver != 'y' ...> AND b.MonthlyRpt = 'y' ...> AND b.BudgetYear = '2021' ...> LEFT JOIN Project_Highlights AS c ON c.ProjID = b.ProjID ...> AND c.CreatedDate LIKE '2021-%' ...> LEFT JOIN LastEntries AS f ...> WHERE ...> a.ProjID = f.projid ...> AND a.InsertDate = f.pl_insert ...> AND b.InsertDate = f.pe_insert ...> ORDER BY a.Manager, a.ProjID ...> ; Error: ON clause references tables to its right ``` One of them is right. Thoughts? Thanks. josé
(2) By Keith Medcalf (kmedcalf) on 2021-08-12 22:25:14 in reply to 1 [link]
`AND a.InsertDate = f.pl_insert` is attached as the descent condition into Project_Extras (b) however the table known as "f" has not yet been introduced. This is an error. Technically, the syntactic sugar "ON" can only be used to introduce a conditional expression which references tables which have already been seen (as in have already been FROM <blah> or JOIN <blah> and a reference to a table which HAS NOT YET BEEN SEEN is an error). The condition between `a` and `f` cannot be attached as a descent qualifier for `b`. The query executes however because the planner chooses a traverse order that ensures that `f` is in outer loop before descent into `b`. Technically, however, many of your descent (ON clause after a LEFT JOIN) are invalid.
(3) By jose isaias cabrera (jicman) on 2021-08-13 13:30:57 in reply to 2 [link]
Thanks, Keith. So, what you are saying is that the .expert response is correct in providing the feedback with an error. Then the sqlite3 tool should not execute the SQLite bad code, and should also provide an error response. However, it is providing an answer. They should both provide the same error. josé
(4) By Keith Medcalf (kmedcalf) on 2021-08-13 19:49:16 in reply to 3
Exactly. You can get around this by complying with standard SQL requirements and make sure that tables used in the ON-clause syntactic sugar have already been "introduced" before they are "used". That is: ``` select * from a left join b on a.a = b.a and c.b = b.b left join c on a.c = c.c ; ``` is mis-stated, however, it can be solved by "advanced query optimization". The correct statement would be: ``` select * from a left join c on a.c = c.c left join b on a.a = b.a and c.b = b.b ; ``` Both forms will produce "the same result". Technically, however, one cannot "solve" the first query as stated without re-ordering the tables.