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

No 'Impossible WHERE' message after pushdown when OR cond is used

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 10.4.4
    • Optimizer
    • None

    Description

      No 'Impossible WHERE' message when OR condition with equalities is used.

      CREATE TABLE t1(a INT, b INT, c INT);
      INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2);
       
      SELECT t1.a,MAX(t1.b),t1.c
      FROM t1
      WHERE (t1.a = 1) OR (t1.a = 3)
      GROUP BY t1.a
      HAVING (t1.a = 2);
       
      SELECT t1.a,MAX(t1.b),t1.c
      FROM t1
      WHERE ((t1.a = 1) OR (t1.a = 3)) AND (t1.a = 2)
      GROUP BY t1.a;
      

      How it works now:

      MariaDB [test]> SELECT t1.a,MAX(t1.b),t1.c FROM t1 WHERE (t1.a = 1) OR (t1.a = 3) GROUP BY t1.a HAVING (t1.a = 2);
      Empty set (0.001 sec)
       
       
      MariaDB [test]> explain format=json SELECT t1.a,MAX(t1.b),t1.c FROM t1 WHERE (t1.a = 1) OR (t1.a = 3) GROUP BY t1.a HAVING (t1.a = 2);
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | EXPLAIN                                                                                                                                                                                                                             |
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows": 3,
            "filtered": 100,
            "attached_condition": "t1.a = 2 and (t1.a = 1 or t1.a = 3)"
          }
        }
      } |
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.002 sec)
      

      How it should be:

      MariaDB [test]> SELECT t1.a,MAX(t1.b),t1.c
          -> FROM t1
          -> WHERE ((t1.a = 1) OR (t1.a = 3)) AND (t1.a = 2)
          -> GROUP BY t1.a;
      Empty set (0.001 sec)
       
       
      MariaDB [test]> explain format=json SELECT t1.a,MAX(t1.b),t1.c
          -> FROM t1
          -> WHERE ((t1.a = 1) OR (t1.a = 3)) AND (t1.a = 2)
          -> GROUP BY t1.a;
      +---------------------------------------------------------------------------------------------------------+
      | EXPLAIN                                                                                                 |
      +---------------------------------------------------------------------------------------------------------+
      | {
        "query_block": {
          "select_id": 1,
          "table": {
            "message": "Impossible WHERE"
          }
        }
      } |
      +---------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
      
      

      Attachments

        Issue Links

          Activity

            People

              shagalla Galina Shalygina (Inactive)
              shagalla Galina Shalygina (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.