diff options
author | Alexander Barkov <bar@mariadb.org> | 2015-09-23 20:42:28 +0400 |
---|---|---|
committer | Alexander Barkov <bar@mariadb.org> | 2015-09-23 20:42:28 +0400 |
commit | 3ad035f66b74284f515b2786f7d4553dba5c14a2 (patch) | |
tree | 4112fdc64b1dd77aa31389e6a92e0bda7cbaba49 | |
parent | b9da3ba45e6a6bad152203f56af47abe79e550ad (diff) | |
download | mariadb-git-3ad035f66b74284f515b2786f7d4553dba5c14a2.tar.gz |
MDEV-8658 DATE(zerofill_column) and DATE(COALESCE(zerofill_column)) return different results
MDEV-8660 TIME(int_zerofill_column) returns a wrong result
-rw-r--r-- | mysql-test/r/func_set.result | 8 | ||||
-rw-r--r-- | mysql-test/r/func_time.result | 13 | ||||
-rw-r--r-- | mysql-test/r/subselect_innodb.result | 2 | ||||
-rw-r--r-- | mysql-test/r/type_date.result | 43 | ||||
-rw-r--r-- | mysql-test/r/type_newdecimal.result | 14 | ||||
-rw-r--r-- | mysql-test/r/type_time.result | 53 | ||||
-rw-r--r-- | mysql-test/suite/funcs_1/r/innodb_func_view.result | 56 | ||||
-rw-r--r-- | mysql-test/suite/funcs_1/r/memory_func_view.result | 56 | ||||
-rw-r--r-- | mysql-test/suite/funcs_1/r/myisam_func_view.result | 56 | ||||
-rw-r--r-- | mysql-test/t/type_date.test | 16 | ||||
-rw-r--r-- | mysql-test/t/type_time.test | 27 | ||||
-rw-r--r-- | sql/field.cc | 18 | ||||
-rw-r--r-- | sql/field.h | 2 |
13 files changed, 241 insertions, 123 deletions
diff --git a/mysql-test/r/func_set.result b/mysql-test/r/func_set.result index dfc3faf3fac..96af966d367 100644 --- a/mysql-test/r/func_set.result +++ b/mysql-test/r/func_set.result @@ -172,8 +172,8 @@ INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) 8 8 Warnings: -Warning 1292 Incorrect datetime value: '10' -Warning 1292 Incorrect datetime value: '11' +Warning 1292 Incorrect datetime value: '10' for column 'pk' at row 1 +Warning 1292 Incorrect datetime value: '11' for column 'pk' at row 2 DROP TABLE t1; # # End of 5.3 tests @@ -233,8 +233,8 @@ INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) 8 8 Warnings: -Warning 1292 Incorrect datetime value: '10' -Warning 1292 Incorrect datetime value: '11' +Warning 1292 Incorrect datetime value: '10' for column 'pk' at row 1 +Warning 1292 Incorrect datetime value: '11' for column 'pk' at row 2 DROP TABLE t1; # # End of 5.3 tests diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 043350ce022..c0cf6d45ae2 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1834,25 +1834,14 @@ select last_day(f2) from t1; last_day(f2) NULL NULL -Warnings: -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '0' select last_day(f2) from t1 where last_day(f2) is null; last_day(f2) NULL NULL -Warnings: -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '0' select * from t1 order by last_day (f2); f2 0 0 -Warnings: -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '0' drop table t1; SET timestamp=unix_timestamp('2001-02-03 10:20:30'); select convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow'); @@ -1908,8 +1897,6 @@ insert ignore t1 values ('04:38:11','0000-00-00 00:00:00',0,'0000-00-00 00:00:00 select least(greatest(f3, f2, f4), f5) from t1; least(greatest(f3, f2, f4), f5) 0000-00-00 00:00:00 -Warnings: -Warning 1292 Incorrect datetime value: '0' drop table t1; select day(coalesce(null)); day(coalesce(null)) diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index 07d00e96549..cfbe5d41418 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -296,8 +296,6 @@ INSERT INTO t1 VALUES ('2011-05-13', 0); SELECT * FROM t1 WHERE b < (SELECT CAST(a as date) FROM t1 GROUP BY a); a b 2011-05-13 0 -Warnings: -Warning 1292 Incorrect datetime value: '0' DROP TABLE t1; # # Bug #11766300 59387: FAILING ASSERTION: CURSOR->POS_STATE == 1997660512 (BTR_PCUR_IS_POSITIONE diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index 155918ccbb7..1de9b989887 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -759,5 +759,48 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = DATE'2001-01-01') DROP TABLE t1; # +# MDEV-8658 DATE(zerofill_column) and DATE(COALESCE(zerofill_column)) return different results +# +CREATE TABLE t1 (a INT(6) ZEROFILL, b DECIMAL(6) ZEROFILL, c DOUBLE(6,0) ZEROFILL); +INSERT INTO t1 VALUES (1,1,1); +INSERT INTO t1 VALUES (10101,10101,10101); +SELECT DATE(a), DATE(b), DATE(c) FROM t1; +DATE(a) DATE(b) DATE(c) +NULL NULL NULL +2001-01-01 2001-01-01 2001-01-01 +Warnings: +Warning 1292 Incorrect datetime value: '1' for column 'a' at row 1 +Warning 1292 Incorrect datetime value: '1' for column 'b' at row 1 +Warning 1292 Incorrect datetime value: '1' for column 'c' at row 1 +SELECT DATE(COALESCE(a)), DATE(COALESCE(b)), DATE(COALESCE(c)) FROM t1; +DATE(COALESCE(a)) DATE(COALESCE(b)) DATE(COALESCE(c)) +NULL NULL NULL +2001-01-01 2001-01-01 2001-01-01 +Warnings: +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1' +DROP TABLE t1; +CREATE TABLE t1 (a INT(6), b DECIMAL(6), c DOUBLE(6,0)); +INSERT INTO t1 VALUES (1,1,1); +INSERT INTO t1 VALUES (10101,10101,10101); +SELECT DATE(a), DATE(b), DATE(c) FROM t1; +DATE(a) DATE(b) DATE(c) +NULL NULL NULL +2001-01-01 2001-01-01 2001-01-01 +Warnings: +Warning 1292 Incorrect datetime value: '1' for column 'a' at row 1 +Warning 1292 Incorrect datetime value: '1' for column 'b' at row 1 +Warning 1292 Incorrect datetime value: '1' for column 'c' at row 1 +SELECT DATE(COALESCE(a)), DATE(COALESCE(b)), DATE(COALESCE(c)) FROM t1; +DATE(COALESCE(a)) DATE(COALESCE(b)) DATE(COALESCE(c)) +NULL NULL NULL +2001-01-01 2001-01-01 2001-01-01 +Warnings: +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1' +DROP TABLE t1; +# # End of 10.1 tests # diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index b498910f13c..13da89e0061 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -2042,33 +2042,19 @@ SELECT t1.* FROM t1 JOIN t2 USING(a); a 10:20:30.000000 10:20:30.000000 -Warnings: -Note 1292 Truncated incorrect time value: '102030.0000000000' -Note 1292 Truncated incorrect time value: '102030.0000000010' SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); a 10:20:30.000000 10:20:30.000000 -Warnings: -Note 1292 Truncated incorrect time value: '102030.0000000000' -Note 1292 Truncated incorrect time value: '102030.0000000000' -Note 1292 Truncated incorrect time value: '102030.0000000010' ALTER TABLE t2 ADD PRIMARY KEY(a); SELECT t1.* FROM t1 JOIN t2 USING(a); a 10:20:30.000000 10:20:30.000000 -Warnings: -Note 1292 Truncated incorrect time value: '102030.0000000000' -Note 1292 Truncated incorrect time value: '102030.0000000010' SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); a 10:20:30.000000 10:20:30.000000 -Warnings: -Note 1292 Truncated incorrect time value: '102030.0000000000' -Note 1292 Truncated incorrect time value: '102030.0000000000' -Note 1292 Truncated incorrect time value: '102030.0000000010' # t2 should NOT be eliminated EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index ba40fbf774b..59de00fa416 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -1153,5 +1153,58 @@ SELECT * FROM t1 WHERE a>TIME('00:00:00'); a DROP TABLE t1; # +# MDEV-8660 TIME(int_zerofill_column) returns a wrong result +# +CREATE TABLE t1 (a BIGINT(15), b BIGINT(15) ZEROFILL); +INSERT INTO t1 VALUES (9,9); +SELECT TIME(a),TIME(b) FROM t1; +TIME(a) TIME(b) +00:00:09 00:00:09 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES (-9223372036854775808); +SELECT CAST(a AS TIME), CAST(-9223372036854775808 AS TIME) FROM t1; +CAST(a AS TIME) CAST(-9223372036854775808 AS TIME) +-838:59:59 -838:59:59 +Warnings: +Warning 1292 Incorrect time value: '-9223372036854775808' for column 'a' at row 1 +Warning 1292 Truncated incorrect time value: '-9223372036854775808' +DROP TABLE t1; +CREATE TABLE t1 (a INT, b DECIMAL, c DOUBLE); +INSERT INTO t1 VALUES (-9000000,-9000000,-9000000); +INSERT INTO t1 VALUES (-1,-1,-1); +INSERT INTO t1 VALUES (0,0,0),(1,1,1),(9,9,9); +INSERT INTO t1 VALUES (9000000,9000000,9000000); +SELECT a, TIME(a),TIME(b),TIME(c) FROM t1 ORDER BY a; +a TIME(a) TIME(b) TIME(c) +-9000000 -838:59:59 -838:59:59 -838:59:59.999999 +-1 -00:00:01 -00:00:01 -00:00:01.000000 +0 00:00:00 00:00:00 00:00:00.000000 +1 00:00:01 00:00:01 00:00:01.000000 +9 00:00:09 00:00:09 00:00:09.000000 +9000000 838:59:59 838:59:59 838:59:59.999999 +Warnings: +Warning 1292 Incorrect time value: '-9000000' for column 'a' at row 1 +Warning 1292 Incorrect time value: '-9000000' for column 'b' at row 1 +Warning 1292 Incorrect time value: '-9000000' for column 'c' at row 1 +Warning 1292 Incorrect time value: '9000000' for column 'a' at row 6 +Warning 1292 Incorrect time value: '9000000' for column 'b' at row 6 +Warning 1292 Incorrect time value: '9000000' for column 'c' at row 6 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b DECIMAL, c DOUBLE); +INSERT INTO t1 VALUES (0,0,0),(1,1,1),(9,9,9); +INSERT INTO t1 VALUES (9000000,9000000,9000000); +SELECT a, TIME(a),TIME(b),TIME(c) FROM t1 ORDER BY a; +a TIME(a) TIME(b) TIME(c) +0 00:00:00 00:00:00 00:00:00.000000 +1 00:00:01 00:00:01 00:00:01.000000 +9 00:00:09 00:00:09 00:00:09.000000 +9000000 838:59:59 838:59:59 838:59:59.999999 +Warnings: +Warning 1292 Incorrect time value: '9000000' for column 'a' at row 4 +Warning 1292 Incorrect time value: '9000000' for column 'b' at row 4 +Warning 1292 Incorrect time value: '9000000' for column 'c' at row 4 +DROP TABLE t1; +# # End of 10.1 tests # diff --git a/mysql-test/suite/funcs_1/r/innodb_func_view.result b/mysql-test/suite/funcs_1/r/innodb_func_view.result index 1e604cb1378..357c8200f34 100644 --- a/mysql-test/suite/funcs_1/r/innodb_func_view.result +++ b/mysql-test/suite/funcs_1/r/innodb_func_view.result @@ -3743,14 +3743,14 @@ my_bigint, id FROM t1_values WHERE select_id = 52 OR select_id IS NULL order by id; CAST(my_bigint AS TIME) my_bigint id NULL NULL 1 -NULL -9223372036854775808 2 -NULL 9223372036854775807 3 +-838:59:59 -9223372036854775808 2 +838:59:59 9223372036854775807 3 00:00:00 0 4 -00:00:01 -1 5 00:17:58 1758 24 Warnings: -Warning 1292 Truncated incorrect time value: '-9223372036854775808' -Warning 1292 Truncated incorrect time value: '9223372036854775807' +Warning 1292 Incorrect time value: '-9223372036854775808' for column 'my_bigint' at row 2 +Warning 1292 Incorrect time value: '9223372036854775807' for column 'my_bigint' at row 3 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_bigint` as time) AS `CAST(my_bigint AS TIME)`,`t1_values`.`my_bigint` AS `my_bigint`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci @@ -3759,14 +3759,14 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 52 OR select_id IS NULL) order by id; CAST(my_bigint AS TIME) my_bigint id NULL NULL 1 -NULL -9223372036854775808 2 -NULL 9223372036854775807 3 +-838:59:59 -9223372036854775808 2 +838:59:59 9223372036854775807 3 00:00:00 0 4 -00:00:01 -1 5 00:17:58 1758 24 Warnings: -Warning 1292 Truncated incorrect time value: '-9223372036854775808' -Warning 1292 Truncated incorrect time value: '9223372036854775807' +Warning 1292 Incorrect time value: '-9223372036854775808' for column 'my_bigint' at row 1 +Warning 1292 Incorrect time value: '9223372036854775807' for column 'my_bigint' at row 1 DROP VIEW v1; @@ -4095,15 +4095,14 @@ CAST(my_bigint AS DATETIME) my_bigint id NULL NULL 1 NULL -9223372036854775808 2 NULL 9223372036854775807 3 -NULL 0 4 +0000-00-00 00:00:00 0 4 NULL -1 5 NULL 200506271758 18 Warnings: -Warning 1292 Incorrect datetime value: '-9223372036854775808' -Warning 1292 Incorrect datetime value: '9223372036854775807' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '-1' -Warning 1292 Incorrect datetime value: '200506271758' +Warning 1292 Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 2 +Warning 1292 Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 3 +Warning 1292 Incorrect datetime value: '-1' for column 'my_bigint' at row 5 +Warning 1292 Incorrect datetime value: '200506271758' for column 'my_bigint' at row 18 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_bigint` as datetime) AS `CAST(my_bigint AS DATETIME)`,`t1_values`.`my_bigint` AS `my_bigint`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci @@ -4114,15 +4113,14 @@ CAST(my_bigint AS DATETIME) my_bigint id NULL NULL 1 NULL -9223372036854775808 2 NULL 9223372036854775807 3 -NULL 0 4 +0000-00-00 00:00:00 0 4 NULL -1 5 NULL 200506271758 18 Warnings: -Warning 1292 Incorrect datetime value: '-9223372036854775808' -Warning 1292 Incorrect datetime value: '9223372036854775807' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '-1' -Warning 1292 Incorrect datetime value: '200506271758' +Warning 1292 Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '-1' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '200506271758' for column 'my_bigint' at row 1 DROP VIEW v1; @@ -4455,14 +4453,13 @@ CAST(my_bigint AS DATE) my_bigint id NULL NULL 1 NULL -9223372036854775808 2 NULL 9223372036854775807 3 -NULL 0 4 +0000-00-00 0 4 NULL -1 5 2005-06-27 20050627 12 Warnings: -Warning 1292 Incorrect datetime value: '-9223372036854775808' -Warning 1292 Incorrect datetime value: '9223372036854775807' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '-1' +Warning 1292 Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 2 +Warning 1292 Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 3 +Warning 1292 Incorrect datetime value: '-1' for column 'my_bigint' at row 5 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_bigint` as date) AS `CAST(my_bigint AS DATE)`,`t1_values`.`my_bigint` AS `my_bigint`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci @@ -4473,14 +4470,13 @@ CAST(my_bigint AS DATE) my_bigint id NULL NULL 1 NULL -9223372036854775808 2 NULL 9223372036854775807 3 -NULL 0 4 +0000-00-00 0 4 NULL -1 5 2005-06-27 20050627 12 Warnings: -Warning 1292 Incorrect datetime value: '-9223372036854775808' -Warning 1292 Incorrect datetime value: '9223372036854775807' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '-1' +Warning 1292 Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '-1' for column 'my_bigint' at row 1 DROP VIEW v1; diff --git a/mysql-test/suite/funcs_1/r/memory_func_view.result b/mysql-test/suite/funcs_1/r/memory_func_view.result index a37213fdcaa..df57c83164e 100644 --- a/mysql-test/suite/funcs_1/r/memory_func_view.result +++ b/mysql-test/suite/funcs_1/r/memory_func_view.result @@ -3744,14 +3744,14 @@ my_bigint, id FROM t1_values WHERE select_id = 52 OR select_id IS NULL order by id; CAST(my_bigint AS TIME) my_bigint id NULL NULL 1 -NULL -9223372036854775808 2 -NULL 9223372036854775807 3 +-838:59:59 -9223372036854775808 2 +838:59:59 9223372036854775807 3 00:00:00 0 4 -00:00:01 -1 5 00:17:58 1758 24 Warnings: -Warning 1292 Truncated incorrect time value: '-9223372036854775808' -Warning 1292 Truncated incorrect time value: '9223372036854775807' +Warning 1292 Incorrect time value: '-9223372036854775808' for column 'my_bigint' at row 2 +Warning 1292 Incorrect time value: '9223372036854775807' for column 'my_bigint' at row 3 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_bigint` as time) AS `CAST(my_bigint AS TIME)`,`t1_values`.`my_bigint` AS `my_bigint`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci @@ -3760,14 +3760,14 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 52 OR select_id IS NULL) order by id; CAST(my_bigint AS TIME) my_bigint id NULL NULL 1 -NULL -9223372036854775808 2 -NULL 9223372036854775807 3 +-838:59:59 -9223372036854775808 2 +838:59:59 9223372036854775807 3 00:00:00 0 4 -00:00:01 -1 5 00:17:58 1758 24 Warnings: -Warning 1292 Truncated incorrect time value: '-9223372036854775808' -Warning 1292 Truncated incorrect time value: '9223372036854775807' +Warning 1292 Incorrect time value: '-9223372036854775808' for column 'my_bigint' at row 1 +Warning 1292 Incorrect time value: '9223372036854775807' for column 'my_bigint' at row 1 DROP VIEW v1; @@ -4096,15 +4096,14 @@ CAST(my_bigint AS DATETIME) my_bigint id NULL NULL 1 NULL -9223372036854775808 2 NULL 9223372036854775807 3 -NULL 0 4 +0000-00-00 00:00:00 0 4 NULL -1 5 NULL 200506271758 18 Warnings: -Warning 1292 Incorrect datetime value: '-9223372036854775808' -Warning 1292 Incorrect datetime value: '9223372036854775807' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '-1' -Warning 1292 Incorrect datetime value: '200506271758' +Warning 1292 Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 2 +Warning 1292 Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 3 +Warning 1292 Incorrect datetime value: '-1' for column 'my_bigint' at row 5 +Warning 1292 Incorrect datetime value: '200506271758' for column 'my_bigint' at row 6 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_bigint` as datetime) AS `CAST(my_bigint AS DATETIME)`,`t1_values`.`my_bigint` AS `my_bigint`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci @@ -4115,15 +4114,14 @@ CAST(my_bigint AS DATETIME) my_bigint id NULL NULL 1 NULL -9223372036854775808 2 NULL 9223372036854775807 3 -NULL 0 4 +0000-00-00 00:00:00 0 4 NULL -1 5 NULL 200506271758 18 Warnings: -Warning 1292 Incorrect datetime value: '-9223372036854775808' -Warning 1292 Incorrect datetime value: '9223372036854775807' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '-1' -Warning 1292 Incorrect datetime value: '200506271758' +Warning 1292 Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '-1' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '200506271758' for column 'my_bigint' at row 1 DROP VIEW v1; @@ -4456,14 +4454,13 @@ CAST(my_bigint AS DATE) my_bigint id NULL NULL 1 NULL -9223372036854775808 2 NULL 9223372036854775807 3 -NULL 0 4 +0000-00-00 0 4 NULL -1 5 2005-06-27 20050627 12 Warnings: -Warning 1292 Incorrect datetime value: '-9223372036854775808' -Warning 1292 Incorrect datetime value: '9223372036854775807' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '-1' +Warning 1292 Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 2 +Warning 1292 Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 3 +Warning 1292 Incorrect datetime value: '-1' for column 'my_bigint' at row 5 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_bigint` as date) AS `CAST(my_bigint AS DATE)`,`t1_values`.`my_bigint` AS `my_bigint`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci @@ -4474,14 +4471,13 @@ CAST(my_bigint AS DATE) my_bigint id NULL NULL 1 NULL -9223372036854775808 2 NULL 9223372036854775807 3 -NULL 0 4 +0000-00-00 0 4 NULL -1 5 2005-06-27 20050627 12 Warnings: -Warning 1292 Incorrect datetime value: '-9223372036854775808' -Warning 1292 Incorrect datetime value: '9223372036854775807' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '-1' +Warning 1292 Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '-1' for column 'my_bigint' at row 1 DROP VIEW v1; diff --git a/mysql-test/suite/funcs_1/r/myisam_func_view.result b/mysql-test/suite/funcs_1/r/myisam_func_view.result index a37213fdcaa..df57c83164e 100644 --- a/mysql-test/suite/funcs_1/r/myisam_func_view.result +++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result @@ -3744,14 +3744,14 @@ my_bigint, id FROM t1_values WHERE select_id = 52 OR select_id IS NULL order by id; CAST(my_bigint AS TIME) my_bigint id NULL NULL 1 -NULL -9223372036854775808 2 -NULL 9223372036854775807 3 +-838:59:59 -9223372036854775808 2 +838:59:59 9223372036854775807 3 00:00:00 0 4 -00:00:01 -1 5 00:17:58 1758 24 Warnings: -Warning 1292 Truncated incorrect time value: '-9223372036854775808' -Warning 1292 Truncated incorrect time value: '9223372036854775807' +Warning 1292 Incorrect time value: '-9223372036854775808' for column 'my_bigint' at row 2 +Warning 1292 Incorrect time value: '9223372036854775807' for column 'my_bigint' at row 3 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_bigint` as time) AS `CAST(my_bigint AS TIME)`,`t1_values`.`my_bigint` AS `my_bigint`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci @@ -3760,14 +3760,14 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 52 OR select_id IS NULL) order by id; CAST(my_bigint AS TIME) my_bigint id NULL NULL 1 -NULL -9223372036854775808 2 -NULL 9223372036854775807 3 +-838:59:59 -9223372036854775808 2 +838:59:59 9223372036854775807 3 00:00:00 0 4 -00:00:01 -1 5 00:17:58 1758 24 Warnings: -Warning 1292 Truncated incorrect time value: '-9223372036854775808' -Warning 1292 Truncated incorrect time value: '9223372036854775807' +Warning 1292 Incorrect time value: '-9223372036854775808' for column 'my_bigint' at row 1 +Warning 1292 Incorrect time value: '9223372036854775807' for column 'my_bigint' at row 1 DROP VIEW v1; @@ -4096,15 +4096,14 @@ CAST(my_bigint AS DATETIME) my_bigint id NULL NULL 1 NULL -9223372036854775808 2 NULL 9223372036854775807 3 -NULL 0 4 +0000-00-00 00:00:00 0 4 NULL -1 5 NULL 200506271758 18 Warnings: -Warning 1292 Incorrect datetime value: '-9223372036854775808' -Warning 1292 Incorrect datetime value: '9223372036854775807' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '-1' -Warning 1292 Incorrect datetime value: '200506271758' +Warning 1292 Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 2 +Warning 1292 Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 3 +Warning 1292 Incorrect datetime value: '-1' for column 'my_bigint' at row 5 +Warning 1292 Incorrect datetime value: '200506271758' for column 'my_bigint' at row 6 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_bigint` as datetime) AS `CAST(my_bigint AS DATETIME)`,`t1_values`.`my_bigint` AS `my_bigint`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci @@ -4115,15 +4114,14 @@ CAST(my_bigint AS DATETIME) my_bigint id NULL NULL 1 NULL -9223372036854775808 2 NULL 9223372036854775807 3 -NULL 0 4 +0000-00-00 00:00:00 0 4 NULL -1 5 NULL 200506271758 18 Warnings: -Warning 1292 Incorrect datetime value: '-9223372036854775808' -Warning 1292 Incorrect datetime value: '9223372036854775807' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '-1' -Warning 1292 Incorrect datetime value: '200506271758' +Warning 1292 Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '-1' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '200506271758' for column 'my_bigint' at row 1 DROP VIEW v1; @@ -4456,14 +4454,13 @@ CAST(my_bigint AS DATE) my_bigint id NULL NULL 1 NULL -9223372036854775808 2 NULL 9223372036854775807 3 -NULL 0 4 +0000-00-00 0 4 NULL -1 5 2005-06-27 20050627 12 Warnings: -Warning 1292 Incorrect datetime value: '-9223372036854775808' -Warning 1292 Incorrect datetime value: '9223372036854775807' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '-1' +Warning 1292 Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 2 +Warning 1292 Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 3 +Warning 1292 Incorrect datetime value: '-1' for column 'my_bigint' at row 5 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_bigint` as date) AS `CAST(my_bigint AS DATE)`,`t1_values`.`my_bigint` AS `my_bigint`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci @@ -4474,14 +4471,13 @@ CAST(my_bigint AS DATE) my_bigint id NULL NULL 1 NULL -9223372036854775808 2 NULL 9223372036854775807 3 -NULL 0 4 +0000-00-00 0 4 NULL -1 5 2005-06-27 20050627 12 Warnings: -Warning 1292 Incorrect datetime value: '-9223372036854775808' -Warning 1292 Incorrect datetime value: '9223372036854775807' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '-1' +Warning 1292 Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 1 +Warning 1292 Incorrect datetime value: '-1' for column 'my_bigint' at row 1 DROP VIEW v1; diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index 56036c4c7ba..34437df2f2c 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -539,6 +539,22 @@ INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02'); EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=DATE'2001-01-01' AND COALESCE(a)>=DATE'2001-01-01'; DROP TABLE t1; +--echo # +--echo # MDEV-8658 DATE(zerofill_column) and DATE(COALESCE(zerofill_column)) return different results +--echo # +CREATE TABLE t1 (a INT(6) ZEROFILL, b DECIMAL(6) ZEROFILL, c DOUBLE(6,0) ZEROFILL); +INSERT INTO t1 VALUES (1,1,1); +INSERT INTO t1 VALUES (10101,10101,10101); +SELECT DATE(a), DATE(b), DATE(c) FROM t1; +SELECT DATE(COALESCE(a)), DATE(COALESCE(b)), DATE(COALESCE(c)) FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a INT(6), b DECIMAL(6), c DOUBLE(6,0)); +INSERT INTO t1 VALUES (1,1,1); +INSERT INTO t1 VALUES (10101,10101,10101); +SELECT DATE(a), DATE(b), DATE(c) FROM t1; +SELECT DATE(COALESCE(a)), DATE(COALESCE(b)), DATE(COALESCE(c)) FROM t1; +DROP TABLE t1; + --echo # --echo # End of 10.1 tests diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test index d9d34ac124f..b257aa4f455 100644 --- a/mysql-test/t/type_time.test +++ b/mysql-test/t/type_time.test @@ -688,6 +688,33 @@ SELECT * FROM t1 WHERE a>TIME'00:00:00'; SELECT * FROM t1 WHERE a>TIME('00:00:00'); DROP TABLE t1; + +--echo # +--echo # MDEV-8660 TIME(int_zerofill_column) returns a wrong result +--echo # +CREATE TABLE t1 (a BIGINT(15), b BIGINT(15) ZEROFILL); +INSERT INTO t1 VALUES (9,9); +SELECT TIME(a),TIME(b) FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES (-9223372036854775808); +SELECT CAST(a AS TIME), CAST(-9223372036854775808 AS TIME) FROM t1; +DROP TABLE t1; + +# Make sure all numeric types produce the same TIME value +CREATE TABLE t1 (a INT, b DECIMAL, c DOUBLE); +INSERT INTO t1 VALUES (-9000000,-9000000,-9000000); +INSERT INTO t1 VALUES (-1,-1,-1); +INSERT INTO t1 VALUES (0,0,0),(1,1,1),(9,9,9); +INSERT INTO t1 VALUES (9000000,9000000,9000000); +SELECT a, TIME(a),TIME(b),TIME(c) FROM t1 ORDER BY a; +DROP TABLE t1; +CREATE TABLE t1 (a INT, b DECIMAL, c DOUBLE); +INSERT INTO t1 VALUES (0,0,0),(1,1,1),(9,9,9); +INSERT INTO t1 VALUES (9000000,9000000,9000000); +SELECT a, TIME(a),TIME(b),TIME(c) FROM t1 ORDER BY a; +DROP TABLE t1; + --echo # --echo # End of 10.1 tests --echo # diff --git a/sql/field.cc b/sql/field.cc index 5ba155d1315..5b33d809ef0 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -1992,6 +1992,16 @@ my_decimal* Field_num::val_decimal(my_decimal *decimal_value) } +bool Field_num::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate) +{ + ASSERT_COLUMN_MARKED_FOR_READ; + longlong nr= val_int(); + bool neg= !(flags & UNSIGNED_FLAG) && nr < 0; + return int_to_datetime_with_warn(neg, neg ? -nr : nr, ltime, fuzzydate, + field_name); +} + + Field_str::Field_str(uchar *ptr_arg,uint32 len_arg, uchar *null_ptr_arg, uchar null_bit_arg, utype unireg_check_arg, const char *field_name_arg, CHARSET_INFO *charset_arg) @@ -3217,6 +3227,14 @@ String *Field_new_decimal::val_str(String *val_buffer, } +bool Field_new_decimal::get_date(MYSQL_TIME *ltime, ulonglong fuzzydate) +{ + my_decimal value; + return decimal_to_datetime_with_warn(val_decimal(&value), + ltime, fuzzydate, field_name); +} + + int Field_new_decimal::cmp(const uchar *a,const uchar*b) { return memcmp(a, b, bin_size); diff --git a/sql/field.h b/sql/field.h index 7b76512bc69..4f5abf6924f 100644 --- a/sql/field.h +++ b/sql/field.h @@ -1487,6 +1487,7 @@ public: { return pos_in_interval_val_real(min, max); } + bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate); }; @@ -1665,6 +1666,7 @@ public: longlong val_int(void); my_decimal *val_decimal(my_decimal *); String *val_str(String*, String *); + bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate); int cmp(const uchar *, const uchar *); void sort_string(uchar *buff, uint length); bool zero_pack() const { return 0; } |