[Commits] b4a223e3381: MDEV-10355 Weird error message upon CREATE TABLE with DEFAULT

jacob.mathew at mariadb.com jacob.mathew at mariadb.com
Wed Mar 29 05:01:33 EEST 2017


revision-id: b4a223e338152c8764c980aeae59fb927eea1b0b (mariadb-10.2.4-98-gb4a223e3381)
parent(s): 3f7455c03008b2428fa9b364b1add4c36834ff71
author: Jacob Mathew
committer: Jacob Mathew
timestamp: 2017-03-28 17:41:56 -0700
message:

MDEV-10355 Weird error message upon CREATE TABLE with DEFAULT

Fixed handling of default values with cached temporal functions so that the
CREATE TABLE statement now succeeds.
Added clearing of cached function values to trigger function reevaluation
when updating default values and when updating virtual column values.
Fixed the error message.
Added quoting of date/time values in cases when this was omitted.
Added a test case.
Updated test result files that include date/time values that were
previously unquoted.

---
 mysql-test/r/derived_cond_pushdown.result         |  6 ++---
 mysql-test/r/func_default_between_temporal.result | 15 +++++++++++
 mysql-test/r/type_time.result                     | 16 ++++++------
 mysql-test/t/func_default_between_temporal.test   | 11 ++++++++
 sql/item.cc                                       | 31 ++++++++++++++--------
 sql/item.h                                        | 14 ++++++++++
 sql/table.cc                                      | 32 +++++++++++++++--------
 7 files changed, 92 insertions(+), 33 deletions(-)

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index fd58ee038c7..5066d4649ab 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -8019,7 +8019,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 3,
       "filtered": 100,
-      "attached_condition": "sq.i = 2007-05-28 00:00:00",
+      "attached_condition": "sq.i = '2007-05-28 00:00:00'",
       "materialized": {
         "query_block": {
           "select_id": 2,
@@ -8071,7 +8071,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 3,
       "filtered": 100,
-      "attached_condition": "sq.i = 2007-05-28",
+      "attached_condition": "sq.i = '2007-05-28'",
       "materialized": {
         "query_block": {
           "select_id": 2,
@@ -8123,7 +8123,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 3,
       "filtered": 100,
-      "attached_condition": "sq.i = 10:00:02",
+      "attached_condition": "sq.i = '10:00:02'",
       "materialized": {
         "query_block": {
           "select_id": 2,
diff --git a/mysql-test/r/func_default_between_temporal.result b/mysql-test/r/func_default_between_temporal.result
new file mode 100644
index 00000000000..db9656f4d37
--- /dev/null
+++ b/mysql-test/r/func_default_between_temporal.result
@@ -0,0 +1,15 @@
+CREATE OR REPLACE TABLE t1 ( col INT DEFAULT ( 1 LIKE ( NOW() BETWEEN '2000-01-01' AND '2012-12-12' ) ) );
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `col` int(11) DEFAULT (1 like (current_timestamp() between '2000-01-01 00:00:00' and '2012-12-12 00:00:00'))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SET timestamp = UNIX_TIMESTAMP( '2004-04-04' );
+INSERT INTO t1 VALUES( DEFAULT );
+SET timestamp = DEFAULT;
+INSERT INTO t1 VALUES( DEFAULT );
+SELECT * FROM t1;
+col
+1
+0
+DROP TABLE t1;
diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result
index a827fb19943..2aabd98229d 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -989,7 +989,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-0
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = 00:00:00
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '00:00:00'
 # TIME cast + DATE literal
 SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE'2015-09-11';
 a
@@ -999,7 +999,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE'2015-09
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = 00:00:00
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '00:00:00'
 # TIME literal + DATE cast
 SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE('2015-09-11');
 a
@@ -1029,7 +1029,7 @@ SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE('2015-09-11')
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '00:00:00' and coalesce(`test`.`t1`.`a`) = 2015-09-11 00:00:00
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '00:00:00' and coalesce(`test`.`t1`.`a`) = '2015-09-11 00:00:00'
 # TIME-alike string literal + DATE literal
 SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE'2015-09-11';
 a
@@ -1049,7 +1049,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE('2015-09-11');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = 0 and coalesce(`test`.`t1`.`a`) = 2015-09-11 00:00:00
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = 0 and coalesce(`test`.`t1`.`a`) = '2015-09-11 00:00:00'
 # TIME-alike integer literal + DATE literal
 SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE'2015-09-11';
 a
@@ -1069,7 +1069,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME('00:0
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = 2015-09-11 00:00:00
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '2015-09-11 00:00:00'
 # DATE cast + TIME literal
 SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME'00:00:00';
 a
@@ -1079,7 +1079,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME'00:00
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = 2015-09-11 00:00:00
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '2015-09-11 00:00:00'
 # DATE cast + TIME-alike string literal
 SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)='00:00:00';
 a
@@ -1089,7 +1089,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)='00:00:00'
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = 2015-09-11 00:00:00 and coalesce(`test`.`t1`.`a`) = '00:00:00'
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '2015-09-11 00:00:00' and coalesce(`test`.`t1`.`a`) = '00:00:00'
 # DATE cast + TIME-alike integer literal
 SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=0;
 a
@@ -1099,7 +1099,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = 2015-09-11 00:00:00 and coalesce(`test`.`t1`.`a`) = 0
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '2015-09-11 00:00:00' and coalesce(`test`.`t1`.`a`) = 0
 # DATE literal + TIME cast
 SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME('00:00:00');
 a
diff --git a/mysql-test/t/func_default_between_temporal.test b/mysql-test/t/func_default_between_temporal.test
new file mode 100644
index 00000000000..6cbd5a342c6
--- /dev/null
+++ b/mysql-test/t/func_default_between_temporal.test
@@ -0,0 +1,11 @@
+CREATE OR REPLACE TABLE t1 ( col INT DEFAULT ( 1 LIKE ( NOW() BETWEEN '2000-01-01' AND '2012-12-12' ) ) );
+SHOW CREATE TABLE t1;
+
+SET timestamp = UNIX_TIMESTAMP( '2004-04-04' );
+INSERT INTO t1 VALUES( DEFAULT );
+SET timestamp = DEFAULT;
+INSERT INTO t1 VALUES( DEFAULT );
+
+SELECT * FROM t1;
+
+DROP TABLE t1;
diff --git a/sql/item.cc b/sql/item.cc
index c34d27fa63b..ee3e43b60ad 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -616,8 +616,9 @@ void Item::print_value(String *str)
     str->append("NULL");
   else
   {
-    switch (result_type()) {
+    switch (cmp_type()) {
     case STRING_RESULT:
+    case TIME_RESULT:
       append_unescaped(str, ptr->ptr(), ptr->length());
       break;
     case DECIMAL_RESULT:
@@ -626,7 +627,6 @@ void Item::print_value(String *str)
       str->append(*ptr);
       break;
     case ROW_RESULT:
-    case TIME_RESULT:
       DBUG_ASSERT(0);
     }
   }
@@ -9433,17 +9433,26 @@ void Item_cache::store(Item *item)
 
 void Item_cache::print(String *str, enum_query_type query_type)
 {
-  if (value_cached)
+  if ( example && ( example->type() == FUNC_ITEM ) &&       // There is a cached function item
+       ( query_type & QT_ITEM_IDENT_SKIP_TABLE_NAMES ) )    // Caller is show-create-table
   {
-    print_value(str);
-    return;
+      // Instead of "cache" or the cached value, print the function name
+      example->print( str, query_type );
   }
-  str->append(STRING_WITH_LEN("<cache>("));
-  if (example)
-    example->print(str, query_type);
   else
-    Item::print(str, query_type);
-  str->append(')');
+  {
+      if (value_cached)
+      {
+          print_value(str);
+          return;
+      }
+      str->append(STRING_WITH_LEN("<cache>("));
+      if (example)
+          example->print(str, query_type);
+      else
+          Item::print(str, query_type);
+      str->append(')');
+  }
 }
 
 /**
@@ -9669,7 +9678,7 @@ int Item_cache_temporal::save_in_field(Field *field, bool no_conversions)
 
 void Item_cache_temporal::store_packed(longlong val_arg, Item *example_arg)
 {
-  /* An explicit values is given, save it. */
+  /* An explicit value is given, save it. */
   store(example_arg);
   value_cached= true;
   value= val_arg;
diff --git a/sql/item.h b/sql/item.h
index 67640ce5f4d..222f8580cb4 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1653,6 +1653,7 @@ class Item: public Value_source,
   {
     return mark_unsupported_function(full_name(), arg, VCOL_IMPOSSIBLE);
   }
+  virtual bool clear_cached_function_value(void *arg) { return 0; }
   virtual bool check_field_expression_processor(void *arg) { return 0; }
   virtual bool check_func_default_processor(void *arg) { return 0; }
   /*
@@ -5450,8 +5451,21 @@ class Item_cache: public Item_basic_constant,
   }
   bool check_vcol_func_processor(void *arg) 
   {
+    if ( example )
+    {
+        return example->check_vcol_func_processor( arg );
+    }
     return mark_unsupported_function("cache", arg, VCOL_IMPOSSIBLE);
   }
+  bool clear_cached_function_value( void *arg )
+  {
+      if ( example && example->type() == Item::FUNC_ITEM )
+      {
+          // Clear the cached function value to trigger reevaluation
+          clear();
+      }
+      return false;
+  }
   /**
      Check if saved item has a non-NULL value.
      Will cache value of saved item if not already done. 
diff --git a/sql/table.cc b/sql/table.cc
index 3a08d1e49ea..9ae035e41aa 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -48,7 +48,7 @@
 #define MYSQL57_GCOL_HEADER_SIZE 4
 
 static Virtual_column_info * unpack_vcol_info_from_frm(THD *, MEM_ROOT *,
-              TABLE *, String *, Virtual_column_info **, bool *);
+              TABLE *, String *, Virtual_column_info **, uint, bool *);
 static bool check_vcol_forward_refs(Field *, Virtual_column_info *);
 
 /* INFORMATION_SCHEMA name */
@@ -1066,13 +1066,13 @@ bool parse_vcol_defs(THD *thd, MEM_ROOT *mem_root, TABLE *table,
     case VCOL_GENERATED_VIRTUAL:
     case VCOL_GENERATED_STORED:
       vcol= unpack_vcol_info_from_frm(thd, mem_root, table, &expr_str,
-                                    &((*field_ptr)->vcol_info), error_reported);
+                                    &((*field_ptr)->vcol_info), type, error_reported);
       *(vfield_ptr++)= *field_ptr;
       break;
     case VCOL_DEFAULT:
       vcol= unpack_vcol_info_from_frm(thd, mem_root, table, &expr_str,
                                       &((*field_ptr)->default_value),
-                                      error_reported);
+                                      type, error_reported);
       *(dfield_ptr++)= *field_ptr;
       if (vcol && (vcol->flags & (VCOL_NON_DETERMINISTIC | VCOL_SESSION_FUNC)))
         table->s->non_determinstic_insert= true;
@@ -1080,12 +1080,12 @@ bool parse_vcol_defs(THD *thd, MEM_ROOT *mem_root, TABLE *table,
     case VCOL_CHECK_FIELD:
       vcol= unpack_vcol_info_from_frm(thd, mem_root, table, &expr_str,
                                       &((*field_ptr)->check_constraint),
-                                      error_reported);
+                                      type, error_reported);
       *check_constraint_ptr++= (*field_ptr)->check_constraint;
       break;
     case VCOL_CHECK_TABLE:
       vcol= unpack_vcol_info_from_frm(thd, mem_root, table, &expr_str,
-                                      check_constraint_ptr, error_reported);
+                                      check_constraint_ptr, type, error_reported);
       check_constraint_ptr++;
       break;
     }
@@ -1106,7 +1106,7 @@ bool parse_vcol_defs(THD *thd, MEM_ROOT *mem_root, TABLE *table,
       expr_str.append(')');
       vcol= unpack_vcol_info_from_frm(thd, mem_root, table, &expr_str,
                                       &((*field_ptr)->default_value),
-                                      error_reported);
+                                      VCOL_DEFAULT, error_reported);
       *(dfield_ptr++)= *field_ptr;
       if (!field->default_value->expr)
         goto end;
@@ -2767,7 +2767,7 @@ bool fix_session_vcol_expr_for_read(THD *thd, Field *field,
 */
 
 static bool fix_and_check_vcol_expr(THD *thd, TABLE *table,
-                                    Virtual_column_info *vcol)
+                                    Virtual_column_info *vcol, uint vcol_type)
 {
   Item* func_expr= vcol->expr;
   DBUG_ENTER("fix_and_check_vcol_expr");
@@ -2802,9 +2802,9 @@ static bool fix_and_check_vcol_expr(THD *thd, TABLE *table,
 
   int error= func_expr->walk(&Item::check_vcol_func_processor, 0, &res);
   if (error || (res.errors & VCOL_IMPOSSIBLE))
-  { // this can only happen if the frm was corrupted
+  {
     my_error(ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED, MYF(0), res.name,
-             "???", "?????");
+             vcol_type_name((enum_vcol_info_type) vcol_type), vcol->name.str);
     DBUG_RETURN(1);
   }
   vcol->flags= res.errors;
@@ -2850,7 +2850,7 @@ static bool fix_and_check_vcol_expr(THD *thd, TABLE *table,
 static Virtual_column_info *
 unpack_vcol_info_from_frm(THD *thd, MEM_ROOT *mem_root, TABLE *table,
                           String *expr_str, Virtual_column_info **vcol_ptr,
-                          bool *error_reported)
+                          uint vcol_type, bool *error_reported)
 {
   Create_field vcol_storage; // placeholder for vcol_info
   Parser_state parser_state;
@@ -2878,7 +2878,7 @@ unpack_vcol_info_from_frm(THD *thd, MEM_ROOT *mem_root, TABLE *table,
   vcol_storage.vcol_info->stored_in_db=      vcol->stored_in_db;
   vcol_storage.vcol_info->name=              vcol->name;
   vcol_storage.vcol_info->utf8=              vcol->utf8;
-  if (!fix_and_check_vcol_expr(thd, table, vcol_storage.vcol_info))
+  if (!fix_and_check_vcol_expr(thd, table, vcol_storage.vcol_info, vcol_type))
   {
     *vcol_ptr= vcol_info= vcol_storage.vcol_info;   // Expression ok
     DBUG_ASSERT(vcol_info->expr);
@@ -7374,6 +7374,10 @@ int TABLE::update_virtual_fields(handler *h, enum_vcol_update_mode update_mode)
 
     if (update)
     {
+      // Clear any cached function values in the virtual field expression
+      // to trigger their reevaluation
+      vcol_info->expr->walk( &Item::clear_cached_function_value, 0, 0 );  // Always returns 0
+
       int field_error __attribute__((unused)) = 0;
       /* Compute the actual value of the virtual fields */
       if (vcol_info->expr->save_in_field(vf, 0))
@@ -7461,7 +7465,13 @@ int TABLE::update_default_fields(bool update_command, bool ignore_errors)
       {
         if (field->default_value &&
             (field->default_value->flags || field->flags & BLOB_FLAG))
+        {
+          // Clear any cached function values in the default value expression
+          // to trigger their reevaluation
+          field->default_value->expr->walk( &Item::clear_cached_function_value, 0, 0 );  // Always returns 0
+          // Store the default value in the field
           res|= (field->default_value->expr->save_in_field(field, 0) < 0);
+        }
       }
       else
         res|= field->evaluate_update_default_function();


More information about the commits mailing list