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

Wrong result caused by equality pushdown from HAVING

Details

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

            The bug is caused by the improper processing of equalities pushdown from HAVING into WHERE.
            More specifically, the case when equality from HAVING is merged with some multiple equality from WHERE.

            E.g.:

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

            (t1.a = 3) is pushed from HAVING into WHERE.
            It is merged with (t1.a = 1).
            The result of this merge is (1 = 3 = t1.a) - impossible condition.

            The impossible multiple equality is marked with 'true' value in its field cond_false. Also it is transformed to '0' item. All its fields remain the same.

            In substitute_for_best_equal_field() for conds eliminate_item_equal() is called for the impossible equality. In this method impossible equality is either transformed back to the equality from WHERE from which it was got (if this equality from WHERE is a part of AND condition) or to the equality from HAVING from which it was got (if WHERE is a single equality).

            This causes wrong result.

            To fix it and_new_conditions_to_optimized_cond() should be changed.

            shagalla Galina Shalygina (Inactive) added a comment - The bug is caused by the improper processing of equalities pushdown from HAVING into WHERE. More specifically, the case when equality from HAVING is merged with some multiple equality from WHERE. E.g.: SELECT t1.a, MAX (t1.b),t1.c FROM t1 WHERE (t1.a = 1) GROUP BY t1.a HAVING (t1.a = 3); (t1.a = 3) is pushed from HAVING into WHERE. It is merged with (t1.a = 1). The result of this merge is (1 = 3 = t1.a) - impossible condition. The impossible multiple equality is marked with 'true' value in its field cond_false. Also it is transformed to '0' item. All its fields remain the same. In substitute_for_best_equal_field() for conds eliminate_item_equal() is called for the impossible equality. In this method impossible equality is either transformed back to the equality from WHERE from which it was got (if this equality from WHERE is a part of AND condition) or to the equality from HAVING from which it was got (if WHERE is a single equality). This causes wrong result. To fix it and_new_conditions_to_optimized_cond() should be changed.
            shagalla Galina Shalygina (Inactive) added a comment - Fixed in MDEV-18769

            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.