MySQL Bugs: #16029: FROM tables not visible from doubly nested subqueries in WHERE clause

Bug #16029	FROM tables not visible from doubly nested subqueries in WHERE clause
Submitted:	28 Dec 2005 4:46	Modified:	13 Jun 2006 8:18
Reporter:	Sharif Ibrahim	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.17, 4.1.14	OS:	Linux (Linux)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Doubly nesting subselects in the WHERE clause of a query can lead to a situation where the relations in the FROM clause are not visible to them.

For example, the query

SELECT t1.foo 
FROM ( SELECT 1 AS foo ) t1 
WHERE
EXISTS 
( SELECT t3.foo FROM 
 ( SELECT t2.foo FROM 
  ( SELECT 1 AS foo ) t2 
 WHERE t2.foo = t1.foo ) t3
)

yields the error

ERROR 1054 (52522): Unknown column 't1.foo' in 'where clause'

For contrast, the following query does _not_ yield the error:

SELECT t1.foo 
FROM ( SELECT 1 AS foo ) t1 
WHERE
EXISTS 
( SELECT t2.foo FROM 
 ( SELECT 1 AS foo ) t2 
WHERE t2.foo = t1.foo )

Expected result in all cases is a single row with the value 1 in the foo column.

While the extra nesting is useless in this very simplified case, there are instances where it is very useful.

How to repeat:
Attempt to execute the query:

SELECT t1.foo 
FROM ( SELECT 1 AS foo ) t1 
WHERE
EXISTS 
( SELECT t3.foo FROM 
 ( SELECT t2.foo FROM 
  ( SELECT 1 AS foo ) t2 
 WHERE t2.foo = t1.foo ) t3
)

Thank you for a bug report. Verified just as described with 5.0.19-BK (ChangeSet@1.1995, 2005-12-26 13:40:07+04:00) on Linux:

mysql> SELECT t1.foo
    -> FROM ( SELECT 1 AS foo ) t1
    -> WHERE
    -> EXISTS
    -> ( SELECT t3.foo FROM
    ->  ( SELECT t2.foo FROM
    ->   ( SELECT 1 AS foo ) t2
    ->  WHERE t2.foo = t1.foo ) t3
    -> );
ERROR 1054 (42S22): Unknown column 't1.foo' in 'where clause'
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)

Not sure if this is the same bug.  Our local bugzilla stopped working after we upgraded to mysql5.  The problem is that a field (bugs_id) in a previously specified table (bugs) in the FROM clause is not visible in a LEFT JOIN.  In the failing query below, changing "FROM bugs, profiles AS map_assigned_to" to "FROM profiles AS map_assigned_to, bugs" fixes the problem.

Your MySQL connection id is 21305 to server version: 5.0.17-log

mysql> SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc, bugs.delta_ts FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (7,5,4,8)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 4 WHERE bugs.assigned_to = map_assigned_to.userid AND (bugs.product_id IN (8)) AND (bugs.bug_status IN ('NEW','ASSIGNED','REOPENED')) AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = 4)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to = 4) ) GROUP BY bugs.bug_id ORDER BY bugs.bug_severity,bugs.priority,bugs.delta_ts, bugs.bug_status, bugs.priority, map_assigned_to.login_name, bugs.bug_id;
ERROR 1054 (42S22): Unknown column 'bugs.bug_id' in 'on clause'

mysqld is running on archlinux.

That's unrelated to this bug report and is actually not a MySQL bug.  It's a documented consequence of greater adherence to ANSI SQL in version 5.0.12 and above.

http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html

You should check with the Bugzilla developers to see if this has been fixed in an updated version of Bugzilla.  A Google search for the error message you provided gives this page as the first result which both confirms the diagnosis of the issue and appears to provide a potential workaround: http://www.chuckcaplan.com/blog/archives/2005/10/bugzilla_and_my.html

Since your issue does not concern this bug report, please file a separate bug report if you determine that there is an underlying MySQL bug.

I have reproduced this in 5.0.15.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61 to server version: 5.0.15-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT t1.foo 
    -> FROM ( SELECT 1 AS foo ) t1
    -> WHERE
    -> EXISTS 
    -> ( SELECT t3.foo FROM 
    ->  ( SELECT t2.foo FROM 
    ->   ( SELECT 1 AS foo ) t2 
    ->  WHERE t2.foo = t1.foo ) t3
    -> )
    -> ;
ERROR 1054 (42S22): Unknown column 't1.foo' in 'where clause'
mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.15-standard |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT t1.foo 
    -> FROM ( SELECT 1 AS foo ) t1 
    -> WHERE
    -> EXISTS 
    -> ( SELECT t2.foo FROM 
    ->  ( SELECT 1 AS foo ) t2 
    -> WHERE t2.foo = t1.foo );
+-----+
| foo |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

CREATE TABLE IF NOT EXISTS T1(
  Id      INTEGER,
  F1      INTEGER,
  T2_Id   INTEGER);

CREATE TABLE IF NOT EXISTS T2(
  Id      INTEGER,
  F2      INTEGER);

---

SELECT 
  T1.F1, 
  (SELECT Q.F2 FROM 
    (SELECT T2.F2 FROM T2 WHERE T2.Id = T1.T2_Id) AS Q
  ) AS F2 
FROM T1;

Error: Unknown column 'T1.T2_Id' in 'where clause'
---
SELECT Version(); 
Output: 5.0.19-nt-max

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:

According to the Reference Manual : 
"Subqueries in the FROM clause cannot be correlated subqueries. They are materialized (executed to produce a result set) before evaluating the outer query, so they cannot be evaluated per row of the outer query."
Here you have subquery in the FROM clause (derived table t3)  that references it's outer context.

Relevant reference manual section : I.3 (http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html).