MySQL Bugs: #15269: subquery - unkown colum error

Bug #15269	subquery - unkown colum error
Submitted:	27 Nov 2005 14:04	Modified:	23 May 2006 12:40
Reporter:	Corin Langosch	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.0.21	OS:	Linux (Linux)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
i read the changes in 5.0.x regardings left join etc, but can't figure out why this subquery should faild be bacause of these changes - i think it's simply a bug ;)

How to repeat:
SELECT id FROM foren_foren ff WHERE EXISTS (SELECT 1 FROM foren_threads ft LEFT JOIN foren_user_forum fuf ON (fuf.forum_id=ff.id))

'Unknown column 'ff.id' in 'on clause'

works with 4.1.x

Thank you for a bug report. Looks like this correlated subquery case was not properly taked into account. The bug is verified on 5.0.17-BK (ChangeSet@1.2009.1.2, 2005-11-25 20:48:26+03:00) using the following simple sequence of statements:

mysql> create table t1 (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t3 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> select id from t1 tt1 where exists (select 1 from t2 tt2 left join t3 tt3
 on (tt3.id = tt1.id));
ERROR 1054 (42S22): Unknown column 'tt1.id' in 'on clause'
mysql> select id from t1 tt1 where exists (select 1 from t2 tt2 join t3 tt3 on (
tt3.id = tt1.id));
ERROR 1054 (42S22): Unknown column 'tt1.id' in 'on clause'

But, in the same time:

mysql> select id from t1 tt1 where exists (select 1 from t2 tt2, t3 tt3 where tt
3.id = tt1.id);
Empty set (0.00 sec)

Reference to the outer table in a correlated subquery (tt1.id) should be treated as constant in any case, I think.

Hello. I previously reported a different bug, #11482. The select statement in that bug report also hits this very reported bug. My question is, are the test cases for the verified bugs added to the mysql test cases before making a new release? If not, how can we trust a new MySQL release to be compatible with the older releases (if not indicated in the docs explicitly)?

Also, in my opinion, severity level for this bug is 1, not 2.

Hi Gokhan,

We *always* add test cases for each bug we fix, when we
push the patch that fixes the bug to the main source tree.
You can check that easily in the test files residing in the
"mysql-test/t" subdirectory of a source tree by grep-ing for
the bug #.

In particular, BUG#11482 was due to a completely different
reason than the current bug, and both are not related, if that
is what you meant. That is why the test for #11482 (which is
in select.test) is successful, but it doesn't catch the current bug.

The test case I attached to the bug #11482 does not work with 5.0.16, so I am not sure how the test case was considered successful by MySQL team. Maybe (and probably) the test case was simplified by the MySQL team and the complex subqueries in my original test case were removed. However, this bug (15269) is really severity 1 in my opinion. And I am having difficulty to understand how such a case can occur in the production release. There must be other *simple* test cases to catch those unexpected situations.

By the way, I support open source and love MySQL full heartedly. I did in the past and still do now my best to help improve MySQL by preparing test cases and reporting bugs. But bugs like this really makes one down.

Best Regards,
Gokhan Demir

this bug is still present in the latest 5.1 release! 

it is impossible to upgrade from 4.1.x to 5.x because of this grave functionality bug. so i raised the severity to critical.

Will this *Severity 1* bug ever gets fixed? When?

this bug is still present in the latest 5.0 release! 

it is impossible to upgrade from 4.1.x to 5.x because of this grave
functionality bug. will you ever fix this bug? please!

The bug is now close to the top of my task list.
This bug will be addressed in the next couple of weeks.
Thank you for your patience.

See also bug#12747

The reported behavior is the correct one according to ANSI SQL.

The old (non-standard) behavior was changed in version 5.0.12 and is described in
great detail in Section "13.2.7.1. JOIN Syntax" of the 5.0 manual. Search for the
string "Join Processing Changes in MySQL 5.0.12".

In summary, according to the standard, the conditions in an ON clause may
reference only fields that are among the fields of the join operands. So to say,
the name space for the ON clause are the tables in the join, and no other
tables.

In the first two cases, the join:
t2 tt2 left join t3 tt3 on (tt3.id = tt1.id)
has an ON clause that refers to table 'tt1' which is not part of the join.

Tha is why MySQL issues an error.

On the other hand the WHERE clause may refer to any column of
any table in the FROM clause, or any other outer table.

I hope this explains things.

Best regards,
Timour