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

order by with incremented variable behaviour is different in 10.6

    XMLWordPrintable

    Details

      Description

      Testcase:

      use d1;
       
      drop table if exists t1 ;
       
      create table t1 (id1 int, id2 int, id3 float);
       
      INSERT INTO `t1` (
      with recursive series as (
      select 1 as id union all
      select id +1 as id from series
      where id < 10)
      select FLOOR(RAND()*(3-1+1))+1,FLOOR(RAND()*(2-1+1))+1 ,(FLOOR(RAND()*(100-1+1))+1)/10 from series);
       
      SELECT b.*,
      (@currank := @currank + 1) AS peringkat
      FROM ( select id1,id2,id3 from t1 a, (SELECT @curRank := 0) r ) b
      ORDER BY id1 desc,id2 desc,id3 desc; 
      

      with 10.3, 10.4 and 10.5 the order if the variable (peringkat) follow the final "order by" clause

      +------+------+------+-----------+
      | id1  | id2  | id3  | peringkat |
      +------+------+------+-----------+
      |    3 |    1 |  5.9 |         1 |
      |    3 |    1 |  2.9 |         2 |
      |    2 |    2 |  8.6 |         3 |
      |    2 |    2 |  6.8 |         4 |
      |    2 |    1 |  7.6 |         5 |
      |    1 |    2 |  7.2 |         6 |
      |    1 |    2 |  3.5 |         7 |
      |    1 |    2 |  0.2 |         8 |
      |    1 |    1 |  7.7 |         9 |
      |    1 |    1 |  2.2 |        10 |
      +------+------+------+-----------+
      10 rows in set (0.001 sec)
      

      So not in 10.6

      +------+------+------+-----------+
      | id1  | id2  | id3  | peringkat |
      +------+------+------+-----------+
      |    3 |    2 |  5.6 |         1 |
      |    3 |    2 |  5.6 |         4 |
      |    3 |    2 |  4.2 |         6 |
      |    3 |    2 |  3.5 |         5 |
      |    3 |    1 |  9.3 |         8 |
      |    2 |    2 |  7.5 |         9 |
      |    2 |    1 |  2.3 |         7 |
      |    1 |    1 |  8.7 |         2 |
      |    1 |    1 |  7.4 |        10 |
      |    1 |    1 |  2.7 |         3 |
      +------+------+------+-----------+
      10 rows in set (0.001 sec)
      

      workarround with row_number() works

       SELECT b.*,row_number() over (order by id1 desc,id2 desc,id3 desc) as peringkat FROM ( select id1,id2,id3 from t1 a ) b;
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              monty Michael Widenius
              Reporter:
              Richard Richard Stracke
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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