[MDEV-21388] Wrong result of DAYNAME()=xxx in combination with condition_pushdown_for_derived=on Created: 2019-12-24  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Optimizer, Views
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-9197 Pushdown conditions into non-mergeabl... Closed

 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.

 Comments   
Comment by Alexander Barkov [ 2019-12-24 ]

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)

Comment by Alexander Barkov [ 2019-12-24 ]

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.

Comment by Alice Sherepa [ 2022-12-05 ]

currently returns correct results on 10.3 (4e9206736c403206915c)-10.10

Generated at Thu Feb 08 09:06:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.