Hacker News new | comments | show | ask | jobs | submit login
Sqlite3 – Bug or hidden feature? (medium.com)
8 points by punnerud 89 days ago | hide | past | web | favorite | 3 comments



This 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


Replying 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`.


Thank you for your good explanation. I have updated the article now with referanse to your answer.




Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact

Search: