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

having variable assigned with order by field provides different result

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.0.21, 10.0, 10.1
    • Fix Version/s: 10.2
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      ubuntu/trusty64 - vagrant base box,
      $ uname -r
      3.13.0-65-generic

      Description

      Not sure whether it's a bug or just specifics of mariaDB internals, but it affects calculations:

      CREATE TABLE `test` (
        `col1` int(11) DEFAULT NULL
      ) ENGINE=InnoDB;
       
      INSERT INTO test VALUES(1), (10), (2), (100), (5);
       
      SET @rank=0;
       
      SELECT IF(col1=2, col1+1, col1+2)  as result, @rank:=@rank+1 FROM test ORDER BY result;
      #+--------+----------------+
      #| result | @rank:=@rank+1 |
      #+--------+----------------+
      #|      3 |              1 |
      #|      3 |              2 |
      #|      7 |              3 |
      #|     12 |              4 |
      #|    102 |              5 |
      #+--------+----------------+
       
       # however when variable is set with order by column
       
      SET @rank=0, @prev_val_placeholder=0;
       
      SELECT @prev_val_placeholder:=IF(col1=2, col1+1, col1+2)  as result, @rank:=@rank+1 FROM test ORDER BY result;
      #+--------+----------------+
      #| result | @rank:=@rank+1 |
      #+--------+----------------+
      #|      3 |              1 |
      #|      3 |              3 |
      #|      7 |              5 |
      #|     12 |              2 |
      #|    102 |              4 |
      #+--------+----------------+

      when running the same queries in mysql (5.5.44 ) getting results as expected:

      SELECT @prev_val_placeholder:=IF(col1=2, col1+1, col1+2)  as result, @rank:=@rank+1 FROM test ORDER BY result;
      +--------+----------------+
      | result | @rank:=@rank+1 |
      +--------+----------------+
      |      3 |              1 |
      |      3 |              2 |
      |      7 |              3 |
      |     12 |              4 |
      |    102 |              5 |
      +--------+----------------+

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            ak Anna
            Votes:
            1 Vote for this issue
            Watchers:
            3 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.