MySQL Bugs: #28814: unknown (existing) column with nested subquery

Bug #28814	unknown (existing) column with nested subquery
Submitted:	31 May 2007 18:47	Modified:	1 Jun 2007 18:40
Reporter:	Silvio Corciolani	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0.27-community-nt	OS:	Windows (XP HE SP2)
Assigned to:		CPU Architecture:	Any
Tags:	subquery, Unknown column

Description:
With slight complex nested subquery, MySQL says "unknown column in where clause", but column exists

How to repeat:
CREATE TABLE `t1` (
  `a` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `t2` (
  `b` int(11) NOT NULL auto_increment,
  `c` int(11) default NULL,
  PRIMARY KEY  (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `t1` (
  `c` int(11) NOT NULL auto_increment,
  `d` int(11) default NULL,
  PRIMARY KEY  (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This query doesn't work:

SELECT
  t1.a,
  (
    SELECT
      t3.d
    FROM
      (
        SELECT DISTINCT
          t2.c
        FROM
          t2
        WHERE
          t2.b = t1.b
      ) AS sel1
        INNER JOIN t3
          ON sel1.c = t3.c
  ) AS d
FROM
  t1

Suggested fix:
I expect outer SELECT would be resolved first of all (in this particular case), then t1 (and its columns) should be visible by the whole inner subquery, thus also by the other deeper one.

I made a mistake.
The third CREATE TABLE refers to t3:

CREATE TABLE `t3` (
  `c` int(11) NOT NULL auto_increment,
  `d` int(11) default NULL,
  PRIMARY KEY  (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The above query doesn't work because MySQL says "Unknown column 't1.b' in 'where clause'".
I'd expect the whole inner subquery (composed by two subqueries) to be evaluated for each row found on the outer subquery; thus the current value of 't1.b' should be visible by both two inner subqueries, like a parameter.

Thank you for a problem report. I was able to get the same error message on latest 5.0.44-BK, but looks like it is intended behaviour. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/correlated-subqueries.html:

"Scoping rule: MySQL evaluates from inside to outside. For example:

SELECT column1 FROM t1 AS x
WHERE x.column1 = (SELECT column1 FROM t2 AS x
WHERE x.column1 = (SELECT column1 FROM t3
WHERE x.column2 = t3.column1));

In this statement, x.column2 must be a column in table t2 because SELECT column1 FROM t2 AS x ... renames t2. It is not a column in table t1 because SELECT column1 FROM t1 ... is an outer query that is farther out."

Moreover (http://dev.mysql.com/doc/refman/5.0/en/unnamed-views.html):

"Subqueries in the FROM clause cannot be correlated subqueries, unless used within the ON  clause of a JOIN operation."

Thank you for fast reply.
Maybe according to the two rules from the manual, m query shouldn't work, but let me write two considerations:

-- First rule.
The example for evaluation order in scoping rules refers to subqueries using renaming with AS clause, and this has little to do with my case.
The following is the example rewritten without AS clause:

SELECT t1.a FROM t1
WHERE t1.b = (SELECT t2.b FROM t2
WHERE t2.c = (SELECT t3.c FROM t3
WHERE t1.a = t3.d));

It works, thus the innermost subquery can see t1.a from the outermost subquery.
Thus, according to this rule, my original query should work.

-- Second rule
Correlated subquery definition says "subquery that contains a reference to a table that also appears in THE outer query", thus it's a relationship by only two statements.
The innermost subquery is in the FROM clause of the middle subquery only, but it doesn't reference tables appearing in it (its "outer query"), thus the innermost subquery is not correlated.
Then I think the middle an the innermost subqueries should be regarded as a whole; this whole subquery contains a reference to t1.b from the outermost query (its "outer query"), but it is not in the FROM clause of the outermost query: instead, it's used as a column of the outermost query. And this works:

SELECT
  t1.a,
  (
    SELECT DISTINCT
      t2.c
    FROM
      t2
    WHERE
      t2.b = t1.b
  ) AS c
FROM
  t1

SELECT * 
FROM 
( SELECT * FROM `user` AS `t0` LIMIT 10 ) `t0`,
( SELECT * FROM `messages` AS `t1` WHERE `t0`.`user_id` = `t1`.`user_id` LIMIT 20 ) `t1`

What realize such request?