Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4(EOL)
-
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
- is caused by
-
MDEV-7486 Condition pushdown from HAVING into WHERE
-
- Closed
-
- is part of
-
MDEV-18769 Assertion `fixed == 1' failed in Item_cond_or::val_int
-
- Closed
-
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.:
(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.