maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #09759
MDEV-8502 DECIMAL accepts out of range DEFAULT values
Hello Sergei,
Please review a patch fixing:
MDEV-8502 DECIMAL accepts out of range DEFAULT values
MDEV-10277 Redundant NOTE when inserting '0.00001 ' into a DECIMAL(2,1)
column
Thanks.
commit 21c77129a072e244d5cc7956d8739c8d21e86af4
Author: Alexander Barkov <bar@xxxxxxxxxxx>
Date: Fri Jun 24 12:47:46 2016 +0400
MDEV-8502 DECIMAL accepts out of range DEFAULT values
MDEV-10277 Redundant NOTE when inserting '0.00001 ' into a DECIMAL(2,1) column
diff --git a/mysql-test/extra/rpl_tests/rpl_extra_col_master.test b/mysql-test/extra/rpl_tests/rpl_extra_col_master.test
index 18b6c05..aa360c9 100644
--- a/mysql-test/extra/rpl_tests/rpl_extra_col_master.test
+++ b/mysql-test/extra/rpl_tests/rpl_extra_col_master.test
@@ -155,7 +155,7 @@ connection master;
f6 ENUM('a', 'b', 'c') default 'a',
f7 DECIMAL(17,9) default '1000.00',
f8 MEDIUMBLOB,
- f9 NUMERIC(6,4) default '2000.00',
+ f9 NUMERIC(6,2) default '2000.00',
f10 VARCHAR(1024),
f11 BINARY(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
f12 SET('a', 'b', 'c') default 'b')
@@ -178,7 +178,7 @@ connection master;
f5 DOUBLE DEFAULT '2.00',
f6 DECIMAL(17,9) default '1000.00',
f7 MEDIUMBLOB,
- f8 NUMERIC(6,4) default '2000.00',
+ f8 NUMERIC(6,2) default '2000.00',
f9 VARCHAR(1024),
f10 BINARY(20) not null default '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
f11 CHAR(255))
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index 1942d07..2f6a3ea 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -2151,5 +2151,95 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a a 6 const 1 Using where; Using index; Using filesort
DROP TABLE t1;
#
+# MDEV-8502 DECIMAL accepts out of range DEFAULT values
+#
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 10000);
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 10000.0);
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 10000e0);
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '10000.0');
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '10000.1');
+ERROR 42000: Invalid default value for 'a'
+#
+# MDEV-10277 Redundant NOTE when inserting '0.00001 ' into a DECIMAL(2,1) column
+#
+CREATE TABLE t1 (a DECIMAL(2,1));
+INSERT INTO t1 VALUES ('0.00001 ');
+Warnings:
+Note 1265 Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES ('1e-10000 ');
+Warnings:
+Note 1265 Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES ('0.1 ');
+Warnings:
+Note 1265 Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES ('0.111 ');
+Warnings:
+Note 1265 Data truncated for column 'a' at row 1
+SELECT * FROM t1;
+a
+0.0
+0.0
+0.1
+0.1
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '1e-10000');
+Warnings:
+Note 1265 Data truncated for column 'a' at row 1
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` decimal(2,1) DEFAULT '0.0'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.1 ');
+Warnings:
+Note 1265 Data truncated for column 'a' at row 1
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` decimal(2,1) DEFAULT '0.1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.10001 ');
+Warnings:
+Note 1265 Data truncated for column 'a' at row 1
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` decimal(2,1) DEFAULT '0.1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.10001');
+Warnings:
+Note 1265 Data truncated for column 'a' at row 1
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` decimal(2,1) DEFAULT '0.1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 0.10001);
+Warnings:
+Note 1265 Data truncated for column 'a' at row 1
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` decimal(2,1) DEFAULT '0.1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 0.10001e0);
+Warnings:
+Note 1265 Data truncated for column 'a' at row 1
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` decimal(2,1) DEFAULT '0.1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
# End of 10.1 tests
#
diff --git a/mysql-test/suite/rpl/r/rpl_extra_col_master_innodb.result b/mysql-test/suite/rpl/r/rpl_extra_col_master_innodb.result
index e5e690b..1e0ac5a 100644
--- a/mysql-test/suite/rpl/r/rpl_extra_col_master_innodb.result
+++ b/mysql-test/suite/rpl/r/rpl_extra_col_master_innodb.result
@@ -96,13 +96,11 @@ f5 DOUBLE DEFAULT '2.00',
f6 ENUM('a', 'b', 'c') default 'a',
f7 DECIMAL(17,9) default '1000.00',
f8 MEDIUMBLOB,
-f9 NUMERIC(6,4) default '2000.00',
+f9 NUMERIC(6,2) default '2000.00',
f10 VARCHAR(1024),
f11 BINARY(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
f12 SET('a', 'b', 'c') default 'b')
ENGINE='InnoDB';
-Warnings:
-Warning 1264 Out of range value for column 'f9' at row 1
CREATE TABLE t3 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
/* extra */
@@ -119,13 +117,11 @@ CREATE TABLE t4 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
f5 DOUBLE DEFAULT '2.00',
f6 DECIMAL(17,9) default '1000.00',
f7 MEDIUMBLOB,
-f8 NUMERIC(6,4) default '2000.00',
+f8 NUMERIC(6,2) default '2000.00',
f9 VARCHAR(1024),
f10 BINARY(20) not null default '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
f11 CHAR(255))
ENGINE='InnoDB';
-Warnings:
-Warning 1264 Out of range value for column 'f8' at row 1
CREATE TABLE t31 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
/* extra */
@@ -184,9 +180,6 @@ INSERT into t2 values (2, 2, 2, 'second',
(3, 3, 3, 'third',
3.0, 'b', 3000.0003, 'f8: medium size blob', 3000, 'f10: some var char',
'01234567', 'c');
-Warnings:
-Warning 1264 Out of range value for column 'f9' at row 1
-Warning 1264 Out of range value for column 'f9' at row 2
INSERT into t3 set f1=1, f2=1, f3=1, f4='first', f10='f10: some var char';
INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10:
binary data';
diff --git a/mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result b/mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result
index 9247ceb..38ef9db 100644
--- a/mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result
+++ b/mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result
@@ -96,13 +96,11 @@ f5 DOUBLE DEFAULT '2.00',
f6 ENUM('a', 'b', 'c') default 'a',
f7 DECIMAL(17,9) default '1000.00',
f8 MEDIUMBLOB,
-f9 NUMERIC(6,4) default '2000.00',
+f9 NUMERIC(6,2) default '2000.00',
f10 VARCHAR(1024),
f11 BINARY(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
f12 SET('a', 'b', 'c') default 'b')
ENGINE='MyISAM';
-Warnings:
-Warning 1264 Out of range value for column 'f9' at row 1
CREATE TABLE t3 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
/* extra */
@@ -119,13 +117,11 @@ CREATE TABLE t4 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
f5 DOUBLE DEFAULT '2.00',
f6 DECIMAL(17,9) default '1000.00',
f7 MEDIUMBLOB,
-f8 NUMERIC(6,4) default '2000.00',
+f8 NUMERIC(6,2) default '2000.00',
f9 VARCHAR(1024),
f10 BINARY(20) not null default '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
f11 CHAR(255))
ENGINE='MyISAM';
-Warnings:
-Warning 1264 Out of range value for column 'f8' at row 1
CREATE TABLE t31 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
/* extra */
@@ -184,9 +180,6 @@ INSERT into t2 values (2, 2, 2, 'second',
(3, 3, 3, 'third',
3.0, 'b', 3000.0003, 'f8: medium size blob', 3000, 'f10: some var char',
'01234567', 'c');
-Warnings:
-Warning 1264 Out of range value for column 'f9' at row 1
-Warning 1264 Out of range value for column 'f9' at row 2
INSERT into t3 set f1=1, f2=1, f3=1, f4='first', f10='f10: some var char';
INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10:
binary data';
diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test
index 8606630..b84d01e 100644
--- a/mysql-test/t/type_newdecimal.test
+++ b/mysql-test/t/type_newdecimal.test
@@ -1684,6 +1684,57 @@ INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
EXPLAIN SELECT * FROM t1 WHERE a='ax' ORDER BY a;
DROP TABLE t1;
+
+--echo #
+--echo # MDEV-8502 DECIMAL accepts out of range DEFAULT values
+--echo #
+
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 10000);
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 10000.0);
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 10000e0);
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '10000.0');
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '10000.1');
+
+--echo #
+--echo # MDEV-10277 Redundant NOTE when inserting '0.00001 ' into a DECIMAL(2,1) column
+--echo #
+CREATE TABLE t1 (a DECIMAL(2,1));
+INSERT INTO t1 VALUES ('0.00001 ');
+INSERT INTO t1 VALUES ('1e-10000 ');
+INSERT INTO t1 VALUES ('0.1 ');
+INSERT INTO t1 VALUES ('0.111 ');
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '1e-10000');
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.1 ');
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.10001 ');
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.10001');
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 0.10001);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 0.10001e0);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
--echo #
--echo # End of 10.1 tests
--echo #
diff --git a/sql/field.cc b/sql/field.cc
index ffa7beb..a7c6550 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -1477,10 +1477,12 @@ Value_source::Converter_string_to_number::check_edom_and_truncation(THD *thd,
*/
-int Field_num::check_edom_and_truncation(const char *type, bool edom,
- CHARSET_INFO *cs,
- const char *str, uint length,
- const char *end)
+int Field_num::check_edom_and_important_data_truncation(const char *type,
+ bool edom,
+ CHARSET_INFO *cs,
+ const char *str,
+ uint length,
+ const char *end)
{
/* Test if we get an empty string or garbage */
if (edom)
@@ -1495,12 +1497,23 @@ int Field_num::check_edom_and_truncation(const char *type, bool edom,
set_warning(WARN_DATA_TRUNCATED, 1);
return 2;
}
- if (end < str + length)
- set_note(WARN_DATA_TRUNCATED, 1);
return 0;
}
+int Field_num::check_edom_and_truncation(const char *type, bool edom,
+ CHARSET_INFO *cs,
+ const char *str, uint length,
+ const char *end)
+{
+ int rc= check_edom_and_important_data_truncation(type, edom,
+ cs, str, length, end);
+ if (!rc && end < str + length)
+ set_note(WARN_DATA_TRUNCATED, 1);
+ return rc;
+}
+
+
/*
Conver a string to an integer then check bounds.
@@ -3005,7 +3018,8 @@ void Field_new_decimal::set_value_on_overflow(my_decimal *decimal_value,
If it does, stores the decimal in the buffer using binary format.
Otherwise sets maximal number that can be stored in the field.
- @param decimal_value my_decimal
+ @param decimal_value my_decimal
+ @param [OUT] native_error the error returned by my_decimal2binary().
@retval
0 ok
@@ -3013,7 +3027,8 @@ void Field_new_decimal::set_value_on_overflow(my_decimal *decimal_value,
1 error
*/
-bool Field_new_decimal::store_value(const my_decimal *decimal_value)
+bool Field_new_decimal::store_value(const my_decimal *decimal_value,
+ int *native_error)
{
ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED;
int error= 0;
@@ -3042,11 +3057,14 @@ bool Field_new_decimal::store_value(const my_decimal *decimal_value)
}
#endif
- if (warn_if_overflow(my_decimal2binary(E_DEC_FATAL_ERROR & ~E_DEC_OVERFLOW,
- decimal_value, ptr, precision, dec)))
+ *native_error= my_decimal2binary(E_DEC_FATAL_ERROR & ~E_DEC_OVERFLOW,
+ decimal_value, ptr, precision, dec);
+
+ if (*native_error == E_DEC_OVERFLOW)
{
my_decimal buff;
DBUG_PRINT("info", ("overflow"));
+ set_warning(ER_WARN_DATA_OUT_OF_RANGE, 1);
set_value_on_overflow(&buff, decimal_value->sign());
my_decimal2binary(E_DEC_FATAL_ERROR, &buff, ptr, precision, dec);
error= 1;
@@ -3057,6 +3075,16 @@ bool Field_new_decimal::store_value(const my_decimal *decimal_value)
}
+bool Field_new_decimal::store_value(const my_decimal *decimal_value)
+{
+ int native_error;
+ bool rc= store_value(decimal_value, &native_error);
+ if (!rc && native_error == E_DEC_TRUNCATED)
+ set_note(WARN_DATA_TRUNCATED, 1);
+ return rc;
+}
+
+
int Field_new_decimal::store(const char *from, uint length,
CHARSET_INFO *charset_arg)
{
@@ -3084,9 +3112,10 @@ int Field_new_decimal::store(const char *from, uint length,
if (thd->count_cuted_fields)
{
- if (check_edom_and_truncation("decimal",
- err && err != E_DEC_TRUNCATED,
- charset_arg, from, length, end))
+ if (check_edom_and_important_data_truncation("decimal",
+ err && err != E_DEC_TRUNCATED,
+ charset_arg,
+ from, length, end))
{
if (!thd->abort_on_warning)
{
@@ -3109,12 +3138,6 @@ int Field_new_decimal::store(const char *from, uint length,
}
DBUG_RETURN(1);
}
- /*
- E_DEC_TRUNCATED means minor truncation '1e-1000000000000' -> 0.0
- A note should be enough.
- */
- if (err == E_DEC_TRUNCATED)
- set_note(WARN_DATA_TRUNCATED, 1);
}
#ifndef DBUG_OFF
@@ -3122,7 +3145,21 @@ int Field_new_decimal::store(const char *from, uint length,
DBUG_PRINT("enter", ("value: %s",
dbug_decimal_as_string(dbug_buff, &decimal_value)));
#endif
- store_value(&decimal_value);
+ int err2;
+ if (store_value(&decimal_value, &err2))
+ DBUG_RETURN(1);
+
+ /*
+ E_DEC_TRUNCATED means minor truncation, a note should be enough:
+ - in err: str2my_decimal() truncated '1e-1000000000000' to 0.0
+ - in err2: store_value() truncated 1.123 to 1.12, e.g. for DECIMAL(10,2)
+ Also, we send a note if a string had some trailing spaces: '1.12 '
+ */
+ if (thd->count_cuted_fields &&
+ (err == E_DEC_TRUNCATED ||
+ err2 == E_DEC_TRUNCATED ||
+ end < from + length))
+ set_note(WARN_DATA_TRUNCATED, 1);
DBUG_RETURN(0);
}
diff --git a/sql/field.h b/sql/field.h
index 08905f2..ca13fa9 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -1471,6 +1471,10 @@ class Field: public Value_source
class Field_num :public Field {
protected:
+ int check_edom_and_important_data_truncation(const char *type, bool edom,
+ CHARSET_INFO *cs,
+ const char *str, uint length,
+ const char *end_of_num);
int check_edom_and_truncation(const char *type, bool edom,
CHARSET_INFO *cs,
const char *str, uint length,
@@ -1708,6 +1712,7 @@ class Field_new_decimal :public Field_num {
Item_result result_type () const { return DECIMAL_RESULT; }
int reset(void);
bool store_value(const my_decimal *decimal_value);
+ bool store_value(const my_decimal *decimal_value, int *native_error);
void set_value_on_overflow(my_decimal *decimal_value, bool sign);
int store(const char *to, uint length, CHARSET_INFO *charset);
int store(double nr);
diff --git a/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_master_tokudb.result b/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_master_tokudb.result
index b58beb6..929f2c5 100644
--- a/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_master_tokudb.result
+++ b/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_master_tokudb.result
@@ -96,13 +96,11 @@ f5 DOUBLE DEFAULT '2.00',
f6 ENUM('a', 'b', 'c') default 'a',
f7 DECIMAL(17,9) default '1000.00',
f8 MEDIUMBLOB,
-f9 NUMERIC(6,4) default '2000.00',
+f9 NUMERIC(6,2) default '2000.00',
f10 VARCHAR(1024),
f11 BINARY(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
f12 SET('a', 'b', 'c') default 'b')
ENGINE=TokuDB;
-Warnings:
-Warning 1264 Out of range value for column 'f9' at row 1
CREATE TABLE t3 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
/* extra */
@@ -119,13 +117,11 @@ CREATE TABLE t4 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
f5 DOUBLE DEFAULT '2.00',
f6 DECIMAL(17,9) default '1000.00',
f7 MEDIUMBLOB,
-f8 NUMERIC(6,4) default '2000.00',
+f8 NUMERIC(6,2) default '2000.00',
f9 VARCHAR(1024),
f10 BINARY(20) not null default '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
f11 CHAR(255))
ENGINE=TokuDB;
-Warnings:
-Warning 1264 Out of range value for column 'f8' at row 1
CREATE TABLE t31 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
/* extra */
@@ -184,9 +180,6 @@ INSERT into t2 values (2, 2, 2, 'second',
(3, 3, 3, 'third',
3.0, 'b', 3000.0003, 'f8: medium size blob', 3000, 'f10: some var char',
'01234567', 'c');
-Warnings:
-Warning 1264 Out of range value for column 'f9' at row 1
-Warning 1264 Out of range value for column 'f9' at row 2
INSERT into t3 set f1=1, f2=1, f3=1, f4='first', f10='f10: some var char';
INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10:
binary data';
