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

Value changed after adding DISTINCT [10.1.10, 10.11.1]

    XMLWordPrintable

Details

    Description

      Description:
      In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:

      SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`); -- sql1
      SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT DISTINCT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`); -- sql2
      

      However, the value 20000120000000 changed to 2001 after adding DISTINCT, 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 (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`);
      +----------------+
      | f3             |
      +----------------+
      | 20000120000000 |
      | 20000120000000 |
      +----------------+
      2 rows in set, 4 warnings (0.00 sec)
       
      mysql> SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT DISTINCT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`);
      +------+
      | f3   |
      +------+
      | 2001 |
      | 2001 |
      +------+
      2 rows in set, 4 warnings (0.00 sec)
      

      How to repeat:

      drop table if exists t;
      CREATE TABLE t (c1 DECIMAL(40,20));
      INSERT INTO t VALUES (-0),(120);
       
      SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`);
      SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT DISTINCT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`);
      

      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.1.10, it cannot be reproduced in mariadb:10.1.2 (we can't find more intermediate versions in dockerhub):

      MariaDB [TEST]> select version();
      +--------------------------+
      | version()                |
      +--------------------------+
      | 10.1.10-MariaDB-1~jessie |
      +--------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [TEST]> SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`);
      +----------------+
      | f3             |
      +----------------+
      | 20000120000000 |
      +----------------+
      1 row in set, 1 warning (0.00 sec)
       
      MariaDB [TEST]> SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT DISTINCT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`);
      +------+
      | f3   |
      +------+
      | 2001 |
      +------+
      1 row in set, 2 warnings (0.00 sec)
       
      MariaDB [TEST]> select version();
      +-----------------------------------+
      | version()                         |
      +-----------------------------------+
      | 10.1.2-MariaDB-1~wheezy-wsrep-log |
      +-----------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [TEST]> SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`);
      Empty set, 8 warnings (0.00 sec)
       
      MariaDB [TEST]> SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT DISTINCT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`);
      Empty set, 4 warnings (0.00 sec)
      

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              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.