Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5.33a, 5.5.34, 10.0.6
-
None
-
None
-
Debian Linux x86_64
Description
SELECT using ORDER BY DESC and LIMIT with suitably selected table
structure, indexes and row content produces unexpected results with
InnoDB/XtraDB engine.
(Note: This may be related to (or even duplicate of?) MDEV-4323, but
with quite different test setups its pretty hard to tell especially
when the set of affected engines seem to differ.)
Reproduced on: Linux ***** 3.2.0-4-amd64 #1 SMP Debian 3.2.51-1 x86_64 GNU/Linux
Errors/warnings on log: none
Reproducible at least on MariaDB versions: 5.5.34, 5.5.33a, 5.5.32,
5.5.30, 5.5.29.
Was NOT able to reproduce (using this test setup at least) on MariaDB
versions: 5.5.28a and 5.5.23.
Was not able to reproduce this using MyISAM engine.
Reproducible on MySQL: unknown (so no bug filed for MySQL yet)
Test case to reproduce and results:
1) Used test case
cat mysql-test/t/select_order_by_desc_limit_problem.test
# Test case for ORDER BY DESC and LIMIT.
|
# Both selects should return same result set in same order.
|
|
|
--source include/have_innodb.inc
|
|
|
--disable_warnings
|
drop table if exists t1, t2;
|
--enable_warnings
|
|
|
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);
|
|
|
select a from t1 where b is null order by a desc limit 2;
|
select a from t2 where b is null order by a desc limit 2;
|
|
|
--disable_warnings
|
drop table if exists t1, t2;
|
--enable_warnings
|
2) First expected/correct results on MariaDB 5.5.28a
cd mariadb-5.5.28a-linux-x86_64/mysql-test/
|
./mysql-test-run.pl --embedded-server --record select_order_by_desc_limit_problem
|
cat r/select_order_by_desc_limit_problem.result
|
drop table if exists t1, t2;
|
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);
|
select a from t1 where b is null order by a desc limit 2;
|
a
|
3
|
2
|
select a from t2 where b is null order by a desc limit 2;
|
a
|
3
|
2
|
drop table if exists t1, t2;
|
3) Then unexpected InnoDB results on MariaDB 5.5.29 (and later):
cd mariadb-5.5.29-linux-x86_64/mysql-test/
|
./mysql-test-run.pl --embedded-server --record select_order_by_desc_limit_problem
|
cat r/select_order_by_desc_limit_problem.result
|
drop table if exists t1, t2;
|
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);
|
select a from t1 where b is null order by a desc limit 2;
|
a
|
3
|
2
|
select a from t2 where b is null order by a desc limit 2;
|
a
|
1
|
2
|
drop table if exists t1, t2;
|