MySQL Bugs: #23940: multiple subqueries causes bork (should succeed) in joins

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:
Assigned to:		CPU Architecture:	Any
Tags:	1054, join, sql error, subquery

Description:
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
  *
FROM
  (SELECT 1 AS Id) t1
LEFT JOIN
  (SELECT 1 AS Chosen) t3 ON t3.Chosen = t1.Id
==============

This query should work too, but doesn't:
==============
SELECT
  *
FROM
  (SELECT 1 AS Id) t1,
  (SELECT 2 AS Id) t2
LEFT JOIN
  (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:
.

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'

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.