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