| Bug #35193 | VIEW query is rewritten without "FROM DUAL", causing syntax error | ||
|---|---|---|---|
| Submitted: | 10 Mar 22:04 | Modified: | 11 Apr 18:12 |
| Reporter: | Todd Farmer | ||
| Status: | Closed | ||
| Category: | Server: Views | Severity: | S2 (Serious) |
| Version: | 5.0.50-sp1 | OS: | Any |
| Assigned to: | Gleb Shchepa | Target Version: | 5.0+ |
| Triage: | D3 (Medium) | ||
[10 Mar 22:05]
Todd Farmer
Unmodified .FRM file for VIEW without "FROM DUAL" clause
Attachment: v1.frm (application/octet-stream, text), 700 bytes.
[10 Mar 22: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 19: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 23: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 10:21]
Bugs System
Pushed into 5.1.24-rc
[28 Mar 10:23]
Bugs System
Pushed into 5.0.60
[31 Mar 15:58]
Bugs System
Pushed into 6.0.5-alpha
[11 Apr 18: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.