diff options
| -rw-r--r-- | mysql-test/r/derived_cond_pushdown.result | 30 | ||||
| -rw-r--r-- | mysql-test/r/func_time.result | 17 | ||||
| -rw-r--r-- | mysql-test/t/derived_cond_pushdown.test | 28 | ||||
| -rw-r--r-- | mysql-test/t/func_time.test | 8 | ||||
| -rw-r--r-- | sql/item_timefunc.cc | 20 | ||||
| -rw-r--r-- | sql/item_timefunc.h | 25 |
6 files changed, 104 insertions, 24 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 # diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index 394552f219e..b7455f36d1b 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -1090,17 +1090,21 @@ longlong Item_func_yearweek::val_int() } -longlong Item_func_weekday::val_int() +static uint weekday_from_item(Item *item, bool *null_value, bool week_starts_on_sunday) { - DBUG_ASSERT(fixed == 1); MYSQL_TIME ltime; - - if (get_arg0_date(<ime, TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE)) + if ((*null_value= item->get_date_with_conversion(<ime, TIME_NO_ZERO_DATE | + TIME_NO_ZERO_IN_DATE))) return 0; + return calc_weekday(calc_daynr(ltime.year, ltime.month, ltime.day), week_starts_on_sunday) + + MY_TEST(week_starts_on_sunday); +} - return (longlong) calc_weekday(calc_daynr(ltime.year, ltime.month, - ltime.day), - odbc_type) + MY_TEST(odbc_type); + +longlong Item_func_weekday::val_int() +{ + DBUG_ASSERT(fixed == 1); + return (longlong) weekday_from_item(args[0], &null_value, odbc_type); } bool Item_func_dayname::fix_length_and_dec() @@ -1119,7 +1123,7 @@ bool Item_func_dayname::fix_length_and_dec() String* Item_func_dayname::val_str(String* str) { DBUG_ASSERT(fixed == 1); - uint weekday=(uint) val_int(); // Always Item_func_weekday() + uint weekday= weekday_from_item(args[0], &null_value, false); const char *day_name; uint err; diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index e37438947dd..c89b6921796 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -410,26 +410,17 @@ public: }; -class Item_func_weekday :public Item_func +class Item_func_weekday :public Item_int_func { bool odbc_type; public: Item_func_weekday(THD *thd, Item *a, bool type_arg): - Item_func(thd, a), odbc_type(type_arg) { collation.set_numeric(); } + Item_int_func(thd, a), odbc_type(type_arg) { } longlong val_int(); - double val_real() { DBUG_ASSERT(fixed == 1); return (double) val_int(); } - String *val_str(String *str) - { - DBUG_ASSERT(fixed == 1); - str->set(val_int(), &my_charset_bin); - return null_value ? 0 : str; - } const char *func_name() const { return (odbc_type ? "dayofweek" : "weekday"); } - enum Item_result result_type () const { return INT_RESULT; } - enum_field_types field_type() const { return MYSQL_TYPE_LONGLONG; } bool fix_length_and_dec() { decimals= 0; @@ -447,21 +438,25 @@ public: { return get_item_copy<Item_func_weekday>(thd, mem_root, this); } }; -class Item_func_dayname :public Item_func_weekday +class Item_func_dayname :public Item_str_func { MY_LOCALE *locale; public: - Item_func_dayname(THD *thd, Item *a): Item_func_weekday(thd, a, 0) {} + Item_func_dayname(THD *thd, Item *a): Item_str_func(thd, a) {} const char *func_name() const { return "dayname"; } String *val_str(String *str); - enum Item_result result_type () const { return STRING_RESULT; } - enum_field_types field_type() const { return MYSQL_TYPE_VARCHAR; } bool fix_length_and_dec(); bool check_partition_func_processor(void *int_arg) {return TRUE;} bool check_vcol_func_processor(void *arg) { return mark_unsupported_function(func_name(), "()", arg, VCOL_SESSION_FUNC); } + bool check_valid_arguments_processor(void *int_arg) + { + return !has_date_args(); + } + Item *get_copy(THD *thd, MEM_ROOT *mem_root) + { return get_item_copy<Item_func_dayname>(thd, mem_root, this); } }; |