[MDEV-18937] Wrong result caused by equality pushdown from HAVING Created: 2019-03-15  Updated: 2019-04-04  Resolved: 2019-04-04

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4
Fix Version/s: 10.4.4

Type: Bug Priority: Major
Reporter: Galina Shalygina (Inactive) Assignee: Galina Shalygina (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-18769 Assertion `fixed == 1' failed in Item... Closed
Problem/Incident
is caused by MDEV-7486 Condition pushdown from HAVING into W... Closed

 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)



 Comments   
Comment by Galina Shalygina (Inactive) [ 2019-03-25 ]

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.

Comment by Galina Shalygina (Inactive) [ 2019-04-04 ]

Fixed in MDEV-18769

Generated at Thu Feb 08 08:47:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.