Richard Hipp wrote:
> On 1/5/20, Keith Medcalf <[email protected]> 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.)
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
