From c050b5fdf9564e5ffd98ff381c61504efdf69f99 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Thu, 2 Jan 2014 15:51:02 +0200 Subject: Fixed MDEV-5424: SELECT using ORDER BY DESC and LIMIT produces unexpected results (InnoDB/XtraDB) This only happend when using an ORDER BY on a primary key part, where all other key parts where constant. Remove of duplicated expressions in ORDER BY (as the old code did this in some strange cases) mysql-test/r/group_by.result: Fixed results to take into account that duplicate order by parts are now deleted mysql-test/r/group_by_innodb.result: Ensure extended keys are on mysql-test/r/innodb_ext_key.result: More tests mysql-test/r/order_by.result: More tests mysql-test/t/group_by.test: Fixed results to take into account that duplicate order by parts are now deleted mysql-test/t/group_by_innodb.test: Ensure extended keys are on mysql-test/t/innodb_ext_key.test: More tests mysql-test/t/order_by.test: More tests sql/sql_select.cc: Fixed bug where we looked at extended key parts when we shouldn't Remove of duplicated expressions in ORDER BY sql/table.cc: Indentation fixes --- mysql-test/t/group_by.test | 14 ++++++++------ mysql-test/t/group_by_innodb.test | 6 ++++++ mysql-test/t/innodb_ext_key.test | 28 ++++++++++++++++++++++++++++ mysql-test/t/order_by.test | 3 +++ 4 files changed, 45 insertions(+), 6 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 1ef514d591d..e4120f284de 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1336,7 +1336,7 @@ INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10), (11),(12),(13),(14),(15),(16),(17),(18),(19),(20); let $query0=SELECT col1 AS field1, col1 AS field2 - FROM t1 GROUP BY field1, field2; + FROM t1 GROUP BY field1, field2+0; # Needs to be range to exercise bug --eval EXPLAIN $query0; @@ -1366,7 +1366,9 @@ LIMIT 3; explain select col1 f1, col1 f2 from t1 order by f2, f1; -select col1 f1, col1 f2 from t1 order by f2, f1; +explain +select col1 f1, col1 f2 from t1 order by f2, f1+0; +select col1 f1, col1 f2 from t1 order by f2, f1+0; explain select col1 f1, col1 f2 from t1 group by f2 order by f2, f1; @@ -1386,12 +1388,12 @@ INSERT INTO t2(col1, col2) VALUES (11,10),(12,9),(13,8),(14,7),(15,6),(16,5),(17,4),(18,3),(19,2),(20,1); explain -select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3; -select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3; +select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3+0; +select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3+0; explain -select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3; -select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3; +select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3+0; +select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3+0; DROP VIEW v1; DROP TABLE t1, t2; diff --git a/mysql-test/t/group_by_innodb.test b/mysql-test/t/group_by_innodb.test index 0d5e5e9ae30..9733610dde1 100644 --- a/mysql-test/t/group_by_innodb.test +++ b/mysql-test/t/group_by_innodb.test @@ -4,6 +4,8 @@ --source include/have_innodb.inc +set @save_ext_key_optimizer_switch=@@optimizer_switch; + --echo # --echo # MDEV-3992 Server crash or valgrind errors in test_if_skip_sort_order/test_if_cheaper_ordering --echo # on GROUP BY with indexes on InnoDB table @@ -15,6 +17,8 @@ CREATE TABLE t1 ( KEY (pk) ) ENGINE=InnoDB; +set optimizer_switch='extended_keys=on'; + INSERT INTO t1 VALUES (1,'a'),(2,'b'); EXPLAIN @@ -36,3 +40,5 @@ WHERE a = 'r' OR pk = 183 GROUP BY field1, field1; drop table t1; --echo End of 5.5 tests + +set optimizer_switch=@save_ext_key_optimizer_switch; diff --git a/mysql-test/t/innodb_ext_key.test b/mysql-test/t/innodb_ext_key.test index 36414105917..6d1890ac1f9 100644 --- a/mysql-test/t/innodb_ext_key.test +++ b/mysql-test/t/innodb_ext_key.test @@ -662,6 +662,34 @@ ORDER BY rev_timestamp ASC LIMIT 10; DROP TABLE t1,t2,t3; +--echo # +--echo # MDEV-5424 SELECT using ORDER BY DESC and LIMIT produces unexpected +--echo # results (InnoDB/XtraDB) +--echo # + +create table t1 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = myisam default character set utf8; +create table t2 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = innodb default character set utf8; + +insert into t1 (b) values (null), (null), (null); +insert into t2 (b) values (null), (null), (null); + +set optimizer_switch='extended_keys=on'; +explain select a from t1 where b is null order by a desc limit 2; +select a from t1 where b is null order by a desc limit 2; +explain select a from t2 where b is null order by a desc limit 2; +select a from t2 where b is null order by a desc limit 2; +set optimizer_switch='extended_keys=off'; +explain select a from t2 where b is null order by a desc limit 2; +select a from t2 where b is null order by a desc limit 2; + +explain select a from t2 where b is null order by a desc; +select a from t2 where b is null order by a desc; + +explain select a from t2 where b is null order by a desc,a,a; +select a from t2 where b is null order by a desc,a,a; + +drop table t1, t2; + set optimizer_switch=@save_optimizer_switch; set optimizer_switch=@save_ext_key_optimizer_switch; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 3e20d22d726..35f1a7f744b 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -261,6 +261,9 @@ desc,b desc; explain select * from t1 where a = 2 and b > 0 order by a desc,b desc; explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; explain select * from t1 where a = 1 order by b desc; +explain select * from t1 where a = 2 and b > 0 order by a desc,b desc,b,a; +explain select * from t1 where a = 2 and b < 2 order by a desc,a,b desc,a,b; + select * from t1 where a = 1 order by b desc; # # Test things when we don't have NULL keys -- cgit v1.2.1