Below is the list of changes that have just been committed into a local
5.0 repository of marcsql. When marcsql 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, 2007-01-16 18:49:43-07:00, malff@weblab.(none) +3 -0
Bug#24562 (ALTER TABLE ... ORDER BY ... with a stored function in expression
asserts)
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@stripped, 2007-01-16 18:47:35-07:00, malff@weblab.(none) +113 -0
Prevent ALTER TABLE ORDER BY clauses to use general expressions.
mysql-test/t/alter_table.test@stripped, 2007-01-16 18:47:35-07:00, malff@weblab.(none) +67 -0
Prevent ALTER TABLE ORDER BY clauses to use general expressions.
sql/sql_yacc.yy@stripped, 2007-01-16 18:47:35-07:00, malff@weblab.(none) +24 -1
Prevent ALTER TABLE ORDER BY clauses to use general expressions.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: malff
# Host: weblab.(none)
# Root: /home/marcsql/TREE/mysql-5.0-24562
--- 1.501/sql/sql_yacc.yy 2007-01-16 18:49:49 -07:00
+++ 1.502/sql/sql_yacc.yy 2007-01-16 18:49:49 -07:00
@@ -3722,7 +3722,7 @@ alter_list_item:
{
Lex->alter_info.flags|= ALTER_FORCE;
}
- | order_clause
+ | alter_order_clause
{
LEX *lex=Lex;
lex->alter_info.flags|= ALTER_ORDER;
@@ -5941,6 +5941,29 @@ olap_opt:
lex->current_select->olap= ROLLUP_TYPE;
}
;
+
+/*
+ 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_nospvar 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
--- 1.60/mysql-test/r/alter_table.result 2007-01-16 18:49:49 -07:00
+++ 1.61/mysql-test/r/alter_table.result 2007-01-16 18:49:49 -07:00
@@ -713,3 +713,116 @@ create table t1 (t varchar(255) default
engine=myisam default charset=latin1;
alter table t1 change t t text;
drop table t1;
+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;
--- 1.47/mysql-test/t/alter_table.test 2007-01-16 18:49:49 -07:00
+++ 1.48/mysql-test/t/alter_table.test 2007-01-16 18:49:49 -07:00
@@ -548,3 +548,70 @@ create table t1 (t varchar(255) default
engine=myisam default charset=latin1;
alter table t1 change t t text;
drop table t1;
+
+#
+# Bug#24562 (ALTER TABLE ... ORDER BY ... with a stored function in
+# 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 ER_PARSE_ERROR
+alter table table_24562 order by 12;
+--error ER_PARSE_ERROR
+alter table table_24562 order by (section + 12);
+--error ER_PARSE_ERROR
+alter table table_24562 order by length(title);
+--error ER_PARSE_ERROR
+alter table table_24562 order by (select 12 from dual);
+
+--error ER_BAD_FIELD_ERROR
+alter table table_24562 order by no_such_col;
+
+drop table table_24562;
+
| Thread |
|---|
| • bk commit into 5.0 tree (malff:1.2386) BUG#24562 | marc.alff | 17 Jan |
