Bug #35193 VIEW query is rewritten without "FROM DUAL", causing syntax error
Submitted: 10 Mar 2008 21:04 Modified: 11 Apr 2008 16:12
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0.50-sp1 OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any
Triage: D3 (Medium)

[10 Mar 2008 21:04] Todd Farmer
Description:
When  VIEW is created that contains a reference to DUAL, this reference is removed when the definition is stored.  For some queries, this results in invalid SQL syntax errors when using the VIEW:

mysql> DROP TABLE IF EXISTS `t1`;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE `t1` (
    ->   `a` int(11) default NULL,
    ->   `b` varchar(100) default NULL,
    ->   `c` text
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql>
mysql> DROP VIEW IF EXISTS v1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE VIEW v1 AS
    ->  SELECT a, b, (SELECT 'Y' FROM DUAL WHERE EXISTS (SELECT 'X' FROM t1 inner_table WHERE inner_table.b = outer_table.a))
    ->  FROM t1 outer_table;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> SELECT * FROM v1;
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 exists(select _utf8'X' AS `X` from `test`.`t1` `inner_table` where (`inner' at line 1
mysql>
mysql> SHOW CREATE TABLE v1;
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 exists(select _utf8'X' AS `X` from `test`.`t1` `inner_table` where (`inner' at line 1
mysql>

Manipulating the v1.frm file to add the reference to DUAL resolves the error:

mysql> SHOW CREATE TABLE v1;
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
 exists(select _utf8'X' AS `X` from `test`.`t1` `inner_table` where (`inner' at
line 1
mysql> SHOW CREATE TABLE v1;
+------+------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+
| View | Create View

                    |
+------+------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFI
NER VIEW `v1` AS select `outer_table`.`a` AS `a`,`outer_table`.`b` AS `b`,(selec
t _utf8'Y' AS `Y` where exists(select _utf8'X' AS `X` from `t1` `inner_table` wh
ere (`inner_table`.`b` = `outer_table`.`a`))) AS `(SELECT 'Y' FROM DUAL WHERE EX
ISTS (SELECT 'X' FROM t1 inner_table WHERE inner_table.b = outer_table.a))` from
 `t1` `outer_table` |
+------+------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+
1 row in set (0.03 sec)

mysql>

How to repeat:
DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1` (
  `a` int(11) default NULL,
  `b` varchar(100) default NULL,
  `c` text
) ENGINE=MyISAM;

DROP VIEW IF EXISTS v1;

CREATE VIEW v1 AS 
 SELECT a, b, (SELECT 'Y' FROM DUAL WHERE EXISTS (SELECT 'X' FROM t1 inner_table WHERE inner_table.b = outer_table.a)) 
 FROM t1 outer_table;
 
SELECT * FROM v1;

SHOW CREATE TABLE v1;

Suggested fix:
Do not rewrite query without necessary "FROM DUAL" clause.
[10 Mar 2008 21:05] Todd Farmer
Unmodified .FRM file for VIEW without "FROM DUAL" clause

Attachment: v1.frm (application/octet-stream, text), 700 bytes.

[10 Mar 2008 21:28] Todd Farmer
For the test case provided, the workaround was to create the VIEW without referencing the DUAL pseudo-table, rewriting the query to use IF():

mysql>  SELECT a, b, (SELECT 'Y' FROM DUAL WHERE EXISTS
    ->  (SELECT 'X' FROM t1 inner_table
    ->      WHERE inner_table.b = outer_table.a)) `d`
    ->  FROM t1 outer_table;
+------+------+------+
| a    | b    | d    |
+------+------+------+
|    1 |    2 | NULL |
|    2 |    3 | Y    |
+------+------+------+
2 rows in set (0.00 sec)

mysql>
mysql> CREATE OR REPLACE VIEW v1 AS
    ->  SELECT a, b,
    ->   IF(EXISTS(SELECT 'X' FROM t1 inner_table
    ->      WHERE inner_table.b = outer_table.a), 'Y', NULL) `d`
    ->  FROM t1 outer_table;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT * FROM v1;
+------+------+------+
| a    | b    | d    |
+------+------+------+
|    1 |    2 | NULL |
|    2 |    3 | Y    |
+------+------+------+
2 rows in set (0.00 sec)
[26 Mar 2008 18:44] Bugs System
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/commits/44476

ChangeSet@1.2604, 2008-03-26 22:43:12+04:00, gshchepa@host.loc +3 -0
  Fixed bug #35193.
  View definition as SELECT ... FROM DUAL WHERE ... has
  valid syntax, but use of such view in SELECT or
  SHOW CREATE VIEW syntax causes unexpected syntax error.
  
  Server omits FROM DUAL clause when storing view body
  string in a .frm file for further evaluation.
  However, syntax of SELECT-witout-FROM query is more
  restrictive than SELECT FROM DUAL syntax, and doesn't
  allow the WHERE clause.
  
  NOTE: this syntax difference is not documented.
  
  
  View registration procedure has been modified to
  preserve original structure of view's body.
[26 Mar 2008 22:54] Bugs System
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/commits/44486

ChangeSet@1.2577, 2008-03-27 00:55:13+04:00, gshchepa@host.loc +1 -0
  view.result:
    Post-merge fix (bug #35193).
[28 Mar 2008 9:21] Bugs System
Pushed into 5.1.24-rc
[28 Mar 2008 9:23] Bugs System
Pushed into 5.0.60
[31 Mar 2008 13:58] Bugs System
Pushed into 6.0.5-alpha
[11 Apr 2008 16:12] Jon Stephens
Documented bugfix in the 5.0.60, 5.1.24, and 6.0.5 changelogs as follows:

        When a VIEW containing a reference to DUAL was created, the reference
        was removed when the definition was stored, causing some queries against
        the view to fail with invalid SQL syntax errors.