Details
Critical
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;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Problem was a wrong test if we should use extended keys or not.
Fix will be in 5.5.35
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.
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 attemptThe previous fix for
MDEV-3992was incomplete, because it still computedincorrectly the number of keyparts of the extended secondary key in the
case when columns of the PK participate in the secondary key.
------------------------------------------------------------