|
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)
|
|
|