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

Lost line caused by ORDER BY [5.5.61, 10.11.1]

    XMLWordPrintable

Details

    Description

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

      SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING NOT ((`f1` != 1) IS FALSE) ORDER BY c1; -- sql1
      SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING 1 ORDER BY c1; -- sql2
      

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

      However, I can't find my line 'NULL' after changing 'HAVING NOT ((`f1` != 1) IS FALSE)' to 'HAVING 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 c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING NOT ((`f1` != 1) IS FALSE) ORDER BY c1; -- sql1
      +------+
      | f1   |
      +------+
      | NULL |
      +------+
      1 row in set, 4 warnings (0.00 sec)
       
      mysql> SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING 1 ORDER BY c1; -- sql2
      Empty set, 1 warning (0.00 sec)
      

      How to repeat:

      drop table if exists t;
      CREATE TABLE t (c1 CHAR(20));
      INSERT INTO t VALUES ('1');
       
      SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING NOT ((`f1` != 1) IS FALSE) ORDER BY c1; -- sql1
      SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING 1 ORDER BY c1; -- sql2
      

      Hope these can be helpful for your debugging:
      1. The bug cannot be reproduced after removing ORDER BY;
      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.61, it cannot be reproduced in mariadb:5.5.60:

      MariaDB [TEST]> select version();
      +-------------------------+
      | version()               |
      +-------------------------+
      | 5.5.61-MariaDB-1~trusty |
      +-------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [TEST]> SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING NOT ((`f1` != 1) IS FALSE) ORDER BY c1; -- sql1
      +------+
      | f1   |
      +------+
      | NULL |
      +------+
      1 row in set, 2 warnings (0.00 sec)
       
      MariaDB [TEST]> SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING 1 ORDER BY c1; -- sql2
      Empty set (0.00 sec)
       
      MariaDB [TEST]> select version();
      +-------------------------+
      | version()               |
      +-------------------------+
      | 5.5.60-MariaDB-1~trusty |
      +-------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [TEST]> SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING NOT ((`f1` != 1) IS FALSE) ORDER BY c1; -- sql1
      +------+
      | f1   |
      +------+
      | NULL |
      +------+
      1 row in set, 2 warnings (0.00 sec)
       
      MariaDB [TEST]> SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING 1 ORDER BY c1; -- sql2
      +------+
      | f1   |
      +------+
      | NULL |
      +------+
      1 row in set, 1 warning (0.00 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.