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

Wrong result caused by equality pushdown from HAVING

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.4
    • 10.4.4
    • Optimizer
    • 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

              shagalla Galina Shalygina
              shagalla Galina Shalygina
              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.