diff options
-rw-r--r-- | mysql-test/r/alter_table.result | 113 | ||||
-rw-r--r-- | mysql-test/t/alter_table.test | 66 | ||||
-rw-r--r-- | sql/sql_yacc.yy | 25 |
3 files changed, 203 insertions, 1 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 60830cd9c45..da5cf688325 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -690,3 +690,116 @@ SHOW INDEX FROM bug24219_2; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment bug24219_2 1 a 1 a A NULL NULL NULL YES BTREE disabled DROP TABLE bug24219_2; +drop table if exists table_24562; +create table table_24562( +section int, +subsection int, +title varchar(50)); +insert into table_24562 values +(1, 0, "Introduction"), +(1, 1, "Authors"), +(1, 2, "Acknowledgements"), +(2, 0, "Basics"), +(2, 1, "Syntax"), +(2, 2, "Client"), +(2, 3, "Server"), +(3, 0, "Intermediate"), +(3, 1, "Complex queries"), +(3, 2, "Stored Procedures"), +(3, 3, "Stored Functions"), +(4, 0, "Advanced"), +(4, 1, "Replication"), +(4, 2, "Load balancing"), +(4, 3, "High availability"), +(5, 0, "Conclusion"); +select * from table_24562; +section subsection title +1 0 Introduction +1 1 Authors +1 2 Acknowledgements +2 0 Basics +2 1 Syntax +2 2 Client +2 3 Server +3 0 Intermediate +3 1 Complex queries +3 2 Stored Procedures +3 3 Stored Functions +4 0 Advanced +4 1 Replication +4 2 Load balancing +4 3 High availability +5 0 Conclusion +alter table table_24562 add column reviewer varchar(20), +order by title; +select * from table_24562; +section subsection title reviewer +1 2 Acknowledgements NULL +4 0 Advanced NULL +1 1 Authors NULL +2 0 Basics NULL +2 2 Client NULL +3 1 Complex queries NULL +5 0 Conclusion NULL +4 3 High availability NULL +3 0 Intermediate NULL +1 0 Introduction NULL +4 2 Load balancing NULL +4 1 Replication NULL +2 3 Server NULL +3 3 Stored Functions NULL +3 2 Stored Procedures NULL +2 1 Syntax NULL +update table_24562 set reviewer="Me" where section=2; +update table_24562 set reviewer="You" where section=3; +alter table table_24562 +order by section ASC, subsection DESC; +select * from table_24562; +section subsection title reviewer +1 2 Acknowledgements NULL +1 1 Authors NULL +1 0 Introduction NULL +2 3 Server Me +2 2 Client Me +2 1 Syntax Me +2 0 Basics Me +3 3 Stored Functions You +3 2 Stored Procedures You +3 1 Complex queries You +3 0 Intermediate You +4 3 High availability NULL +4 2 Load balancing NULL +4 1 Replication NULL +4 0 Advanced NULL +5 0 Conclusion NULL +alter table table_24562 +order by table_24562.subsection ASC, table_24562.section DESC; +select * from table_24562; +section subsection title reviewer +5 0 Conclusion NULL +4 0 Advanced NULL +3 0 Intermediate You +2 0 Basics Me +1 0 Introduction NULL +4 1 Replication NULL +3 1 Complex queries You +2 1 Syntax Me +1 1 Authors NULL +4 2 Load balancing NULL +3 2 Stored Procedures You +2 2 Client Me +1 2 Acknowledgements NULL +4 3 High availability NULL +3 3 Stored Functions You +2 3 Server Me +alter table table_24562 order by 12; +ERROR 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 '12' at line 1 +alter table table_24562 order by (section + 12); +ERROR 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 '(section + 12)' at line 1 +alter table table_24562 order by length(title); +ERROR 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 'length(title)' at line 1 +alter table table_24562 order by (select 12 from dual); +ERROR 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 '(select 12 from dual)' at line 1 +alter table table_24562 order by no_such_col; +ERROR 42S22: Unknown column 'no_such_col' in 'order clause' +drop table table_24562; diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 3e1e7952af1..52a569dfb57 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -518,4 +518,70 @@ SHOW INDEX FROM bug24219_2; DROP TABLE bug24219_2; +# +# Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts) +# + +--disable_warnings +drop table if exists table_24562; +--enable_warnings + +create table table_24562( + section int, + subsection int, + title varchar(50)); + +insert into table_24562 values +(1, 0, "Introduction"), +(1, 1, "Authors"), +(1, 2, "Acknowledgements"), +(2, 0, "Basics"), +(2, 1, "Syntax"), +(2, 2, "Client"), +(2, 3, "Server"), +(3, 0, "Intermediate"), +(3, 1, "Complex queries"), +(3, 2, "Stored Procedures"), +(3, 3, "Stored Functions"), +(4, 0, "Advanced"), +(4, 1, "Replication"), +(4, 2, "Load balancing"), +(4, 3, "High availability"), +(5, 0, "Conclusion"); + +select * from table_24562; + +alter table table_24562 add column reviewer varchar(20), +order by title; + +select * from table_24562; + +update table_24562 set reviewer="Me" where section=2; +update table_24562 set reviewer="You" where section=3; + +alter table table_24562 +order by section ASC, subsection DESC; + +select * from table_24562; + +alter table table_24562 +order by table_24562.subsection ASC, table_24562.section DESC; + +select * from table_24562; + +--error 1064 +alter table table_24562 order by 12; +--error 1064 +alter table table_24562 order by (section + 12); +--error 1064 +alter table table_24562 order by length(title); +--error 1064 +alter table table_24562 order by (select 12 from dual); + +--error 1054 +alter table table_24562 order by no_such_col; + +drop table table_24562; + # End of 4.1 tests + diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 30283a47b21..ee57a4d611c 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -2021,7 +2021,7 @@ alter_list_item: lex->alter_info.is_simple= 0; lex->alter_info.flags|= ALTER_OPTIONS; } - | order_clause + | alter_order_clause { LEX *lex=Lex; lex->alter_info.is_simple= 0; @@ -3781,6 +3781,29 @@ olap_opt: ; /* + Order by statement in ALTER TABLE +*/ + +alter_order_clause: + ORDER_SYM BY alter_order_list + ; + +alter_order_list: + alter_order_list ',' alter_order_item + | alter_order_item + ; + +alter_order_item: + simple_ident order_dir + { + THD *thd= YYTHD; + bool ascending= ($2 == 1) ? true : false; + if (add_order_to_list(thd, $1, ascending)) + YYABORT; + } + ; + +/* Order by statement in select */ |