Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
Description
I create two tables and a view as follows:
DROP TABLE IF EXISTS t1, t2; |
DROP VIEW IF EXISTS v1; |
|
|
CREATE TABLE t1 (a INT, b DATE, c INT); |
INSERT INTO t1 VALUES |
(1,'2001-01-21',345), |
(6,'2001-01-20',315), |
(6,'2001-01-20',214); |
|
|
CREATE TABLE t2 (a INT, b INT); |
INSERT INTO t2 VALUES (2,19), (7,20); |
CREATE VIEW v1 AS SELECT a, b, max(c) AS max_c FROM t1 |
GROUP BY a,b HAVING max_c < 707; |
and run this query:
SELECT *, dayname(v1.b) FROM v1,t2 WHERE (v1.max_c>214) AND (t2.a>v1.a); |
+------+------------+-------+------+------+---------------+
|
| a | b | max_c | a | b | dayname(v1.b) |
|
+------+------------+-------+------+------+---------------+
|
| 1 | 2001-01-21 | 345 | 2 | 19 | Sunday |
|
| 1 | 2001-01-21 | 345 | 7 | 20 | Sunday |
|
| 6 | 2001-01-20 | 315 | 7 | 20 | Saturday |
|
+------+------------+-------+------+------+---------------+
|
So far so good.
Now I add and extra condition to select only Sunday records:
SET optimizer_switch='condition_pushdown_for_derived=off'; |
SELECT *, dayname(v1.b) FROM v1,t2 WHERE (v1.max_c>214) AND (t2.a>v1.a) AND dayname(v1.b)='Sunday'; |
+------+------------+-------+------+------+---------------+
|
| a | b | max_c | a | b | dayname(v1.b) |
|
+------+------------+-------+------+------+---------------+
|
| 1 | 2001-01-21 | 345 | 2 | 19 | Sunday |
|
| 1 | 2001-01-21 | 345 | 7 | 20 | Sunday |
|
+------+------------+-------+------+------+---------------+
|
Everything is still fine.
Now I change the optimizer switch and run the same query:
SET optimizer_switch='condition_pushdown_for_derived=on'; |
SELECT *, dayname(v1.b) FROM v1,t2 WHERE (v1.max_c>214) AND (t2.a>v1.a) AND dayname(v1.b)='Sunday'; |
It retuns empty set with the following warning:
+---------+------+--------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+--------------------------------------------+
|
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Sunday' |
|
+---------+------+--------------------------------------------+
|
The result of the last query looks wrong. It should return two records, like the previous query does.
Attachments
Issue Links
- relates to
-
MDEV-9197 Pushdown conditions into non-mergeable views/derived tables
-
- Closed
-