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

Result lost caused by key() [10.5.3,10.11.1]

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5.3, 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 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS F
      LSE); -- sql1
      SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS
      FALSE); -- sql2
      

      Because the condition >=ANY in sql2 is more relaxed than the condition =ANY in sql1.

      However, MariaDB tells us the result of sql2(empty) ⊆ result of sql1(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 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql1
      +---+
      | 1 |
      +---+
      | 1 |
      +---+
      1 row in set (0.01 sec)
       
      mysql> SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql2
      Empty set (0.00 sec)
      

      How to repeat:

      drop table if exists t;
      CREATE TABLE t (c1 FLOAT,c2 VARCHAR(20),key(c1));
      INSERT INTO t VALUES (94.1106,'-0'),(1,'3	'),(0.0001,'-1');
       
      SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql1
      SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql2
      

      Hope these can be helpful for your debugging:
      1. The bug cannot be reproduced after removing key(c1);
      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:10.5.3, it cannot be reproduced in mariadb:10.5.2:

      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 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql1
      +---+
      | 1 |
      +---+
      | 1 |
      +---+
      1 row in set (0.000 sec)
       
      MariaDB [TEST]> SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql2
      Empty set (0.000 sec)
       
      MariaDB [TEST]> select version();
      +--------------------------------------+
      | version()                            |
      +--------------------------------------+
      | 10.5.2-MariaDB-1:10.5.2+maria~bionic |
      +--------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [TEST]> SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql1
      +---+
      | 1 |
      +---+
      | 1 |
      | 1 |
      | 1 |
      +---+
      3 rows in set (0.000 sec)
       
      MariaDB [TEST]> SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql2
      +---+
      | 1 |
      +---+
      | 1 |
      | 1 |
      | 1 |
      +---+
      3 rows in set (0.000 sec)
      

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            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.