At file:///Users/bell/maria/bzr/work-maria-5.1-view-order-bug/ ------------------------------------------------------------ revno: 2833 revision-id: sanja@askmonty.org-20100318191914-wupwctzwixm1144h parent: sergii@pisem.net-20100312190521-jw1nggiv4427l5sm committer: sanja@askmonty.org 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;