path: root/mysql-test/r/type_time.result

drop table if exists t1;
create table t1 (t time);
insert into t1 values("10:22:33"),("12:34:56.78"),(10),(1234),(123456.78),(1234559.99),("1"),("1:23"),("1:23:45"), ("10.22"), ("-10  1:22:33.45"),("20 10:22:33"),("1999-02-03 20:33:34");
Warnings:
Note	1265	Data truncated for column 't' at row 13
insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32");
select * from t1;
t
10:22:33
12:34:56
00:00:10
00:12:34
12:34:56
123:45:59
00:00:01
01:23:00
01:23:45
00:00:10
-241:22:33
490:22:33
20:33:34
00:00:30
00:12:30
00:12:30
12:30:00
12:30:35
36:30:31
insert ignore into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a");
Warnings:
Warning	1265	Data truncated for column 't' at row 1
Warning	1265	Data truncated for column 't' at row 2
Warning	1265	Data truncated for column 't' at row 3
Warning	1265	Data truncated for column 't' at row 4
Warning	1265	Data truncated for column 't' at row 6
select * from t1;
t
10:22:33
12:34:56
00:00:10
00:12:34
12:34:56
123:45:59
00:00:01
01:23:00
01:23:45
00:00:10
-241:22:33
490:22:33
20:33:34
00:00:30
00:12:30
00:12:30
12:30:00
12:30:35
36:30:31
00:00:10
00:00:00
00:00:00
00:00:00
262:22:00
00:00:12
drop table t1;
create table t1 (t time);
insert into t1 values ('09:00:00'),('13:00:00'),('19:38:34'), ('13:00:00'),('09:00:00'),('09:00:00'),('13:00:00'),('13:00:00'),('13:00:00'),('09:00:00');
select t, time_to_sec(t),sec_to_time(time_to_sec(t)) from t1;
t	time_to_sec(t)	sec_to_time(time_to_sec(t))
09:00:00	32400	09:00:00
13:00:00	46800	13:00:00
19:38:34	70714	19:38:34
13:00:00	46800	13:00:00
09:00:00	32400	09:00:00
09:00:00	32400	09:00:00
13:00:00	46800	13:00:00
13:00:00	46800	13:00:00
13:00:00	46800	13:00:00
09:00:00	32400	09:00:00
select sec_to_time(time_to_sec(t)) from t1;
sec_to_time(time_to_sec(t))
09:00:00
13:00:00
19:38:34
13:00:00
09:00:00
09:00:00
13:00:00
13:00:00
13:00:00
09:00:00
drop table t1;
End of 4.1 tests
select cast('100:55:50' as time) < cast('24:00:00' as time);
cast('100:55:50' as time) < cast('24:00:00' as time)
0
select cast('100:55:50' as time) < cast('024:00:00' as time);
cast('100:55:50' as time) < cast('024:00:00' as time)
0
select cast('300:55:50' as time) < cast('240:00:00' as time);
cast('300:55:50' as time) < cast('240:00:00' as time)
0
select cast('100:55:50' as time) > cast('24:00:00' as time);
cast('100:55:50' as time) > cast('24:00:00' as time)
1
select cast('100:55:50' as time) > cast('024:00:00' as time);
cast('100:55:50' as time) > cast('024:00:00' as time)
1
select cast('300:55:50' as time) > cast('240:00:00' as time);
cast('300:55:50' as time) > cast('240:00:00' as time)
1
create table t1 (f1 time);
insert into t1 values ('24:00:00');
select cast('24:00:00' as time) = (select f1 from t1);
cast('24:00:00' as time) = (select f1 from t1)
1
drop table t1;
create table t1(f1 time, f2 time);
insert into t1 values('20:00:00','150:00:00');
select 1 from t1 where cast('100:00:00' as time) between f1 and f2;
1
1
drop table t1;
CREATE TABLE  t1 (
f2 date NOT NULL,
f3 int(11) unsigned NOT NULL default '0',
PRIMARY KEY  (f3, f2)
);
insert into t1 values('2007-07-01', 1);
insert into t1 values('2007-07-01', 2);
insert into t1 values('2007-07-02', 1);
insert into t1 values('2007-07-02', 2);
SELECT sum(f3) FROM t1 where f2='2007-07-01 00:00:00' group by f2;
sum(f3)
3
drop table t1;
#
# Bug #44792: valgrind warning when casting from time to time
#
CREATE TABLE t1 (c TIME);
INSERT INTO t1 VALUES ('0:00:00');
SELECT CAST(c AS TIME) FROM t1;
CAST(c AS TIME)
00:00:00
DROP TABLE t1;
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')
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);
select * from t1;
a
-13:14:15
drop table t1;
create table t1 (f1 time , f2 varchar(5), key(f1));
insert into t1 values ('00:20:01','a'),('00:20:03','b');
select * from t1 force key (f1) where f1 < curdate();
f1	f2
select * from t1 ignore key (f1) where f1 < curdate();
f1	f2
select * from t1 force key (f1) where f1 > curdate();
f1	f2
00:20:01	a
00:20:03	b
select * from t1 ignore key (f1) where f1 > curdate();
f1	f2
00:20:01	a
00:20:03	b
delete from t1;
insert into t1 values ('-00:20:01','a'),('-00:20:03','b');
select * from t1 force key (f1) where f1 < curdate();
f1	f2
-00:20:01	a
-00:20:03	b
select * from t1 ignore key (f1) where f1 < curdate();
f1	f2
-00:20:01	a
-00:20:03	b
drop table t1;
create table t1(f1 time);
insert into t1 values ('23:38:57');
select f1, f1 = '2010-10-11 23:38:57' from t1;
f1	f1 = '2010-10-11 23:38:57'
23:38:57	0
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')
2001-02-03 07:05:00
SET timestamp=DEFAULT;
#
# MDEV-4652 Wrong result for CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00')))
#
SELECT CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00')));
CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00')))
00:00:01
SELECT CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00')));
CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00')))
768:00:01
#
# MDEV-4859 Wrong value and data type of "SELECT MAX(time_column) + 1 FROM t1"
#
CREATE TABLE t1 (t0 TIME);
INSERT INTO t1 VALUES ('10:10:10');
SELECT MAX(t0)+1 FROM t1;
MAX(t0)+1
101011
CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1;
SELECT * FROM t2;
MAX(t0)+1
101011
SHOW COLUMNS FROM t2;
Field	Type	Null	Key	Default	Extra
MAX(t0)+1	bigint(12)	YES		NULL	
DROP TABLE t2,t1;
CREATE TABLE t1 (t0 TIME);
INSERT INTO t1 VALUES ('10:10:10');
SELECT MAX(t0)+1.1 FROM t1;
MAX(t0)+1.1
101011.1
CREATE TABLE t2 AS SELECT MAX(t0)+1.1 FROM t1;
SELECT * FROM t2;
MAX(t0)+1.1
101011.1
SHOW COLUMNS FROM t2;
Field	Type	Null	Key	Default	Extra
MAX(t0)+1.1	decimal(12,1)	YES		NULL	
DROP TABLE t2,t1;
CREATE TABLE t1 (t0 TIME);
INSERT INTO t1 VALUES ('10:10:10');
SELECT MAX(t0)+1e0 FROM t1;
MAX(t0)+1e0
101011
CREATE TABLE t2 AS SELECT MAX(t0)+1e0 FROM t1;
SELECT * FROM t2;
MAX(t0)+1e0
101011
SHOW COLUMNS FROM t2;
Field	Type	Null	Key	Default	Extra
MAX(t0)+1e0	double	YES		NULL	
DROP TABLE t2,t1;
CREATE TABLE t1 (t1 TIME(1));
INSERT INTO t1 VALUES ('10:10:10');
SELECT MAX(t1)+1 FROM t1;
MAX(t1)+1
101011.0
CREATE TABLE t2 AS SELECT MAX(t1)+1 FROM t1;
SELECT * FROM t2;
MAX(t1)+1
101011.0
SHOW COLUMNS FROM t2;
Field	Type	Null	Key	Default	Extra
MAX(t1)+1	decimal(13,1)	YES		NULL	
DROP TABLE t2,t1;
CREATE TABLE t1 (t0 DATETIME);
INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
SELECT MAX(t0)+1 FROM t1;
MAX(t0)+1
20010101101011
CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1;
SELECT * FROM t2;
MAX(t0)+1
20010101101011
SHOW COLUMNS FROM t2;
Field	Type	Null	Key	Default	Extra
MAX(t0)+1	bigint(21)	YES		NULL	
DROP TABLE t2,t1;
CREATE TABLE t1 (t0 DATETIME);
INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
SELECT MAX(t0)+1.1 FROM t1;
MAX(t0)+1.1
20010101101011.1
CREATE TABLE t2 AS SELECT MAX(t0)+1.1 FROM t1;
SELECT * FROM t2;
MAX(t0)+1.1
20010101101011.1
SHOW COLUMNS FROM t2;
Field	Type	Null	Key	Default	Extra
MAX(t0)+1.1	decimal(21,1)	YES		NULL	
DROP TABLE t2,t1;
CREATE TABLE t1 (t0 DATETIME);
INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
SELECT MAX(t0)+1e0 FROM t1;
MAX(t0)+1e0
20010101101011
CREATE TABLE t2 AS SELECT MAX(t0)+1e0 FROM t1;
SELECT * FROM t2;
MAX(t0)+1e0
20010101101011
SHOW COLUMNS FROM t2;
Field	Type	Null	Key	Default	Extra
MAX(t0)+1e0	double	YES		NULL	
DROP TABLE t2,t1;
CREATE TABLE t1 (t1 DATETIME(1));
INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
SELECT MAX(t1)+1 FROM t1;
MAX(t1)+1
20010101101011.0
CREATE TABLE t2 AS SELECT MAX(t1)+1 FROM t1;
SELECT * FROM t2;
MAX(t1)+1
20010101101011.0
SHOW COLUMNS FROM t2;
Field	Type	Null	Key	Default	Extra
MAX(t1)+1	decimal(22,1)	YES		NULL	
DROP TABLE t2,t1;
#
# MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
#
SET sql_mode=traditional;
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED));
ERROR 22007: Incorrect time value: '18446744069414584320' for column 'a' at row 1
SET sql_mode=DEFAULT;
INSERT IGNORE INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED));
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
SELECT * FROM t1;
a
838:59:59.999999
DROP TABLE t1;
SELECT TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED);
TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED)
0
Warnings:
Warning	1292	Truncated incorrect time value: '18446744069414584320'
#
# MDEV-6100 No warning on CAST(9000000 AS TIME)
#
SELECT CAST(9000000 AS TIME);
CAST(9000000 AS TIME)
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '9000000'
#
# End of 5.3 tests
#
CREATE TABLE t1 (f1 TIME);
INSERT INTO t1 VALUES ('24:00:00');
SELECT      '24:00:00' = (SELECT f1 FROM t1);
'24:00:00' = (SELECT f1 FROM t1)
1
SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1);
CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1)
1
SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1);
CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1)
0
TRUNCATE t1;
INSERT INTO t1 VALUES ('-24:00:00');
SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1);
CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1)
0
SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1);
CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1)
1
SELECT '-24:00:00' = (SELECT f1 FROM t1);
'-24:00:00' = (SELECT f1 FROM t1)
1
DROP TABLE t1;
#
# MDEV-6592 Assertion `ltime->day == 0' failed with TIMESTAMP, MAKETIME
#
CREATE TABLE t1 (d DATE, c VARCHAR(10), KEY(d))  engine=myisam;
INSERT INTO t1 VALUES ('2008-10-02','2008-10-02'), ('2008-10-02','2008-10-02');
SELECT * FROM t1 WHERE TIMESTAMP(c,'02:04:42') AND d <=> MAKETIME(97,0,7);
d	c
DROP TABLE t1;
#
# End of 5.5 tests
#
#
# Start of 10.0 tests
#
#
# MDEV-6102 Comparison between TIME and DATETIME does not use CURRENT_DATE
#
SET timestamp=UNIX_TIMESTAMP('2014-04-14 01:02:03');
SELECT CAST(TIME'10:20:30' AS DATETIME), TIME'10:20:30'=TIMESTAMP'2014-04-14 10:20:30' AS cmp;
CAST(TIME'10:20:30' AS DATETIME)	cmp
2014-04-14 10:20:30	1
SET timestamp=DEFAULT;
#
# End of 10.0 tests
#
#
# Start of 10.1 tests
#
#
# MDEV-8699 Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x'
#
# Trailing garbage in string literals
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
00:00:00
00:00:01
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='00:00:00x';
a
00:00:00
Warnings:
Warning	1292	Truncated incorrect time value: '00:00:00x'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='00:00:00x';
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:
Warning	1292	Truncated incorrect time value: '00:00:00x'
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='00:00:00x';
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:
Warning	1292	Truncated incorrect time value: '00:00:00x'
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and <cache>(length(TIME'00:00:00')) = 30 + rand()
DROP TABLE t1;
# Trailing fractional digits in string literals
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
00:00:00
00:00:01
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='00:00:00.000000';
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='00:00:00.000000';
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 `test`.`t1`.`a` = TIME'00:00:00'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='00:00:00.000000';
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 `test`.`t1`.`a` = TIME'00:00:00' and <cache>(length(TIME'00:00:00')) = 30 + rand()
DROP TABLE t1;
# Trailing fractional digits in temporal literals
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
00:00:00
00:00:01
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'00:00:00.000000';
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'00:00:00.000000';
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 `test`.`t1`.`a` = TIME'00:00:00.000000'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'00:00:00.000000';
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 `test`.`t1`.`a` = TIME'00:00:00.000000' and <cache>(length(TIME'00:00:00')) = 30 + rand()
DROP TABLE t1;
# Trailing fractional digits in temporal literals, same precision
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'00:00:00.000000';
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'00:00:00.000000';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'00:00:00.000000';
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 `test`.`t1`.`a` = TIME'00:00:00.000000' and <cache>(length(TIME'00:00:00.000000')) = 30 + rand()
DROP TABLE t1;
# Leading spaces in string literals
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
00:00:00
00:00:01
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=' 00:00:00';
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND 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 `test`.`t1`.`a` = TIME'00:00:00'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND 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 `test`.`t1`.`a` = TIME'00:00:00' and <cache>(length(TIME'00:00:00')) = 30 + rand()
DROP TABLE t1;
# Numeric format in string literals
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
00:00:00
00:00:01
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='000000';
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='000000';
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 `test`.`t1`.`a` = TIME'00:00:00'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='000000';
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 `test`.`t1`.`a` = TIME'00:00:00' and <cache>(length(TIME'00:00:00')) = 30 + rand()
DROP TABLE t1;
# 
# MDEV-8766 Wrong result for SELECT..WHERE LENGTH(time_column)=8 AND time_column=TIMESTAMP'2001-01-01 10:20:31'
#
SET timestamp=UNIX_TIMESTAMP('2001-01-01 01:02:03');
# TIMESTAMP literal with the same scale, ok to propagate
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:31';
a
10:20:31
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
10:20:31
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31';
a
10:20:31
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31';
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 `test`.`t1`.`a` = TIME'10:20:31'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31';
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 `test`.`t1`.`a` = TIME'10:20:31' and <cache>(length(TIME'10:20:31')) = 30 + rand()
DROP TABLE t1;
# TIMESTAMP literal with a bigger scale and fractional second truncation
# Ok to propagate with precision truncation
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:31.123';
a
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
10:20:31
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.123';
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.123';
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 `test`.`t1`.`a` = TIME'10:20:31.123000'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31.123';
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 `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(length(TIME'10:20:31')) = 30 + rand()
DROP TABLE t1;
# TIMESTAMP literal with a bigger scale and no fractional second truncation
# Ok to propagate
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:31.000';
a
10:20:31
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
10:20:31
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.000';
a
10:20:31
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.000';
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 `test`.`t1`.`a` = TIME'10:20:31'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31.000';
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 `test`.`t1`.`a` = TIME'10:20:31' and <cache>(length(TIME'10:20:31')) = 30 + rand()
DROP TABLE t1;
# TIMESTAMP literal with a smaller scale
# Ok to propagate
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:31.123';
a
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.123';
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.123';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31.123';
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 `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(length(TIME'10:20:31.123000')) = 30 + rand()
DROP TABLE t1;
# TIME literal with a bigger scale and fractional second truncation
# Ok to propagate with precision truncation
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIME'10:20:31.123';
a
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
10:20:31
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.123';
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.123';
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 `test`.`t1`.`a` = TIME'10:20:31.123'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'10:20:31.123';
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 `test`.`t1`.`a` = TIME'10:20:31.123' and <cache>(length(TIME'10:20:31')) = 30 + rand()
DROP TABLE t1;
# TIME literal with a bigger scale and no fractional second truncation
# Ok to propagate
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIME'10:20:31.000';
a
10:20:31
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
10:20:31
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.000';
a
10:20:31
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.000';
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 `test`.`t1`.`a` = TIME'10:20:31.000'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'10:20:31.000';
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 `test`.`t1`.`a` = TIME'10:20:31.000' and <cache>(length(TIME'10:20:31')) = 30 + rand()
DROP TABLE t1;
# TIME literal with a smaller scale
# Ok to propagate
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIME'10:20:31.123';
a
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.123';
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.123';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'10:20:31.123';
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 `test`.`t1`.`a` = TIME'10:20:31.123' and <cache>(length(TIME'10:20:31.123000')) = 30 + rand()
DROP TABLE t1;
# TIME-alike string literal with a bigger scale and fractional second truncation
# Ok to propagate with precision truncation
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a='10:20:31.123';
a
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
10:20:31
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.123';
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.123';
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 `test`.`t1`.`a` = TIME'10:20:31.123000'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='10:20:31.123';
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 `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(length(TIME'10:20:31')) = 30 + rand()
DROP TABLE t1;
# TIME-alike string literal with a bigger scale and no fractional second truncation
# Ok to propagate
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a='10:20:31.000';
a
10:20:31
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
10:20:31
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.000';
a
10:20:31
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.000';
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 `test`.`t1`.`a` = TIME'10:20:31'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='10:20:31.000';
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 `test`.`t1`.`a` = TIME'10:20:31' and <cache>(length(TIME'10:20:31')) = 30 + rand()
DROP TABLE t1;
# TIME-alike string literal with a smaller scale
# Ok to propagate
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a='10:20:31.123';
a
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.123';
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.123';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='10:20:31.123';
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 `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(length(TIME'10:20:31.123000')) = 30 + rand()
DROP TABLE t1;
SET timestamp=DEFAULT;
SET @@old_mode=zero_date_time_cast;
# TIMESTAMP literal, old mode
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('0000-00-00 10:20:30'),('0000-00-00 10:20:31');
INSERT INTO t1 VALUES ('0000-00-01 10:20:30'),('0000-00-01 10:20:31');
INSERT INTO t1 VALUES ('31 10:20:30'),('32 10:20:30'),('33 10:20:30'),('34 10:20:30');
SELECT * FROM t1;
a
10:20:30
10:20:31
34:20:30
34:20:31
754:20:30
778:20:30
802:20:30
826:20:30
# Old mode, TIMESTAMP literal, zero YYYYMMDD, Ok to propagate
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 10:20:30';
a
10:20:30
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 10:20:30' AND LENGTH(a)=8;
a
10:20:30
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 10:20:30' AND LENGTH(a)=8;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 10:20:30' AND LENGTH(a)=30+RAND();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30' and <cache>(length(TIME'10:20:30')) = 30 + rand()
# Old mode, TIMESTAMP literal, zon-zero YYYYMMDD, no propagation
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30';
a
34:20:30
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30' AND LENGTH(a)=8;
a
34:20:30
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30' AND LENGTH(a)=8;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'0000-00-01 10:20:30' and length(`test`.`t1`.`a`) = 8
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30' AND LENGTH(a)=30+RAND();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'0000-00-01 10:20:30' and length(`test`.`t1`.`a`) = 30 + rand()
# Old mode, TIMESTAMP-alike string literal, zero YYYYMMDD, Ok to propagate
SELECT * FROM t1 WHERE a='0000-00-00 10:20:30';
a
10:20:30
SELECT * FROM t1 WHERE a='0000-00-00 10:20:30' AND LENGTH(a)=8;
a
10:20:30
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a='0000-00-00 10:20:30' AND LENGTH(a)=8;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a='0000-00-00 10:20:30' AND LENGTH(a)=30+RAND();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30' and <cache>(length(TIME'10:20:30')) = 30 + rand()
# Old mode, TIMESTAMP-alike literal, zon-zero YYYYMMDD, no propagation
SELECT * FROM t1 WHERE a='0000-00-01 10:20:30';
a
34:20:30
SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8;
a
34:20:30
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '0000-00-01 10:20:30' and length(`test`.`t1`.`a`) = 8
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=30+RAND();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '0000-00-01 10:20:30' and length(`test`.`t1`.`a`) = 30 + rand()
DROP TABLE t1;
SET @@old_mode=DEFAULT;
#
# MDEV-8706 Wrong result for SELECT..WHERE time_column=TIMESTAMP'2015-08-30 00:00:00' AND time_column='00:00:00'
#
SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30');
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00';
a
00:00:00
SELECT * FROM t1 WHERE a='00:00:00';
a
00:00:00
SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00' AND a='00:00:00';
a
00:00:00
SELECT * FROM t1 WHERE a>=TIMESTAMP'2015-08-30 00:00:00' AND a='00:00:00';
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a>=TIMESTAMP'2015-08-30 00:00:00' AND 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 `test`.`t1`.`a` = TIME'00:00:00'
DROP TABLE t1;
SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30');
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE DATE(a)<=TIMESTAMP'2015-08-30 00:00:00.1' AND a='00:00:00';
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE DATE(a)<=TIMESTAMP'2015-08-30 00:00:00.1' AND 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 `test`.`t1`.`a` = TIME'00:00:00'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE TIMESTAMP('2015-08-08',a+RAND())<=TIMESTAMP'2015-08-30 00:00:00.1' AND 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 `test`.`t1`.`a` = TIME'00:00:00' and timestamp('2015-08-08',TIME'00:00:00' + rand()) <= TIMESTAMP'2015-08-30 00:00:00.1'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE TIMESTAMP('2015-08-08',a+RAND())<=TIMESTAMP'2015-08-30 00:00:00.1' AND a='00:00:00.1';
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 `test`.`t1`.`a` = TIME'00:00:00.100000' and timestamp('2015-08-08',TIME'00:00:00' + rand()) <= TIMESTAMP'2015-08-30 00:00:00.1'
DROP TABLE t1;
SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30');
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE DATE(a)<=DATE'2015-08-30' AND a='00:00:00';
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE DATE(a)<=DATE'2015-08-30' AND 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 `test`.`t1`.`a` = TIME'00:00:00'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE TIMESTAMP('2015-08-08',a+RAND())<=DATE'2015-08-30' AND 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 `test`.`t1`.`a` = TIME'00:00:00' and timestamp('2015-08-08',TIME'00:00:00' + rand()) <= DATE'2015-08-30'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE TIMESTAMP('2015-08-08',a+RAND())<=DATE'2015-08-30' AND a='00:00:00.1';
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 `test`.`t1`.`a` = TIME'00:00:00.100000' and timestamp('2015-08-08',TIME'00:00:00' + rand()) <= DATE'2015-08-30'
DROP TABLE t1;
SET timestamp=DEFAULT;
#
# MDEV-8795 Equal expression propagation does not work for temporal literals
#
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:01'),('00:00:02');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:01' AND COALESCE(a)>=TIME'00:00:01';
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`) = TIME'00:00:01'
DROP TABLE t1;
#
# MDEV-8793 Wrong result set for SELECT ... WHERE COALESCE(time_column)=TIME('00:00:00') AND COALESCE(time_column)=DATE('2015-09-11')
#
SET timestamp=UNIX_TIMESTAMP('2015-09-11 20:20:20');
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES('10:20:30'),('00:00:00');
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00');
a
00:00:00
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11');
a
00:00:00
# TIME cast + DATE cast
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11');
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=TIME('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'
# TIME cast + DATE literal
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE'2015-09-11';
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=TIME('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'
# TIME literal + DATE cast
SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE('2015-09-11');
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=TIME'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`) = TIME'00:00:00'
# TIME literal + DATE literal
SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11';
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=TIME'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`) = TIME'00:00:00'
# TIME-alike string literal + DATE cast
SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE('2015-09-11');
a
00:00:00
EXPLAIN EXTENDED
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'
# TIME-alike string literal + DATE literal
SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE'2015-09-11';
a
00:00:00
EXPLAIN EXTENDED
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`) = DATE'2015-09-11'
# TIME-alike integer literal + DATE cast
SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE('2015-09-11');
a
00:00:00
EXPLAIN EXTENDED
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'
# TIME-alike integer literal + DATE literal
SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE'2015-09-11';
a
00:00:00
EXPLAIN EXTENDED
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`) = DATE'2015-09-11'
# DATE cast + TIME cast
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME('00:00:00');
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME('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'
# DATE cast + TIME literal
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME'00:00:00';
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME'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'
# DATE cast + TIME-alike string literal
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)='00:00:00';
a
00:00:00
EXPLAIN EXTENDED
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'
# DATE cast + TIME-alike integer literal
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=0;
a
00:00:00
EXPLAIN EXTENDED
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
# DATE literal + TIME cast
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME('00:00:00');
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME('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`) = DATE'2015-09-11'
# DATE literal + TIME literal
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME'00:00:00';
a
00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME'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`) = DATE'2015-09-11'
# DATE literal + TIME-alike string literal
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)='00:00:00';
a
00:00:00
EXPLAIN EXTENDED
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`) = DATE'2015-09-11' and coalesce(`test`.`t1`.`a`) = '00:00:00'
# DATE literal + TIME-alike integer literal
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=0;
a
00:00:00
EXPLAIN EXTENDED
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`) = DATE'2015-09-11' and coalesce(`test`.`t1`.`a`) = 0
DROP TABLE t1;
SET timestamp=DEFAULT;
#
# MDEV-8814 Wrong result for WHERE datetime_column > TIME('00:00:00')
#
CREATE TABLE t1 (a DATETIME);
INSERT INTO t1 VALUES ('2000-09-12 00:00:00'), ('2007-04-25 05:08:49');
SELECT * FROM t1 WHERE a>TIME'00:00:00';
a
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;
#
# MDEV-8862 Wrong field type for MAX(COALESCE(datetime_column))
#
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:10:10'),('10:20:30');
SELECT MAX(a), MAX(COALESCE(a)) FROM t1;
MAX(a)	MAX(COALESCE(a))
10:20:30	10:20:30
DROP TABLE t1;
#
# End of 10.1 tests
#
#
# Start of 10.2 tests
#
#
# MDEV-9393 Split Copy_field::get_copy_func() into virtual methods in Field
#
CREATE TABLE t1 (a YEAR, b TIME, c YEAR);
CREATE TABLE t2 (a YEAR);
INSERT INTO t2 VALUES (0),(1999),(2000),(2030),(2050),(2070);
INSERT INTO t1 (a,b,c) SELECT a,a,a FROM t2;
Warnings:
Warning	1265	Data truncated for column 'b' at row 2
Warning	1265	Data truncated for column 'b' at row 6
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
ALTER TABLE t1 MODIFY c TIME;
Warnings:
Warning	1265	Data truncated for column 'c' at row 2
Warning	1265	Data truncated for column 'c' at row 6
SELECT * FROM t1;
a	b	c
0000	00:00:00	00:00:00
1999	00:00:00	00:00:00
2000	00:20:00	00:20:00
2030	00:20:30	00:20:30
2050	00:20:50	00:20:50
2070	00:00:00	00:00:00
DROP TABLE t1,t2;
#
# End of 10.2 tests
#
#
# Start of 10.3 tests
#
#
# MDEV-11514 IN with a mixture of TIME and DATETIME returns a wrong result
#
SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00');
SELECT TIME'10:20:30' IN (102030,TIME'10:20:31');
TIME'10:20:30' IN (102030,TIME'10:20:31')
1
SELECT TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32');
TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32')
1
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:30'),('10:20:31'),('10:20:32');
SELECT a FROM t1 WHERE a IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32') ORDER BY a;
a
10:20:30
10:20:31
10:20:32
DROP TABLE t1;
SET timestamp=DEFAULT;