[MDEV-5424] SELECT using ORDER BY DESC and LIMIT produces unexpected results (InnoDB/XtraDB) Created: 2013-12-09  Updated: 2014-01-29  Resolved: 2014-01-02

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.33a, 5.5.34, 10.0.6
Fix Version/s: 5.5.35, 10.0.8

Type: Bug Priority: Critical
Reporter: Mikko Granqvist Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None
Environment:

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;



 Comments   
Comment by Elena Stepanova [ 2013-12-10 ]

Thanks for the perfect test case. Reproducible as described.

The problem was introduced in 5.5 tree with the following revision:

------------------------------------------------------------
revno: 3620
fixes bug: https://mariadb.atlassian.net/browse/MDEV-3992
committer: timour@askmonty.org
branch nick: 5.5
timestamp: Tue 2013-01-15 14:33:08 +0200
message:
Fix for bug MDEV-3992, second attempt

The previous fix for MDEV-3992 was incomplete, because it still computed
incorrectly the number of keyparts of the extended secondary key in the
case when columns of the PK participate in the secondary key.

------------------------------------------------------------

Comment by Michael Widenius [ 2014-01-02 ]

Problem was a wrong test if we should use extended keys or not.
Fix will be in 5.5.35

Comment by Michael Widenius [ 2014-01-02 ]

While fixing this, I noticed that the bug had a side effect that some queries was using extended keys even if they should not.

The effect was that queries that used an order by on a repetition of the primary key, when all the used key parts where constant where optimized to use the key

... WHERE secondary_key=1 ORDER BY pk,pk;

After my fix, this was not the case anymore.

To get things to work as before, I added removal of duplicated elements in ORDER BY.

ORDER BY a,b,a,b -> ORDER BY a,b
ORDER BY a DESC, a, b DESC, b DESC ->ORDER BY a DESC, b DESC

This is similar to what is in MySQL 5.6, except that we can remove any kind of expression.

Comment by Daniel Bartholomew [ 2014-01-29 ]

http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/4010

Generated at Thu Feb 08 07:04:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.