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

Wrong result caused by equality pushdown from HAVING

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4
    • Fix Version/s: 10.4.4
    • Component/s: Optimizer
    • Labels:
      None

      Description

      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);
      

      If to push an equality from HAVING that counteracts with equality in WHERE it replaces this equality.

      SELECT t1.a,MAX(t1.b),t1.c
      FROM t1
      WHERE (t1.a = 1)
      GROUP BY t1.a
      HAVING (t1.a = 3);
      

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

      If to push an equality from HAVING that counteracts with equality in WHERE which is a part of AND condition it desappears.

      SELECT t1.a,MAX(t1.b),t1.c
      FROM t1
      WHERE (t1.a = 1) AND (t1.c=4)
      GROUP BY t1.a
      HAVING (t1.a = 3);
      

      MariaDB [test]> SELECT t1.a,MAX(t1.b),t1.c FROM t1 WHERE (t1.a = 1) AND (t1.c = 3) GROUP BY t1.a HAVING (t1.a = 3);
      +------+-----------+------+
      | a    | MAX(t1.b) | c    |
      +------+-----------+------+
      |    1 |        14 |    3 |
      +------+-----------+------+
      1 row in set (0.002 sec)
       
      MariaDB [test]> explain format=json SELECT t1.a,MAX(t1.b),t1.c FROM t1 WHERE (t1.a = 1) AND (t1.c = 3) GROUP BY t1.a HAVING (t1.a = 3);
      +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | EXPLAIN                                                                                                                                                                                                               |
      +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows": 5,
            "filtered": 100,
            "attached_condition": "t1.a = 1 and t1.c = 3"
          }
        }
      } |
      +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.002 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: