MDEV-21388 Wrong result of DAYNAME()=xxx in combination with condition_pushdown_for_derived=on

4 files changed, 82 insertions, 1 deletions

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index ac16dac73de..1010ed6eccb 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -10551,4 +10551,34 @@ EXPLAIN
 }
 DROP TABLE t1;
 DROP VIEW v1;
+#
+# MDEV-21388 Wrong result of DAYNAME()=xxx in combination with condition_pushdown_for_derived=on
+#
+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;
+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
+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
+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';
+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
+DROP VIEW v1;
+DROP TABLE t1, t2;
+SET optimizer_switch=DEFAULT;
 # End of 10.2 tests
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index b0c69a5334f..a82cf3c19c2 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -159,7 +159,9 @@ date_format('1999-12-31','%x-%v')	date_format('2000-01-01','%x-%v')
 1999-52	1999-52
 select dayname("1962-03-03"),dayname("1962-03-03")+0;
 dayname("1962-03-03")	dayname("1962-03-03")+0
-Saturday	5
+Saturday	0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'Saturday'
 select monthname("1972-03-04"),monthname("1972-03-04")+0;
 monthname("1972-03-04")	monthname("1972-03-04")+0
 March	0
@@ -3508,5 +3510,18 @@ v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VI
 DROP VIEW v1,v2,v3;
 DROP TABLE t1,t2;
 #
+# MDEV-21388 Wrong result of DAYNAME()=xxx in combination with condition_pushdown_for_derived=on
+#
+SELECT DAYNAME('2019-01-05')+0;
+DAYNAME('2019-01-05')+0
+0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'Saturday'
+SELECT CAST(DAYNAME('2019-01-05') AS SIGNED);
+CAST(DAYNAME('2019-01-05') AS SIGNED)
+0
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'Saturday'
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index c9c8fb21ac7..ec15791ddbd 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -2140,4 +2140,32 @@ SELECT * FROM (SELECT 1 FROM v1 UNION (SELECT 1 FROM v1 WHERE @a := uuid())) dt;
 DROP TABLE t1;
 DROP VIEW v1;
 
+--echo #
+--echo # MDEV-21388 Wrong result of DAYNAME()=xxx in combination with condition_pushdown_for_derived=on
+--echo #
+
+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;
+
+SELECT *, dayname(v1.b) FROM v1,t2 WHERE (v1.max_c>214) AND (t2.a>v1.a);
+
+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';
+
+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';
+
+DROP VIEW v1;
+DROP TABLE t1, t2;
+
+SET optimizer_switch=DEFAULT;
+
 --echo # End of 10.2 tests
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 6c5b1cab95f..22729b4300b 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -1970,5 +1970,13 @@ DROP VIEW v1,v2,v3;
 DROP TABLE t1,t2;
 
 --echo #
+--echo # MDEV-21388 Wrong result of DAYNAME()=xxx in combination with condition_pushdown_for_derived=on
+--echo #
+
+SELECT DAYNAME('2019-01-05')+0;
+SELECT CAST(DAYNAME('2019-01-05') AS SIGNED);
+
+
+--echo #
 --echo # End of 10.2 tests
 --echo #