Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts)

WL#3681 (ALTER TABLE ORDER BY)

Before this fix, the ALTER TABLE statement implemented an ORDER BY option
with the following characteristics :

1) The order by clause accepts a list of criteria, with optional ASC or
DESC keywords

2) Each criteria can be a general expression, involving operators,
native functions, stored functions, user defined functions, subselects ...

With this fix :

1) has been left unchanged, since it's a de-facto existing feature,
that was already present in the code base and partially covered in the test
suite. Code coverage for ASC and DESC was missing and has been improved.

2) has been changed to limit the kind of criteria that are permissible:
now only a column name is valid.


mysql-test/r/alter_table.result:
  Prevent ALTER TABLE ORDER BY clauses to use general expressions.
mysql-test/t/alter_table.test:
  Prevent ALTER TABLE ORDER BY clauses to use general expressions.
sql/sql_yacc.yy:
  Prevent ALTER TABLE ORDER BY clauses to use general expressions.

1 files changed, 66 insertions, 0 deletions

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
+