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

Value changed after relaxing HAVING condition [10.5.4,10.11.1]

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5.4, 10.11.1, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
    • 10.5, 10.6
    • Optimizer
    • ubuntu 18.04

    Description

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

      SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING !(`f1`=1); -- sql1
      SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING 1; -- sql2
      

      Because the 'HAVING 1' in sql2 is always true, but the 'HAVING !(`f1`=1)' in sql1 may not be true.

      However, the value `14757395258967642091` changed to `14757395258987761147` after relaxing HAVING condition, 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 (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING !(`f1`=1); -- sql1
      +----------------------+
      | f1                   |
      +----------------------+
      | 14757395258967642091 |
      +----------------------+
      1 row in set, 2 warnings (0.01 sec)
       
      mysql> SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING 1; -- sql2
      +----------------------+
      | f1                   |
      +----------------------+
      | 14757395258987761147 |
      +----------------------+
      1 row in set (0.00 sec)
      

      How to repeat:

      drop table if exists t;
      CREATE TABLE t (c1 VARCHAR(20));
      INSERT INTO t VALUES ('\n3');
       
      SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING !(`f1`=1); -- sql1
      SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING 1; -- sql2
      

      Hope these can be helpful for your debugging:
      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:10.5.4, it cannot be reproduced in mariadb:10.5.3:

      MariaDB [TEST]> select version();
      +-------------------------------------+
      | version()                           |
      +-------------------------------------+
      | 10.5.4-MariaDB-1:10.5.4+maria~focal |
      +-------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [TEST]> SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING !(`f1`=1); -- sql1
      +----------------------+
      | f1                   |
      +----------------------+
      | 14757395258967642091 |
      +----------------------+
      1 row in set, 2 warnings (0.000 sec)
       
      MariaDB [TEST]> SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING 1; -- sql2
      +----------------------+
      | f1                   |
      +----------------------+
      | 14757395258987761147 |
      +----------------------+
      1 row in set (0.000 sec)
       
      MariaDB [TEST]> select version();
      +-------------------------------------+
      | version()                           |
      +-------------------------------------+
      | 10.5.3-MariaDB-1:10.5.3+maria~focal |
      +-------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [TEST]> SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING !(`f1`=1); -- sql1
      +---------------------+
      | f1                  |
      +---------------------+
      | 9223372036854775807 |
      +---------------------+
      1 row in set, 4 warnings (0.000 sec)
       
      MariaDB [TEST]> SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING 1; -- sql2
      +---------------------+
      | f1                  |
      +---------------------+
      | 9223372036854775807 |
      +---------------------+
      1 row in set, 1 warning (0.000 sec)
      

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            qaqcatz qaqcatz
            Votes:
            0 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.