Hi,
On Tue, Jan 7, 2020 at 2:01 AM Clemens Ladisch <clem...@ladisch.de> wrote:
>
> Richard Hipp wrote:
> > On 1/5/20, Keith Medcalf <kmedc...@dessus.com> wrote:
> >> select * from a, b, c using (id); -- very strange result
> >
> > PostgreSQL and MySQL process the query as follows:
> >
> > SELECT * FROM a, (b JOIN c USING(id));
> >
> > SQLite processes the query like this:
> >
> > SELECT * FROM (a,b) JOIN c USING (id);
> >
> > I don't know which is correct. Perhaps the result is undefined.
>
> Assuming the following query:
>
> SELECT * FROM a, b JOIN c USING (id);
>
> SQL-92 says:
> | 7.4 <from clause>
> |
> | <from clause> ::= FROM <table reference> [ { <comma> <table
> reference> }... ]
> |
> | 6.3 <table reference>
> |
> | <table reference> ::=
> | <table name> [ [ AS ] <correlation name>
> | [ <left paren> <derived column list> <right paren> ] ]
> | | <derived table> [ AS ] <correlation name>
> | [ <left paren> <derived column list> <right paren> ]
> | | <joined table>
> |
> | 7.5 <joined table>
> |
> | <joined table> ::=
> | <cross join>
> | | <qualified join>
> | | <left paren> <joined table> <right paren>
> |
> | <cross join> ::=
> | <table reference> CROSS JOIN <table reference>
> |
> | <qualified join> ::=
> | <table reference> [ NATURAL ] [ <join type> ] JOIN
> | <table reference> [ <join specification> ]
>
> It is not possible to have such a <comma> inside a <table reference>, so
> b and c must be joined first.
>
> SQLite actually parses the comma as a join:
>
> SELECT * FROM a CROSS JOIN b JOIN c USING (id);
>
> If the query were written like this, joining a and b first would be
> correct. (As far as I can see, the standard does not say how to handle
> ambiguous parts of the grammar, so it would also be allowed to produce
> "b JOIN c" first.)
That's why one should never use that "MS JOIN extension" and should
simply write:
SELECT ... FROM a,b,c WHERE a.x = b.x AND b.x = c.y AND...;
Thank you.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
