Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5424

SELECT using ORDER BY DESC and LIMIT produces unexpected results (InnoDB/XtraDB)

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5.33a, 5.5.34, 10.0.6
    • 5.5.35, 10.0.8
    • 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;

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment - - edited

          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.

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

          elenst Elena Stepanova added a comment - - edited 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. ------------------------------------------------------------
          monty Michael Widenius added a comment -

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

          monty Michael Widenius added a comment - Problem was a wrong test if we should use extended keys or not. Fix will be in 5.5.35
          monty Michael Widenius added a comment -

          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.

          monty Michael Widenius added a comment - 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.
          dbart Daniel Bartholomew added a comment -

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

          dbart Daniel Bartholomew added a comment - http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/4010

          People

            monty Michael Widenius
            mgranqvist Mikko Granqvist
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.