maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

Hello Jerome,


On 11/10/2017 05:20 PM, jerome brauge wrote:
> Hello Alexander,
> 
>  
> 
> To have a behavior the nearest possible to Oracle, I use
> STRICT_TRANS_TABLES sql_mode.
> 
> But then I have a problem with cast to varchar that failed with
> “truncated incorrect value” .
> 
>  
> 
> Ex:
> 
> set sql_mode='ORACLE,STRICT_TRANS_TABLES';
> 
> delimiter /
> 
> CREATE or replace procedure p3(b1 integer)
> 
> AS
> 
>   strres VARCHAR(4); 
> 
> BEGIN
> 
>   strres:=cast('123456' as char(4));
> 
> END
> 
> /
> 
> call p3(-1)
> 
> /
> 
> -- Failed with : ERROR 1292 (22007): Truncated incorrect CHAR(4) value:
> '123456'
> 
>  
> 
> But
> 
>  
> 
> select cast('11111' as varchar(2)) from dual;
> 
>  
> 
> just issue a warning, not an error.

How can I get such warnings from Oracle?
For me the above query just truncates silently, without warnings.


> 
>  
> 
> On Oracle, Sybase,SQLServer and DB2, cast a string to a shorter string
> never issue warnings nor error.


Thanks for noticing this.


I've filed a bug report for this:

MDEV-14376 Explicit CAST(CHAR(N)) erroneously escalates warnings to
errors in STRICT_ALL_TABLES


The attached patch fixes this problem.
Do you see any problems with the patch?

Thanks!

> 
>  
> 
> What do you think about ?
> 
>  
> 
> Regards,
> 
> Jérôme.
> 

diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 3c9cbb1..ca31457 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -1168,3 +1168,112 @@ CAST('-1' AS UNSIGNED)
 18446744073709551615
 DROP TABLE t1;
 SET sql_mode=DEFAULT;
+#
+# MDEV-14376 Explicit CAST(CHAR(N)) erroneously escalates warnings to errors in STRICT_ALL_TABLES
+#
+SET sql_mode=STRICT_ALL_TABLES;
+SELECT CAST('xxx' AS CHAR(1));
+CAST('xxx' AS CHAR(1))
+x
+Warnings:
+Warning	1292	Truncated incorrect CHAR(1) value: 'xxx'
+CREATE OR REPLACE TABLE t1 (a VARCHAR(1));
+INSERT INTO t1 VALUES (CAST('xxx' AS CHAR(1)));
+Warnings:
+Warning	1292	Truncated incorrect CHAR(1) value: 'xxx'
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a VARCHAR(3));
+INSERT INTO t1 VALUES ('xxx');
+UPDATE t1 SET a=CAST(a AS CHAR(1));
+Warnings:
+Warning	1292	Truncated incorrect CHAR(1) value: 'xxx'
+DROP TABLE t1;
+BEGIN NOT ATOMIC
+DECLARE a VARCHAR(30) CHARACTER SET latin1;
+SET a=CAST('xxx' AS CHAR(1));
+END;
+$$
+Warnings:
+Warning	1292	Truncated incorrect CHAR(1) value: 'xxx'
+BEGIN NOT ATOMIC
+DECLARE a VARCHAR(30) CHARACTER SET latin1;
+SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET latin1);
+END;
+$$
+Warnings:
+Warning	1292	Truncated incorrect CHAR(1) value: 'xxx'
+BEGIN NOT ATOMIC
+DECLARE a VARCHAR(30) CHARACTER SET latin1;
+SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET utf8);
+END;
+$$
+Warnings:
+Warning	1292	Truncated incorrect CHAR(1) value: 'xxx'
+BEGIN NOT ATOMIC
+DECLARE a VARCHAR(30) CHARACTER SET utf8;
+SET a=CAST('xxx' AS CHAR(1));
+END;
+$$
+Warnings:
+Warning	1292	Truncated incorrect CHAR(1) value: 'xxx'
+BEGIN NOT ATOMIC
+DECLARE a VARCHAR(30) CHARACTER SET utf8;
+SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET latin1);
+END;
+$$
+Warnings:
+Warning	1292	Truncated incorrect CHAR(1) value: 'xxx'
+BEGIN NOT ATOMIC
+DECLARE a VARCHAR(30) CHARACTER SET utf8;
+SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET utf8);
+END;
+$$
+Warnings:
+Warning	1292	Truncated incorrect CHAR(1) value: 'xxx'
+# Conversion problems still escalate warnings to errors (without right truncation)
+BEGIN NOT ATOMIC
+DECLARE a VARCHAR(30) CHARACTER SET utf8;
+SET a=CAST(_utf8 0xD18F AS CHAR(1) CHARACTER SET latin1);
+END;
+$$
+ERROR HY000: Cannot convert 'utf8' character 0xD18F to 'latin1'
+# Conversion problems still escalate warnings to errors (with right truncation)
+BEGIN NOT ATOMIC
+DECLARE a VARCHAR(30) CHARACTER SET utf8;
+SET a=CAST(_utf8 0xD18FD18F AS CHAR(1) CHARACTER SET latin1);
+END;
+$$
+ERROR HY000: Cannot convert 'utf8' character 0xD18F to 'latin1'
+# CAST(number AS CHAR) escalates warnings to errors on truncation
+CREATE OR REPLACE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES (CAST(123 AS CHAR(1)));
+ERROR 22007: Truncated incorrect CHAR(1) value: '123'
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('1');
+UPDATE t1 SET a=CAST(123 AS CHAR(1));
+ERROR 22007: Truncated incorrect CHAR(1) value: '123'
+DROP TABLE t1;
+BEGIN NOT ATOMIC
+DECLARE a VARCHAR(10);
+SET a=CAST(123 AS CHAR(1));
+END;
+$$
+ERROR 22007: Truncated incorrect CHAR(1) value: '123'
+# CAST(temporal AS CHAR) escalates warnings to errors on truncation
+CREATE OR REPLACE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES (CAST(TIME'10:20:30' AS CHAR(1)));
+ERROR 22007: Truncated incorrect CHAR(1) value: '10:20:30'
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('1');
+UPDATE t1 SET a=CAST(TIME'10:20:30' AS CHAR(1));
+ERROR 22007: Truncated incorrect CHAR(1) value: '10:20:30'
+DROP TABLE t1;
+BEGIN NOT ATOMIC
+DECLARE a VARCHAR(10);
+SET a=CAST(TIME'10:20:30' AS CHAR(1));
+END;
+$$
+ERROR 22007: Truncated incorrect CHAR(1) value: '10:20:30'
+SET sql_mode=DEFAULT;
diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index 17e8be8..b514dbb 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -587,3 +587,140 @@ SHOW CREATE TABLE t1;
 SELECT * FROM t1;
 DROP TABLE t1;
 SET sql_mode=DEFAULT;
+
+
+--echo #
+--echo # MDEV-14376 Explicit CAST(CHAR(N)) erroneously escalates warnings to errors in STRICT_ALL_TABLES
+--echo #
+
+SET sql_mode=STRICT_ALL_TABLES;
+SELECT CAST('xxx' AS CHAR(1));
+
+CREATE OR REPLACE TABLE t1 (a VARCHAR(1));
+INSERT INTO t1 VALUES (CAST('xxx' AS CHAR(1)));
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a VARCHAR(3));
+INSERT INTO t1 VALUES ('xxx');
+UPDATE t1 SET a=CAST(a AS CHAR(1));
+DROP TABLE t1;
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET latin1;
+  SET a=CAST('xxx' AS CHAR(1));
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET latin1;
+  SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET latin1);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET latin1;
+  SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET utf8);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET utf8;
+  SET a=CAST('xxx' AS CHAR(1));
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET utf8;
+  SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET latin1);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET utf8;
+  SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET utf8);
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # Conversion problems still escalate warnings to errors (without right truncation)
+
+DELIMITER $$;
+--error ER_CANNOT_CONVERT_CHARACTER
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET utf8;
+  SET a=CAST(_utf8 0xD18F AS CHAR(1) CHARACTER SET latin1);
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # Conversion problems still escalate warnings to errors (with right truncation)
+
+DELIMITER $$;
+--error ER_CANNOT_CONVERT_CHARACTER
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET utf8;
+  SET a=CAST(_utf8 0xD18FD18F AS CHAR(1) CHARACTER SET latin1);
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # CAST(number AS CHAR) escalates warnings to errors on truncation
+
+CREATE OR REPLACE TABLE t1 (a VARCHAR(10));
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t1 VALUES (CAST(123 AS CHAR(1)));
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('1');
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET a=CAST(123 AS CHAR(1));
+DROP TABLE t1;
+
+DELIMITER $$;
+--error ER_TRUNCATED_WRONG_VALUE
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(10);
+  SET a=CAST(123 AS CHAR(1));
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # CAST(temporal AS CHAR) escalates warnings to errors on truncation
+
+CREATE OR REPLACE TABLE t1 (a VARCHAR(10));
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t1 VALUES (CAST(TIME'10:20:30' AS CHAR(1)));
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('1');
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET a=CAST(TIME'10:20:30' AS CHAR(1));
+DROP TABLE t1;
+
+DELIMITER $$;
+--error ER_TRUNCATED_WRONG_VALUE
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(10);
+  SET a=CAST(TIME'10:20:30' AS CHAR(1));
+END;
+$$
+DELIMITER ;$$
+
+SET sql_mode=DEFAULT;
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index f0d7cd1..a0fdd4b 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -2434,6 +2434,8 @@ void Item_char_typecast::check_truncation_with_warn(String *src, uint dstlen)
     THD *thd= current_thd;
     char char_type[40];
     ErrConvString err(src);
+    bool save_abort_on_warning= thd->abort_on_warning;
+    thd->abort_on_warning&= !m_suppress_warning_to_error_escalation;
     my_snprintf(char_type, sizeof(char_type), "%s(%lu)",
                 cast_cs == &my_charset_bin ? "BINARY" : "CHAR",
                 (ulong) cast_length);
@@ -2441,6 +2443,7 @@ void Item_char_typecast::check_truncation_with_warn(String *src, uint dstlen)
                         ER_TRUNCATED_WRONG_VALUE,
                         ER_THD(thd, ER_TRUNCATED_WRONG_VALUE), char_type,
                         err.ptr());
+    thd->abort_on_warning= save_abort_on_warning;
   }
 }
 
@@ -2551,7 +2554,7 @@ void Item_char_typecast::fix_length_and_dec_numeric()
 }
 
 
-void Item_char_typecast::fix_length_and_dec_str()
+void Item_char_typecast::fix_length_and_dec_generic()
 {
   fix_length_and_dec_internal(from_cs= args[0]->dynamic_result() ?
                                        0 :
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 9c102e8..f94a0d3 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -1089,6 +1089,7 @@ class Item_char_typecast :public Item_str_func
   CHARSET_INFO *cast_cs, *from_cs;
   bool charset_conversion;
   String tmp_value;
+  bool m_suppress_warning_to_error_escalation;
   bool has_explicit_length() const { return cast_length != ~0U; }
   String *reuse(String *src, uint32 length);
   String *copy(String *src, CHARSET_INFO *cs);
@@ -1097,14 +1098,20 @@ class Item_char_typecast :public Item_str_func
   void fix_length_and_dec_internal(CHARSET_INFO *fromcs);
 public:
   Item_char_typecast(THD *thd, Item *a, uint length_arg, CHARSET_INFO *cs_arg):
-    Item_str_func(thd, a), cast_length(length_arg), cast_cs(cs_arg) {}
+    Item_str_func(thd, a), cast_length(length_arg), cast_cs(cs_arg),
+    m_suppress_warning_to_error_escalation(false) {}
   enum Functype functype() const { return CHAR_TYPECAST_FUNC; }
   bool eq(const Item *item, bool binary_cmp) const;
   const char *func_name() const { return "cast_as_char"; }
   CHARSET_INFO *cast_charset() const { return cast_cs; }
   String *val_str(String *a);
+  void fix_length_and_dec_generic();
   void fix_length_and_dec_numeric();
-  void fix_length_and_dec_str();
+  void fix_length_and_dec_str()
+  {
+    fix_length_and_dec_generic();
+    m_suppress_warning_to_error_escalation= true;
+  }
   void fix_length_and_dec()
   {
     args[0]->type_handler()->Item_char_typecast_fix_length_and_dec(this);
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 5d16a49..9333021 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -4339,7 +4339,7 @@ bool Type_handler::
 bool Type_handler::
        Item_char_typecast_fix_length_and_dec(Item_char_typecast *item) const
 {
-  item->fix_length_and_dec_str();
+  item->fix_length_and_dec_generic();
   return false;
 }
 
@@ -4352,6 +4352,14 @@ bool Type_handler_numeric::
 }
 
 
+bool Type_handler_string_result::
+       Item_char_typecast_fix_length_and_dec(Item_char_typecast *item) const
+{
+  item->fix_length_and_dec_str();
+  return false;
+}
+
+
 bool Type_handler::
        Item_time_typecast_fix_length_and_dec(Item_time_typecast *item) const
 {
diff --git a/sql/sql_type.h b/sql/sql_type.h
index ec1b5c5..7536cb9 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -1717,6 +1717,7 @@ class Type_handler_string_result: public Type_handler
                                   MYSQL_TIME *, ulonglong fuzzydate) const;
   bool Item_func_between_fix_length_and_dec(Item_func_between *func) const;
   longlong Item_func_between_val_int(Item_func_between *func) const;
+  bool Item_char_typecast_fix_length_and_dec(Item_char_typecast *) const;
   cmp_item *make_cmp_item(THD *thd, CHARSET_INFO *cs) const;
   in_vector *make_in_vector(THD *, const Item_func_in *, uint nargs) const;
   bool Item_func_in_fix_comparator_compatible_types(THD *thd,