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

Value changed after relaxing WHERE condition [5.5.40, 10.11.1]

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5.40, 10.3, 10.4, 10.11.1, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
    • 10.4, 10.5, 10.6
    • Optimizer
    • ubuntu 18.04

    Description

      Description:
      In theory, the result of sql1 ⊆ the result of sql2:

      SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where f1 != 1; -- sql1
      SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where 1; -- sql2
      

      Because the condition of sql2 (where 1) is always true, but the condition of sql1 (where f1 != 1) may be false.

      However, the value 2 changed to -1.8446744073709552e19 after changing f1 != 1 to 1, seems like a logical bug:

      mysql> select version();
      +-----------------------------------------+
      | version()                               |
      +-----------------------------------------+
      | 10.11.1-MariaDB-1:10.11.1+maria~ubu2204 |
      +-----------------------------------------+
      1 row in set (0.00 sec)
       
      mysql> SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where f1 != 1;
      +------+
      | f1   |
      +------+
      |    2 |
      +------+
      1 row in set (0.00 sec)
       
      mysql> SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where 1;
      +------------------------+
      | f1                     |
      +------------------------+
      | -1.8446744073709552e19 |
      +------------------------+
      1 row in set (0.00 sec)
      

      How to repeat:

      drop table if exists t;
      CREATE TABLE t (c1 FLOAT UNSIGNED);
      INSERT INTO t VALUES (0);
       
      SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where f1 != 1;
      SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where 1;
      

      Hope these can be helpful for your debugging:
      1. The bug cannot be reproduced after deleting '~';
      2. We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/_/mariadb/tags
      We found that the bug first occurred in mariadb:5.5.40, and we can't find any order versions in dockerhub:

      MariaDB [TEST]> select version();
      +-----------------------------+
      | version()                   |
      +-----------------------------+
      | 5.5.40-MariaDB-1~wheezy-log |
      +-----------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [TEST]> SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where f1 != 1;
      +------+
      | f1   |
      +------+
      |    2 |
      +------+
      1 row in set (0.00 sec)
       
      MariaDB [TEST]> SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where 1;
      +------------------------+
      | f1                     |
      +------------------------+
      | -1.8446744073709552e19 |
      +------------------------+
      1 row in set (0.00 sec)
      

      Attachments

        Activity

          People

            serg Sergei Golubchik
            qaqcatz qaqcatz
            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.