MySQL Bugs: #14092: Optimizer requires table aliases to be declared in order

Bug #14092	Optimizer requires table aliases to be declared in order
Submitted:	17 Oct 2005 23:06	Modified:	18 Oct 2005 8:39
Reporter:	Dave Gullo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	Mysql 5 RC 1	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
In all past versions of mysql I have ever used, the order that table aliases appear in a statement does not matter.  The optimizer usually pre-parses the statement to identify all table aliases so that they do not need to be declared in the order they are used.

In the current release candidate, there is a bug that is solved by declaring the statement in a different order.  While it is good style to use to use table aliases in the order you declare them, it should not be a requirement.

How to repeat:
/* BAD SQL 
Usually I declare all INNER JOIN statements first, but putting a few LEFT OUTER's first causes the problem. 
*/
SELECT
    o.orderid,
    c.customerid,
    concat(c.firstname, ' ', c.lastname) AS customer,
    DATE_FORMAT(o.settledate, '%Y-%m-%d %H:%i') AS settledate,
    o.comments,
    p.productid,
    p.name,
    m.name AS manufacturer,
    DATE_FORMAT(p.estshipdate, '%m-%d') AS estshipdate,
    p.style,
    att.name AS attributes,
    oi.orderitemid,
    oi.quantity,
    format(oi.price, 2) AS price,
    format(oi.cost, 2) AS cost,
    o.shipping,
    o.shipmethod,
    oi.trackinginfo,
    oi.comments,
    oi.lastmodified
  FROM orders o
  INNER JOIN customers c ON o.customerid = c.customerid
  LEFT OUTER JOIN orderitemid_attributeid a ON oi.orderitemid = a.orderitemid  /* CONTAINS oi.orderitemid REFERENCE WHICH CAUSES BUG */
  LEFT OUTER JOIN attributes att ON a.attributeid = att.attributeid
  INNER JOIN orderitems oi ON o.orderid = oi.orderid AND oi.status = 'Pending Shipment' /* BAD LINE */
  INNER JOIN products p ON oi.productid = p.productid
  INNER JOIN manufacturers m ON p.manufacturerid = m.manufacturerid
  WHERE o.settledate > DATE_ADD(now(), INTERVAL (60* -1) DAY)
  GROUP BY oi.orderitemid
  ORDER BY o.orderid DESC;

PRODUCES
ERROR 1054 (42S22): Unknown column 'oi.orderitemid' in 'on clause'

/* 
************************************************************************
Good SQL */

SELECT
    o.orderid,
    c.customerid,
    concat(c.firstname, ' ', c.lastname) AS customer,
    DATE_FORMAT(o.settledate, '%Y-%m-%d %H:%i') AS settledate,
    o.comments,
    p.productid,
    p.name,
    m.name AS manufacturer,
    DATE_FORMAT(p.estshipdate, '%m-%d') AS estshipdate,
    p.style,
    att.name AS attributes,
    oi.orderitemid,
    oi.quantity,
    format(oi.price, 2) AS price,
    format(oi.cost, 2) AS cost,
    o.shipping,
    o.shipmethod,
    oi.trackinginfo,
    oi.comments,
    oi.lastmodified
  FROM orders o
  INNER JOIN customers c ON o.customerid = c.customerid
  INNER JOIN orderitems oi ON o.orderid = oi.orderid AND oi.status = 'Pending Shipment' /* MOVED THIS UP TO FIX THE BUG */
  LEFT OUTER JOIN orderitemid_attributeid a ON oi.orderitemid = a.orderitemid
  LEFT OUTER JOIN attributes att ON a.attributeid = att.attributeid
  INNER JOIN products p ON oi.productid = p.productid
  INNER JOIN manufacturers m ON p.manufacturerid = m.manufacturerid
  WHERE o.settledate > DATE_ADD(now(), INTERVAL (60* -1) DAY)
  GROUP BY oi.orderitemid
  ORDER BY o.orderid DESC

PRODUCES
<expected result set>

Suggested fix:
Investigate whether or not the engine should be pre-parsing statements to discover all table aliases before attempting to run.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/join.html). It is an intended change in 5.0.12 to become closer to the standard:

"Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. Redundant output columns specified in USING clauses are eliminated, and output columns are ordered correctly.

This means that some queries that appeared to work correctly prior to 5.0.12 must be rewritten to comply with the standard. For example, a query of the form

SELECT t1.id,t2.id,t3.id 
    FROM t1,t2 
    LEFT JOIN t3 ON (t3.id=t1.id)
    WHERE t1.id=t2.id;

will be interpreted in MySQL 5.0.12 and later as

SELECT t1.id,t2.id,t3.id 
    FROM t1,(  t2 LEFT JOIN t3 ON (t3.id=t1.id)  )
    WHERE t1.id=t2.id;

Such a query must now be written like so:

SELECT t1.id,t2.id,t3.id 
    FROM (t1,t2) 
    LEFT JOIN t3 ON (t3.id=t1.id)
    WHERE t1.id=t2.id;"