| blob | fc0d79e95686268eeaae887f73bdc99a306339ee |
1 select CAST(1-2 AS UNSIGNED);
2 CAST(1-2 AS UNSIGNED)
3 18446744073709551615
4 select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER);
5 CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER)
6 -1
7 select CAST('10 ' as unsigned integer);
8 CAST('10 ' as unsigned integer)
9 10
10 Warnings:
11 Warning 1292 Truncated incorrect INTEGER value: '10 '
12 select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1;
13 cast(-5 as unsigned) | 1 cast(-5 as unsigned) & -1
14 18446744073709551611 18446744073709551611
15 select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1;
16 cast(-5 as unsigned) -1 cast(-5 as unsigned) + 1
17 18446744073709551610 18446744073709551612
18 select ~5, cast(~5 as signed);
19 ~5 cast(~5 as signed)
20 18446744073709551610 -6
21 explain extended select ~5, cast(~5 as signed);
22 id select_type table type possible_keys key key_len ref rows filtered Extra
23 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
24 Warnings:
25 Note 1003 select ~(5) AS `~5`,cast(~(5) as signed) AS `cast(~5 as signed)`
26 select cast(5 as unsigned) -6.0;
27 cast(5 as unsigned) -6.0
28 -1.0
29 select cast(NULL as signed), cast(1/0 as signed);
30 cast(NULL as signed) cast(1/0 as signed)
31 NULL NULL
32 select cast(NULL as unsigned), cast(1/0 as unsigned);
33 cast(NULL as unsigned) cast(1/0 as unsigned)
34 NULL NULL
35 select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A";
36 cast("A" as binary) = "a" cast(BINARY "a" as CHAR) = "A"
37 0 1
38 select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME);
39 cast("2001-1-1" as DATE) cast("2001-1-1" as DATETIME)
40 2001-01-01 2001-01-01 00:00:00
41 select cast("1:2:3" as TIME);
42 cast("1:2:3" as TIME)
43 01:02:03
44 select CONVERT("2004-01-22 21:45:33",DATE);
45 CONVERT("2004-01-22 21:45:33",DATE)
46 2004-01-22
47 select 10+'10';
48 10+'10'
49 20
50 select 10.0+'10';
51 10.0+'10'
52 20
53 select 10E+0+'10';
54 10E+0+'10'
55 20
56 select CONVERT(DATE "2004-01-22 21:45:33" USING latin1);
57 CONVERT(DATE "2004-01-22 21:45:33" USING latin1)
58 2004-01-22 21:45:33
59 select CONVERT(DATE "2004-01-22 21:45:33",CHAR);
60 CONVERT(DATE "2004-01-22 21:45:33",CHAR)
61 2004-01-22 21:45:33
62 select CONVERT(DATE "2004-01-22 21:45:33",CHAR(4));
63 CONVERT(DATE "2004-01-22 21:45:33",CHAR(4))
64 2004
65 Warnings:
66 Warning 1292 Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33'
67 select CONVERT(DATE "2004-01-22 21:45:33",BINARY(4));
68 CONVERT(DATE "2004-01-22 21:45:33",BINARY(4))
69 2004
70 Warnings:
71 Warning 1292 Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33'
72 select CAST(DATE "2004-01-22 21:45:33" AS BINARY(4));
73 CAST(DATE "2004-01-22 21:45:33" AS BINARY(4))
74 2004
75 Warnings:
76 Warning 1292 Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33'
77 select CAST(0xb3 as signed);
78 CAST(0xb3 as signed)
79 179
80 select CAST(0x8fffffffffffffff as signed);
81 CAST(0x8fffffffffffffff as signed)
82 -8070450532247928833
83 select CAST(0xffffffffffffffff as unsigned);
84 CAST(0xffffffffffffffff as unsigned)
85 18446744073709551615
86 select CAST(0xfffffffffffffffe as signed);
87 CAST(0xfffffffffffffffe as signed)
88 -2
89 select cast('-10a' as signed integer);
90 cast('-10a' as signed integer)
91 -10
92 Warnings:
93 Warning 1292 Truncated incorrect INTEGER value: '-10a'
94 select cast('a10' as unsigned integer);
95 cast('a10' as unsigned integer)
96 0
97 Warnings:
98 Warning 1292 Truncated incorrect INTEGER value: 'a10'
99 select 10+'a';
100 10+'a'
101 10
102 Warnings:
103 Warning 1292 Truncated incorrect DOUBLE value: 'a'
104 select 10.0+cast('a' as decimal);
105 10.0+cast('a' as decimal)
106 10.0
107 Warnings:
108 Warning 1292 Truncated incorrect DECIMAL value: 'a'
109 select 10E+0+'a';
110 10E+0+'a'
111 10
112 Warnings:
113 Warning 1292 Truncated incorrect DOUBLE value: 'a'
114 select cast('18446744073709551616' as unsigned);
115 cast('18446744073709551616' as unsigned)
116 18446744073709551615
117 Warnings:
118 Warning 1292 Truncated incorrect INTEGER value: '18446744073709551616'
119 select cast('18446744073709551616' as signed);
120 cast('18446744073709551616' as signed)
121 -1
122 Warnings:
123 Warning 1292 Truncated incorrect INTEGER value: '18446744073709551616'
124 select cast('9223372036854775809' as signed);
125 cast('9223372036854775809' as signed)
126 -9223372036854775807
127 Warnings:
128 Warning 1105 Cast to signed converted positive out-of-range integer to it's negative complement
129 select cast('-1' as unsigned);
130 cast('-1' as unsigned)
131 18446744073709551615
132 Warnings:
133 Warning 1105 Cast to unsigned converted negative integer to it's positive complement
134 select cast('abc' as signed);
135 cast('abc' as signed)
136 0
137 Warnings:
138 Warning 1292 Truncated incorrect INTEGER value: 'abc'
139 select cast('1a' as signed);
140 cast('1a' as signed)
141 1
142 Warnings:
143 Warning 1292 Truncated incorrect INTEGER value: '1a'
144 select cast('' as signed);
145 cast('' as signed)
146 0
147 Warnings:
148 Warning 1292 Truncated incorrect INTEGER value: ''
149 set names binary;
150 select cast(_latin1'test' as char character set latin2);
151 cast(_latin1'test' as char character set latin2)
152 test
153 select cast(_koi8r'ÔÅÓÔ' as char character set cp1251);
154 cast(_koi8r'ÔÅÓÔ' as char character set cp1251)
155 òåñò
156 create table t1 select cast(_koi8r'ÔÅÓÔ' as char character set cp1251) as t;
157 show create table t1;
158 Table Create Table
159 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 select
164 cast(_latin1'ab' AS char) as c1,
165 cast(_latin1'a ' AS char) as c2,
166 cast(_latin1'abc' AS char(2)) as c3,
167 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:
172 Warning 1292 Truncated incorrect BINARY(2) value: 'abc'
173 Warning 1292 Truncated incorrect BINARY(2) value: 'a '
174 select cast(1000 as CHAR(3));
175 cast(1000 as CHAR(3))
176 100
177 Warnings:
178 Warning 1292 Truncated incorrect BINARY(3) value: '1000'
179 create table t1 select
180 cast(_latin1'ab' AS char) as c1,
181 cast(_latin1'a ' AS char) as c2,
182 cast(_latin1'abc' AS char(2)) as c3,
183 cast(_latin1'a ' AS char(2)) as c4,
184 cast(_latin1'a' AS char(2)) as c5;
185 Warnings:
186 Warning 1292 Truncated incorrect BINARY(2) value: 'abc'
187 Warning 1292 Truncated incorrect BINARY(2) value: 'a '
188 select c1,c2,c3,c4,hex(c5) from t1;
189 c1 c2 c3 c4 hex(c5)
190 ab a ab a 6100
191 show create table t1;
192 Table Create Table
193 t1 CREATE TABLE `t1` (
194 `c1` varbinary(2) NOT NULL DEFAULT '',
195 `c2` varbinary(2) NOT NULL DEFAULT '',
196 `c3` varbinary(2) NOT NULL DEFAULT '',
197 `c4` varbinary(2) NOT NULL DEFAULT '',
198 `c5` varbinary(2) NOT NULL DEFAULT ''
199 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
200 drop table t1;
201 select
202 cast(_koi8r'ÆÇ' AS nchar) as c1,
203 cast(_koi8r'Æ ' AS nchar) as c2,
204 cast(_koi8r'ÆÇÈ' AS nchar(2)) as c3,
205 cast(_koi8r'Æ ' AS nchar(2)) as c4,
206 cast(_koi8r'Æ' AS nchar(2)) as c5;
207 c1 c2 c3 c4 c5
208 фг ф фг ф ф
209 Warnings:
210 Warning 1292 Truncated incorrect CHAR(4) value: 'фгх'
211 Warning 1292 Truncated incorrect CHAR(3) value: 'ф '
212 create table t1 select
213 cast(_koi8r'ÆÇ' AS nchar) as c1,
214 cast(_koi8r'Æ ' AS nchar) as c2,
215 cast(_koi8r'ÆÇÈ' AS nchar(2)) as c3,
216 cast(_koi8r'Æ ' AS nchar(2)) as c4,
217 cast(_koi8r'Æ' AS nchar(2)) as c5;
218 Warnings:
219 Warning 1292 Truncated incorrect CHAR(4) value: 'фгх'
220 Warning 1292 Truncated incorrect CHAR(3) value: 'ф '
221 select * from t1;
222 c1 c2 c3 c4 c5
223 фг ф фг ф ф
224 show create table t1;
225 Table Create Table
226 t1 CREATE TABLE `t1` (
227 `c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
228 `c2` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
229 `c3` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
230 `c4` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
231 `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 insert into t1 values (_binary'ÔÅÓÔ',_binary'ÔÅÓÔ');
236 select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
237 a b cast(a as char character set cp1251) cast(b as binary)
238 ÔÅÓÔ ÔÅÓÔ ÔÅÓÔ ÔÅÓÔ
239 set names koi8r;
240 select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
241 a b cast(a as char character set cp1251) cast(b as binary)
242 ÔÅÓÔ ÔÅÓÔ æåõæ ÔÅÓÔ
243 set names cp1251;
244 select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
245 a b cast(a as char character set cp1251) cast(b as binary)
246 ÔÅÓÔ òåñò ÔÅÓÔ ÔÅÓÔ
247 drop table t1;
248 set names binary;
249 select cast("2001-1-1" as date) = "2001-01-01";
250 cast("2001-1-1" as date) = "2001-01-01"
251 1
252 select cast("2001-1-1" as datetime) = "2001-01-01 00:00:00";
253 cast("2001-1-1" as datetime) = "2001-01-01 00:00:00"
254 1
255 select cast("1:2:3" as TIME) = "1:02:03";
256 cast("1:2:3" as TIME) = "1:02:03"
257 0
258 select cast(NULL as DATE);
259 cast(NULL as DATE)
260 NULL
261 select cast(NULL as BINARY);
262 cast(NULL as BINARY)
263 NULL
264 CREATE TABLE t1 (a enum ('aac','aab','aaa') not null);
265 INSERT INTO t1 VALUES ('aaa'),('aab'),('aac');
266 SELECT a, CAST(a AS CHAR) FROM t1 ORDER BY CAST(a AS UNSIGNED) ;
267 a CAST(a AS CHAR)
268 aac aac
269 aab aab
270 aaa aaa
271 SELECT a, CAST(a AS CHAR(3)) FROM t1 ORDER BY CAST(a AS CHAR(2)), a;
272 a CAST(a AS CHAR(3))
273 aac aac
274 aab aab
275 aaa aaa
276 Warnings:
277 Warning 1292 Truncated incorrect BINARY(2) value: 'aaa'
278 Warning 1292 Truncated incorrect BINARY(2) value: 'aab'
279 Warning 1292 Truncated incorrect BINARY(2) value: 'aac'
280 SELECT a, CAST(a AS UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR) ;
281 a CAST(a AS UNSIGNED)
282 aaa 3
283 aab 2
284 aac 1
285 SELECT a, CAST(a AS CHAR(2)) FROM t1 ORDER BY CAST(a AS CHAR(3)), a;
286 a CAST(a AS CHAR(2))
287 aaa aa
288 aab aa
289 aac aa
290 Warnings:
291 Warning 1292 Truncated incorrect BINARY(2) value: 'aaa'
292 Warning 1292 Truncated incorrect BINARY(2) value: 'aab'
293 Warning 1292 Truncated incorrect BINARY(2) value: 'aac'
294 DROP TABLE t1;
295 select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour);
296 date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour)
297 2004-12-30 00:00:00
298 select timediff(cast('2004-12-30 12:00:00' as time), '12:00:00');
299 timediff(cast('2004-12-30 12:00:00' as time), '12:00:00')
300 00:00:00
301 select timediff(cast('1 12:00:00' as time), '12:00:00');
302 timediff(cast('1 12:00:00' as time), '12:00:00')
303 24:00:00
304 select cast(18446744073709551615 as unsigned);
305 cast(18446744073709551615 as unsigned)
306 18446744073709551615
307 select cast(18446744073709551615 as signed);
308 cast(18446744073709551615 as signed)
309 -1
310 select cast('18446744073709551615' as unsigned);
311 cast('18446744073709551615' as unsigned)
312 18446744073709551615
313 select cast('18446744073709551615' as signed);
314 cast('18446744073709551615' as signed)
315 -1
316 Warnings:
317 Warning 1105 Cast to signed converted positive out-of-range integer to it's negative complement
318 select cast('9223372036854775807' as signed);
319 cast('9223372036854775807' as signed)
320 9223372036854775807
321 select cast(concat('184467440','73709551615') as unsigned);
322 cast(concat('184467440','73709551615') as unsigned)
323 18446744073709551615
324 select cast(concat('184467440','73709551615') as signed);
325 cast(concat('184467440','73709551615') as signed)
326 -1
327 Warnings:
328 Warning 1105 Cast to signed converted positive out-of-range integer to it's negative complement
329 select cast(repeat('1',20) as unsigned);
330 cast(repeat('1',20) as unsigned)
331 11111111111111111111
332 select cast(repeat('1',20) as signed);
333 cast(repeat('1',20) as signed)
334 -7335632962598440505
335 Warnings:
336 Warning 1105 Cast to signed converted positive out-of-range integer to it's negative complement
337 select cast(1.0e+300 as signed int);
338 cast(1.0e+300 as signed int)
339 9223372036854775807
340 CREATE TABLE t1 (f1 double);
341 INSERT INTO t1 SET f1 = -1.0e+30 ;
342 INSERT INTO t1 SET f1 = +1.0e+30 ;
343 SELECT f1 AS double_val, CAST(f1 AS SIGNED INT) AS cast_val FROM t1;
344 double_val cast_val
345 -1e+30 -9223372036854775808
346 1e+30 9223372036854775807
347 Warnings:
348 Warning 1292 Truncated incorrect INTEGER value: '-1e+30'
349 Warning 1292 Truncated incorrect INTEGER value: '1e+30'
350 DROP TABLE t1;
351 select isnull(date(NULL)), isnull(cast(NULL as DATE));
352 isnull(date(NULL)) isnull(cast(NULL as DATE))
353 1 1
354 SELECT CAST(cast('01-01-01' as date) AS UNSIGNED);
355 CAST(cast('01-01-01' as date) AS UNSIGNED)
356 20010101
357 SELECT CAST(cast('01-01-01' as date) AS SIGNED);
358 CAST(cast('01-01-01' as date) AS SIGNED)
359 20010101
360 End of 4.1 tests
361 select cast('1.2' as decimal(3,2));
362 cast('1.2' as decimal(3,2))
363 1.20
364 select 1e18 * cast('1.2' as decimal(3,2));
365 1e18 * cast('1.2' as decimal(3,2))
366 1.2e+18
367 select cast(cast('1.2' as decimal(3,2)) as signed);
368 cast(cast('1.2' as decimal(3,2)) as signed)
369 1
370 set @v1=1e18;
371 select cast(@v1 as decimal(22, 2));
372 cast(@v1 as decimal(22, 2))
373 1000000000000000000.00
374 select cast(-1e18 as decimal(22,2));
375 cast(-1e18 as decimal(22,2))
376 -1000000000000000000.00
377 create table t1(s1 time);
378 insert into t1 values ('11:11:11');
379 select cast(s1 as decimal(7,2)) from t1;
380 cast(s1 as decimal(7,2))
381 99999.99
382 Warnings:
383 Error 1264 Out of range value for column 'cast(s1 as decimal(7,2))' at row 1
384 drop table t1;
385 CREATE TABLE t1 (v varchar(10), tt tinytext, t text,
386 mt mediumtext, lt longtext);
387 INSERT INTO t1 VALUES ('1.01', '2.02', '3.03', '4.04', '5.05');
388 SELECT CAST(v AS DECIMAL), CAST(tt AS DECIMAL), CAST(t AS DECIMAL),
389 CAST(mt AS DECIMAL), CAST(lt AS DECIMAL) from t1;
390 CAST(v AS DECIMAL) CAST(tt AS DECIMAL) CAST(t AS DECIMAL) CAST(mt AS DECIMAL) CAST(lt AS DECIMAL)
391 1 2 3 4 5
392 DROP TABLE t1;
393 select cast(NULL as decimal(6)) as t1;
394 t1
395 NULL
396 set names latin1;
397 select hex(cast('a' as char(2) binary));
398 hex(cast('a' as char(2) binary))
399 61
400 select hex(cast('a' as binary(2)));
401 hex(cast('a' as binary(2)))
402 6100
403 select hex(cast('a' as char(2) binary));
404 hex(cast('a' as char(2) binary))
405 61
406 CREATE TABLE t1 (d1 datetime);
407 INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL),
408 ('2007-07-19 08:34:00'), (NULL), ('2007-07-19 08:36:00');
409 SELECT cast(date(d1) as signed) FROM t1;
410 cast(date(d1) as signed)
411 20070719
412 NULL
413 20070719
414 NULL
415 20070719
416 drop table t1;
417 CREATE TABLE t1 (f1 DATE);
418 INSERT INTO t1 VALUES ('2007-07-19'), (NULL);
419 SELECT HOUR(f1),
420 MINUTE(f1),
421 SECOND(f1) FROM t1;
422 HOUR(f1) MINUTE(f1) SECOND(f1)
423 0 0 0
424 NULL NULL NULL
425 SELECT HOUR(CAST('2007-07-19' AS DATE)),
426 MINUTE(CAST('2007-07-19' AS DATE)),
427 SECOND(CAST('2007-07-19' AS DATE));
428 HOUR(CAST('2007-07-19' AS DATE)) MINUTE(CAST('2007-07-19' AS DATE)) SECOND(CAST('2007-07-19' AS DATE))
429 0 0 0
430 SELECT HOUR(CAST(NULL AS DATE)),
431 MINUTE(CAST(NULL AS DATE)),
432 SECOND(CAST(NULL AS DATE));
433 HOUR(CAST(NULL AS DATE)) MINUTE(CAST(NULL AS DATE)) SECOND(CAST(NULL AS DATE))
434 NULL NULL NULL
435 SELECT HOUR(NULL),
436 MINUTE(NULL),
437 SECOND(NULL);
438 HOUR(NULL) MINUTE(NULL) SECOND(NULL)
439 NULL NULL NULL
440 DROP TABLE t1;
441 End of 5.0 tests
442 #
443 # Bug #44766: valgrind error when using convert() in a subquery
444 #
445 CREATE TABLE t1(a tinyint);
446 INSERT INTO t1 VALUES (127);
447 SELECT 1 FROM
448 (
449 SELECT CONVERT(t2.a USING UTF8) FROM t1, t1 t2 LIMIT 1
450 ) AS s LIMIT 1;
451 1
452 1
453 DROP TABLE t1;
454 #
455 # Bug #11765023: 57934: DOS POSSIBLE SINCE BINARY CASTING
456 # DOESN'T ADHERE TO MAX_ALLOWED_PACKET
457 SET @@GLOBAL.max_allowed_packet=2048;
458 Warnings:
459 Warning 1105 The value of 'max_allowed_packet' should be no less than the value of 'net_buffer_length'
460 SELECT CONVERT('a', BINARY(2049));
461 CONVERT('a', BINARY(2049))
462 NULL
463 Warnings:
464 Warning 1301 Result of cast_as_binary() was larger than max_allowed_packet (2048) - truncated
465 SELECT CONVERT('a', CHAR(2049));
466 CONVERT('a', CHAR(2049))
467 NULL
468 Warnings:
469 Warning 1301 Result of cast_as_char() was larger than max_allowed_packet (2048) - truncated
470 SET @@GLOBAL.max_allowed_packet=default;
471 #
472 # Bug#13519724 63793: CRASH IN DTCOLLATION::SET(DTCOLLATION &SET)
473 #
474 CREATE TABLE t1 (a VARCHAR(50));
475 SELECT a FROM t1
476 WHERE CAST(a as BINARY)=x'62736D697468'
477 AND CAST(a AS BINARY)=x'65736D697468';
478 a
479 DROP TABLE t1;
480 #
481 # Bug#13581962 HIGH MEMORY USAGE ATTEMPT, THEN CRASH WITH
482 # LONGTEXT, UNION, USER VARIABLE
483 # Bug#14096619 UNABLE TO RESTORE DATABASE DUMP
484 #
485 CREATE TABLE t1 AS SELECT CONCAT(CAST(REPEAT('9', 1000) AS SIGNED)),
486 CONCAT(CAST(REPEAT('9', 1000) AS UNSIGNED));
487 Warnings:
488 Warning 1292 Truncated incorrect INTEGER value: '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999'
489 Warning 1292 Truncated incorrect INTEGER value: '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999'
490 SHOW CREATE TABLE t1;
491 Table Create Table
492 t1 CREATE TABLE `t1` (
493 `CONCAT(CAST(REPEAT('9', 1000) AS SIGNED))` varbinary(21) NOT NULL DEFAULT '',
494 `CONCAT(CAST(REPEAT('9', 1000) AS UNSIGNED))` varbinary(21) NOT NULL DEFAULT ''
495 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
496 DROP TABLE t1;
497 # End of test for Bug#13581962, Bug#14096619
498 End of 5.1 tests