select CAST(1-2 AS UNSIGNED);
CAST(1-2 AS UNSIGNED)
18446744073709551615
select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER);
CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER)
-1
select CAST('10 ' as unsigned integer);
CAST('10 ' as unsigned integer)
10
select CAST('10x' as unsigned integer);
CAST('10x' as unsigned integer)
10
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '10x'
select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1;
cast(-5 as unsigned) | 1	cast(-5 as unsigned) & -1
18446744073709551611	18446744073709551611
select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1;
cast(-5 as unsigned) -1	cast(-5 as unsigned) + 1
18446744073709551610	18446744073709551612
select ~5, cast(~5 as signed);
~5	cast(~5 as signed)
18446744073709551610	-6
explain select ~5, cast(~5 as signed);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	/* select#1 */ select ~(5) AS `~5`,cast(~(5) as signed) AS `cast(~5 as signed)`
select cast(5 as unsigned) -6.0;
cast(5 as unsigned) -6.0
-1.0
select cast(NULL as signed), cast(1/0 as signed);
cast(NULL as signed)	cast(1/0 as signed)
NULL	NULL
Warnings:
Warning	1365	Division by 0
select cast(NULL as unsigned), cast(1/0 as unsigned);
cast(NULL as unsigned)	cast(1/0 as unsigned)
NULL	NULL
Warnings:
Warning	1365	Division by 0
select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A";
cast("A" as binary) = "a"	cast(BINARY "a" as CHAR) = "A"
0	1
Warnings:
Warning	1287	'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME);
cast("2001-1-1" as DATE)	cast("2001-1-1" as DATETIME)
2001-01-01	2001-01-01 00:00:00
select cast("1:2:3" as TIME);
cast("1:2:3" as TIME)
01:02:03
select CONVERT("2004-01-22 21:45:33",DATE);
CONVERT("2004-01-22 21:45:33",DATE)
2004-01-22
select 10+'10';
10+'10'
20
select 10.0+'10';
10.0+'10'
20
select 10E+0+'10';
10E+0+'10'
20
SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33" USING latin1);
CONVERT(TIMESTAMP "2004-01-22 21:45:33" USING latin1)
2004-01-22 21:45:33
SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33", CHAR);
CONVERT(TIMESTAMP "2004-01-22 21:45:33", CHAR)
2004-01-22 21:45:33
SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33", CHAR(4));
CONVERT(TIMESTAMP "2004-01-22 21:45:33", CHAR(4))
2004
Warnings:
Warning	1292	Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33'
SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33", BINARY(4));
CONVERT(TIMESTAMP "2004-01-22 21:45:33", BINARY(4))
2004
Warnings:
Warning	1292	Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33'
select CAST(TIMESTAMP "2004-01-22 21:45:33" AS BINARY(4));
CAST(TIMESTAMP "2004-01-22 21:45:33" AS BINARY(4))
2004
Warnings:
Warning	1292	Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33'
select CAST(0xb3 as signed);
CAST(0xb3 as signed)
179
select CAST(0x8fffffffffffffff as signed);
CAST(0x8fffffffffffffff as signed)
-8070450532247928833
select CAST(0xffffffffffffffff as unsigned);
CAST(0xffffffffffffffff as unsigned)
18446744073709551615
select CAST(0xfffffffffffffffe as signed);
CAST(0xfffffffffffffffe as signed)
-2
select cast('-10a' as signed integer);
cast('-10a' as signed integer)
-10
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '-10a'
select cast('a10' as unsigned integer);
cast('a10' as unsigned integer)
0
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'a10'
select 10+'a';
10+'a'
10
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'a'
select 10.0+cast('a' as decimal);
10.0+cast('a' as decimal)
10.0
Warnings:
Warning	1292	Truncated incorrect DECIMAL value: 'a'
select 10E+0+'a';
10E+0+'a'
10
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'a'
select cast('18446744073709551616' as unsigned);
cast('18446744073709551616' as unsigned)
18446744073709551615
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '18446744073709551616'
select cast('18446744073709551616' as signed);
cast('18446744073709551616' as signed)
-1
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '18446744073709551616'
select cast('9223372036854775809' as signed);
cast('9223372036854775809' as signed)
-9223372036854775807
Warnings:
Warning	1105	Cast to signed converted positive out-of-range integer to its negative complement
select cast('-1' as unsigned);
cast('-1' as unsigned)
18446744073709551615
Warnings:
Warning	1105	Cast to unsigned converted negative integer to its positive complement
select cast('abc' as signed);
cast('abc' as signed)
0
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'abc'
select cast('1a' as signed);
cast('1a' as signed)
1
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '1a'
select cast('' as signed);
cast('' as signed)
0
Warnings:
Warning	1292	Truncated incorrect INTEGER value: ''
select hex(cast(_latin1'test' as char character set latin2));
hex(cast(_latin1'test' as char character set latin2))
74657374
select hex(cast(_koi8r x'D4C5D3D4' as char character set cp1251));
hex(cast(_koi8r x'D4C5D3D4' as char character set cp1251))
F2E5F1F2
create table t1 select cast(_koi8r x'D4C5D3D4' as char character set cp1251) as t;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t` varchar(4) CHARACTER SET cp1251 DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
drop table t1;
select
cast(_latin1'ab'  AS char charset binary)    as c1,
cast(_latin1'a '  AS char charset binary)    as c2,
cast(_latin1'abc' AS char(2) charset binary) as c3,
cast(_latin1'a  ' AS char(2) charset binary) as c4,
hex(cast(_latin1'a'   AS char(2) charset binary)) as c5;
c1	c2	c3	c4	c5
ab	a 	ab	a 	6100
Warnings:
Warning	1292	Truncated incorrect BINARY(2) value: 'abc'
Warning	1292	Truncated incorrect BINARY(2) value: 'a  '
select cast(1000 as CHAR(3) charset binary);
cast(1000 as CHAR(3) charset binary)
100
Warnings:
Warning	1292	Truncated incorrect BINARY(3) value: '1000'
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
create table t1 select
cast(_latin1'ab'  AS char charset binary)    as c1,
cast(_latin1'a '  AS char charset binary)    as c2,
cast(_latin1'abc' AS char(2) charset binary) as c3,
cast(_latin1'a  ' AS char(2) charset binary) as c4,
cast(_latin1'a'   AS char(2) charset binary) as c5;
Warnings:
Warning	1292	Truncated incorrect BINARY(2) value: 'abc'
Warning	1292	Truncated incorrect BINARY(2) value: 'a  '
select c1,c2,c3,c4,hex(c5) from t1;
c1	c2	c3	c4	hex(c5)
ab	a 	ab	a 	6100
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varbinary(2) NOT NULL DEFAULT '',
  `c2` varbinary(2) NOT NULL DEFAULT '',
  `c3` varbinary(2) NOT NULL DEFAULT '',
  `c4` varbinary(2) NOT NULL DEFAULT '',
  `c5` varbinary(2) NOT NULL DEFAULT ''
) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
drop table t1;
select
cast(_koi8r x'C6C7'   AS nchar)    as c1,
cast(_koi8r x'C620'   AS nchar)    as c2,
cast(_koi8r x'C6C7C8' AS nchar(2)) as c3,
cast(_koi8r x'C62020' AS nchar(2)) as c4,
cast(_koi8r x'C6'     AS nchar(2)) as c5;
c1	c2	c3	c4	c5
фг	ф 	фг	ф 	ф
Warnings:
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	1292	Truncated incorrect CHAR(4) value: 'фгх'
Warning	1292	Truncated incorrect CHAR(3) value: 'ф  '
create table t1 select
cast(_koi8r x'C6C7'   AS nchar)    as c1,
cast(_koi8r x'C620'   AS nchar)    as c2,
cast(_koi8r x'C6C7C8' AS nchar(2)) as c3,
cast(_koi8r x'C62020' AS nchar(2)) as c4,
cast(_koi8r x'C6'     AS nchar(2)) as c5;
Warnings:
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	1292	Truncated incorrect CHAR(4) value: 'фгх'
Warning	1292	Truncated incorrect CHAR(3) value: 'ф  '
select * from t1;
c1	c2	c3	c4	c5
фг	ф 	фг	ф 	ф
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
  `c2` varchar(2) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
  `c3` varchar(2) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
  `c4` varchar(2) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
  `c5` varchar(2) CHARACTER SET utf8mb3 NOT NULL DEFAULT ''
) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
drop table t1;
#
# Bug #24934161: FAILURE OF SYNONYMY OF NCHAR AND NATIONAL CHAR
#
SELECT
CAST(_gb2312 x'CAFDBEDD'     AS NATIONAL CHAR)    AS c1,
CAST(_gb2312 x'CAFD20'       AS NATIONAL CHAR)    AS c2,
CAST(_gb2312 x'CAFDBEDDBFE2' AS NATIONAL CHAR(2)) AS c3,
CAST(_gb2312 x'CAFD2020'     AS NATIONAL CHAR(2)) AS c4,
CAST(_gb2312 x'CAFD'         AS NATIONAL CHAR(2)) AS c5;
c1	c2	c3	c4	c5
数据	数 	数据	数 	数
Warnings:
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	1292	Truncated incorrect CHAR(6) value: '数据库'
Warning	1292	Truncated incorrect CHAR(4) value: '数  '
CREATE TABLE t1 SELECT
CAST(_gb2312 x'CAFDBEDD'     AS NATIONAL CHAR)    AS c1,
CAST(_gb2312 x'CAFD20'       AS NATIONAL CHAR)    AS c2,
CAST(_gb2312 x'CAFDBEDDBFE2' AS NATIONAL CHAR(2)) AS c3,
CAST(_gb2312 x'CAFD2020'     AS NATIONAL CHAR(2)) AS c4,
CAST(_gb2312 x'CAFD'         AS NATIONAL CHAR(2)) AS c5;
Warnings:
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	1292	Truncated incorrect CHAR(6) value: '数据库'
Warning	1292	Truncated incorrect CHAR(4) value: '数  '
SELECT * FROM t1;
c1	c2	c3	c4	c5
数据	数 	数据	数 	数
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
  `c2` varchar(2) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
  `c3` varchar(2) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
  `c4` varchar(2) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
  `c5` varchar(2) CHARACTER SET utf8mb3 NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
SET sql_mode = default;
create table t1 (a binary(4), b char(4) character set koi8r);
insert into t1 values (_binary x'D4C5D3D4',_binary x'D4C5D3D4');
select hex(a),hex(b),hex(cast(a as char character set cp1251)),hex(cast(b as binary)) from t1;
hex(a)	hex(b)	hex(cast(a as char character set cp1251))	hex(cast(b as binary))
D4C5D3D4	D4C5D3D4	D4C5D3D4	D4C5D3D4
drop table t1;
select cast("2001-1-1" as date) = "2001-01-01";
cast("2001-1-1" as date) = "2001-01-01"
1
select cast("2001-1-1" as datetime) = "2001-01-01 00:00:00";
cast("2001-1-1" as datetime) = "2001-01-01 00:00:00"
1
select cast("1:2:3" as TIME) = "1:02:03";
cast("1:2:3" as TIME) = "1:02:03"
0
select cast(NULL as DATE);
cast(NULL as DATE)
NULL
select cast(NULL as BINARY);
cast(NULL as BINARY)
NULL
CREATE TABLE t1 (a enum ('aac','aab','aaa') not null);
INSERT INTO t1 VALUES ('aaa'),('aab'),('aac');
SELECT a, CAST(a AS CHAR charset binary) FROM t1 ORDER BY CAST(a AS UNSIGNED) ;
a	CAST(a AS CHAR charset binary)
aac	aac
aab	aab
aaa	aaa
SELECT a, CAST(a AS CHAR(3) charset binary) FROM t1 ORDER BY CAST(a AS CHAR(2) charset binary), a;
a	CAST(a AS CHAR(3) charset binary)
aac	aac
aab	aab
aaa	aaa
Warnings:
Warning	1292	Truncated incorrect BINARY(2) value: 'aaa'
Warning	1292	Truncated incorrect BINARY(2) value: 'aab'
Warning	1292	Truncated incorrect BINARY(2) value: 'aac'
SELECT a, CAST(a AS UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR charset binary) ;
a	CAST(a AS UNSIGNED)
aaa	3
aab	2
aac	1
SELECT a, CAST(a AS CHAR(2) charset binary ) FROM t1 ORDER BY CAST(a AS CHAR(3) charset binary), a;
a	CAST(a AS CHAR(2) charset binary )
aaa	aa
aab	aa
aac	aa
Warnings:
Warning	1292	Truncated incorrect BINARY(2) value: 'aaa'
Warning	1292	Truncated incorrect BINARY(2) value: 'aab'
Warning	1292	Truncated incorrect BINARY(2) value: 'aac'
DROP TABLE t1;
select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour);
date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour)
2004-12-30 00:00:00
select timediff(cast('2004-12-30 12:00:00' as time), '12:00:00');
timediff(cast('2004-12-30 12:00:00' as time), '12:00:00')
00:00:00
select timediff(cast('1 12:00:00' as time), '12:00:00');
timediff(cast('1 12:00:00' as time), '12:00:00')
24:00:00
select cast(18446744073709551615 as unsigned);
cast(18446744073709551615 as unsigned)
18446744073709551615
select cast(18446744073709551615 as signed);
cast(18446744073709551615 as signed)
-1
select cast('18446744073709551615' as unsigned);
cast('18446744073709551615' as unsigned)
18446744073709551615
select cast('18446744073709551615' as signed);
cast('18446744073709551615' as signed)
-1
Warnings:
Warning	1105	Cast to signed converted positive out-of-range integer to its negative complement
select cast('9223372036854775807' as signed);
cast('9223372036854775807' as signed)
9223372036854775807
select cast(concat('184467440','73709551615') as unsigned);
cast(concat('184467440','73709551615') as unsigned)
18446744073709551615
select cast(concat('184467440','73709551615') as signed);
cast(concat('184467440','73709551615') as signed)
-1
Warnings:
Warning	1105	Cast to signed converted positive out-of-range integer to its negative complement
select cast(repeat('1',20) as unsigned);
cast(repeat('1',20) as unsigned)
11111111111111111111
select cast(repeat('1',20) as signed);
cast(repeat('1',20) as signed)
-7335632962598440505
Warnings:
Warning	1105	Cast to signed converted positive out-of-range integer to its negative complement
select cast(1.0e+300 as signed int);
cast(1.0e+300 as signed int)
9223372036854775807
CREATE TABLE t1 (f1 double);
INSERT INTO t1 SET f1 = -1.0e+30 ;
INSERT INTO t1 SET f1 = +1.0e+30 ;
SELECT f1 AS double_val, CAST(f1 AS SIGNED INT) AS cast_val FROM t1;
double_val	cast_val
-1e30	-9223372036854775808
1e30	9223372036854775807
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '-1e30'
Warning	1292	Truncated incorrect INTEGER value: '1e30'
DROP TABLE t1;
select isnull(date(NULL)), isnull(cast(NULL as DATE));
isnull(date(NULL))	isnull(cast(NULL as DATE))
1	1
SELECT CAST(cast('01-01-01' as date) AS UNSIGNED);
CAST(cast('01-01-01' as date) AS UNSIGNED)
20010101
SELECT CAST(cast('01-01-01' as date) AS SIGNED);
CAST(cast('01-01-01' as date) AS SIGNED)
20010101
End of 4.1 tests
select cast('1.2' as decimal(3,2));
cast('1.2' as decimal(3,2))
1.20
select 1e18 * cast('1.2' as decimal(3,2));
1e18 * cast('1.2' as decimal(3,2))
1.2e18
select cast(cast('1.2' as decimal(3,2)) as signed);
cast(cast('1.2' as decimal(3,2)) as signed)
1
set @v1=1e18;
select cast(@v1 as decimal(22, 2));
cast(@v1 as decimal(22, 2))
1000000000000000000.00
select cast(-1e18 as decimal(22,2));
cast(-1e18 as decimal(22,2))
-1000000000000000000.00
create table t1(s1 time);
insert into t1 values ('11:11:11');
select cast(s1 as decimal(7,2)) from t1;
cast(s1 as decimal(7,2))
99999.99
Warnings:
Warning	1264	Out of range value for column 'cast(s1 as decimal(7,2))' at row 1
drop table t1;
CREATE TABLE t1 (v varchar(10), tt tinytext, t text,
mt mediumtext, lt longtext);
INSERT INTO t1 VALUES ('1.01', '2.02', '3.03', '4.04', '5.05');
SELECT CAST(v AS DECIMAL), CAST(tt AS DECIMAL), CAST(t AS DECIMAL),
CAST(mt AS DECIMAL), CAST(lt AS DECIMAL) from t1;
CAST(v AS DECIMAL)	CAST(tt AS DECIMAL)	CAST(t AS DECIMAL)	CAST(mt AS DECIMAL)	CAST(lt AS DECIMAL)
1	2	3	4	5
DROP TABLE t1;
select cast(NULL as decimal(6)) as t1;
t1
NULL
set names latin1;
select hex(cast('a' as char(2) binary));
hex(cast('a' as char(2) binary))
61
Warnings:
Warning	1287	'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
select hex(cast('a' as binary(2)));
hex(cast('a' as binary(2)))
6100
select hex(cast('a' as char(2) binary));
hex(cast('a' as char(2) binary))
61
Warnings:
Warning	1287	'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
CREATE TABLE t1 (d1 datetime);
INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL),
('2007-07-19 08:34:00'), (NULL), ('2007-07-19 08:36:00');
SELECT cast(date(d1) as signed) FROM t1;
cast(date(d1) as signed)
20070719
NULL
20070719
NULL
20070719
drop table t1;
CREATE TABLE t1 (f1 DATE);
INSERT INTO t1 VALUES ('2007-07-19'), (NULL);
SELECT HOUR(f1),
MINUTE(f1),
SECOND(f1) FROM t1;
HOUR(f1)	MINUTE(f1)	SECOND(f1)
0	0	0
NULL	NULL	NULL
SELECT HOUR(CAST('2007-07-19' AS DATE)),
MINUTE(CAST('2007-07-19' AS DATE)),
SECOND(CAST('2007-07-19' AS DATE));
HOUR(CAST('2007-07-19' AS DATE))	MINUTE(CAST('2007-07-19' AS DATE))	SECOND(CAST('2007-07-19' AS DATE))
0	0	0
SELECT HOUR(CAST(NULL AS DATE)),
MINUTE(CAST(NULL AS DATE)),
SECOND(CAST(NULL AS DATE));
HOUR(CAST(NULL AS DATE))	MINUTE(CAST(NULL AS DATE))	SECOND(CAST(NULL AS DATE))
NULL	NULL	NULL
SELECT HOUR(NULL),
MINUTE(NULL),
SECOND(NULL);
HOUR(NULL)	MINUTE(NULL)	SECOND(NULL)
NULL	NULL	NULL
DROP TABLE t1;
End of 5.0 tests
#
#  Bug #44766: valgrind error when using convert() in a subquery
#
CREATE TABLE t1(a tinyint);
INSERT INTO t1 VALUES (127);
SELECT 1 FROM
(
SELECT CONVERT(t2.a USING UTF8) FROM t1, t1 t2 LIMIT 1
) AS s LIMIT 1;
1
1
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
DROP TABLE t1;
#
# Bug #11765023: 57934: DOS POSSIBLE SINCE BINARY CASTING 
#   DOESN'T ADHERE TO MAX_ALLOWED_PACKET
SET @@GLOBAL.max_allowed_packet=2048;
Warnings:
Warning	1708	The value of 'max_allowed_packet' should be no less than the value of 'net_buffer_length'
SELECT CONVERT('a', BINARY(2049));
CONVERT('a', BINARY(2049))
NULL
Warnings:
Warning	1301	Result of cast_as_binary() was larger than max_allowed_packet (2048) - truncated
SELECT CONVERT('a', CHAR(2049));
CONVERT('a', CHAR(2049))
NULL
Warnings:
Warning	1301	Result of cast_as_char() was larger than max_allowed_packet (2048) - truncated
SET @@GLOBAL.max_allowed_packet=default;
#
# Bug#13519724 63793: CRASH IN DTCOLLATION::SET(DTCOLLATION &SET)
#
CREATE TABLE t1 (a VARCHAR(50));
SELECT a FROM t1 
WHERE CAST(a as BINARY)=x'62736D697468' 
AND CAST(a AS BINARY)=x'65736D697468';
a
DROP TABLE t1;
End of 5.1 tests
#
# Bug#22885819: CAST( .. AS BINARY(N)) GETS UNEXPECTED NULL
#
SELECT CAST( 'a' AS BINARY(429496729));
CAST( 'a' AS BINARY(429496729))
NULL
Warnings:
Warning	1301	Result of cast_as_binary() was larger than max_allowed_packet (67108864) - truncated
SELECT CAST( 'a' AS BINARY(4294967294));
CAST( 'a' AS BINARY(4294967294))
NULL
Warnings:
Warning	1301	Result of cast_as_binary() was larger than max_allowed_packet (67108864) - truncated
SELECT CAST( 'a' AS BINARY(4294967295));
CAST( 'a' AS BINARY(4294967295))
NULL
Warnings:
Warning	1301	Result of cast_as_binary() was larger than max_allowed_packet (67108864) - truncated
SELECT CAST( 'a' AS BINARY(4294967296));
ERROR 42000: Display width out of range for column 'cast as char' (max = 4294967295)
SELECT CAST( 'a' AS BINARY(4294967296784564));
ERROR 42000: Display width out of range for column 'cast as char' (max = 4294967295)
#
# Bug#13581962 HIGH MEMORY USAGE ATTEMPT, THEN CRASH WITH LONGTEXT, UNION, USER VARIABLE
#
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1 AS SELECT CONCAT(CAST(REPEAT('9', 1000) AS SIGNED)),
CONCAT(CAST(REPEAT('9', 1000) AS UNSIGNED));
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999'
Warning	1292	Truncated incorrect INTEGER value: '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999'
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `CONCAT(CAST(REPEAT('9', 1000) AS SIGNED))` varchar(21) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `CONCAT(CAST(REPEAT('9', 1000) AS UNSIGNED))` varchar(21) CHARACTER SET latin1 NOT NULL DEFAULT ''
) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
SET sql_mode = default;
End of 5.5 tests
#
# Bug#28547906 ENUM TYPE CASTING WORKS WRONG WITH SUBQUERIES
#
CREATE TABLE t (c1 ENUM('a','b','c'));
INSERT INTO t VALUES ('a'), ('b'), ('c');
SELECT CAST(c1 AS UNSIGNED) AS c5 FROM t;
c5
1
2
3
SELECT CAST(c1 AS UNSIGNED) AS c5 FROM (SELECT c1 FROM t) t;
c5
1
2
3
DROP TABLE t;
#
# CAST as DOUBLE/FLOAT/REAL
#
SELECT CAST(1/3 AS FLOAT) as float_col,
CAST(1/3 AS DOUBLE) as double_col,
CAST(1/3 AS DOUBLE PRECISION) as double_prec_col,
CAST(1/3 AS REAL) as real_col;
float_col	double_col	double_prec_col	real_col
0.333333	0.333333333	0.333333333	0.333333333
SELECT CAST(1/3 AS FLOAT(10)), CAST(1/3 AS FLOAT(53));
CAST(1/3 AS FLOAT(10))	CAST(1/3 AS FLOAT(53))
0.333333	0.333333333
SELECT CAST(1/3 AS FLOAT(-1));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1))' at line 1
SELECT CAST(1/3 AS FLOAT(54));
ERROR 42000: Too-big precision 54 specified for 'CAST'. Maximum is 53.
SELECT CAST(1/3 AS DOUBLE(52));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(52))' at line 1
SELECT CAST(1/3 AS REAL(34));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(34))' at line 1
SELECT CAST(999.00009 AS FLOAT(7,4)) as float_col;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',4)) as float_col' at line 1
SELECT CAST(999.00009 AS DOUBLE(7,4)) as double_col;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(7,4)) as double_col' at line 1
SELECT CAST(999.00009 AS REAL(7,4)) as real_col;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(7,4)) as real_col' at line 1
SELECT ADDDATE(CAST("20010101235959.9" AS DOUBLE), INTERVAL 1 DAY);
ADDDATE(CAST("20010101235959.9" AS DOUBLE), INTERVAL 1 DAY)
2001-01-02 23:59:59.898438
SELECT TIMEDIFF(CAST("101112" AS DOUBLE), TIME'101010');
TIMEDIFF(CAST("101112" AS DOUBLE), TIME'101010')
00:01:02.000000
SELECT CAST(DATE'2000-01-01' AS FLOAT), CAST(DATE'2000-01-01' AS DOUBLE);
CAST(DATE'2000-01-01' AS FLOAT)	CAST(DATE'2000-01-01' AS DOUBLE)
20000100	20000101
SELECT CAST(TIME'23:59:59' AS FLOAT), CAST(TIME'23:59:59' AS DOUBLE);
CAST(TIME'23:59:59' AS FLOAT)	CAST(TIME'23:59:59' AS DOUBLE)
235959	235959
SELECT CAST(TIME'23:59:59.123456' AS FLOAT),
CAST(TIME'23:59:59.123456' AS DOUBLE);
CAST(TIME'23:59:59.123456' AS FLOAT)	CAST(TIME'23:59:59.123456' AS DOUBLE)
235959	235959.123456
SELECT CAST(TIMESTAMP'2000-01-01 23:59:59' AS FLOAT),
CAST(TIMESTAMP'2000-01-01 23:59:59' AS DOUBLE);
CAST(TIMESTAMP'2000-01-01 23:59:59' AS FLOAT)	CAST(TIMESTAMP'2000-01-01 23:59:59' AS DOUBLE)
20000100000000	20000101235959
SELECT CAST(TIMESTAMP'2000-01-01 23:59:59.123456' AS FLOAT),
CAST(TIMESTAMP'2000-01-01 23:59:59.123456' AS DOUBLE);
CAST(TIMESTAMP'2000-01-01 23:59:59.123456' AS FLOAT)	CAST(TIMESTAMP'2000-01-01 23:59:59.123456' AS DOUBLE)
20000100000000	20000101235959.125
CREATE TABLE t1 as SELECT CAST(1/3 AS FLOAT) as float_col,
CAST(1/3 AS DOUBLE) as double_col,
CAST(CAST(999.00009 AS DECIMAL(7,4)) AS DOUBLE) as d2;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `float_col` float DEFAULT NULL,
  `double_col` double DEFAULT NULL,
  `d2` double NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
SELECT PERIOD_ADD(200905, CAST(3.14e19 AS DOUBLE));
ERROR 22003: BIGINT value is out of range in 'cast(3.14e19 as double)'
SELECT -1.0 *  CAST(3.14e19 AS DOUBLE);
-1.0 *  CAST(3.14e19 AS DOUBLE)
-3.14e19
SELECT CAST("3.14e100" AS FLOAT);
ERROR 22003: DOUBLE value is out of range in 'cast('3.14e100' as float)'
SELECT CAST(-1e308 as FLOAT);
ERROR 22003: DOUBLE value is out of range in 'cast(-(1e308) as float)'
SELECT CONCAT("value=", CAST("3.4e5" AS FLOAT));
CONCAT("value=", CAST("3.4e5" AS FLOAT))
value=340000
CREATE VIEW v1 AS SELECT CAST(1/3 AS REAL), CAST(1/3 AS FLOAT(2)), CAST(1/3 AS FLOAT(50));
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((1 / 3) as double) AS `CAST(1/3 AS REAL)`,cast((1 / 3) as float) AS `CAST(1/3 AS FLOAT(2))`,cast((1 / 3) as double) AS `CAST(1/3 AS FLOAT(50))`	latin1	latin1_swedish_ci
DROP VIEW v1;
SELECT CAST(NULL AS REAL), CAST(NULL AS FLOAT), CAST(NULL AS DOUBLE);
CAST(NULL AS REAL)	CAST(NULL AS FLOAT)	CAST(NULL AS DOUBLE)
NULL	NULL	NULL
SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=REAL_AS_FLOAT;
CREATE TABLE t AS SELECT CAST(34 AS REAL);
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `CAST(34 AS REAL)` float NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t;
SET @@SQL_MODE=@OLD_SQL_MODE;
CREATE TABLE t AS SELECT CAST(34 AS REAL);
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `CAST(34 AS REAL)` double NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t;
SELECT MAKETIME(1, 2, CAST("1.6" AS FLOAT));
MAKETIME(1, 2, CAST("1.6" AS FLOAT))
01:02:01.600000
#
# WL#12108: Inject type cast nodes into the item tree to avoid data
# type mismatches.
#
CREATE TABLE dt_t (dt DATETIME, d DATE, t TIME);
CREATE TABLE n_t (i INT, d DECIMAL, f FLOAT, dc DECIMAL);
#
# DATETIME + NUMERICS
#
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.dt = n_t.i;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	n_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`dt` as double) = cast(`test`.`n_t`.`i` as double))
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.dt = n_t.d;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	n_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`dt` as double) = cast(`test`.`n_t`.`d` as double))
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.dt = n_t.f;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	n_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`dt` as double) = `test`.`n_t`.`f`)
#
# DATE + NUMERICS
#
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.d = n_t.i;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	n_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`d` as double) = cast(`test`.`n_t`.`i` as double))
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.d = n_t.d;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	n_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`d` as double) = cast(`test`.`n_t`.`d` as double))
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.d = n_t.f;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	n_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`d` as double) = `test`.`n_t`.`f`)
EXPLAIN SELECT * from dt_t JOIN n_t on dt_t.d = n_t.dc;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	n_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`d` as double) = cast(`test`.`n_t`.`dc` as double))
#
# TIME + NUMERICS
#
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.t = n_t.i;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	n_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`t` as double) = cast(`test`.`n_t`.`i` as double))
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.t = n_t.d;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	n_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`t` as double) = cast(`test`.`n_t`.`d` as double))
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.t = n_t.f;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	n_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`t` as double) = `test`.`n_t`.`f`)
#
# DATETIME + DATE
#
EXPLAIN SELECT * from dt_t dt1 JOIN dt_t dt2 ON dt1.dt = dt2.d;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	dt2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt1`.`dt` AS `dt`,`test`.`dt1`.`d` AS `d`,`test`.`dt1`.`t` AS `t`,`test`.`dt2`.`dt` AS `dt`,`test`.`dt2`.`d` AS `d`,`test`.`dt2`.`t` AS `t` from `test`.`dt_t` `dt1` join `test`.`dt_t` `dt2` where (`test`.`dt1`.`dt` = cast(`test`.`dt2`.`d` as datetime))
#
# DATETIME + TIME
#
EXPLAIN SELECT * from dt_t dt1 JOIN dt_t dt2 ON dt1.dt = dt2.t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	dt2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt1`.`dt` AS `dt`,`test`.`dt1`.`d` AS `d`,`test`.`dt1`.`t` AS `t`,`test`.`dt2`.`dt` AS `dt`,`test`.`dt2`.`d` AS `d`,`test`.`dt2`.`t` AS `t` from `test`.`dt_t` `dt1` join `test`.`dt_t` `dt2` where (`test`.`dt1`.`dt` = cast(`test`.`dt2`.`t` as datetime))
#
# DATE + TIME
#
EXPLAIN SELECT * from dt_t dt1 JOIN dt_t dt2 ON dt1.d = dt2.t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	dt2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt1`.`dt` AS `dt`,`test`.`dt1`.`d` AS `d`,`test`.`dt1`.`t` AS `t`,`test`.`dt2`.`dt` AS `dt`,`test`.`dt2`.`d` AS `d`,`test`.`dt2`.`t` AS `t` from `test`.`dt_t` `dt1` join `test`.`dt_t` `dt2` where (cast(`test`.`dt1`.`d` as datetime) = cast(`test`.`dt2`.`t` as datetime))
EXPLAIN SELECT * FROM dt_t dt1 JOIN dt_t dt2 ON dt1.d = dt2.d;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	dt2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`dt1`.`dt` AS `dt`,`test`.`dt1`.`d` AS `d`,`test`.`dt1`.`t` AS `t`,`test`.`dt2`.`dt` AS `dt`,`test`.`dt2`.`d` AS `d`,`test`.`dt2`.`t` AS `t` from `test`.`dt_t` `dt1` join `test`.`dt_t` `dt2` where (`test`.`dt2`.`d` = `test`.`dt1`.`d`)
EXPLAIN SELECT dt_t.dt = n_t.i from dt_t, n_t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	n_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`dt_t`.`dt` as double) = cast(`test`.`n_t`.`i` as double)) AS `dt_t.dt = n_t.i` from `test`.`dt_t` join `test`.`n_t`
EXPLAIN SELECT MAX(dt_t.d) AS max_d, MAX(n_t.i) AS max_i
FROM dt_t, n_t HAVING max_d = max_i;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	n_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select max(`test`.`dt_t`.`d`) AS `max_d`,max(`test`.`n_t`.`i`) AS `max_i` from `test`.`dt_t` join `test`.`n_t` having (cast(`max_d` as double) = cast(`max_i` as double))
EXPLAIN SELECT dt=d from dt_t ORDER BY dt = d;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using filesort
Warnings:
Note	1003	/* select#1 */ select (`test`.`dt_t`.`dt` = cast(`test`.`dt_t`.`d` as datetime)) AS `dt=d` from `test`.`dt_t` order by (`test`.`dt_t`.`dt` = cast(`test`.`dt_t`.`d` as datetime))
EXPLAIN SELECT * from dt_t ORDER BY dt = d;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using filesort
Warnings:
Note	1003	/* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t` from `test`.`dt_t` order by (`test`.`dt_t`.`dt` = cast(`test`.`dt_t`.`d` as datetime))
EXPLAIN SELECT d=t, LEAD(d,1) OVER w FROM dt_t WINDOW w AS (ORDER BY d=t);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using filesort
Warnings:
Note	3598	To get information about window functions use EXPLAIN FORMAT=JSON
Note	1003	/* select#1 */ select (cast(`test`.`dt_t`.`d` as datetime) = cast(`test`.`dt_t`.`t` as datetime)) AS `d=t`,lead(`test`.`dt_t`.`d`,1) OVER `w` AS `LEAD(d,1) OVER w` from `test`.`dt_t` window `w` AS (ORDER BY (cast(`test`.`dt_t`.`d` as datetime) = cast(`test`.`dt_t`.`t` as datetime)) ) 
EXPLAIN SELECT LEAD(d,1) OVER w FROM dt_t WINDOW w AS (ORDER BY d=t);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dt_t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using filesort
Warnings:
Note	3598	To get information about window functions use EXPLAIN FORMAT=JSON
Note	1003	/* select#1 */ select lead(`test`.`dt_t`.`d`,1) OVER `w` AS `LEAD(d,1) OVER w` from `test`.`dt_t` window `w` AS (ORDER BY (cast(`test`.`dt_t`.`d` as datetime) = cast(`test`.`dt_t`.`t` as datetime)) ) 
DROP TABLE dt_t, n_t;
CREATE TABLE t1 (spID int, userID int, date date);
INSERT INTO t1 VALUES (1,1,'1998-01-01');
INSERT INTO t1 VALUES (2,2,'2001-02-03');
INSERT INTO t1 VALUES (3,1,'1988-12-20');
INSERT INTO t1 VALUES (4,2,'1972-12-12');
EXPLAIN SELECT MIN(t1.userID) = MIN(date) FROM t1  GROUP BY userid;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using temporary
Warnings:
Note	1003	/* select#1 */ select (cast(min(`test`.`t1`.`userID`) as double) = cast(min(`test`.`t1`.`date`) as double)) AS `MIN(t1.userID) = MIN(date)` from `test`.`t1` group by `test`.`t1`.`userID`
EXPLAIN SELECT FIRST_VALUE(date) OVER (ORDER BY spID = date) FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using filesort
Warnings:
Note	3598	To get information about window functions use EXPLAIN FORMAT=JSON
Note	1003	/* select#1 */ select first_value(`test`.`t1`.`date`) OVER (ORDER BY (cast(`test`.`t1`.`spID` as double) = cast(`test`.`t1`.`date` as double)) )  AS `FIRST_VALUE(date) OVER (ORDER BY spID = date)` from `test`.`t1`
EXPLAIN SELECT date, spid = FIRST_VALUE(date) OVER (ORDER BY date ) FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using filesort
Warnings:
Note	3598	To get information about window functions use EXPLAIN FORMAT=JSON
Note	1003	/* select#1 */ select `test`.`t1`.`date` AS `date`,(cast(`test`.`t1`.`spid` as double) = cast(first_value(`test`.`t1`.`date`) OVER (ORDER BY `test`.`t1`.`date` )  as double)) AS `spid = FIRST_VALUE(date) OVER (ORDER BY date )` from `test`.`t1`
#
# No casts when constants or constant functions are involved
#
EXPLAIN SELECT * from t1 WHERE userID = DATE'2012-02-20';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	25.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`spID` AS `spID`,`test`.`t1`.`userID` AS `userID`,`test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`userID` = DATE'2012-02-20')
EXPLAIN SELECT * FROM t1 WHERE date = NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	25.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`spID` AS `spID`,`test`.`t1`.`userID` AS `userID`,`test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = NULL)
EXPLAIN SELECT * FROM t1 WHERE date = CAST('20:21:22' AS TIME);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	25.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`spID` AS `spID`,`test`.`t1`.`userID` AS `userID`,`test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = <cache>(cast('20:21:22' as time)))
DROP TABLE t1;
#
# WL#13456: Inject CAST nodes for comparisons with STRING non-const expressions. 
# 
CREATE TABLE t(c CHAR(64), v VARCHAR(256), txt TEXT, b BINARY(64), vb VARBINARY(32),
e ENUM ("v1", "v2"), set1 SET('101', '102'), bl  BLOB, i INT,
si SMALLINT, ti TINYINT, mi MEDIUMINT, bi BIGINT, bt BIT,
d DECIMAL, f FLOAT, dbl DOUBLE, dt DATETIME, dd  DATE, t TIME,
y YEAR);
INSERT INTO t
VALUES ("char", "vchar","text", "binary", "varbinary", "v1", '101,102', "blob", 
2001, 2, 3, 4, 200000002, 0x01, 2001.0, 2001.0, 2001.2,
"2001-01-02 22:00", "2001-01-02", "20:01", 2010);
# String vs INT
EXPLAIN SELECT v = i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`i` as double)) AS `v = i` from `test`.`t`
# String vs SMALLINT
EXPLAIN SELECT v = si FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`si` as double)) AS `v = si` from `test`.`t`
# String vs TINYINT
EXPLAIN SELECT v = ti FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`ti` as double)) AS `v = ti` from `test`.`t`
# String vs MEDIUMINT
EXPLAIN SELECT v = mi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`mi` as double)) AS `v = mi` from `test`.`t`
# String vs BIGINT
EXPLAIN SELECT v = bi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`bi` as double)) AS `v = bi` from `test`.`t`
# String vs BIT
EXPLAIN SELECT v = bt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`bt` as double)) AS `v = bt` from `test`.`t`
# String vs YEAR
EXPLAIN SELECT v = y FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`y` as double)) AS `v = y` from `test`.`t`
# String vs DECIMAL 
EXPLAIN SELECT v = d FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`d` as double)) AS `v = d` from `test`.`t`
# String vs FLOAT 
EXPLAIN SELECT v = f FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) = `test`.`t`.`f`) AS `v = f` from `test`.`t`
# String vs DOUBLE
EXPLAIN SELECT v = dbl FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) = `test`.`t`.`dbl`) AS `v = dbl` from `test`.`t`
# String vs DATETIME
EXPLAIN SELECT v = dt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as datetime(6)) = `test`.`t`.`dt`) AS `v = dt` from `test`.`t`
# String vs DATE
EXPLAIN SELECT v = dd FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as date) = `test`.`t`.`dd`) AS `v = dd` from `test`.`t`
# String vs TIME
EXPLAIN SELECT v = t FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`v` = `test`.`t`.`t`) AS `v = t` from `test`.`t`
# String vs another equality 
EXPLAIN SELECT v = (v = y) FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) = cast((cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`y` as double)) as double)) AS `v = (v = y)` from `test`.`t`
# String vs expression
EXPLAIN SELECT v = 1 * i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) = cast((1 * `test`.`t`.`i`) as double)) AS `v = 1 * i` from `test`.`t`
# Other operators
EXPLAIN SELECT v <=> i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) <=> cast(`test`.`t`.`i` as double)) AS `v <=> i` from `test`.`t`
EXPLAIN SELECT v <> i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) <> cast(`test`.`t`.`i` as double)) AS `v <> i` from `test`.`t`
EXPLAIN SELECT v > i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) > cast(`test`.`t`.`i` as double)) AS `v > i` from `test`.`t`
EXPLAIN SELECT v >= i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) >= cast(`test`.`t`.`i` as double)) AS `v >= i` from `test`.`t`
EXPLAIN SELECT v <= i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) <= cast(`test`.`t`.`i` as double)) AS `v <= i` from `test`.`t`
EXPLAIN SELECT v < i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`v` as double) < cast(`test`.`t`.`i` as double)) AS `v < i` from `test`.`t`
# Test data type combinations
EXPLAIN SELECT c = i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`i` as double)) AS `c = i` from `test`.`t`
EXPLAIN SELECT c = si FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`si` as double)) AS `c = si` from `test`.`t`
EXPLAIN SELECT c = ti FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`ti` as double)) AS `c = ti` from `test`.`t`
EXPLAIN SELECT c = mi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`mi` as double)) AS `c = mi` from `test`.`t`
EXPLAIN SELECT c = bi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`bi` as double)) AS `c = bi` from `test`.`t`
EXPLAIN SELECT c = bt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`bt` as double)) AS `c = bt` from `test`.`t`
EXPLAIN SELECT c = y FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`y` as double)) AS `c = y` from `test`.`t`
EXPLAIN SELECT c = d FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`d` as double)) AS `c = d` from `test`.`t`
EXPLAIN SELECT c = f FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`c` as double) = `test`.`t`.`f`) AS `c = f` from `test`.`t`
EXPLAIN SELECT c = dbl FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`c` as double) = `test`.`t`.`dbl`) AS `c = dbl` from `test`.`t`
EXPLAIN SELECT c = dt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`c` as datetime(6)) = `test`.`t`.`dt`) AS `c = dt` from `test`.`t`
EXPLAIN SELECT c = dd FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`c` as date) = `test`.`t`.`dd`) AS `c = dd` from `test`.`t`
EXPLAIN SELECT c = t FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`c` = `test`.`t`.`t`) AS `c = t` from `test`.`t`
EXPLAIN SELECT txt = i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`i` as double)) AS `txt = i` from `test`.`t`
EXPLAIN SELECT txt = si FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`si` as double)) AS `txt = si` from `test`.`t`
EXPLAIN SELECT txt = ti FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`ti` as double)) AS `txt = ti` from `test`.`t`
EXPLAIN SELECT txt = mi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`mi` as double)) AS `txt = mi` from `test`.`t`
EXPLAIN SELECT txt = bi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`bi` as double)) AS `txt = bi` from `test`.`t`
EXPLAIN SELECT txt = bt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`bt` as double)) AS `txt = bt` from `test`.`t`
EXPLAIN SELECT txt = y FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`y` as double)) AS `txt = y` from `test`.`t`
EXPLAIN SELECT txt = d FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`d` as double)) AS `txt = d` from `test`.`t`
EXPLAIN SELECT txt = f FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`txt` as double) = `test`.`t`.`f`) AS `txt = f` from `test`.`t`
EXPLAIN SELECT txt = dbl FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`txt` as double) = `test`.`t`.`dbl`) AS `txt = dbl` from `test`.`t`
EXPLAIN SELECT txt = dt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`txt` as datetime(6)) = `test`.`t`.`dt`) AS `txt = dt` from `test`.`t`
EXPLAIN SELECT txt = dd FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`txt` as date) = `test`.`t`.`dd`) AS `txt = dd` from `test`.`t`
EXPLAIN SELECT txt = t FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`txt` = `test`.`t`.`t`) AS `txt = t` from `test`.`t`
EXPLAIN SELECT b = i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`i` as double)) AS `b = i` from `test`.`t`
EXPLAIN SELECT b = si FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`si` as double)) AS `b = si` from `test`.`t`
EXPLAIN SELECT b = ti FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`ti` as double)) AS `b = ti` from `test`.`t`
EXPLAIN SELECT b = mi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`mi` as double)) AS `b = mi` from `test`.`t`
EXPLAIN SELECT b = bi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`bi` as double)) AS `b = bi` from `test`.`t`
EXPLAIN SELECT b = bt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`bt` as double)) AS `b = bt` from `test`.`t`
EXPLAIN SELECT b = y FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`y` as double)) AS `b = y` from `test`.`t`
EXPLAIN SELECT b = d FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`d` as double)) AS `b = d` from `test`.`t`
EXPLAIN SELECT b = f FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`b` as double) = `test`.`t`.`f`) AS `b = f` from `test`.`t`
EXPLAIN SELECT b = dbl FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`b` as double) = `test`.`t`.`dbl`) AS `b = dbl` from `test`.`t`
EXPLAIN SELECT b = dt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`b` as datetime(6)) = `test`.`t`.`dt`) AS `b = dt` from `test`.`t`
EXPLAIN SELECT b = dd FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`b` as date) = `test`.`t`.`dd`) AS `b = dd` from `test`.`t`
EXPLAIN SELECT b = t FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`b` = `test`.`t`.`t`) AS `b = t` from `test`.`t`
EXPLAIN SELECT vb = i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`i` as double)) AS `vb = i` from `test`.`t`
EXPLAIN SELECT vb = si FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`si` as double)) AS `vb = si` from `test`.`t`
EXPLAIN SELECT vb = ti FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`ti` as double)) AS `vb = ti` from `test`.`t`
EXPLAIN SELECT vb = mi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`mi` as double)) AS `vb = mi` from `test`.`t`
EXPLAIN SELECT vb = bi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`bi` as double)) AS `vb = bi` from `test`.`t`
EXPLAIN SELECT vb = bt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`bt` as double)) AS `vb = bt` from `test`.`t`
EXPLAIN SELECT vb = y FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`y` as double)) AS `vb = y` from `test`.`t`
EXPLAIN SELECT vb = d FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`d` as double)) AS `vb = d` from `test`.`t`
EXPLAIN SELECT vb = f FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`vb` as double) = `test`.`t`.`f`) AS `vb = f` from `test`.`t`
EXPLAIN SELECT vb = dbl FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`vb` as double) = `test`.`t`.`dbl`) AS `vb = dbl` from `test`.`t`
EXPLAIN SELECT vb = dt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`vb` as datetime(6)) = `test`.`t`.`dt`) AS `vb = dt` from `test`.`t`
EXPLAIN SELECT vb = dd FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`vb` as date) = `test`.`t`.`dd`) AS `vb = dd` from `test`.`t`
EXPLAIN SELECT vb = t FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`vb` = `test`.`t`.`t`) AS `vb = t` from `test`.`t`
EXPLAIN SELECT e = i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`i` as double)) AS `e = i` from `test`.`t`
EXPLAIN SELECT e = si FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`si` as double)) AS `e = si` from `test`.`t`
EXPLAIN SELECT e = ti FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`ti` as double)) AS `e = ti` from `test`.`t`
EXPLAIN SELECT e = mi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`mi` as double)) AS `e = mi` from `test`.`t`
EXPLAIN SELECT e = bi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`bi` as double)) AS `e = bi` from `test`.`t`
EXPLAIN SELECT e = bt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`bt` as double)) AS `e = bt` from `test`.`t`
EXPLAIN SELECT e = y FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`y` as double)) AS `e = y` from `test`.`t`
EXPLAIN SELECT e = d FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`d` as double)) AS `e = d` from `test`.`t`
EXPLAIN SELECT e = f FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`e` as double) = `test`.`t`.`f`) AS `e = f` from `test`.`t`
EXPLAIN SELECT e = dbl FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`e` as double) = `test`.`t`.`dbl`) AS `e = dbl` from `test`.`t`
EXPLAIN SELECT e = dt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`e` as datetime(6)) = `test`.`t`.`dt`) AS `e = dt` from `test`.`t`
EXPLAIN SELECT e = dd FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`e` as date) = `test`.`t`.`dd`) AS `e = dd` from `test`.`t`
EXPLAIN SELECT e = t FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`e` = `test`.`t`.`t`) AS `e = t` from `test`.`t`
EXPLAIN SELECT set1 = i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`i` as double)) AS `set1 = i` from `test`.`t`
EXPLAIN SELECT set1 = si FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`si` as double)) AS `set1 = si` from `test`.`t`
EXPLAIN SELECT set1 = ti FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`ti` as double)) AS `set1 = ti` from `test`.`t`
EXPLAIN SELECT set1 = mi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`mi` as double)) AS `set1 = mi` from `test`.`t`
EXPLAIN SELECT set1 = bi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`bi` as double)) AS `set1 = bi` from `test`.`t`
EXPLAIN SELECT set1 = bt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`bt` as double)) AS `set1 = bt` from `test`.`t`
EXPLAIN SELECT set1 = y FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`y` as double)) AS `set1 = y` from `test`.`t`
EXPLAIN SELECT set1 = d FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`d` as double)) AS `set1 = d` from `test`.`t`
EXPLAIN SELECT set1 = f FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`set1` as double) = `test`.`t`.`f`) AS `set1 = f` from `test`.`t`
EXPLAIN SELECT set1 = dbl FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`set1` as double) = `test`.`t`.`dbl`) AS `set1 = dbl` from `test`.`t`
EXPLAIN SELECT set1 = dt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`set1` as datetime(6)) = `test`.`t`.`dt`) AS `set1 = dt` from `test`.`t`
EXPLAIN SELECT set1 = dd FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`set1` as date) = `test`.`t`.`dd`) AS `set1 = dd` from `test`.`t`
EXPLAIN SELECT set1 = t FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`set1` = `test`.`t`.`t`) AS `set1 = t` from `test`.`t`
EXPLAIN SELECT bl = i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`i` as double)) AS `bl = i` from `test`.`t`
EXPLAIN SELECT bl = si FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`si` as double)) AS `bl = si` from `test`.`t`
EXPLAIN SELECT bl = ti FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`ti` as double)) AS `bl = ti` from `test`.`t`
EXPLAIN SELECT bl = mi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`mi` as double)) AS `bl = mi` from `test`.`t`
EXPLAIN SELECT bl = bi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`bi` as double)) AS `bl = bi` from `test`.`t`
EXPLAIN SELECT bl = bt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`bt` as double)) AS `bl = bt` from `test`.`t`
EXPLAIN SELECT bl = y FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`y` as double)) AS `bl = y` from `test`.`t`
EXPLAIN SELECT bl = d FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`d` as double)) AS `bl = d` from `test`.`t`
EXPLAIN SELECT bl = f FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`bl` as double) = `test`.`t`.`f`) AS `bl = f` from `test`.`t`
EXPLAIN SELECT bl = dbl FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`bl` as double) = `test`.`t`.`dbl`) AS `bl = dbl` from `test`.`t`
EXPLAIN SELECT bl = dt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`bl` as datetime(6)) = `test`.`t`.`dt`) AS `bl = dt` from `test`.`t`
EXPLAIN SELECT bl = dd FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (cast(`test`.`t`.`bl` as date) = `test`.`t`.`dd`) AS `bl = dd` from `test`.`t`
EXPLAIN SELECT bl = t FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`bl` = `test`.`t`.`t`) AS `bl = t` from `test`.`t`
# YEAR vs data types
EXPLAIN SELECT y = i FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`i`) AS `y = i` from `test`.`t`
EXPLAIN SELECT y = si FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`si`) AS `y = si` from `test`.`t`
EXPLAIN SELECT y = ti FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`ti`) AS `y = ti` from `test`.`t`
EXPLAIN SELECT y = mi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`mi`) AS `y = mi` from `test`.`t`
EXPLAIN SELECT y = bi FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`bi`) AS `y = bi` from `test`.`t`
EXPLAIN SELECT y = bt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`bt`) AS `y = bt` from `test`.`t`
EXPLAIN SELECT y = y FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`y`) AS `y = y` from `test`.`t`
EXPLAIN SELECT y = d FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`d`) AS `y = d` from `test`.`t`
EXPLAIN SELECT y = f FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`f`) AS `y = f` from `test`.`t`
EXPLAIN SELECT y = dbl FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`dbl`) AS `y = dbl` from `test`.`t`
EXPLAIN SELECT y = dt FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`dt`) AS `y = dt` from `test`.`t`
EXPLAIN SELECT y = dd FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`dd`) AS `y = dd` from `test`.`t`
EXPLAIN SELECT y = t FROM t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`t`) AS `y = t` from `test`.`t`
# cleanup
DROP TABLE t;
#
# Bug#31023252: RESULTSET MISMATCH USING STRCMP() WITH DATE AND STRING DATATYPE
#
CREATE TABLE t1(a YEAR, b VARCHAR(10));
INSERT INTO t1 VALUES ('1997','random_str');
SELECT STRCMP(a, b) FROM t1;
STRCMP(a, b)
-1
DROP TABLE t1;
#
# Bug#30626100: WL13456 RESULTSET DISTINCT DIFFERENCE
#
CREATE TABLE t (col_datetime datetime, col_date date, col_time time, col_char char);
insert into t values ('2013-03-15 18:35:20', '2013-03-15', '18:35:20','L'),
('2003-01-10 00:00:23', '2003-01-10', '00:00:23', NULL);
SELECT CAST(col_char AS DATETIME) FROM t;
CAST(col_char AS DATETIME)
NULL
NULL
Warnings:
Warning	1292	Incorrect datetime value: 'L'

SELECT col_char /*CAST(col_char as datetime)*/ <> col_datetime FROM t;
col_char /*CAST(col_char as datetime)*/ <> col_datetime
1
NULL
Warnings:
Warning	1292	Incorrect datetime value: 'L'

SELECT CAST(col_char AS DATE) FROM t;
CAST(col_char AS DATE)
NULL
NULL
Warnings:
Warning	1292	Incorrect datetime value: 'L'

SELECT col_char <> col_date FROM t;
col_char <> col_date
1
NULL
Warnings:
Warning	1292	Incorrect datetime value: 'L'
SELECT CAST(col_char as TIME) FROM t;
CAST(col_char as TIME)
NULL
NULL
Warnings:
Warning	1292	Truncated incorrect time value: 'L'
SELECT col_char <> col_time FROM t;
col_char <> col_time
1
NULL
DROP TABLE t;
#
# Bug#31095719 WL13456 RESULT SET COMPARISON DIFFERENCE WITH JOINS
#
CREATE TABLE `BB` (`col_char_key` char(1));
CREATE TABLE `CC` ( `pk` int, `col_datetime_key` datetime,
KEY `idx_CC_col_datetime_key` (`col_datetime_key`));
INSERT INTO `BB` VALUES ('X');
INSERT INTO `CC` VALUES (1,'2027-03-17 00:10:00'), (2,'2004-11-14 12:46:43');
SELECT COUNT(table1.pk) FROM CC table1 JOIN BB table3 JOIN CC table2
WHERE (table3.col_char_key < table2.col_datetime_key);
COUNT(table1.pk)
4
Warnings:
Warning	1292	Incorrect datetime value: 'X'
Warning	1292	Incorrect datetime value: 'X'
Warning	1292	Incorrect datetime value: 'X'
Warning	1292	Incorrect datetime value: 'X'
DROP TABLE `BB`;
DROP TABLE `CC`;
#
# CAST AS YEAR
#
SELECT CAST(CAST(-1 AS SIGNED) AS YEAR);
CAST(CAST(-1 AS SIGNED) AS YEAR)
NULL
Warnings:
Warning	1292	Truncated incorrect YEAR value: '-1'
SELECT CAST(CAST(-99 AS SIGNED) AS YEAR);
CAST(CAST(-99 AS SIGNED) AS YEAR)
NULL
Warnings:
Warning	1292	Truncated incorrect YEAR value: '-99'
SELECT CAST(CAST(0 AS SIGNED) AS YEAR);
CAST(CAST(0 AS SIGNED) AS YEAR)
0
SELECT CAST(CAST(69 AS SIGNED) AS YEAR);
CAST(CAST(69 AS SIGNED) AS YEAR)
2069
SELECT CAST(CAST(70 AS SIGNED) AS YEAR);
CAST(CAST(70 AS SIGNED) AS YEAR)
1970
SELECT CAST(CAST(99 AS SIGNED) AS YEAR);
CAST(CAST(99 AS SIGNED) AS YEAR)
1999
SELECT CAST(CAST(100 AS SIGNED) AS YEAR);
CAST(CAST(100 AS SIGNED) AS YEAR)
NULL
Warnings:
Warning	1292	Truncated incorrect YEAR value: '100'
SELECT CAST(CAST(2010 AS SIGNED) AS YEAR);
CAST(CAST(2010 AS SIGNED) AS YEAR)
2010
SELECT CAST(-1.1 AS YEAR);
CAST(-1.1 AS YEAR)
NULL
Warnings:
Warning	1292	Truncated incorrect YEAR value: '-1'
SELECT CAST(1.1 AS YEAR);
CAST(1.1 AS YEAR)
2001
SELECT CAST(0.0 AS YEAR);
CAST(0.0 AS YEAR)
0
SELECT CAST(69.1 AS YEAR);
CAST(69.1 AS YEAR)
2069
SELECT CAST(70.1 AS YEAR);
CAST(70.1 AS YEAR)
1970
SELECT CAST(100.1 AS YEAR);
CAST(100.1 AS YEAR)
NULL
Warnings:
Warning	1292	Truncated incorrect YEAR value: '100'
SELECT CAST(2010.9 AS YEAR);
CAST(2010.9 AS YEAR)
2011
SELECT CAST(CAST(-1.1 AS DECIMAL) AS YEAR);
CAST(CAST(-1.1 AS DECIMAL) AS YEAR)
NULL
Warnings:
Warning	1292	Truncated incorrect YEAR value: '-1'
SELECT CAST(CAST(1.1 AS DECIMAL) AS YEAR);
CAST(CAST(1.1 AS DECIMAL) AS YEAR)
2001
SELECT CAST(CAST(0.0 AS DECIMAL) AS YEAR);
CAST(CAST(0.0 AS DECIMAL) AS YEAR)
0
SELECT CAST(CAST(69.1 AS DECIMAL) AS YEAR);
CAST(CAST(69.1 AS DECIMAL) AS YEAR)
2069
SELECT CAST(CAST(70.1 AS DECIMAL) AS YEAR);
CAST(CAST(70.1 AS DECIMAL) AS YEAR)
1970
SELECT CAST(CAST(100.1 AS DECIMAL) AS YEAR);
CAST(CAST(100.1 AS DECIMAL) AS YEAR)
NULL
Warnings:
Warning	1292	Truncated incorrect YEAR value: '100'
SELECT CAST(CAST(2010.9 AS DECIMAL) AS YEAR);
CAST(CAST(2010.9 AS DECIMAL) AS YEAR)
2011
SELECT CAST("-1" AS YEAR);
CAST("-1" AS YEAR)
NULL
Warnings:
Warning	1525	Incorrect YEAR value: '-1'
SELECT CAST("-99" AS YEAR);
CAST("-99" AS YEAR)
NULL
Warnings:
Warning	1525	Incorrect YEAR value: '-99'
SELECT CAST("0" AS YEAR);
CAST("0" AS YEAR)
2000
SELECT CAST("69" AS YEAR);
CAST("69" AS YEAR)
2069
SELECT CAST("70" AS YEAR);
CAST("70" AS YEAR)
1970
SELECT CAST("99" AS YEAR);
CAST("99" AS YEAR)
1999
SELECT CAST("100" AS YEAR);
CAST("100" AS YEAR)
NULL
Warnings:
Warning	1292	Truncated incorrect YEAR value: '100'
SELECT CAST("2010" AS YEAR);
CAST("2010" AS YEAR)
2010
SELECT CAST("extra" AS YEAR);
CAST("extra" AS YEAR)
NULL
Warnings:
Warning	1525	Incorrect YEAR value: 'extra'
SELECT CAST("22extra" AS YEAR);
CAST("22extra" AS YEAR)
2022
Warnings:
Warning	1292	Truncated incorrect YEAR value: '22extra'
SELECT CAST("2020extra" AS YEAR);
CAST("2020extra" AS YEAR)
2020
Warnings:
Warning	1292	Truncated incorrect YEAR value: '2020extra'
SET timestamp = UNIX_TIMESTAMP('2020-12-22 03:30:00');
SELECT CAST(TIMESTAMP'2010-01-01 00:00' AS YEAR);
CAST(TIMESTAMP'2010-01-01 00:00' AS YEAR)
2010
SET SQL_MODE = "";
SELECT CAST(TIMESTAMP'0000-00-00 00:00' AS YEAR);
CAST(TIMESTAMP'0000-00-00 00:00' AS YEAR)
0
SET SQL_MODE = default;
SELECT CAST(TIMESTAMP'2010-01-01 08:09:10' AS YEAR);
CAST(TIMESTAMP'2010-01-01 08:09:10' AS YEAR)
2010
SELECT CAST(TIME'08:09:10' AS YEAR);
CAST(TIME'08:09:10' AS YEAR)
2020
SELECT CAST(TIME'00:00:00' AS YEAR);
CAST(TIME'00:00:00' AS YEAR)
2020
SET timestamp = DEFAULT;
SELECT CAST(ST_PointFromText('POINT(10 10)') AS YEAR);
ERROR HY000: Incorrect arguments to cast_as_year
CREATE TABLE t AS SELECT CAST("2010" AS YEAR);
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `CAST("2010" AS YEAR)` year DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t;
SELECT CAST(JSON_EXTRACT('{"key_year":1934}', '$.key_year') AS YEAR);
CAST(JSON_EXTRACT('{"key_year":1934}', '$.key_year') AS YEAR)
1934
SELECT CAST(CAST('{"_id":"192312412512"}' AS JSON) AS YEAR);
CAST(CAST('{"_id":"192312412512"}' AS JSON) AS YEAR)
0
Warnings:
Warning	3156	Invalid JSON value for CAST to INTEGER from column cast_as_json at row 1
CREATE TABLE t1 (i INT, j JSON) CHARSET utf8mb4;
INSERT INTO t1 VALUES (0, NULL);
INSERT INTO t1 VALUES (1, '"1901"');
INSERT INTO t1 VALUES (2, 'true');
INSERT INTO t1 VALUES (3, 'false');
INSERT INTO t1 VALUES (4, 'null');
INSERT INTO t1 VALUES (5, '-1');
INSERT INTO t1 VALUES (6, CAST(CAST(1 AS UNSIGNED) AS JSON));
INSERT INTO t1 VALUES (7, '1901');
INSERT INTO t1 VALUES (8, '-1901');
INSERT INTO t1 VALUES (9, '2147483647');
INSERT INTO t1 VALUES (10, '2147483648');
INSERT INTO t1 VALUES (11, '-2147483648');
INSERT INTO t1 VALUES (12, '-2147483649');
INSERT INTO t1 VALUES (13, '3.14');
INSERT INTO t1 VALUES (14, '{}');
INSERT INTO t1 VALUES (15, '[]');
INSERT INTO t1 VALUES (16, CAST(CAST('2015-01-15 23:24:25' AS DATETIME) AS JSON));
INSERT INTO t1 VALUES (17, CAST(CAST('23:24:25' AS TIME) AS JSON));
INSERT INTO t1 VALUES (18, CAST(CAST('2015-01-15' AS DATE) AS JSON));
INSERT INTO t1 VALUES (19, CAST(TIMESTAMP'2015-01-15 23:24:25' AS JSON));
INSERT INTO t1 VALUES (20, CAST(ST_GeomFromText('POINT(1 1)') AS JSON));
INSERT INTO t1 VALUES (21, CAST('1988' AS CHAR CHARACTER SET 'ascii'));
INSERT INTO t1 VALUES (22, CAST(x'07C4' AS JSON));
INSERT INTO t1 VALUES (23, CAST(x'07C407C4' AS JSON));
SELECT i, CAST(j AS YEAR), CAST(j AS SIGNED) FROM t1 ORDER BY i;
i	CAST(j AS YEAR)	CAST(j AS SIGNED)
0	NULL	NULL
1	1901	1901
2	2001	1
3	0	0
4	0	0
5	NULL	-1
6	2001	1
7	1901	1901
8	NULL	-1901
9	NULL	2147483647
10	NULL	2147483648
11	NULL	-2147483648
12	NULL	-2147483649
13	2003	3
14	0	0
15	0	0
16	0	0
17	0	0
18	0	0
19	0	0
20	0	0
21	1988	1988
22	0	0
23	0	0
Warnings:
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 5
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 5
Warning	1292	Truncated incorrect YEAR value: '-1'
Warning	1292	Truncated incorrect YEAR value: '-1901'
Warning	1292	Truncated incorrect YEAR value: '2147483647'
Warning	1292	Truncated incorrect YEAR value: '2147483648'
Warning	1292	Truncated incorrect YEAR value: '-2147483648'
Warning	1292	Truncated incorrect YEAR value: '-2147483649'
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 15
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 15
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 16
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 16
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 17
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 17
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 18
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 18
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 19
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 19
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 20
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 20
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 21
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 21
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 23
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 23
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 24
Warning	3156	Invalid JSON value for CAST to INTEGER from column j at row 24
DROP TABLE t1;
CREATE TABLE t(numbers ENUM('0','1','2020'), colors ENUM('red', 'green', 'blue'));
INSERT INTO t values('2020', 'blue');
SELECT CAST(numbers AS YEAR), CAST(colors AS YEAR) FROM t;
CAST(numbers AS YEAR)	CAST(colors AS YEAR)
2003	2003
DROP TABLE t;
CREATE TABLE t(y YEAR);
INSERT INTO t values(CAST("2020extra" AS YEAR));
ERROR 22007: Truncated incorrect YEAR value: '2020extra'
INSERT INTO t values(CAST(20201 AS YEAR));
ERROR 22007: Truncated incorrect YEAR value: '20201'
SET SQL_MODE = "";
INSERT INTO t values(CAST("2020extra" AS YEAR));
Warnings:
Warning	1292	Truncated incorrect YEAR value: '2020extra'
INSERT INTO t values(CAST(20201 AS YEAR));
Warnings:
Warning	1292	Truncated incorrect YEAR value: '20201'
SELECT * FROM t;
y
2020
NULL
SET SQL_MODE = default;
DROP TABLE t;
SELECT CAST(1988 AS YEAR), REPEAT(CAST(1988 AS YEAR), 3) AS c2;
CAST(1988 AS YEAR)	c2
1988	198819881988
SELECT CONCAT('x', CAST(1988 AS YEAR));
CONCAT('x', CAST(1988 AS YEAR))
x1988
SELECT CAST(1988 AS YEAR) + 1.5e0;
CAST(1988 AS YEAR) + 1.5e0
1989.5
SELECT CAST(CAST(1988 AS YEAR) AS DECIMAL);
CAST(CAST(1988 AS YEAR) AS DECIMAL)
1988
SELECT DATE_ADD(CAST(1988 AS YEAR), INTERVAL 1 DAY);
DATE_ADD(CAST(1988 AS YEAR), INTERVAL 1 DAY)
NULL
Warnings:
Warning	1292	Incorrect datetime value: '1988'
SELECT TIME_TO_SEC(CAST('2030' AS YEAR));
TIME_TO_SEC(CAST('2030' AS YEAR))
1230
SELECT TIMESTAMPADD(MINUTE, 1, CAST(1988 AS YEAR));
TIMESTAMPADD(MINUTE, 1, CAST(1988 AS YEAR))
NULL
Warnings:
Warning	1292	Incorrect datetime value: '1988'
SELECT CAST(CAST(1988 AS YEAR) AS SIGNED);
CAST(CAST(1988 AS YEAR) AS SIGNED)
1988
SELECT CAST(CAST(1988 AS YEAR) AS UNSIGNED);
CAST(CAST(1988 AS YEAR) AS UNSIGNED)
1988
SELECT CAST(TIMESTAMP'579-10-10 10:10:10' AS YEAR);
CAST(TIMESTAMP'579-10-10 10:10:10' AS YEAR)
NULL
Warnings:
Warning	1292	Truncated incorrect YEAR value: '579'
SELECT CAST(STR_TO_DATE('nope','%d-%m-%Y') AS YEAR);
CAST(STR_TO_DATE('nope','%d-%m-%Y') AS YEAR)
NULL
Warnings:
Warning	1411	Incorrect datetime value: 'nope' for function str_to_date
# WL#14109: Implement a consistent comparison type rule matrix
SET @zero_date = '0000-00-00';
SET @zero_day = '2000-01-00';
SET @zero_month = '2000-00-01';
SET @zero_year = '0000-01-01';
SET @invalid_date = '2000-02-31';
SET @bad_date = 'YYYY-MM-DD';
SET @zero_datetime = '0000-00-00 00:00:00.000000';
SET @zero_day_dt = '2000-01-00 00:00:00.000000';
SET @zero_month_dt = '2000-00-01 00:00:00.000000';
SET @zero_year_dt = '0000-01-01 00:00:00.000000';
SET @invalid_datetime = '2000-02-31 00:00:00.000000';
SET @bad_datetime = 'YYYY-MM-DD HH:MM:SS.ffffff';
SET SQL_MODE=DEFAULT;
SELECT CAST('0000-00-00' AS DATE) AS d1,
CAST(@zero_date AS DATE) AS d2,
CAST('0000-00-00 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_datetime AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: '0000-00-00'
Warning	1292	Incorrect datetime value: '0000-00-00'
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00.000000'
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00.000000'
SELECT CAST('2000-01-00' AS DATE) AS d1,
CAST(@zero_day AS DATE) AS d2,
CAST('2000-01-00 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_day_dt AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: '2000-01-00'
Warning	1292	Incorrect datetime value: '2000-01-00'
Warning	1292	Incorrect datetime value: '2000-01-00 00:00:00.000000'
Warning	1292	Incorrect datetime value: '2000-01-00 00:00:00.000000'
SELECT CAST('2000-00-01' AS DATE) AS d1,
CAST(@zero_month AS DATE) AS d2,
CAST('2000-00-01 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_month_dt AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: '2000-00-01'
Warning	1292	Incorrect datetime value: '2000-00-01'
Warning	1292	Incorrect datetime value: '2000-00-01 00:00:00.000000'
Warning	1292	Incorrect datetime value: '2000-00-01 00:00:00.000000'
SELECT CAST('0000-01-01' AS DATE) AS d1,
CAST(@zero_year AS DATE) AS d2,
CAST('0000-01-01 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_year_dt AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
0000-01-01	0000-01-01	0000-01-01 00:00:00.000000	0000-01-01 00:00:00.000000
SELECT CAST('2000-02-31' AS DATE) AS d1,
CAST(@invalid_date AS DATE) AS d2,
CAST('2000-02-31 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@invalid_datetime AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: '2000-02-31'
Warning	1292	Incorrect datetime value: '2000-02-31'
Warning	1292	Incorrect datetime value: '2000-02-31 00:00:00.000000'
Warning	1292	Incorrect datetime value: '2000-02-31 00:00:00.000000'
SELECT CAST('YYYY-MM-DD' AS DATE) AS d1,
CAST(@bad_date AS DATE) AS d2,
CAST('YYYY-MM-DD HH:MM:SS.ffffff' AS DATETIME(6)) AS dt1,
CAST(@bad_datetime AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD'
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD'
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD HH:MM:SS.ffffff'
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD HH:MM:SS.ffffff'
SET SQL_MODE='NO_ZERO_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
SELECT CAST('0000-00-00' AS DATE) AS d1,
CAST(@zero_date AS DATE) AS d2,
CAST('0000-00-00 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_datetime AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: '0000-00-00'
Warning	1292	Incorrect datetime value: '0000-00-00'
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00.000000'
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00.000000'
SELECT CAST('2000-01-00' AS DATE) AS d1,
CAST(@zero_day AS DATE) AS d2,
CAST('2000-01-00 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_day_dt AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
2000-01-00	2000-01-00	2000-01-00 00:00:00.000000	2000-01-00 00:00:00.000000
SELECT CAST('2000-00-01' AS DATE) AS d1,
CAST(@zero_month AS DATE) AS d2,
CAST('2000-00-01 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_month_dt AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
2000-00-01	2000-00-01	2000-00-01 00:00:00.000000	2000-00-01 00:00:00.000000
SELECT CAST('0000-01-01' AS DATE) AS d1,
CAST(@zero_year AS DATE) AS d2,
CAST('0000-01-01 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_year_dt AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
0000-01-01	0000-01-01	0000-01-01 00:00:00.000000	0000-01-01 00:00:00.000000
SELECT CAST('2000-02-31' AS DATE) AS d1,
CAST(@invalid_date AS DATE) AS d2,
CAST('2000-02-31 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@invalid_datetime AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: '2000-02-31'
Warning	1292	Incorrect datetime value: '2000-02-31'
Warning	1292	Incorrect datetime value: '2000-02-31 00:00:00.000000'
Warning	1292	Incorrect datetime value: '2000-02-31 00:00:00.000000'
SELECT CAST('YYYY-MM-DD' AS DATE) AS d1,
CAST(@bad_date AS DATE) AS d2,
CAST('YYYY-MM-DD HH:MM:SS.ffffff' AS DATETIME(6)) AS dt1,
CAST(@bad_datetime AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD'
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD'
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD HH:MM:SS.ffffff'
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD HH:MM:SS.ffffff'
SET SQL_MODE='NO_ZERO_IN_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
SELECT CAST('0000-00-00' AS DATE) AS d1,
CAST(@zero_date AS DATE) AS d2,
CAST('0000-00-00 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_datetime AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
0000-00-00	0000-00-00	0000-00-00 00:00:00.000000	0000-00-00 00:00:00.000000
SELECT CAST('2000-01-00' AS DATE) AS d1,
CAST(@zero_day AS DATE) AS d2,
CAST('2000-01-00 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_day_dt AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: '2000-01-00'
Warning	1292	Incorrect datetime value: '2000-01-00'
Warning	1292	Incorrect datetime value: '2000-01-00 00:00:00.000000'
Warning	1292	Incorrect datetime value: '2000-01-00 00:00:00.000000'
SELECT CAST('2000-00-01' AS DATE) AS d1,
CAST(@zero_month AS DATE) AS d2,
CAST('2000-00-01 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_month_dt AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: '2000-00-01'
Warning	1292	Incorrect datetime value: '2000-00-01'
Warning	1292	Incorrect datetime value: '2000-00-01 00:00:00.000000'
Warning	1292	Incorrect datetime value: '2000-00-01 00:00:00.000000'
SELECT CAST('0000-01-01' AS DATE) AS d1,
CAST(@zero_year AS DATE) AS d2,
CAST('0000-01-01 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_year_dt AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
0000-01-01	0000-01-01	0000-01-01 00:00:00.000000	0000-01-01 00:00:00.000000
SELECT CAST('2000-02-31' AS DATE) AS d1,
CAST(@invalid_date AS DATE) AS d2,
CAST('2000-02-31 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@invalid_datetime AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: '2000-02-31'
Warning	1292	Incorrect datetime value: '2000-02-31'
Warning	1292	Incorrect datetime value: '2000-02-31 00:00:00.000000'
Warning	1292	Incorrect datetime value: '2000-02-31 00:00:00.000000'
SELECT CAST('YYYY-MM-DD' AS DATE) AS d1,
CAST(@bad_date AS DATE) AS d2,
CAST('YYYY-MM-DD HH:MM:SS.ffffff' AS DATETIME(6)) AS dt1,
CAST(@bad_datetime AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD'
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD'
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD HH:MM:SS.ffffff'
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD HH:MM:SS.ffffff'
SET SQL_MODE='ALLOW_INVALID_DATES';
SELECT CAST('0000-00-00' AS DATE) AS d1,
CAST(@zero_date AS DATE) AS d2,
CAST('0000-00-00 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_datetime AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
0000-00-00	0000-00-00	0000-00-00 00:00:00.000000	0000-00-00 00:00:00.000000
SELECT CAST('2000-01-00' AS DATE) AS d1,
CAST(@zero_day AS DATE) AS d2,
CAST('2000-01-00 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_day_dt AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
2000-01-00	2000-01-00	2000-01-00 00:00:00.000000	2000-01-00 00:00:00.000000
SELECT CAST('2000-00-01' AS DATE) AS d1,
CAST(@zero_month AS DATE) AS d2,
CAST('2000-00-01 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_month_dt AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
2000-00-01	2000-00-01	2000-00-01 00:00:00.000000	2000-00-01 00:00:00.000000
SELECT CAST('0000-01-01' AS DATE) AS d1,
CAST(@zero_year AS DATE) AS d2,
CAST('0000-01-01 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@zero_year_dt AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
0000-01-01	0000-01-01	0000-01-01 00:00:00.000000	0000-01-01 00:00:00.000000
SELECT CAST('2000-02-31' AS DATE) AS d1,
CAST(@invalid_date AS DATE) AS d2,
CAST('2000-02-31 00:00:00.000000' AS DATETIME(6)) AS dt1,
CAST(@invalid_datetime AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
2000-02-31	2000-02-31	2000-02-31 00:00:00.000000	2000-02-31 00:00:00.000000
SELECT CAST('YYYY-MM-DD' AS DATE) AS d1,
CAST(@bad_date AS DATE) AS d2,
CAST('YYYY-MM-DD HH:MM:SS.ffffff' AS DATETIME(6)) AS dt1,
CAST(@bad_datetime AS DATETIME(6)) AS dt2;
d1	d2	dt1	dt2
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD'
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD'
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD HH:MM:SS.ffffff'
Warning	1292	Incorrect datetime value: 'YYYY-MM-DD HH:MM:SS.ffffff'
SET SQL_MODE=DEFAULT;
#
# Bug#32371039: ITEM->MAX_LENGTH FIELD IS NOT ACCURATE FOR
#               FROM_BASE64 FUNCTION
#
CREATE TABLE t AS
SELECT CONCAT(CAST(-1 AS UNSIGNED)) AS col1,
1.0 + CAST(-1 AS UNSIGNED) AS col2,
CONCAT(CAST(9223372036854775808 AS SIGNED)) AS col3;
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `col1` varchar(21) CHARACTER SET latin1 DEFAULT NULL,
  `col2` decimal(23,1) NOT NULL DEFAULT '0.0',
  `col3` varchar(21) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t;
col1	col2	col3
18446744073709551615	18446744073709551616.0	-9223372036854775808
DROP TABLE t;
#
# Bug#32942327: CAST: ITEM_TYPECAST_UNSIGNED:
#               ASSERTION `!NULL_VALUE || IS_NULLABLE()' FAILED
#
CREATE TABLE t (x VARCHAR(10) NOT NULL);
INSERT INTO t VALUES ('');
SELECT CAST(x <= 0x4891f642 AS UNSIGNED) FROM t;
ERROR HY000: Cannot convert string 'H\x91\xF6B' from binary to utf8mb4
SELECT CAST(STRCMP(x, 0x4891f642) AS UNSIGNED) FROM t;
ERROR HY000: Cannot convert string 'H\x91\xF6B' from binary to utf8mb4
DROP TABLE t;
#
# Bug#33199145: CONVERT(.. USING charset) gets wrong max_length
#
CREATE TABLE tmp(beer CHAR(1));
INSERT INTO tmp VALUES (CONVERT(X'F09F8DBA' USING utf8mb4));
SELECT HEX(beer), beer FROM tmp;
HEX(beer)	beer
F09F8DBA	?
# Used this round-about way originally to get a utfmb4 character to
# convert in next DML/DDL query to avoid change encoding of this file
# and to avoid bug in max_length of CONVERT(X'F09F8DBA' USING UTF8MB4)
# which (used to) set to 4 instead of one (beer), fixed now, though,
# see round-trip test below.
CREATE TABLE t AS
SELECT CONVERT((SELECT beer FROM tmp) USING binary) AS beer;
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `beer` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT HEX(beer) FROM t;
HEX(beer)
F09F8DBA
DROP TABLE t, tmp;
CREATE TABLE t AS
SELECT CONVERT(X'F09F8DBA' USING utf8mb4) AS beer;
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `beer` varchar(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT HEX(beer) FROM t;
HEX(beer)
F09F8DBA
DROP TABLE t;
# Check round trip.
CREATE TABLE t AS
SELECT CONVERT(CONVERT(X'F09F8DBA' USING utf8mb4) USING binary) AS beer;
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `beer` varbinary(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT HEX(beer) FROM t;
HEX(beer)
F09F8DBA
DROP TABLE t;
# Check round trip.
CREATE TABLE t AS
SELECT CONVERT(CONVERT(X'F09F8DBA', CHAR(1) CHARACTER SET utf8mb4) USING binary) AS beer;
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `beer` varbinary(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT HEX(beer) FROM t;
HEX(beer)
F09F8DBA
DROP TABLE t;
# Check round trip: should stay VARCHAR(1).
CREATE TABLE t AS
SELECT CONVERT(CONVERT(_utf8mb3'a' USING utf8mb4) USING utf8mb3) AS a;
Warnings:
Warning	1287	'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead
Warning	1287	'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` varchar(1) CHARACTER SET utf8mb3 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT a FROM t;
a
a
DROP TABLE t;
# Character set with different min char size (utf-16: 2):
CREATE TABLE t AS
SELECT CONVERT(X'D83CDF7A' USING utf16) as beer;
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `beer` varchar(2) CHARACTER SET utf16 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT HEX(beer), CHAR_LENGTH(beer) FROM t;
HEX(beer)	CHAR_LENGTH(beer)
D83CDF7A	1
DROP TABLE t;
#
# Bug #33406728 Item_charset_conversion::compute_max_char_length:
#     Assertion `from->max_length == from->max_char_length()' failed.
#
set @a:= st_aswkb(st_astext(@b:=log10(0.0)));
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	3020	Invalid argument for logarithm
set @a:= @b;
do convert(@a using latin1);
#
# Bug#34554755: DISTINCT on FLOAT values gives duplicates
#               with SQL_BIG_RESULT
#
SELECT CAST(1111111111111111 AS FLOAT) = CAST(1111111111111110 AS FLOAT) AS eq;
eq
1
SELECT CAST(CAST(1111111111111111 AS FLOAT) AS CHAR) AS v;
v
1.11111e15
SELECT CAST(CAST(1111111111111111 AS FLOAT) AS SIGNED) AS v;
v
1111111126482944
SELECT CAST(CAST(1111111111111111 AS FLOAT) AS DECIMAL(20,2)) AS v;
v
1111111126482944.00