MySQL Bugs: #20126: SQL confused about aliases

Bug #20126	SQL confused about aliases
Submitted:	29 May 2006 14:24	Modified:	30 May 2006 9:00
Reporter:	Raimund Jacob	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.21, 5.1.9	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Sorry for the bad synposis. Below you find some DDL and DML that work both on mysql 4.0 and 4.1 but fail on 5.0.21 and 5.1.9. Tested on AMD64 (downloaded from mysq.com, all versions) and i386 (5.0, from debian).

The queries represent the errornous part of larger queries used by my application. I connect via JDBC, but that probably doesnt matter much. The SQLException thrown when running the last query is:

[Error Code: 1054, SQL State: 42S22]  Unknown column 'main.oid' in 'on clause'

How to repeat:
/* 5.0.21, 5.1.9-beta binary amd64, debian i386, jdbc connect */
drop table if exists foo2;
drop table if exists foo1;

CREATE TABLE `foo1` (
  `oid` bigint(20) NOT NULL,
   PRIMARY KEY  (`oid`)
);

CREATE TABLE `foo2` (
  `oid` bigint(20) NOT NULL,
  `keycol` varchar(128) NOT NULL,
  `valcol` text NOT NULL,
  PRIMARY KEY  (`oid`,`keycol`)
);

/* works, empty result */
SELECT DISTINCT main.oid FROM foo1 main
  LEFT OUTER JOIN users_attr p2 ON ( binary main.oid = p2.oid and  BINARY p2.keycol = 'bar');
 
/* triggers error 1054: Unkown column 'main.oid' in 'on clause' */
SELECT DISTINCT main.oid FROM foo1 main, foo2 p0, foo2 p1
  LEFT OUTER JOIN users_attr p2 ON ( binary main.oid = p2.oid and  BINARY p2.keycol = 'bar')
 WHERE 
((main.oid = p0.oid) and (main.oid = p1.oid));

Suggested fix:
make it work again, i have more queries to test that used to work in 4.x :)

Thank you for the bug report. That isn't a bug instead is a changed done
since version 5.0.12. Please read the Join syntax changes:

http://dev.mysql.com/doc/refman/5.0/en/join.html

 Join Processing Changes in MySQL 5.0.12

Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions must be rewritten to comply with the standard.

You mean to tell me that i just have to convert "from t1, t2, t3" to "from t1 join t2 join t3" and everything works as expected? This does seem to work (unit tests succeeded as they did for 4.x) and might not be optimal, but it solves my problem for now.

The documentation you mentioned mostly deals "*" resolution (which i dont care for) so i had to read between the lines a little.

ok, thanks and sorry for the disturbance. unfortunately i would never have guessed this to be a reasonable feature, since the "old" syntax seems intuitive to me and worked for quite some years.

Thank you for the feedback. Then could you please complete your test case
because the queries you provided aren't according the create table you
provided in the how to repeat.

oops, sorry about that. so much for trying to anonymize my table schema :)
ok, here you go with an updated scenario, also with data:
---8<---
drop table if exists foo2;
drop table if exists foo1;

CREATE TABLE foo1 (
  oid int NOT NULL,
  PRIMARY KEY  (oid)
);

CREATE TABLE foo2 (
  oid int NOT NULL,
  keycol varchar(128) NOT NULL,
  valcol text NOT NULL,
  PRIMARY KEY  (oid,keycol)
);

insert into foo1 values (1);
insert into foo1 values (2);
insert into foo1 values (3);
insert into foo2 values (3, '3', 'drei');
insert into foo2 values (4, '4', 'vier');

/* (1) works, result 1 2 3 */
SELECT DISTINCT main.oid FROM foo1 main
  LEFT OUTER JOIN foo2 p2 ON ( binary main.oid = p2.oid and  BINARY p2.keycol = 'bar');
 
/* (2) should work and return 3 
    triggers error 1054: Unkown column 'main.oid' in 'on clause' */
SELECT DISTINCT main.oid FROM foo1 main, foo2 p0
  LEFT OUTER JOIN foo2 p2 ON (binary main.oid = p2.oid and  BINARY p2.keycol = 'bar')
 WHERE 
(main.oid = p0.oid);

/* (3) the same but one more join */
SELECT DISTINCT main.oid FROM foo1 main, foo2 p0, foo2 p1
  LEFT OUTER JOIN foo2 p2 ON ( binary main.oid = p2.oid and  BINARY p2.keycol = 'bar')
 WHERE 
((main.oid = p0.oid) and (main.oid = p1.oid));

/* (4) works in 4.x and 5.x, returns 3 */
SELECT DISTINCT main.oid FROM foo1 main JOIN foo2 p0
  LEFT OUTER JOIN users_attr p2 ON (binary main.oid = p2.oid and  BINARY p2.keycol = 'bar')
 WHERE 
(main.oid = p0.oid);
--->8---

all DML statements work as expected in mysql 4.x,. statements (2) and (3) fail in mysql 5.x. the workaround would be turning (2) into (4) which works in all versions.

i will not start a fight about sql compliance and obey whatever you say on conformance to a particular version of sql. i just observe a change of behaviour without seeing the benefit of the change. if i include the workaround in my application, i will have just one more quirk for mysql (there are others, and there are some for other DBs) since this join behavior is not present in any of the other DBs i connect (MaxDB, Oracle, PostgreSQL, MsSql). just tell me what you think about this issue and i will go and do what's neccessary for my application to work.

thanks for any comment/advice

oh boy, it's not my day. here, use this as (4)
---8<---
/* (4) works in 4.x and 5.x, returns 3 */
SELECT DISTINCT main.oid FROM foo1 main JOIN foo2 p0
  LEFT OUTER JOIN foo2 p2 ON (binary main.oid = p2.oid and  BINARY p2.keycol = 'bar')
 WHERE 
(main.oid = p0.oid);
--->8<---

Let's consider this query:

SELECT DISTINCT main.oid FROM foo1 main JOIN foo2 p0
  LEFT OUTER JOIN foo2 p2 ON (binary main.oid = p2.oid and  BINARY p2.keycol = 'bar') WHERE (main.oid = p0.oid);

from your last comment. Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/join.html):

"- Previously, the ON clause could refer to columns in tables named to its right. Now an ON clause can refer only to its operands.

Example:

CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i2 INT);
CREATE TABLE t3 (i3 INT);
SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;

Previously, the SELECT statement was legal. Now the statement fails with an Unknown column 'i3' in 'on clause' error because i3 is a column in t3, which is not an operand of the ON clause. The statement should be rewritten as follows:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);"

So, this is not a bug, but intended and documented behaviour since 5.0.12.

ok, great. I did as i wrote, just use "JOIN" instead of "," and my application starts to work, all unit tests pass. thanks again for your help.