|
|
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
|
|
|
Warnings:
|
|
|
Warning 1292 Truncated incorrect INTEGER value: '10 '
|
|
|
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
|
|
|
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 it's negative complement
|
|
|
select cast('-1' as unsigned);
|
|
|
cast('-1' as unsigned)
|
|
|
18446744073709551615
|
|
|
Warnings:
|
|
|
Warning 1105 Cast to unsigned converted negative integer to it's 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: ''
|
|
|
set names binary;
|
|
|
select cast(_latin1'test' as char character set latin2);
|
|
|
cast(_latin1'test' as char character set latin2)
|
|
|
test
|
|
|
select cast(_koi8r'<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' as char character set cp1251);
|
|
|
cast(_koi8r'<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' as char character set cp1251)
|
|
|
<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|
|
create table t1 select cast(_koi8r'<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' 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) as c1,
|
|
|
cast(_latin1'a ' AS char) as c2,
|
|
|
cast(_latin1'abc' AS char(2)) as c3,
|
|
|
cast(_latin1'a ' AS char(2)) as c4,
|
|
|
hex(cast(_latin1'a' AS char(2))) 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));
|
|
|
cast(1000 as CHAR(3))
|
|
|
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) as c1,
|
|
|
cast(_latin1'a ' AS char) as c2,
|
|
|
cast(_latin1'abc' AS char(2)) as c3,
|
|
|
cast(_latin1'a ' AS char(2)) as c4,
|
|
|
cast(_latin1'a' AS char(2)) 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'<EFBFBD><EFBFBD>' AS nchar) as c1,
|
|
|
cast(_koi8r'<EFBFBD> ' AS nchar) as c2,
|
|
|
cast(_koi8r'<EFBFBD><EFBFBD><EFBFBD>' AS nchar(2)) as c3,
|
|
|
cast(_koi8r'<EFBFBD> ' AS nchar(2)) as c4,
|
|
|
cast(_koi8r'<EFBFBD>' 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'<EFBFBD><EFBFBD>' AS nchar) as c1,
|
|
|
cast(_koi8r'<EFBFBD> ' AS nchar) as c2,
|
|
|
cast(_koi8r'<EFBFBD><EFBFBD><EFBFBD>' AS nchar(2)) as c3,
|
|
|
cast(_koi8r'<EFBFBD> ' AS nchar(2)) as c4,
|
|
|
cast(_koi8r'<EFBFBD>' 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 utf8 NOT NULL DEFAULT '',
|
|
|
`c2` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
|
|
|
`c3` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
|
|
|
`c4` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
|
|
|
`c5` varchar(2) CHARACTER SET utf8 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 utf8 NOT NULL DEFAULT '',
|
|
|
`c2` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
|
|
|
`c3` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
|
|
|
`c4` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
|
|
|
`c5` varchar(2) CHARACTER SET utf8 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'<EFBFBD><EFBFBD><EFBFBD><EFBFBD>',_binary'<EFBFBD><EFBFBD><EFBFBD><EFBFBD>');
|
|
|
select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
|
|
|
a b cast(a as char character set cp1251) cast(b as binary)
|
|
|
<EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|
|
set names koi8r;
|
|
|
select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
|
|
|
a b cast(a as char character set cp1251) cast(b as binary)
|
|
|
<EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|
|
set names cp1251;
|
|
|
select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
|
|
|
a b cast(a as char character set cp1251) cast(b as binary)
|
|
|
<EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|
|
drop table t1;
|
|
|
set names binary;
|
|
|
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) FROM t1 ORDER BY CAST(a AS UNSIGNED) ;
|
|
|
a CAST(a AS CHAR)
|
|
|
aac aac
|
|
|
aab aab
|
|
|
aaa aaa
|
|
|
SELECT a, CAST(a AS CHAR(3)) FROM t1 ORDER BY CAST(a AS CHAR(2)), a;
|
|
|
a CAST(a AS CHAR(3))
|
|
|
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) ;
|
|
|
a CAST(a AS UNSIGNED)
|
|
|
aaa 3
|
|
|
aab 2
|
|
|
aac 1
|
|
|
SELECT a, CAST(a AS CHAR(2)) FROM t1 ORDER BY CAST(a AS CHAR(3)), a;
|
|
|
a CAST(a AS CHAR(2))
|
|
|
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 it's 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 it's 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 it's 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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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 (Block Nested Loop)
|
|
|
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;
|
|
|
|
