MySQL Bugs: #11745: insert where not exists failing

Bug #11745	insert where not exists failing
Submitted:	5 Jul 2005 20:16	Modified:	3 Aug 2005 20:56
Reporter:	Joe Bob	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.12a	OS:	Any (All)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
I am executing the following query through the MySQL Query Browser (All columns are MySQL Integer datatypes):

INSERT INTO SUBSCRIPTIONS (ARTICLE_ID, USER_ID, ADDED_BY)
SELECT 50, 3, 3 FROM DUAL
WHERE NOT EXISTS
(SELECT * FROM SUBSCRIPTIONS WHERE ARTICLE_ID = 50 AND USER_ID = 3)

This query should do the insert as long as nothing is selected from the subquery, as stated in the documentation.

However, it produces the following error:

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 'WHERE NOT EXISTS
(SELECT * FROM R10_RP_SUBSCRIPTIONS WHERE ARTICLE_ID = 50)' at line 3

How to repeat:
Run the above statement in the MySQL query browser or through JDBC.

ERROR 1064 (42000): 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 'WHERE NOT EXISTS
(SELECT * FROM SUBSCRIPTIONS WHERE ARTICLE_ID = 50 AND USER_ID ' at line 3
mysql> exit

jorge-include/mysql> grep 1064 *
mysqld_error.h:#define ER_PARSE_ERROR 1064

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27233

Where clause was not accepted with FROM DUAL by our syntax before.
I corrected the grammar rule and added a support for this case.

ChangeSet
  1.2337 05/07/17 09:46:14 igor@rurik.mysql.com +4 -0
  select.result, select.test:
    Added a test case for bug #11745.
  sql_select.cc:
    Fixed bug # 11745.
    Added support of where clause for queries with FROM DUAL.
  sql_yacc.yy:
    Fixed bug # 11745.
    Added optional where clause for queries with FROM DUAL.

The fix will appear in 4.1.14 and 5.0.11

Documented in 4.1.14 and 5.0.11 changelogs:

<listitem><para>Added support of where clause for queries with <literal>FROM DUAL</literal>. (Bug #11745)</para></listitem>