3 ms·
Sqlite3 – Bug or hidden feature?
- advisedwang 9y agoReplying here as I don't want to sign up on medium. The blog indicates they believe SELECT * FROM table1, table2 ON table1.id=table2.id; is being parsed as: SELECT result-column=* FROM table-or-subquery=table1 , join-clause=ON table1.id=table2.id table-or-subquery=table2 join-operator= EMPTY!! table-or-subquery=EMPTY!! join-constraint=ON table1.id=table2 This is not correct, it is being parsed as: SELECT result-column=* FROM join-clause=table1, table2 ON table1.id=table2.id table-or-subquery=table1 join-operator=, table-or-subquery=table2 join-constraint=ON table1.id=table2 In other words, because the constraint is present, both tables are part of the join clause, and `table1, table2` is just another way of writing `table1 JOIN table2`.
- punnerud 9y agoThank you for your good explanation. I have updated the article now with referanse to your answer.
- deathanatos 9y agoThis part: > That was strange.. This is not how it worked for me in sqlite3. It was more this way: > [diagram] The author needs to visit the grammar for SELECT[1] and specifically expand the `join-operator` grammar; he will see that the diagram that he drew the red arrow on is, in fact, going through the grammar diagram as it is listed. (The join operator here is the "," used to separate the tables.) Also, I feel like we keep using "with-clause" to mean "where clause". Now, what the semantic meaning of this grammar is, and whether that was a bug … not sure. Syntactically, it seems ok, but I agree with the "semi explicit join" pondering of the author. PostgreSQL's docs, for example, indicate that this is not syntactically valid. [1]: https://www.sqlite.org/lang_select.html https://www.sqlite.org/lang_select.html