MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:<gshchepa Date:March 26 2008 6:43pm
Subject:bk commit into 5.0 tree (gshchepa:1.2604) BUG#35193
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of gshchepa.  When gshchepa does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-03-26 22:43:12+04:00, gshchepa@stripped +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.
  

  mysql-test/r/view.result@stripped, 2008-03-26 22:09:59+04:00, gshchepa@stripped +18 -0
    Added test case for bug #35193.

  mysql-test/t/view.test@stripped, 2008-03-26 22:09:59+04:00, gshchepa@stripped +23 -0
    Added test case for bug #35193.

  sql/sql_select.cc@stripped, 2008-03-26 22:10:00+04:00, gshchepa@stripped +8 -0
    Fixed bug #35193.
    The st_select_lex::print function always omits FROM DUAL clause,
    even if original SELECT query has the WHERE clause.
    
    The mysql_register_view function uses this function to reconstruct
    a body of view's AS clause for further evaluation and stores that
    reconstructed clause in a .frm file.
    
    SELECT without FROM syntax is more restrictive than 
    SELECT FROM DUAL syntax: second one allows
    the WHERE clause, but first one is not.
    
    Use of this view in SELECT or SHOW CREATE VIEW queries
    causes unexpected syntax errors.
    
    
    The st_select_lex::print function has been modified to
    reconstruct FROM DUAL clause in queries when needed.
    
    
    TODO: Syntax difference is not documented and should be
    eliminated, however improvement of
    the SELECT-without-FROM syntax is not trivial and leads to
    significant modification of grammar file because of additional
    shift/reduce conflicts.

diff -Nrup a/mysql-test/r/view.result b/mysql-test/r/view.result
--- a/mysql-test/r/view.result	2008-03-12 11:59:14 +04:00
+++ b/mysql-test/r/view.result	2008-03-26 22:09:59 +04:00
@@ -3660,5 +3660,23 @@ DROP TABLE t1;
 # -- End of test case for Bug#34337.
 
 # -----------------------------------------------------------------
+# -- Bug#35193: VIEW query is rewritten without "FROM DUAL",
+# --            causing syntax error
+# -----------------------------------------------------------------
+
+CREATE VIEW v1 AS SELECT 1 FROM DUAL WHERE 1;
+
+SELECT * FROM v1;
+1
+1
+SHOW CREATE TABLE v1;
+View	Create View
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` from DUAL  where 1
+
+DROP VIEW v1;
+
+# -- End of test case for Bug#35193.
+
+# -----------------------------------------------------------------
 # -- End of 5.0 tests.
 # -----------------------------------------------------------------
diff -Nrup a/mysql-test/t/view.test b/mysql-test/t/view.test
--- a/mysql-test/t/view.test	2008-03-12 11:59:14 +04:00
+++ b/mysql-test/t/view.test	2008-03-26 22:09:59 +04:00
@@ -3538,5 +3538,28 @@ DROP TABLE t1;
 ###########################################################################
 
 --echo # -----------------------------------------------------------------
+--echo # -- Bug#35193: VIEW query is rewritten without "FROM DUAL",
+--echo # --            causing syntax error
+--echo # -----------------------------------------------------------------
+--echo
+
+CREATE VIEW v1 AS SELECT 1 FROM DUAL WHERE 1;
+
+--echo
+
+SELECT * FROM v1;
+SHOW CREATE TABLE v1;
+
+--echo
+
+DROP VIEW v1;
+
+--echo
+--echo # -- End of test case for Bug#35193.
+--echo
+
+###########################################################################
+
+--echo # -----------------------------------------------------------------
 --echo # -- End of 5.0 tests.
 --echo # -----------------------------------------------------------------
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2008-03-03 21:35:38 +04:00
+++ b/sql/sql_select.cc	2008-03-26 22:10:00 +04:00
@@ -15806,6 +15806,14 @@ void st_select_lex::print(THD *thd, Stri
     /* go through join tree */
     print_join(thd, str, &top_join_list);
   }
+  else if (where)
+  {
+    /*
+      "SELECT 1 FROM DUAL WHERE 2" should not be printed as 
+      "SELECT 1 WHERE 2": the 1st syntax is valid, but the 2nd is not.
+    */
+    str->append(STRING_WITH_LEN(" from DUAL "));
+  }
 
   // Where
   Item *cur_where= where;
Thread
bk commit into 5.0 tree (gshchepa:1.2604) BUG#35193gshchepa26 Mar