cast.result 12.8 KB
Newer Older
1 2 3 4 5 6
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
7 8 9
select CAST('10 ' as unsigned integer);
CAST('10 ' as unsigned integer)
10
monty@mysql.com's avatar
monty@mysql.com committed
10 11
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '10 '
12 13 14 15 16 17 18 19 20
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
21 22 23 24
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:
25
Note	1003	select ~(5) AS `~5`,cast(~(5) as signed) AS `cast(~5 as signed)`
26 27 28
select cast(5 as unsigned) -6.0;
cast(5 as unsigned) -6.0
-1.0
29 30 31 32 33 34
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
35 36 37 38 39
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)
40
2001-01-01	2001-01-01 00:00:00
41 42
select cast("1:2:3" as TIME);
cast("1:2:3" as TIME)
43
01:02:03
44 45 46
select CONVERT("2004-01-22 21:45:33",DATE);
CONVERT("2004-01-22 21:45:33",DATE)
2004-01-22
47 48 49 50 51 52 53 54 55
select 10+'10';
10+'10'
20
select 10.0+'10';
10.0+'10'
20
select 10E+0+'10';
10E+0+'10'
20
56 57 58 59 60 61 62 63 64
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
65 66
Warnings:
Warning	1292	Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33'
67 68
select CONVERT(DATE "2004-01-22 21:45:33",BINARY(4));
CONVERT(DATE "2004-01-22 21:45:33",BINARY(4))
69
2004
70
Warnings:
bar@mysql.com's avatar
bar@mysql.com committed
71
Warning	1292	Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33'
72 73
select CAST(DATE "2004-01-22 21:45:33" AS BINARY(4));
CAST(DATE "2004-01-22 21:45:33" AS BINARY(4))
74
2004
75
Warnings:
bar@mysql.com's avatar
bar@mysql.com committed
76
Warning	1292	Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33'
77 78 79 80 81 82 83 84 85 86 87 88
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
89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
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.00
Warnings:
hf@deer.(none)'s avatar
hf@deer.(none) committed
108
Warning	1292	Truncated incorrect DECIMAL value: 'a'
109 110 111 112 113
select 10E+0+'a';
10E+0+'a'
10
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'a'
114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148
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: ''
149
set names binary;
150 151 152 153 154 155 156 157 158 159
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` (
160
  `t` varchar(4) character set cp1251 NOT NULL default ''
161
) ENGINE=MyISAM DEFAULT CHARSET=latin1
162
drop table t1;
163 164 165 166 167
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,
168
hex(cast(_latin1'a'   AS char(2))) as c5;
169
c1	c2	c3	c4	c5
170
ab	a 	ab	a 	6100
171
Warnings:
bar@mysql.com's avatar
bar@mysql.com committed
172 173
Warning	1292	Truncated incorrect BINARY(2) value: 'abc'
Warning	1292	Truncated incorrect BINARY(2) value: 'a  '
174 175 176 177
select cast(1000 as CHAR(3));
cast(1000 as CHAR(3))
100
Warnings:
bar@mysql.com's avatar
bar@mysql.com committed
178
Warning	1292	Truncated incorrect BINARY(3) value: '1000'
179 180 181 182 183 184
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;
185
Warnings:
bar@mysql.com's avatar
bar@mysql.com committed
186 187
Warning	1292	Truncated incorrect BINARY(2) value: 'abc'
Warning	1292	Truncated incorrect BINARY(2) value: 'a  '
188 189 190
select c1,c2,c3,c4,hex(c5) from t1;
c1	c2	c3	c4	hex(c5)
ab	a 	ab	a 	6100
191 192 193
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
194 195 196 197 198
  `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 ''
199
) ENGINE=MyISAM DEFAULT CHARSET=latin1
200 201 202 203 204 205 206 207 208
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
фг	ф 	фг	ф 	ф
209 210 211
Warnings:
Warning	1292	Truncated incorrect CHAR(4) value: 'фгх'
Warning	1292	Truncated incorrect CHAR(3) value: 'ф  '
212 213 214 215 216 217
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;
218 219 220
Warnings:
Warning	1292	Truncated incorrect CHAR(4) value: 'фгх'
Warning	1292	Truncated incorrect CHAR(3) value: 'ф  '
221 222
select * from t1;
c1	c2	c3	c4	c5
223
фг	ф 	фг	ф 	ф
224 225 226
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
227 228 229 230 231
  `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 ''
232
) ENGINE=MyISAM DEFAULT CHARSET=latin1
233
drop table t1;
234
create table t1 (a binary(4), b char(4) character set koi8r);
235 236 237 238 239 240 241 242 243 244 245 246 247 248
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;
249 250
select cast("2001-1-1" as date) = "2001-01-01";
cast("2001-1-1" as date) = "2001-01-01"
251
1
252 253
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"
254
1
255 256 257
select cast("1:2:3" as TIME) = "1:02:03";
cast("1:2:3" as TIME) = "1:02:03"
0
258 259 260
select cast(NULL as DATE);
cast(NULL as DATE)
NULL
261 262 263
select cast(NULL as BINARY);
cast(NULL as BINARY)
NULL
264 265 266 267 268 269 270 271 272 273 274 275
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
276
Warnings:
bar@mysql.com's avatar
bar@mysql.com committed
277 278 279
Warning	1292	Truncated incorrect BINARY(2) value: 'aaa'
Warning	1292	Truncated incorrect BINARY(2) value: 'aab'
Warning	1292	Truncated incorrect BINARY(2) value: 'aac'
280 281 282 283 284 285 286 287 288 289
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
290
Warnings:
bar@mysql.com's avatar
bar@mysql.com committed
291 292 293
Warning	1292	Truncated incorrect BINARY(2) value: 'aaa'
Warning	1292	Truncated incorrect BINARY(2) value: 'aab'
Warning	1292	Truncated incorrect BINARY(2) value: 'aac'
294
DROP TABLE t1;
295 296 297 298 299 300 301 302 303
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
304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336
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
337 338 339
select cast(1.0e+300 as signed int);
cast(1.0e+300 as signed int)
9223372036854775807
bar@mysql.com's avatar
bar@mysql.com committed
340 341 342 343 344 345 346
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
bar@mysql.com's avatar
bar@mysql.com committed
347 348 349
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '-1e+30'
Warning	1292	Truncated incorrect INTEGER value: '1e+30'
bar@mysql.com's avatar
bar@mysql.com committed
350
DROP TABLE t1;
351 352 353
select isnull(date(NULL)), isnull(cast(NULL as DATE));
isnull(date(NULL))	isnull(cast(NULL as DATE))
1	1
354 355 356 357 358 359
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
360
End of 4.1 tests
361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376
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.2e+18
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
377 378 379 380 381 382
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))
111111.00
drop table t1;
383 384 385 386 387 388 389 390
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.01	2.02	3.03	4.04	5.05
DROP TABLE t1;
391 392 393
select cast(NULL as decimal(6)) as t1;
t1
NULL
394 395 396 397 398 399 400 401 402 403 404
set names latin1;
select hex(cast('a' as char(2) binary));
hex(cast('a' as char(2) binary))
61
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
End of 5.0 tests