| 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: | |
| Category: | MySQL Server: Views | Severity: | S2 (Serious) |
| Version: | 5.0.50-sp1 | OS: | Any |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
[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.
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.