temporal-related changes. don't apply sql_mode flags on the lower level (str_to_datetime),

do it on the upper level, in items that return temporal values.

update tests results to match 5.6 better and to fix old bugs.

9 files changed, 35 insertions, 30 deletions

diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result
index 925fb38a0f8..ed7397c2b4f 100644
--- a/mysql-test/r/dyncol.result
+++ b/mysql-test/r/dyncol.result
@@ -1036,9 +1036,7 @@ Warnings:
 Warning	1292	Truncated incorrect time value: '2011-13-01 8:46:06.23434'
 select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as time);
 column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as time)
-NULL
-Warnings:
-Warning	1292	Truncated incorrect time value: '2011-02-30 8:46:06.23434'
+08:46:06
 select column_get(column_create(1, "2001-02-03"), 1 as time);
 column_get(column_create(1, "2001-02-03"), 1 as time)
 00:20:01
diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result
index fa3d37c08c2..16b66bf5cbf 100644
--- a/mysql-test/r/func_sapdb.result
+++ b/mysql-test/r/func_sapdb.result
@@ -62,7 +62,9 @@ datediff("1997-11-30 23:59:59.000001","1997-12-31")
 SET @@SQL_MODE="ALLOW_INVALID_DATES";
 select datediff("1997-11-31 23:59:59.000001","1997-12-31");
 datediff("1997-11-31 23:59:59.000001","1997-12-31")
--30
+NULL
+Warnings:
+Warning	1292	Incorrect datetime value: '1997-11-31 23:59:59.000001'
 SET @@SQL_MODE="";
 select datediff("1997-11-31 23:59:59.000001","1997-12-31");
 datediff("1997-11-31 23:59:59.000001","1997-12-31")
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 4c8b0dc222b..9b2b396dd0a 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -4175,22 +4175,24 @@ str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
 set SQL_MODE=TRADITIONAL;
 select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
 str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
-0
+NULL
 Warnings:
-Warning	1292	Incorrect datetime value: '2007-10-00 12:34'
+Warning	1411	Incorrect datetime value: '2007-10-00 12:34' for function str_to_date
 select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
 str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
 0
-Warnings:
-Warning	1292	Incorrect datetime value: '2007-10-00 12:34'
 select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
 str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'
-0
+NULL
+Warnings:
+Warning	1411	Incorrect datetime value: '2007-10-00 12:34' for function str_to_date
 select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
                                                 and '2007/10/20';
 str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
                                                 and '2007/10/20'
-1
+NULL
+Warnings:
+Warning	1411	Incorrect datetime value: '2007-10-00' for function str_to_date
 set SQL_MODE=DEFAULT;
 select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
 str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 4c8c9f21897..950762a6855 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -4186,22 +4186,24 @@ str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
 set SQL_MODE=TRADITIONAL;
 select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
 str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
-0
+NULL
 Warnings:
-Warning	1292	Incorrect datetime value: '2007-10-00 12:34'
+Warning	1411	Incorrect datetime value: '2007-10-00 12:34' for function str_to_date
 select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
 str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
 0
-Warnings:
-Warning	1292	Incorrect datetime value: '2007-10-00 12:34'
 select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
 str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'
-0
+NULL
+Warnings:
+Warning	1411	Incorrect datetime value: '2007-10-00 12:34' for function str_to_date
 select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
                                                 and '2007/10/20';
 str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
                                                 and '2007/10/20'
-1
+NULL
+Warnings:
+Warning	1411	Incorrect datetime value: '2007-10-00' for function str_to_date
 set SQL_MODE=DEFAULT;
 select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
 str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index 4c8b0dc222b..9b2b396dd0a 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -4175,22 +4175,24 @@ str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
 set SQL_MODE=TRADITIONAL;
 select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
 str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
-0
+NULL
 Warnings:
-Warning	1292	Incorrect datetime value: '2007-10-00 12:34'
+Warning	1411	Incorrect datetime value: '2007-10-00 12:34' for function str_to_date
 select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
 str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
 0
-Warnings:
-Warning	1292	Incorrect datetime value: '2007-10-00 12:34'
 select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
 str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'
-0
+NULL
+Warnings:
+Warning	1411	Incorrect datetime value: '2007-10-00 12:34' for function str_to_date
 select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
                                                 and '2007/10/20';
 str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
                                                 and '2007/10/20'
-1
+NULL
+Warnings:
+Warning	1411	Incorrect datetime value: '2007-10-00' for function str_to_date
 set SQL_MODE=DEFAULT;
 select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
 str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'
diff --git a/sql/item.h b/sql/item.h
index 2d6cf68d818..5dd49d9c6b2 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1041,7 +1041,7 @@ public:
                        Item **ref, bool skip_registered);
   virtual bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate);
   bool get_time(MYSQL_TIME *ltime)
-  { return get_date(ltime, TIME_TIME_ONLY | TIME_FUZZY_DATE); }
+  { return get_date(ltime, TIME_TIME_ONLY | TIME_FUZZY_DATE | TIME_INVALID_DATES); }
   bool get_seconds(ulonglong *sec, ulong *sec_part);
   virtual bool get_date_result(MYSQL_TIME *ltime, ulonglong fuzzydate)
   { return get_date(ltime,fuzzydate); }
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 3decf076f4f..6ea58dfd2c9 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -4114,10 +4114,9 @@ bool Item_func_dyncol_create::prepare_arguments(bool force_names_arg)
       }
       break;
     case DYN_COL_DATETIME:
-      args[valpos]->get_date(&vals[i].x.time_value, TIME_FUZZY_DATE);
-      break;
     case DYN_COL_DATE:
-      args[valpos]->get_date(&vals[i].x.time_value, TIME_FUZZY_DATE);
+      args[valpos]->get_date(&vals[i].x.time_value,
+                             TIME_FUZZY_DATE | sql_mode_for_dates());
       break;
     case DYN_COL_TIME:
       args[valpos]->get_time(&vals[i].x.time_value);
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 011fd06211f..e9a6727162a 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -2433,6 +2433,7 @@ bool Item_time_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
 
 bool Item_date_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
 {
+  fuzzy_date |= sql_mode_for_dates();
   if (get_arg0_date(ltime, fuzzy_date & ~TIME_TIME_ONLY))
     return 1;
 
@@ -2454,6 +2455,7 @@ bool Item_date_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
 
 bool Item_datetime_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
 {
+  fuzzy_date |= sql_mode_for_dates();
   if (get_arg0_date(ltime, fuzzy_date & ~TIME_TIME_ONLY))
     return 1;
 
@@ -3123,7 +3125,7 @@ bool Item_func_str_to_date::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
   date_time_format.format.length= format->length();
   if (extract_date_time(&date_time_format, val->ptr(), val->length(),
 			ltime, cached_timestamp_type, 0, "datetime",
-                        fuzzy_date))
+                        fuzzy_date | sql_mode_for_dates()))
     return (null_value=1);
   if (cached_timestamp_type == MYSQL_TIMESTAMP_TIME && ltime->day)
   {
diff --git a/sql/sql_time.cc b/sql/sql_time.cc
index e989ce6f3a0..6f15ada2dcd 100644
--- a/sql/sql_time.cc
+++ b/sql/sql_time.cc
@@ -295,9 +295,7 @@ str_to_datetime_with_warn(CHARSET_INFO *cs,
   THD *thd= current_thd;
   timestamp_type ts_type;
   
-  ts_type= str_to_datetime(cs, str, length, l_time,
-                           (flags | (sql_mode_for_dates(thd))),
-                           &was_cut);
+  ts_type= str_to_datetime(cs, str, length, l_time, flags, &was_cut);
   if (was_cut || ts_type <= MYSQL_TIMESTAMP_ERROR)
     make_truncated_value_warning(thd, Sql_condition::WARN_LEVEL_WARN,
                                  str, length, flags & TIME_TIME_ONLY ?