Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
Description
No 'Impossible WHERE' message when OR condition with equalities is used.
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); |
|
SELECT t1.a,MAX(t1.b),t1.c |
FROM t1 |
WHERE (t1.a = 1) OR (t1.a = 3) |
GROUP BY t1.a |
HAVING (t1.a = 2); |
|
SELECT t1.a,MAX(t1.b),t1.c |
FROM t1 |
WHERE ((t1.a = 1) OR (t1.a = 3)) AND (t1.a = 2) |
GROUP BY t1.a; |
How it works now:
MariaDB [test]> SELECT t1.a,MAX(t1.b),t1.c FROM t1 WHERE (t1.a = 1) OR (t1.a = 3) GROUP BY t1.a HAVING (t1.a = 2);
|
Empty set (0.001 sec)
|
|
|
MariaDB [test]> explain format=json SELECT t1.a,MAX(t1.b),t1.c FROM t1 WHERE (t1.a = 1) OR (t1.a = 3) GROUP BY t1.a HAVING (t1.a = 2);
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| EXPLAIN |
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 3,
|
"filtered": 100,
|
"attached_condition": "t1.a = 2 and (t1.a = 1 or t1.a = 3)"
|
}
|
}
|
} |
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.002 sec)
|
How it should be:
MariaDB [test]> SELECT t1.a,MAX(t1.b),t1.c
|
-> FROM t1
|
-> WHERE ((t1.a = 1) OR (t1.a = 3)) AND (t1.a = 2)
|
-> GROUP BY t1.a;
|
Empty set (0.001 sec)
|
|
|
MariaDB [test]> explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
-> FROM t1
|
-> WHERE ((t1.a = 1) OR (t1.a = 3)) AND (t1.a = 2)
|
-> GROUP BY t1.a;
|
+---------------------------------------------------------------------------------------------------------+
|
| EXPLAIN |
|
+---------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"message": "Impossible WHERE"
|
}
|
}
|
} |
|
+---------------------------------------------------------------------------------------------------------+
|
1 row in set (0.001 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