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

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

    XMLWordPrintable

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

          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.