Bug #4733 EXISTS subquery does not work for MySQL

• Submitted: 23 Jul 2004 23:30
• Modified: 23 Jul 2004 23:56
• Reporter: Jie Shang
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.0.20
• OS: Windows (Windows XP)
• CPU Architecture: Any

[23 Jul 2004 23:30] Jie Shang

Description:
I am trying to use exists subquery in my select statement as documented in mySQL documentation (4.0.20):

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

But I got the following error message:

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MYSQL server version for the right syntax to use near 'EXISTS (SELECT * FROM t2) at line 1

Is this a bug as the MySQL 4.0.20 docuemnt mentioned that EXISTS and NOT EXISTS are supported.

I tried in MySQL 4.1.3 Beta, the statement works fine.

Thanks,
 

How to repeat:
Create two table, say t1(a int, b char(20)) and t2 (a int, b char(10))
and insert one or two rows in t1 and t2.

Start MySQL and execute the following statement:

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

The above statement should return result back instead of giving the error message.

Thanks,

[23 Jul 2004 23:41] Jie Shang

Please help as we are using MySQL as production database and I have several queries that need EXISTS or NOT EXISTS subquery. Thanks a lot

[23 Jul 2004 23:56] MySQL Verification Team

Subquries are only supported by version 4.1.X and above.

[24 Jul 2004 1:04] Paul DuBois

The details about what is supported and when are given
at the beginning of the subquery material in the manual:

http://dev.mysql.com/doc/mysql/en/Subqueries.html

This information is present in the manual that comes
with MySQL 4.0.20 because the manual is current
for all versions.