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
-
Activity
More anomalies happen because Item_func_dayname erroneously derives from Item_func_weekday.
For some reasons this query returns a non-zero result although 'Saturday'+5 should return 0 with a warning:
SELECT DAYNAME('2019-01-05')+0; |
+-------------------------+
|
| DAYNAME('2019-01-05')+0 |
|
+-------------------------+
|
| 5 |
|
+-------------------------+
|
Note, if I apply the explicit CAST, it does return 0 with a warning, as expected:
SELECT CAST(DAYNAME('2019-01-05') AS SIGNED); |
+---------------------------------------+
|
| CAST(DAYNAME('2019-01-05') AS SIGNED) |
|
+---------------------------------------+
|
| 0 |
|
+---------------------------------------+
|
+---------+------+-----------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-----------------------------------------------+
|
| Warning | 1292 | Truncated incorrect INTEGER value: 'Saturday' |
|
+---------+------+-----------------------------------------------+
|
This inconsistency should be fixed.
Item_func_dayname should derive from Item_int_func rather than from Item_func_weekday.
currently returns correct results on 10.3 (4e9206736c403206915c)-10.10
The problem happens because Item_func_dayname derives from Item_func_weekday but it does not override this method:
Item *get_copy(THD *thd, MEM_ROOT *mem_root)