MySQL Bugs: #42374: using a value from DUAL in a subquery returns Unknown column

Bug #42374	using a value from DUAL in a subquery returns Unknown column
Submitted:	27 Jan 2009 13:53	Modified:	27 Jan 2009 17:18
Reporter:	Werner Schram	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	DUAL sub-query

Description:
When I use the following query on mysql 5.1.30 (freebsd), it does not behave as I expect:

INSERT INTO t1 (f1) SELECT 1 AS x FROM DUAL WHERE NOT EXISTS (SELECT * FROM t1 WHERE f1=x);

The table t1 is created as follows:

CREATE TABLE t1 ( f1 INTEGER );

on my 5.0.42 (gentoo linux) system it returns "Query OK" when executed. On 5.0.75 and 5.1.30 (both freebsd) it returns:

ERROR 1054 (42S22): Unknown column 'x' in 'where clause'

It seems to be related to using the DUAL table. If I create a similar query with an real table in stead of DUAL, the behaviour as I expect it to be.

How to repeat:
CREATE TABLE t1 ( f1 INTEGER  );

INSERT INTO t1 (f1) SELECT 1 AS x FROM DUAL WHERE NOT EXISTS (SELECT * FROM t1 WHERE f1=x);

Suggested fix:
I would suggest using:

INSERT INTO t1 (f1) SELECT @x:=1 FROM DUAL WHERE NOT EXISTS ( SELECT * FROM t1 WHERE f1=@x);

But that gives me trouble with DBI or DBD::MySQL, which I will elaborate on in a different bugreport ;)

I have the same behaviour both with DUAL and with real table. Look:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-enterprise-gpl-advanced-debug-log MySQL Enterprise Server
 - Advanced Edition Debug (GPL)

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

mysql> CREATE TABLE t1 ( f1 INTEGER  );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t1 (f1) SELECT 1 AS x FROM DUAL WHERE NOT EXISTS (SELECT * FR
OM t1 WHERE
    -> f1=x);
ERROR 1054 (42S22): Unknown column 'x' in 'where clause'
mysql> create table mydual(c1 int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mydual values(1);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t1 (f1) SELECT 1 AS x FROM mydual WHERE NOT EXISTS (SELECT *
FROM t1 WHERE
    -> f1=x);
ERROR 1054 (42S22): Unknown column 'x' in 'where clause'
mysql> select * from mydual;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.06 sec)

Do I miss something in my test case?

No you are not missing anything. It seems that I am wrong with my comment about it being related to the DUAL table. I have probably used the 5.0.42 server by mistake to check if it was related to the DUAL table. I'm sorry for that.

OK, then I think this is NOT a bug. You can not refer to column alias in WHERE, according to http://dev.mysql.com/doc/refman/5.1/en/problems-with-alias.html.