Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4(EOL)
-
None
Description
WHERE clause after pushdown from HAVING into WHERE should look the same as if the pushed condition was initially in the WHERE clause.
Now consider queries which after pushdown will cause impossible WHERE:
CREATE TABLE t1 (a INT, b INT); |
INSERT INTO t1 VALUES (1,2),(3,2),(5,6),(3,4); |
|
|
--Q1
|
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 WHERE t1.a>3 GROUP BY t1.a HAVING t1.a=3; |
--Q2
|
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 WHERE t1.a=3 GROUP BY t1.a HAVING t1.a<3; |
--Q3
|
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 WHERE t1.a>3 AND t1.a=3 GROUP BY t1.a ; |
Q1 is a query with inequality in WHERE clause and equality in HAVING clause.
Q2 is a query with equality in WHERE clause and inequality in HAVING clause.
Q3 is how should Q1 and Q2 should look if pushdown is made before optimize_cond().
MariaDB [test]> --Q1
|
MariaDB [test]> EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 WHERE t1.a>3 GROUP BY t1.a HAVING t1.a=3;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> --Q2
|
MariaDB [test]> EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 WHERE t1.a=3 GROUP BY t1.a HAVING t1.a<3;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> --Q3
|
MariaDB [test]> EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 WHERE t1.a>3 AND t1.a=3 GROUP BY t1.a ;
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
1 row in set (0.000 sec)
|
|
It can be seen that there are different “Extra” messages. Q3 is said to have impossible after optimize_cond() for WHERE. Q1 and Q2 are said to have impossible WHERE only after reading const tables. So there is difference while it shouldn’t be.
If to look on Q4 and Q5 queries in debugger it can be found out that after pushdown from HAVING into WHERE WHERE clause is looking this way:
3>3 AND a=3
|
|
It can be said on this step that WHERE is impossible and finish processing of these queries. Now queries are processed further until it is found out that they are impossible after reading const tables.
Let’s look at another queries.
Q4 and Q5 have OR condition in WHERE and HAVING respectively.
Q6 is how Q4 and Q5 should look if pushdown is made before optimize_cond().
--Q4
|
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 WHERE (t1.a<2 OR t1.a>3) GROUP BY t1.a HAVING t1.a=3; |
--Q5
|
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 WHERE t1.a=3 GROUP BY t1.a HAVING (t1.a<2 OR t1.a>3); |
--Q6
|
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 WHERE t1.a=3 AND (t1.a<2 OR t1.a>3) GROUP BY t1.a; |
|
MariaDB [test]> --Q4
|
MariaDB [test]> EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 WHERE (t1.a<2 OR t1.a>3) GROUP BY t1.a HAVING t1.a=3;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> --Q5
|
MariaDB [test]> EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 WHERE t1.a=3 GROUP BY t1.a HAVING (t1.a<2 OR t1.a>3);
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> --Q6
|
MariaDB [test]> EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 WHERE t1.a=3 AND (t1.a<2 OR t1.a>3) GROUP BY t1.a;
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
1 row in set (0.000 sec)
|
|
It can be seen that Q5 and Q6 have both “Impossible WHERE”. While Q4 is found to have impossible WHERE only after reading const tables.
Q1,Q2 and Q4 queries should be found to be impossible just after pushdown was made as it is done for Q5