Bug #23940 multiple subqueries causes bork (should succeed) in joins
Submitted: 3 Nov 2006 13:21 Modified: 6 Nov 2006 8:55
Reporter: d di (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.22 OS:
Assigned to: CPU Architecture:Any
Tags: 1054, join, sql error, subquery

[3 Nov 2006 13:21] d di
Using multiple subqueries in combination with a join causes MySQL to abort the query with an "invalid SQL" message.  The SQL is actually valid, as far as I can see.

Here's the details.

This query works:
  (SELECT 1 AS Id) t1
  (SELECT 1 AS Chosen) t3 ON t3.Chosen = t1.Id

This query should work too, but doesn't:
  (SELECT 1 AS Id) t1,
  (SELECT 2 AS Id) t2
  (SELECT 1 AS Chosen) t3 ON t3.Chosen = t1.Id

The error received is #1054.

The type of join is not significant, but the ordering of the subqueries in the FROM paragraph is.

(Related note: It would be sooooo nice if we didn't have to invent crappy names that we never use anyway for every single subquery just because MySQL internally requires it.  Being forced by MySQL Server to introduce loads of SQL spam is not nice.)

How to repeat:
[5 Nov 2006 12:54] Valeriy Kravchuk
Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/join.html. It is expected behaviour since 5.0.12. That page also have some explanation on how to avoid these error messages:

1054 SQLSTATE: 42S22 ( ER_BAD_FIELD_ERROR ) Message: Unknown column '%s' in '%s'
[6 Nov 2006 8:55] d di
Eep, you're right.

If someone else stumbles upon this bug, here's the relevant change.

> Previously, the comma operator (,) and JOIN both had the same precedence,
> so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3).
> Now JOIN has higher precedence, so the expression is interpreted as
> (t1, (t2 JOIN t3)). This change affects statements that use an ON clause,
> because that clause can refer only to columns in the operands of the join,
> and the change in precedence changes interpretation of what those operands
> are.