cast.result 9.1 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
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(-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
16 17 18 19
explain extended select ~5, cast(~5 as signed);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
unknown's avatar
unknown committed
20
Note	1003	select ~(5) AS `~5`,cast(~(5) as signed) AS `cast(~5 as signed)`
21 22 23
select cast(5 as unsigned) -6.0;
cast(5 as unsigned) -6.0
-1.0
unknown's avatar
unknown committed
24 25 26 27 28 29
select cast(NULL as signed), cast(1/0 as signed);
cast(NULL as signed)	cast(1/0 as signed)
NULL	NULL
select cast(NULL as unsigned), cast(1/0 as unsigned);
cast(NULL as unsigned)	cast(1/0 as unsigned)
NULL	NULL
30 31 32 33 34
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)
35
2001-01-01	2001-01-01 00:00:00
36 37
select cast("1:2:3" as TIME);
cast("1:2:3" as TIME)
38
01:02:03
39 40 41
select CONVERT("2004-01-22 21:45:33",DATE);
CONVERT("2004-01-22 21:45:33",DATE)
2004-01-22
unknown's avatar
unknown committed
42 43 44 45 46 47 48 49 50
select CONVERT(DATE "2004-01-22 21:45:33" USING latin1);
CONVERT(DATE "2004-01-22 21:45:33" USING latin1)
2004-01-22 21:45:33
select CONVERT(DATE "2004-01-22 21:45:33",CHAR);
CONVERT(DATE "2004-01-22 21:45:33",CHAR)
2004-01-22 21:45:33
select CONVERT(DATE "2004-01-22 21:45:33",CHAR(4));
CONVERT(DATE "2004-01-22 21:45:33",CHAR(4))
2004
51 52
select CONVERT(DATE "2004-01-22 21:45:33",BINARY(4));
CONVERT(DATE "2004-01-22 21:45:33",BINARY(4))
unknown's avatar
unknown committed
53
2004
54 55
select CAST(DATE "2004-01-22 21:45:33" AS BINARY(4));
CAST(DATE "2004-01-22 21:45:33" AS BINARY(4))
unknown's avatar
unknown committed
56
2004
57 58 59 60 61 62 63 64 65 66 67 68
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
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103
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: ''
104
set names binary;
105 106 107 108 109 110 111 112 113 114 115
select cast(_latin1'test' as char character set latin2);
cast(_latin1'test' as char character set latin2)
test
select cast(_koi8r'' as char character set cp1251);
cast(_koi8r'' as char character set cp1251)

create table t1 select cast(_koi8r'' as char character set cp1251) as t;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t` char(4) character set cp1251 NOT NULL default ''
unknown's avatar
unknown committed
116
) ENGINE=MyISAM DEFAULT CHARSET=latin1
117
drop table t1;
118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137
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;
c1	c2	c3	c4	c5
ab	a 	ab	a 	a
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;
select * from t1;
c1	c2	c3	c4	c5
ab	a	ab	a	a
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
138 139 140 141 142
  `c1` binary(2) NOT NULL default '',
  `c2` binary(2) NOT NULL default '',
  `c3` binary(2) NOT NULL default '',
  `c4` binary(2) NOT NULL default '',
  `c5` binary(2) NOT NULL default ''
unknown's avatar
unknown committed
143
) ENGINE=MyISAM DEFAULT CHARSET=latin1
144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169
drop table t1;
select
cast(_koi8r''  AS nchar)    as c1,
cast(_koi8r' '  AS nchar)    as c2,
cast(_koi8r'' AS nchar(2)) as c3,
cast(_koi8r'  ' AS nchar(2)) as c4,
cast(_koi8r''   AS nchar(2)) as c5;
c1	c2	c3	c4	c5
фг	ф 	фг	ф 	ф
create table t1 select
cast(_koi8r''  AS nchar)    as c1,
cast(_koi8r' '  AS nchar)    as c2,
cast(_koi8r'' AS nchar(2)) as c3,
cast(_koi8r'  ' AS nchar(2)) as c4,
cast(_koi8r''   AS nchar(2)) as c5;
select * from t1;
c1	c2	c3	c4	c5
фг	ф	фг	ф	ф
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` char(2) character set utf8 NOT NULL default '',
  `c2` char(2) character set utf8 NOT NULL default '',
  `c3` char(2) character set utf8 NOT NULL default '',
  `c4` char(2) character set utf8 NOT NULL default '',
  `c5` char(2) character set utf8 NOT NULL default ''
unknown's avatar
unknown committed
170
) ENGINE=MyISAM DEFAULT CHARSET=latin1
171
drop table t1;
172 173 174 175 176 177 178 179 180 181 182 183 184 185 186
create table t1 (a binary(10), b char(10) character set koi8r);
insert into t1 values (_binary'',_binary'');
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)
			
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)
			
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)
			
drop table t1;
set names binary;
187 188
select cast("2001-1-1" as date) = "2001-01-01";
cast("2001-1-1" as date) = "2001-01-01"
unknown's avatar
unknown committed
189
1
190 191
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"
unknown's avatar
unknown committed
192
1
193 194 195
select cast("1:2:3" as TIME) = "1:02:03";
cast("1:2:3" as TIME) = "1:02:03"
0
unknown's avatar
unknown committed
196 197 198
select cast(NULL as DATE);
cast(NULL as DATE)
NULL
199 200 201
select cast(NULL as BINARY);
cast(NULL as BINARY)
NULL
202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224
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
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
DROP TABLE t1;
225 226 227 228 229 230 231 232 233
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
234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266
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
267 268 269
select cast(19999999999999999999 as unsigned);
cast(19999999999999999999 as unsigned)
18446744073709551615
270 271 272
select cast(1.0e+300 as signed int);
cast(1.0e+300 as signed int)
9223372036854775807
273 274 275 276 277 278 279 280
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
-1e+30	-9223372036854775808
1e+30	9223372036854775807
DROP TABLE t1;