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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.4.4
    • Component/s: Optimizer
    • Labels:
      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

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

                Dates

                Created:
                Updated:
                Resolved: