maria-developers team mailing list archive

[<sanja@xxxxxxxxxxxx>]

At file:///Users/bell/maria/bzr/work-maria-5.1-view-order-bug/

------------------------------------------------------------
revno: 2833
revision-id: sanja@xxxxxxxxxxxx-20100318191914-wupwctzwixm1144h
parent: sergii@xxxxxxxxx-20100312190521-jw1nggiv4427l5sm
committer: sanja@xxxxxxxxxxxx
branch nick: work-maria-5.1-view-order-bug
timestamp: Thu 2010-03-18 21:19:14 +0200
message:
  Fixed bug in view code when numeric reference in ORDER BY makes unusable View.
  
  In view representation we prints expresion instead of its numeric reference.

=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2010-02-10 19:06:24 +0000
+++ b/mysql-test/r/view.result	2010-03-18 19:19:14 +0000
@@ -3844,6 +3844,53 @@
 ALTER TABLE v1;
 DROP VIEW v1;
 DROP TABLE t1;
+#
+# Maria Bug #???: ORDER BY column reference in view leads
+# to unusable view
+#
+create table t1 (a int, b int);
+insert into t1 values (2,70), (8, 30), (1, 20);
+create view v1 as select a, b from t1 order by 2;
+select v1.a from v1;
+a
+1
+8
+2
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` order by `t1`.`b`	latin1	latin1_swedish_ci
+drop view v1;
+create view v1 as select a, b+3 as d from t1 order by 2;
+select v1.a from v1;
+a
+1
+8
+2
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,(`t1`.`b` + 3) AS `d` from `t1` order by (`t1`.`b` + 3)	latin1	latin1_swedish_ci
+drop view v1;
+create view v1 (a,v) as select a, b+3 as d from t1 order by 2;
+select v1.a from v1;
+a
+1
+8
+2
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,(`t1`.`b` + 3) AS `v` from `t1` order by (`t1`.`b` + 3)	latin1	latin1_swedish_ci
+drop view v1;
+create view v1 as select a, 3 as d from t1 order by 2;
+select v1.a from v1;
+a
+2
+8
+1
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,3 AS `d` from `t1` order by (2 + 0)	latin1	latin1_swedish_ci
+drop view v1;
+drop table t1;
 # -----------------------------------------------------------------
 # -- End of 5.1 tests.
 # -----------------------------------------------------------------

=== modified file 'mysql-test/t/view.test'
--- a/mysql-test/t/view.test	2010-02-10 19:06:24 +0000
+++ b/mysql-test/t/view.test	2010-03-18 19:19:14 +0000
@@ -3869,6 +3869,29 @@
 DROP VIEW v1;
 DROP TABLE t1;
 
+--echo #
+--echo # Maria Bug #???: ORDER BY column reference in view leads
+--echo # to unusable view
+--echo #
+create table t1 (a int, b int);
+insert into t1 values (2,70), (8, 30), (1, 20);
+create view v1 as select a, b from t1 order by 2;
+select v1.a from v1;
+show create view v1;
+drop view v1;
+create view v1 as select a, b+3 as d from t1 order by 2;
+select v1.a from v1;
+show create view v1;
+drop view v1;
+create view v1 (a,v) as select a, b+3 as d from t1 order by 2;
+select v1.a from v1;
+show create view v1;
+drop view v1;
+create view v1 as select a, 3 as d from t1 order by 2;
+select v1.a from v1;
+show create view v1;
+drop view v1;
+drop table t1;
 
 --echo # -----------------------------------------------------------------
 --echo # -- End of 5.1 tests.

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2010-03-04 08:03:07 +0000
+++ b/sql/item.cc	2010-03-18 19:19:14 +0000
@@ -2410,7 +2410,7 @@
 
 void Item_string::print(String *str, enum_query_type query_type)
 {
-  if (query_type == QT_ORDINARY && is_cs_specified())
+  if (query_type != QT_IS && is_cs_specified())
   {
     str->append('_');
     str->append(collation.collation->csname);
@@ -2418,7 +2418,7 @@
 
   str->append('\'');
 
-  if (query_type == QT_ORDINARY ||
+  if (query_type != QT_IS ||
       my_charset_same(str_value.charset(), system_charset_info))
   {
     str_value.print(str);

=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h	2010-03-04 08:03:07 +0000
+++ b/sql/mysql_priv.h	2010-03-18 19:19:14 +0000
@@ -52,12 +52,15 @@
 
   QT_ORDINARY -- ordinary SQL query.
   QT_IS -- SQL query to be shown in INFORMATION_SCHEMA (in utf8 and without
+  QT_VIEW_INTERNAL -- view internal representation (like QT_ORDINARY except
+    ORDER BY clause)
   character set introducers).
 */
 enum enum_query_type
 {
   QT_ORDINARY,
-  QT_IS
+  QT_IS,
+  QT_VIEW_INTERNAL
 };
 
 /* TODO convert all these three maps to Bitmap classes */

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2010-03-10 10:32:14 +0000
+++ b/sql/sql_lex.cc	2010-03-18 19:19:14 +0000
@@ -2057,9 +2057,27 @@
   {
     if (order->counter_used)
     {
-      char buffer[20];
-      size_t length= my_snprintf(buffer, 20, "%d", order->counter);
-      str->append(buffer, (uint) length);
+      if (query_type != QT_VIEW_INTERNAL)
+      {
+        char buffer[20];
+        size_t length= my_snprintf(buffer, 20, "%d", order->counter);
+        str->append(buffer, (uint) length);
+      }
+      else
+      {
+        /* replace numeric reference with expression */
+        if (order->item[0]->type() == Item::INT_ITEM &&
+            order->item[0]->basic_const_item())
+        {
+          char buffer[20];
+          size_t length= my_snprintf(buffer, 20, "%d", order->counter);
+          str->append(buffer, (uint) length);
+          /* make it expression instead of integer constant */
+          str->append(STRING_WITH_LEN("+0"));
+        }
+        else
+          (*order->item)->print(str, query_type);
+      }
     }
     else
       (*order->item)->print(str, query_type);
@@ -2069,7 +2087,7 @@
       str->append(',');
   }
 }
- 
+
 
 void st_select_lex::print_limit(THD *thd,
                                 String *str,

=== modified file 'sql/sql_view.cc'
--- a/sql/sql_view.cc	2010-03-04 08:03:07 +0000
+++ b/sql/sql_view.cc	2010-03-18 19:19:14 +0000
@@ -814,7 +814,7 @@
     ulong sql_mode= thd->variables.sql_mode & MODE_ANSI_QUOTES;
     thd->variables.sql_mode&= ~MODE_ANSI_QUOTES;
 
-    lex->unit.print(&view_query, QT_ORDINARY);
+    lex->unit.print(&view_query, QT_VIEW_INTERNAL);
     lex->unit.print(&is_query, QT_IS);
 
     thd->variables.sql_mode|= sql_mode;