summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
authorAlexander Barkov <bar@mnogosearch.org>2014-03-07 00:21:25 +0400
committerAlexander Barkov <bar@mnogosearch.org>2014-03-07 00:21:25 +0400
commit89e171535563dcdf1eeb309e54abc4da2f5dd539 (patch)
tree79331d73d127f4226b274e86f476ba1f86c721e9 /mysql-test/r
parentb95c8ce530cbbd92b232324dc2c4376615bd1b5d (diff)
downloadmariadb-git-89e171535563dcdf1eeb309e54abc4da2f5dd539.tar.gz
MDEV-5372 Make "CAST(time_expr AS DATETIME)" compatible with MySQL-5.6 (and the SQL Standard)
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/cast.result13
-rw-r--r--mysql-test/r/dyncol.result8
-rw-r--r--mysql-test/r/func_time.result36
-rw-r--r--mysql-test/r/old-mode.result80
-rw-r--r--mysql-test/r/temporal_literal.result34
-rw-r--r--mysql-test/r/timezone2.result10
-rw-r--r--mysql-test/r/type_datetime_hires.result6
-rw-r--r--mysql-test/r/type_time.result10
-rw-r--r--mysql-test/r/type_time_hires.result28
-rw-r--r--mysql-test/r/type_timestamp_hires.result14
10 files changed, 161 insertions, 78 deletions
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 5e933914f5d..8ae61881c07 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -1,3 +1,4 @@
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
select CAST(1-2 AS UNSIGNED);
CAST(1-2 AS UNSIGNED)
18446744073709551615
@@ -62,7 +63,7 @@ cast(12.444 as double)
12.444
select cast(cast("20:01:01" as time) as datetime);
cast(cast("20:01:01" as time) as datetime)
-0000-00-00 20:01:01
+2001-02-03 20:01:01
select cast(cast("8:46:06.23434" AS time) as decimal(32,10));
cast(cast("8:46:06.23434" AS time) as decimal(32,10))
84606.0000000000
@@ -764,7 +765,7 @@ cast(cast("2101-00-01 02:03:04" as datetime) as time)
02:03:04
SELECT CAST(CAST('20:05:05' AS TIME) as date);
CAST(CAST('20:05:05' AS TIME) as date)
-0000-00-00
+2001-02-03
set sql_mode= TRADITIONAL;
select cast("2101-00-01 02:03:04" as datetime);
cast("2101-00-01 02:03:04" as datetime)
@@ -778,9 +779,7 @@ Warnings:
Warning 1292 Incorrect datetime value: '2101-00-01 02:03:04'
SELECT CAST(CAST('20:05:05' AS TIME) as date);
CAST(CAST('20:05:05' AS TIME) as date)
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '20:05:05'
+2001-02-03
set sql_mode=DEFAULT;
create table t1 (f1 time, f2 date, f3 datetime);
insert into t1 values ('11:22:33','2011-12-13','2011-12-13 11:22:33');
@@ -790,9 +789,7 @@ cast(f1 as unsigned) cast(f2 as unsigned) cast(f3 as unsigned)
drop table t1;
SELECT CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY;
CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '0000-00-00'
+2001-02-04
SET SQL_MODE=ALLOW_INVALID_DATES;
SELECT DATE("foo");
DATE("foo")
diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result
index efe519fe3f9..ca2130cd700 100644
--- a/mysql-test/r/dyncol.result
+++ b/mysql-test/r/dyncol.result
@@ -697,14 +697,14 @@ column_get(column_create(1, 0), 1 as datetime)
select column_get(column_create(1, "2001021"), 1 as datetime);
column_get(column_create(1, "2001021"), 1 as datetime)
2020-01-02 01:00:00
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as datetime);
column_get(column_create(1, "8:46:06.23434" AS time), 1 as datetime)
-0000-00-00 08:46:06
+2001-02-03 08:46:06
select column_get(column_create(1, "-808:46:06.23434" AS time), 1 as datetime);
column_get(column_create(1, "-808:46:06.23434" AS time), 1 as datetime)
-NULL
-Warnings:
-Warning 1292 Truncated incorrect datetime value: '-808:46:06'
+2000-12-31 07:13:53
+SET timestamp=DEFAULT;
set @@sql_mode="allow_invalid_dates";
select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as datetime);
column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as datetime)
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index fab0ebe2ba7..a548d5c18f1 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -1719,6 +1719,8 @@ create table t1(a time);
insert into t1 values ('00:00:00'),('00:01:00');
select 1 from t1 where 1 < some (select cast(a as datetime) from t1);
1
+1
+1
drop table t1;
select time('10:10:10') > 10;
time('10:10:10') > 10
@@ -1853,9 +1855,11 @@ 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');
convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow')
NULL
+SET timestamp=DEFAULT;
create table t1 (f1 integer, f2 date);
insert into t1 values (1,'2011-05-05'),(2,'2011-05-05'),(3,'2011-05-05'),(4,'2011-05-05'),(5,'2011-05-05'),(6, '2011-05-06');
select * from t1 where 1 and concat(f2)=MAKEDATE(2011, 125);
@@ -2330,6 +2334,7 @@ SELECT * FROM t1;
TIMESTAMP('2001-01-01 00:00:00','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1','10:10:10') TIMESTAMP('2001-01-01 00:00:00.12','10:10:10') TIMESTAMP('2001-01-01 00:00:00.123','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10') TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10') TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10')
2001-01-01 10:10:10 2001-01-01 10:10:10.1 2001-01-01 10:10:10.12 2001-01-01 10:10:10.123 2001-01-01 10:10:10.1234 2001-01-01 10:10:10.12345 2001-01-01 10:10:10.123456 2001-01-01 10:10:10.123456
DROP TABLE t1;
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
CREATE TABLE t1 AS SELECT
TIMESTAMP('00:00:00','10:10:10'),
TIMESTAMP(TIME('00:00:00'),'10:10:10');
@@ -2339,8 +2344,9 @@ TIMESTAMP('00:00:00','10:10:10') datetime YES NULL
TIMESTAMP(TIME('00:00:00'),'10:10:10') datetime YES NULL
SELECT * FROM t1;
TIMESTAMP('00:00:00','10:10:10') TIMESTAMP(TIME('00:00:00'),'10:10:10')
-NULL NULL
+NULL 2001-02-03 10:10:10
DROP TABLE t1;
+SET timestamp=DEFAULT;
#
# MDEV-4869 Wrong result of MAKETIME(0, 0, -0.1)
#
@@ -2350,6 +2356,7 @@ NULL
#
# MDEV-4857 Wrong result of HOUR('1 00:00:00')
#
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT HOUR('1 02:00:00'), HOUR('26:00:00');
HOUR('1 02:00:00') HOUR('26:00:00')
26 26
@@ -2361,7 +2368,7 @@ HOUR(TIME('1 02:00:00')) HOUR(TIME('26:00:00'))
26 26
SELECT DAY(TIME('1 02:00:00')), DAY(TIME('26:00:00'));
DAY(TIME('1 02:00:00')) DAY(TIME('26:00:00'))
-0 0
+4 4
SELECT EXTRACT(HOUR FROM '1 02:00:00'), EXTRACT(HOUR FROM '26:00:00');
EXTRACT(HOUR FROM '1 02:00:00') EXTRACT(HOUR FROM '26:00:00')
2 2
@@ -2374,6 +2381,7 @@ EXTRACT(HOUR FROM TIME('1 02:00:00')) EXTRACT(HOUR FROM TIME('26:00:00'))
SELECT EXTRACT(DAY FROM TIME('1 02:00:00')), EXTRACT(DAY FROM TIME('26:00:00'));
EXTRACT(DAY FROM TIME('1 02:00:00')) EXTRACT(DAY FROM TIME('26:00:00'))
1 1
+SET timestamp=DEFAULT;
#
# MDEV-5458 RQG hits 'sql/tztime.cc:799: my_time_t sec_since_epoch(int, int, int, int, int, int): Assertion `mon > 0 && mon < 13' failed.'
#
@@ -2382,13 +2390,11 @@ CREATE TABLE t1 (t TIME);
INSERT INTO t1 VALUES ('03:22:30'),('18:30:05');
SELECT CONVERT_TZ(GREATEST(t, CURRENT_DATE()), '+02:00', '+10:00') FROM t1;
CONVERT_TZ(GREATEST(t, CURRENT_DATE()), '+02:00', '+10:00')
-NULL
-NULL
+2014-02-26 06:59:59
+2014-02-26 06:59:59
Warnings:
Warning 1292 Truncated incorrect time value: '1296:00:00'
-Warning 1292 Incorrect datetime value: '838:59:59'
Warning 1292 Truncated incorrect time value: '1296:00:00'
-Warning 1292 Incorrect datetime value: '838:59:59'
SELECT GREATEST(t, CURRENT_DATE()) FROM t1;
GREATEST(t, CURRENT_DATE())
838:59:59
@@ -2446,30 +2452,32 @@ SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR)
2012-12-21 00:00:00
DROP TABLE t1;
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
CREATE TABLE t1 (a DATETIME, b TIME);
INSERT INTO t1 VALUES (NULL, '00:20:12');
SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1;
IF(1,ADDDATE(IFNULL(a,b),0),1)
-0000-00-00 00:20:12
+2001-02-03 00:20:12
SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1;
CAST(ADDDATE(IFNULL(a,b),0) AS CHAR)
-0000-00-00 00:20:12
+2001-02-03 00:20:12
SELECT CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) FROM t1;
CAST(ADDDATE(COALESCE(a,b),0) AS CHAR)
-0000-00-00 00:20:12
+2001-02-03 00:20:12
SELECT CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR)
-0000-00-00 00:20:12
+2001-02-03 00:20:12
SELECT IF(1,ADDTIME(IFNULL(a,b),0),1) FROM t1;
IF(1,ADDTIME(IFNULL(a,b),0),1)
-NULL
+2001-02-03 00:20:12
SELECT CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) FROM t1;
CAST(ADDTIME(IFNULL(a,b),0) AS CHAR)
-NULL
+2001-02-03 00:20:12
SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1;
CAST(ADDTIME(COALESCE(a,b),0) AS CHAR)
-NULL
+2001-02-03 00:20:12
SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR)
-NULL
+2001-02-03 00:20:12
DROP TABLE t1;
+SET timestamp=DEFAULT;
diff --git a/mysql-test/r/old-mode.result b/mysql-test/r/old-mode.result
index eec08d4d5c8..0b10067c19b 100644
--- a/mysql-test/r/old-mode.result
+++ b/mysql-test/r/old-mode.result
@@ -19,3 +19,83 @@ drop table t1,t2;
SHOW PROCESSLIST;
Id User Host db Command Time State Info
<Id> root <Host> test Query <Time> <State> SHOW PROCESSLIST
+#
+# MDEV-5372 Make "CAST(time_expr AS DATETIME)" compatible with the SQL Standard)
+#
+SELECT CAST(TIME'-10:30:30' AS DATETIME);
+CAST(TIME'-10:30:30' AS DATETIME)
+NULL
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '-10:30:30'
+SELECT CAST(TIME'10:20:30' AS DATETIME);
+CAST(TIME'10:20:30' AS DATETIME)
+0000-00-00 10:20:30
+SELECT CAST(TIME'830:20:30' AS DATETIME);
+CAST(TIME'830:20:30' AS DATETIME)
+0000-01-03 14:20:30
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES (TIME'-10:20:30');
+Warnings:
+Warning 1265 Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES (TIME'10:20:30');
+INSERT INTO t1 VALUES (TIME'830:20:30');
+SELECT * FROM t1;
+a
+0000-00-00 00:00:00
+0000-00-00 10:20:30
+0000-01-03 14:20:30
+DROP TABLE t1;
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES (TIME'-10:20:30');
+Warnings:
+Warning 1265 Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES (TIME'10:20:30');
+Warnings:
+Warning 1265 Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES (TIME'830:20:30');
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+DROP TABLE t1;
+CREATE TABLE t1 (a TIME);
+INSERT INTO t1 VALUES (TIME'-10:20:30');
+INSERT INTO t1 VALUES (TIME'10:20:30');
+INSERT INTO t1 VALUES (TIME'830:20:30');
+SELECT a, CAST(a AS DATETIME), TO_DAYS(a) FROM t1;
+a CAST(a AS DATETIME) TO_DAYS(a)
+-10:20:30 NULL NULL
+10:20:30 0000-00-00 10:20:30 NULL
+830:20:30 0000-01-03 14:20:30 NULL
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '-10:20:30'
+Warning 1264 Out of range value for column 'a' at row 1
+Warning 1264 Out of range value for column 'a' at row 2
+Warning 1264 Out of range value for column 'a' at row 3
+DROP TABLE t1;
+SELECT TO_DAYS(TIME'-10:20:30');
+TO_DAYS(TIME'-10:20:30')
+NULL
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '-10:20:30'
+SELECT TO_DAYS(TIME'10:20:30');
+TO_DAYS(TIME'10:20:30')
+NULL
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '10:20:30'
+SELECT TO_DAYS(TIME'830:20:30');
+TO_DAYS(TIME'830:20:30')
+3
+CREATE TABLE t1 (a DATETIME, b TIME);
+INSERT INTO t1 VALUES (NULL, '00:20:12');
+INSERT INTO t1 VALUES (NULL, '-00:20:12');
+SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1;
+IF(1,ADDDATE(IFNULL(a,b),0),1)
+0000-00-00 00:20:12
+NULL
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '-00:20:12'
+DROP TABLE t1;
diff --git a/mysql-test/r/temporal_literal.result b/mysql-test/r/temporal_literal.result
index ea0421c0940..44525aae761 100644
--- a/mysql-test/r/temporal_literal.result
+++ b/mysql-test/r/temporal_literal.result
@@ -371,49 +371,35 @@ DROP TABLE t1;
#
# TIME literals in no-zero date context
#
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT TO_DAYS(TIME'00:00:00');
TO_DAYS(TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+730884
SELECT TO_SECONDS(TIME'00:00:00');
TO_SECONDS(TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+63148377600
SELECT DAYOFYEAR(TIME'00:00:00');
DAYOFYEAR(TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+34
SELECT WEEK(TIME'00:00:00');
WEEK(TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+4
SELECT YEARWEEK(TIME'00:00:00');
YEARWEEK(TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+200104
SELECT WEEKDAY(TIME'00:00:00');
WEEKDAY(TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+5
SELECT CONVERT_TZ(TIME'00:00:00','+00:00','+01:00');
CONVERT_TZ(TIME'00:00:00','+00:00','+01:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+2001-02-03 01:00:00
SELECT DATE_ADD(TIME'00:00:00', INTERVAL 1 HOUR);
DATE_ADD(TIME'00:00:00', INTERVAL 1 HOUR)
01:00:00
SELECT TIMESTAMPDIFF(SECOND,TIME'00:00:00', TIME'00:00:00');
TIMESTAMPDIFF(SECOND,TIME'00:00:00', TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+0
+SET timestamp=DEFAULT;
#
# Testing Item_func::fix_fields()
#
diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result
index 0ac4ddca277..096e996bffb 100644
--- a/mysql-test/r/timezone2.result
+++ b/mysql-test/r/timezone2.result
@@ -315,16 +315,14 @@ End of 5.1 tests
#
# MDEV-4653 Wrong result for CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5')
#
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5');
CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+2001-02-03 07:05:00
SELECT CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5');
CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+2001-02-03 07:05:00
+SET timestamp=DEFAULT;
#
# MDEV-5506 safe_mutex: Trying to lock unitialized mutex at safemalloc.c on server shutdown after SELECT with CONVERT_TZ
#
diff --git a/mysql-test/r/type_datetime_hires.result b/mysql-test/r/type_datetime_hires.result
index 203e45b86cb..61c8001d98e 100644
--- a/mysql-test/r/type_datetime_hires.result
+++ b/mysql-test/r/type_datetime_hires.result
@@ -1,3 +1,4 @@
+SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
drop table if exists t1, t2, t3;
create table t1 (a datetime(7));
ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6.
@@ -197,7 +198,7 @@ bigint_f5_datetime 20101112111417
varchar_f6_datetime 2010-11-12 11:14:17.765432
alter table t1 modify time4_f0_datetime datetime(0), modify datetime3_f1_datetime datetime(1), modify date_f2_datetime datetime(2), modify double_f3_datetime datetime(3), modify decimal5_f4_datetime datetime(4), modify bigint_f5_datetime datetime(5), modify varchar_f6_datetime datetime(6);
select * from t1;
-time4_f0_datetime 0000-00-00 11:14:17
+time4_f0_datetime 2001-02-03 11:14:17
datetime3_f1_datetime 2010-11-12 11:14:17.7
date_f2_datetime 2010-11-12 00:00:00.00
double_f3_datetime 2010-11-12 11:14:17.766
@@ -207,7 +208,7 @@ varchar_f6_datetime 2010-11-12 11:14:17.765432
delete from t1;
insert t1 select * from t2;
select * from t1;
-time4_f0_datetime 0000-00-00 11:14:17
+time4_f0_datetime 2001-02-03 11:14:17
datetime3_f1_datetime 2010-11-12 11:14:17.7
date_f2_datetime 2010-11-12 00:00:00.00
double_f3_datetime 2010-11-12 11:14:17.765
@@ -262,6 +263,7 @@ a b
2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561
drop view v1;
drop table t1, t2;
+SET timestamp=DEFAULT;
CREATE TABLE t1 (
taken datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00',
id int(11) NOT NULL DEFAULT '0',
diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result
index ef02368e375..00a7a9b783e 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -144,12 +144,14 @@ End of 5.0 tests
#
# Bug#53942 valgrind warnings with timestamp() function and incomplete datetime values
#
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
CREATE TABLE t1(f1 TIME);
INSERT INTO t1 VALUES ('23:38:57');
SELECT TIMESTAMP(f1,'1') FROM t1;
TIMESTAMP(f1,'1')
-NULL
+2001-02-03 23:38:58
DROP TABLE t1;
+SET timestamp=DEFAULT;
End of 5.1 tests
create table t1 (a time);
insert t1 values (-131415);
@@ -177,11 +179,11 @@ drop table t1;
#
# MDEV-4634 Crash in CONVERT_TZ
#
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT CONVERT_TZ(GREATEST(TIME('00:00:00'),TIME('00:00:00')),'+00:00','+7:5');
CONVERT_TZ(GREATEST(TIME('00:00:00'),TIME('00:00:00')),'+00:00','+7:5')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+2001-02-03 07:05:00
+SET timestamp=DEFAULT;
#
# MDEV-4652 Wrong result for CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00')))
#
diff --git a/mysql-test/r/type_time_hires.result b/mysql-test/r/type_time_hires.result
index bf54434a04c..2239ecfcaaa 100644
--- a/mysql-test/r/type_time_hires.result
+++ b/mysql-test/r/type_time_hires.result
@@ -1,3 +1,4 @@
+SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
drop table if exists t1, t2, t3;
create table t1 (a time(7));
ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6.
@@ -194,8 +195,8 @@ f5_time 11:14:17.76543
f6_time 11:14:17.765432
select cast(f0_time as time(4)) time4_f0_time, cast(f1_time as datetime(3)) datetime3_f1_time, cast(f2_time as date) date_f2_time, cast(f4_time as double) double_f3_time, cast(f4_time as decimal(40,5)) decimal5_f4_time, cast(f5_time as signed) bigint_f5_time, cast(f6_time as char(255)) varchar_f6_time from t1;
time4_f0_time 11:14:17.0000
-datetime3_f1_time 0000-00-00 11:14:17.700
-date_f2_time 0000-00-00
+datetime3_f1_time 2001-02-03 11:14:17.700
+date_f2_time 2001-02-03
double_f3_time 111417.7654
decimal5_f4_time 111417.76540
bigint_f5_time 111417
@@ -208,8 +209,8 @@ Code 1265
Message Data truncated for column 'date_f2_time' at row 1
select * from t2;
time4_f0_time 11:14:17.0000
-datetime3_f1_time 0000-00-00 11:14:17.700
-date_f2_time 0000-00-00
+datetime3_f1_time 2001-02-03 11:14:17.700
+date_f2_time 2001-02-03
double_f3_time 111417.765
decimal5_f4_time 111417.76540
bigint_f5_time 111417
@@ -221,13 +222,20 @@ Code 1265
Message Data truncated for column 'date_f2_time' at row 1
select * from t1;
time4_f0_time 11:14:17.0000
-datetime3_f1_time 0000-00-00 11:14:17.700
-date_f2_time 0000-00-00
+datetime3_f1_time 2001-02-03 11:14:17.700
+date_f2_time 2001-02-03
double_f3_time 111417.765
decimal5_f4_time 111417.76540
bigint_f5_time 111417
varchar_f6_time 11:14:17.765432
alter table t1 modify time4_f0_time time(0), modify datetime3_f1_time time(1), modify date_f2_time time(2), modify double_f3_time time(3), modify decimal5_f4_time time(4), modify bigint_f5_time time(5), modify varchar_f6_time time(6);
+Warnings:
+Level Note
+Code 1265
+Message Data truncated for column 'datetime3_f1_time' at row 1
+Level Note
+Code 1265
+Message Data truncated for column 'date_f2_time' at row 1
select * from t1;
time4_f0_time 11:14:17
datetime3_f1_time 11:14:17.7
@@ -238,6 +246,13 @@ bigint_f5_time 11:14:17.00000
varchar_f6_time 11:14:17.765432
delete from t1;
insert t1 select * from t2;
+Warnings:
+Level Note
+Code 1265
+Message Data truncated for column 'datetime3_f1_time' at row 1
+Level Note
+Code 1265
+Message Data truncated for column 'date_f2_time' at row 1
select * from t1;
time4_f0_time 11:14:17
datetime3_f1_time 11:14:17.7
@@ -301,6 +316,7 @@ a b
04:05:06.000000 04:05:06.789100
drop view v1;
drop table t1, t2;
+SET timestamp=DEFAULT;
create table t1 (a time(4) not null, key(a));
insert into t1 values ('1:2:3.001'),('1:2:3'), ('-00:00:00.6'),('-00:00:00.7'),('-00:00:00.8'),('-00:00:00.9'),('-00:00:01.0'),('-00:00:01.1'),('-00:00:01.000000'),('-00:00:01.100001'),('-00:00:01.000002'),('-00:00:01.090000');
select * from t1 order by a;
diff --git a/mysql-test/r/type_timestamp_hires.result b/mysql-test/r/type_timestamp_hires.result
index cc2cb6a403d..dc1cbb377f9 100644
--- a/mysql-test/r/type_timestamp_hires.result
+++ b/mysql-test/r/type_timestamp_hires.result
@@ -1,3 +1,4 @@
+SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
drop table if exists t1, t2, t3;
create table t1 (a timestamp(7));
ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6.
@@ -196,12 +197,8 @@ decimal5_f4_timestamp 20101112111417.76540
bigint_f5_timestamp 20101112111417
varchar_f6_timestamp 2010-11-12 11:14:17.765432
alter table t1 modify time4_f0_timestamp timestamp(0), modify datetime3_f1_timestamp timestamp(1), modify date_f2_timestamp timestamp(2), modify double_f3_timestamp timestamp(3), modify decimal5_f4_timestamp timestamp(4), modify bigint_f5_timestamp timestamp(5), modify varchar_f6_timestamp timestamp(6);
-Warnings:
-Level Warning
-Code 1265
-Message Data truncated for column 'time4_f0_timestamp' at row 1
select * from t1;
-time4_f0_timestamp 0000-00-00 00:00:00
+time4_f0_timestamp 2001-02-03 11:14:17
datetime3_f1_timestamp 2010-11-12 11:14:17.7
date_f2_timestamp 2010-11-12 00:00:00.00
double_f3_timestamp 2010-11-12 11:14:17.766
@@ -210,12 +207,8 @@ bigint_f5_timestamp 2010-11-12 11:14:17.00000
varchar_f6_timestamp 2010-11-12 11:14:17.765432
delete from t1;
insert t1 select * from t2;
-Warnings:
-Level Warning
-Code 1265
-Message Data truncated for column 'time4_f0_timestamp' at row 1
select * from t1;
-time4_f0_timestamp 0000-00-00 00:00:00
+time4_f0_timestamp 2001-02-03 11:14:17
datetime3_f1_timestamp 2010-11-12 11:14:17.7
date_f2_timestamp 2010-11-12 00:00:00.00
double_f3_timestamp 2010-11-12 11:14:17.765
@@ -270,6 +263,7 @@ a b
2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561
drop view v1;
drop table t1, t2;
+SET timestamp=DEFAULT;
set time_zone='+03:00';
set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456;
create table t1 (a timestamp(5));
View Lorry Controller Status