Debian Codegraph @

test/cast.test

raw
1 # 2005 June 25
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the CAST operator.
13 #
14 # $Id: cast.test,v 1.10 2008/11/06 15:33:04 drh Exp $
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18
19 # Only run these tests if the build includes the CAST operator
20 ifcapable !cast {
21 finish_test
22 return
23 }
24
25 # Tests for the CAST( AS blob), CAST( AS text) and CAST( AS numeric) built-ins
26 #
27 ifcapable bloblit {
28 do_test cast-1.1 {
29 execsql {SELECT x'616263'}
30 } abc
31 do_test cast-1.2 {
32 execsql {SELECT typeof(x'616263')}
33 } blob
34 do_test cast-1.3 {
35 execsql {SELECT CAST(x'616263' AS text)}
36 } abc
37 do_test cast-1.4 {
38 execsql {SELECT typeof(CAST(x'616263' AS text))}
39 } text
40 do_test cast-1.5 {
41 execsql {SELECT CAST(x'616263' AS numeric)}
42 } 0
43 do_test cast-1.6 {
44 execsql {SELECT typeof(CAST(x'616263' AS numeric))}
45 } integer
46 do_test cast-1.7 {
47 execsql {SELECT CAST(x'616263' AS blob)}
48 } abc
49 do_test cast-1.8 {
50 execsql {SELECT typeof(CAST(x'616263' AS blob))}
51 } blob
52 do_test cast-1.9 {
53 execsql {SELECT CAST(x'616263' AS integer)}
54 } 0
55 do_test cast-1.10 {
56 execsql {SELECT typeof(CAST(x'616263' AS integer))}
57 } integer
58 }
59 do_test cast-1.11 {
60 execsql {SELECT null}
61 } {{}}
62 do_test cast-1.12 {
63 execsql {SELECT typeof(NULL)}
64 } null
65 do_test cast-1.13 {
66 execsql {SELECT CAST(NULL AS text)}
67 } {{}}
68 do_test cast-1.14 {
69 execsql {SELECT typeof(CAST(NULL AS text))}
70 } null
71 do_test cast-1.15 {
72 execsql {SELECT CAST(NULL AS numeric)}
73 } {{}}
74 do_test cast-1.16 {
75 execsql {SELECT typeof(CAST(NULL AS numeric))}
76 } null
77 do_test cast-1.17 {
78 execsql {SELECT CAST(NULL AS blob)}
79 } {{}}
80 do_test cast-1.18 {
81 execsql {SELECT typeof(CAST(NULL AS blob))}
82 } null
83 do_test cast-1.19 {
84 execsql {SELECT CAST(NULL AS integer)}
85 } {{}}
86 do_test cast-1.20 {
87 execsql {SELECT typeof(CAST(NULL AS integer))}
88 } null
89 do_test cast-1.21 {
90 execsql {SELECT 123}
91 } {123}
92 do_test cast-1.22 {
93 execsql {SELECT typeof(123)}
94 } integer
95 do_test cast-1.23 {
96 execsql {SELECT CAST(123 AS text)}
97 } {123}
98 do_test cast-1.24 {
99 execsql {SELECT typeof(CAST(123 AS text))}
100 } text
101 do_test cast-1.25 {
102 execsql {SELECT CAST(123 AS numeric)}
103 } 123
104 do_test cast-1.26 {
105 execsql {SELECT typeof(CAST(123 AS numeric))}
106 } integer
107 do_test cast-1.27 {
108 execsql {SELECT CAST(123 AS blob)}
109 } {123}
110 do_test cast-1.28 {
111 execsql {SELECT typeof(CAST(123 AS blob))}
112 } blob
113 do_test cast-1.29 {
114 execsql {SELECT CAST(123 AS integer)}
115 } {123}
116 do_test cast-1.30 {
117 execsql {SELECT typeof(CAST(123 AS integer))}
118 } integer
119 do_test cast-1.31 {
120 execsql {SELECT 123.456}
121 } {123.456}
122 do_test cast-1.32 {
123 execsql {SELECT typeof(123.456)}
124 } real
125 do_test cast-1.33 {
126 execsql {SELECT CAST(123.456 AS text)}
127 } {123.456}
128 do_test cast-1.34 {
129 execsql {SELECT typeof(CAST(123.456 AS text))}
130 } text
131 do_test cast-1.35 {
132 execsql {SELECT CAST(123.456 AS numeric)}
133 } 123.456
134 do_test cast-1.36 {
135 execsql {SELECT typeof(CAST(123.456 AS numeric))}
136 } real
137 do_test cast-1.37 {
138 execsql {SELECT CAST(123.456 AS blob)}
139 } {123.456}
140 do_test cast-1.38 {
141 execsql {SELECT typeof(CAST(123.456 AS blob))}
142 } blob
143 do_test cast-1.39 {
144 execsql {SELECT CAST(123.456 AS integer)}
145 } {123}
146 do_test cast-1.38 {
147 execsql {SELECT typeof(CAST(123.456 AS integer))}
148 } integer
149 do_test cast-1.41 {
150 execsql {SELECT '123abc'}
151 } {123abc}
152 do_test cast-1.42 {
153 execsql {SELECT typeof('123abc')}
154 } text
155 do_test cast-1.43 {
156 execsql {SELECT CAST('123abc' AS text)}
157 } {123abc}
158 do_test cast-1.44 {
159 execsql {SELECT typeof(CAST('123abc' AS text))}
160 } text
161 do_test cast-1.45 {
162 execsql {SELECT CAST('123abc' AS numeric)}
163 } 123
164 do_test cast-1.46 {
165 execsql {SELECT typeof(CAST('123abc' AS numeric))}
166 } integer
167 do_test cast-1.47 {
168 execsql {SELECT CAST('123abc' AS blob)}
169 } {123abc}
170 do_test cast-1.48 {
171 execsql {SELECT typeof(CAST('123abc' AS blob))}
172 } blob
173 do_test cast-1.49 {
174 execsql {SELECT CAST('123abc' AS integer)}
175 } 123
176 do_test cast-1.50 {
177 execsql {SELECT typeof(CAST('123abc' AS integer))}
178 } integer
179 do_test cast-1.51 {
180 execsql {SELECT CAST('123.5abc' AS numeric)}
181 } 123.5
182 do_test cast-1.53 {
183 execsql {SELECT CAST('123.5abc' AS integer)}
184 } 123
185
186 do_test cast-1.60 {
187 execsql {SELECT CAST(null AS REAL)}
188 } {{}}
189 do_test cast-1.61 {
190 execsql {SELECT typeof(CAST(null AS REAL))}
191 } {null}
192 do_test cast-1.62 {
193 execsql {SELECT CAST(1 AS REAL)}
194 } {1.0}
195 do_test cast-1.63 {
196 execsql {SELECT typeof(CAST(1 AS REAL))}
197 } {real}
198 do_test cast-1.64 {
199 execsql {SELECT CAST('1' AS REAL)}
200 } {1.0}
201 do_test cast-1.65 {
202 execsql {SELECT typeof(CAST('1' AS REAL))}
203 } {real}
204 do_test cast-1.66 {
205 execsql {SELECT CAST('abc' AS REAL)}
206 } {0.0}
207 do_test cast-1.67 {
208 execsql {SELECT typeof(CAST('abc' AS REAL))}
209 } {real}
210 do_test cast-1.68 {
211 execsql {SELECT CAST(x'31' AS REAL)}
212 } {1.0}
213 do_test cast-1.69 {
214 execsql {SELECT typeof(CAST(x'31' AS REAL))}
215 } {real}
216
217
218 # Ticket #1662. Ignore leading spaces in numbers when casting.
219 #
220 do_test cast-2.1 {
221 execsql {SELECT CAST(' 123' AS integer)}
222 } 123
223 do_test cast-2.2 {
224 execsql {SELECT CAST(' -123.456' AS real)}
225 } -123.456
226
227 # ticket #2364. Use full percision integers if possible when casting
228 # to numeric. Do not fallback to real (and the corresponding 48-bit
229 # mantissa) unless absolutely necessary.
230 #
231 do_test cast-3.1 {
232 execsql {SELECT CAST(9223372036854774800 AS integer)}
233 } 9223372036854774800
234 do_test cast-3.2 {
235 execsql {SELECT CAST(9223372036854774800 AS numeric)}
236 } 9223372036854774800
237 breakpoint
238 do_realnum_test cast-3.3 {
239 execsql {SELECT CAST(9223372036854774800 AS real)}
240 } 9.22337203685477e+18
241 do_test cast-3.4 {
242 execsql {SELECT CAST(CAST(9223372036854774800 AS real) AS integer)}
243 } 9223372036854774784
244 do_test cast-3.5 {
245 execsql {SELECT CAST(-9223372036854774800 AS integer)}
246 } -9223372036854774800
247 do_test cast-3.6 {
248 execsql {SELECT CAST(-9223372036854774800 AS numeric)}
249 } -9223372036854774800
250 do_realnum_test cast-3.7 {
251 execsql {SELECT CAST(-9223372036854774800 AS real)}
252 } -9.22337203685477e+18
253 do_test cast-3.8 {
254 execsql {SELECT CAST(CAST(-9223372036854774800 AS real) AS integer)}
255 } -9223372036854774784
256 do_test cast-3.11 {
257 execsql {SELECT CAST('9223372036854774800' AS integer)}
258 } 9223372036854774800
259 do_test cast-3.12 {
260 execsql {SELECT CAST('9223372036854774800' AS numeric)}
261 } 9223372036854774800
262 do_realnum_test cast-3.13 {
263 execsql {SELECT CAST('9223372036854774800' AS real)}
264 } 9.22337203685477e+18
265 do_test cast-3.14 {
266 execsql {SELECT CAST(CAST('9223372036854774800' AS real) AS integer)}
267 } 9223372036854774784
268 do_test cast-3.15 {
269 execsql {SELECT CAST('-9223372036854774800' AS integer)}
270 } -9223372036854774800
271 do_test cast-3.16 {
272 execsql {SELECT CAST('-9223372036854774800' AS numeric)}
273 } -9223372036854774800
274 do_realnum_test cast-3.17 {
275 execsql {SELECT CAST('-9223372036854774800' AS real)}
276 } -9.22337203685477e+18
277 do_test cast-3.18 {
278 execsql {SELECT CAST(CAST('-9223372036854774800' AS real) AS integer)}
279 } -9223372036854774784
280 if {[db eval {PRAGMA encoding}]=="UTF-8"} {
281 do_test cast-3.21 {
282 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS integer)}
283 } 9223372036854774800
284 do_test cast-3.22 {
285 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS numeric)}
286 } 9223372036854774800
287 do_realnum_test cast-3.23 {
288 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS real)}
289 } 9.22337203685477e+18
290 do_test cast-3.24 {
291 execsql {
292 SELECT CAST(CAST(x'39323233333732303336383534373734383030' AS real)
293 AS integer)
294 }
295 } 9223372036854774784
296 }
297 do_test cast-3.31 {
298 execsql {SELECT CAST(NULL AS numeric)}
299 } {{}}
300
301 # Test to see if it is possible to trick SQLite into reading past
302 # the end of a blob when converting it to a number.
303 do_test cast-3.32.1 {
304 set blob "1234567890"
305 set DB [sqlite3_connection_pointer db]
306 set ::STMT [sqlite3_prepare $DB {SELECT CAST(? AS real)} -1 TAIL]
307 sqlite3_bind_blob -static $::STMT 1 $blob 5
308 sqlite3_step $::STMT
309 } {SQLITE_ROW}
310 do_test cast-3.32.2 {
311 sqlite3_column_int $::STMT 0
312 } {12345}
313 do_test cast-3.32.3 {
314 sqlite3_finalize $::STMT
315 } {SQLITE_OK}
316
317
318 do_test cast-4.1 {
319 db eval {
320 CREATE TABLE t1(a);
321 INSERT INTO t1 VALUES('abc');
322 SELECT a, CAST(a AS integer) FROM t1;
323 }
324 } {abc 0}
325 do_test cast-4.2 {
326 db eval {
327 SELECT CAST(a AS integer), a FROM t1;
328 }
329 } {0 abc}
330 do_test cast-4.3 {
331 db eval {
332 SELECT a, CAST(a AS integer), a FROM t1;
333 }
334 } {abc 0 abc}
335 do_test cast-4.4 {
336 db eval {
337 SELECT CAST(a AS integer), a, CAST(a AS real), a FROM t1;
338 }
339 } {0 abc 0.0 abc}
340
341 # Added 2018-01-26
342 #
343 # EVIDENCE-OF: R-48741-32454 If the prefix integer is greater than
344 # +9223372036854775807 then the result of the cast is exactly
345 # +9223372036854775807.
346 do_execsql_test cast-5.1 {
347 SELECT CAST('9223372036854775808' AS integer);
348 SELECT CAST(' +000009223372036854775808' AS integer);
349 SELECT CAST('12345678901234567890123' AS INTEGER);
350 } {9223372036854775807 9223372036854775807 9223372036854775807}
351
352 # EVIDENCE-OF: R-06028-16857 Similarly, if the prefix integer is less
353 # than -9223372036854775808 then the result of the cast is exactly
354 # -9223372036854775808.
355 do_execsql_test cast-5.2 {
356 SELECT CAST('-9223372036854775808' AS integer);
357 SELECT CAST('-9223372036854775809' AS integer);
358 SELECT CAST('-12345678901234567890123' AS INTEGER);
359 } {-9223372036854775808 -9223372036854775808 -9223372036854775808}
360
361 # EVIDENCE-OF: R-33990-33527 When casting to INTEGER, if the text looks
362 # like a floating point value with an exponent, the exponent will be
363 # ignored because it is no part of the integer prefix.
364 # EVIDENCE-OF: R-24225-46995 For example, "(CAST '123e+5' AS INTEGER)"
365 # results in 123, not in 12300000.
366 do_execsql_test cast-5.3 {
367 SELECT CAST('123e+5' AS INTEGER);
368 SELECT CAST('123e+5' AS NUMERIC);
369 SELECT CAST('123e+5' AS REAL);
370 } {123 12300000 12300000.0}
371
372
373 # The following does not have anything to do with the CAST operator,
374 # but it does deal with affinity transformations.
375 #
376 do_execsql_test cast-6.1 {
377 DROP TABLE IF EXISTS t1;
378 CREATE TABLE t1(a NUMERIC);
379 INSERT INTO t1 VALUES
380 ('9000000000000000001'),
381 ('9000000000000000001 '),
382 (' 9000000000000000001'),
383 (' 9000000000000000001 ');
384 SELECT * FROM t1;
385 } {9000000000000000001 9000000000000000001 9000000000000000001 9000000000000000001}
386
387 # 2019-06-07
388 # https://sqlite.org/src/info/4c2d7639f076aa7c
389 do_execsql_test cast-7.1 {
390 SELECT CAST('-' AS NUMERIC);
391 } {0}
392 do_execsql_test cast-7.2 {
393 SELECT CAST('-0' AS NUMERIC);
394 } {0}
395 do_execsql_test cast-7.3 {
396 SELECT CAST('+' AS NUMERIC);
397 } {0}
398 do_execsql_test cast-7.4 {
399 SELECT CAST('/' AS NUMERIC);
400 } {0}
401
402 # 2019-06-07
403 # https://sqlite.org/src/info/e8bedb2a184001bb
404 do_execsql_test cast-7.10 {
405 SELECT '' - 2851427734582196970;
406 } {-2851427734582196970}
407 do_execsql_test cast-7.11 {
408 SELECT 0 - 2851427734582196970;
409 } {-2851427734582196970}
410 do_execsql_test cast-7.12 {
411 SELECT '' - 1;
412 } {-1}
413
414 # 2019-06-10
415 # https://sqlite.org/src/info/dd6bffbfb6e61db9
416 #
417 # EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC
418 # yields either an INTEGER or a REAL result.
419 #
420 do_execsql_test cast-7.20 {
421 DROP TABLE IF EXISTS t0;
422 CREATE TABLE t0 (c0 TEXT);
423 INSERT INTO t0(c0) VALUES ('1.0');
424 SELECT CAST(c0 AS NUMERIC) FROM t0;
425 } {1}
426
427 # 2019-06-10
428 # https://sqlite.org/src/info/27de823723a41df45af3
429 #
430 do_execsql_test cast-7.30 {
431 SELECT -'.';
432 } 0
433 do_execsql_test cast-7.31 {
434 SELECT '.'+0;
435 } 0
436 do_execsql_test cast-7.32 {
437 SELECT CAST('.' AS numeric);
438 } 0
439 do_execsql_test cast-7.33 {
440 SELECT -CAST('.' AS numeric);
441 } 0
442
443 # 2019-06-12
444 # https://sqlite.org/src/info/674385aeba91c774
445 #
446 do_execsql_test cast-7.40 {
447 SELECT CAST('-0.0' AS numeric);
448 } 0
449 do_execsql_test cast-7.41 {
450 SELECT CAST('0.0' AS numeric);
451 } 0
452 do_execsql_test cast-7.42 {
453 SELECT CAST('+0.0' AS numeric);
454 } 0
455 do_execsql_test cast-7.43 {
456 SELECT CAST('-1.0' AS numeric);
457 } -1
458
459 ifcapable utf16 {
460 reset_db
461 execsql { PRAGMA encoding='utf16' }
462
463 do_execsql_test cast-8.1 {
464 SELECT quote(X'310032003300')==quote(substr(X'310032003300', 1))
465 } 1
466 do_execsql_test cast-8.2 {
467 SELECT CAST(X'310032003300' AS TEXT)
468 ==CAST(substr(X'310032003300', 1) AS TEXT)
469 } 1
470 }
471
472 reset_db
473 do_execsql_test cast-9.0 {
474 CREATE TABLE t0(c0);
475 INSERT INTO t0(c0) VALUES (0);
476 CREATE VIEW v1(c0, c1) AS
477 SELECT CAST(0.0 AS NUMERIC), COUNT(*) OVER () FROM t0;
478 SELECT v1.c0 FROM v1, t0 WHERE v1.c0=0;
479 } {0.0}
480
481 # Set the 2022-12-10 "reopen" of ticket [https://sqlite.org/src/tktview/57c47526c3]
482 #
483 do_execsql_test cast-9.1 {
484 CREATE TABLE dual(dummy TEXT);
485 INSERT INTO dual VALUES('X');
486 SELECT CAST(4 AS NUMERIC);
487 } {4}
488 do_execsql_test cast-9.2 {
489 SELECT CAST(4.0 AS NUMERIC);
490 } {4.0}
491 do_execsql_test cast-9.3 {
492 SELECT CAST(4.5 AS NUMERIC);
493 } {4.5}
494 do_execsql_test cast-9.4 {
495 SELECT x, typeof(x) FROM (SELECT CAST(4 AS NUMERIC) AS x) JOIN dual;
496 } {4 integer}
497 do_execsql_test cast-9.5 {
498 SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4 AS NUMERIC) AS x);
499 } {4 integer}
500 do_execsql_test cast-9.10 {
501 SELECT x, typeof(x) FROM (SELECT CAST(4.0 AS NUMERIC) AS x) JOIN dual;
502 } {4.0 real}
503 do_execsql_test cast-9.11 {
504 SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4.0 AS NUMERIC) AS x);
505 } {4.0 real}
506 do_execsql_test cast-9.12 {
507 SELECT x, typeof(x) FROM (SELECT CAST(4.5 AS NUMERIC) AS x) JOIN dual;
508 } {4.5 real}
509 do_execsql_test cast-9.13 {
510 SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4.5 AS NUMERIC) AS x);
511 } {4.5 real}
512
513 # 2022-12-15 dbsqlfuzz c9ee6f9a0a8b8fefb02cf69de2a8b67ca39525c8
514 #
515 # Added a new SQLITE_AFF_FLEXNUM that does not try to convert int to real or
516 # real to int.
517 #
518 do_execsql_test cast-10.1 {
519 VALUES(CAST(44 AS REAL)),(55);
520 } {44.0 55}
521 do_execsql_test cast-10.2 {
522 SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55;
523 } {44.0 55}
524 do_execsql_test cast-10.3 {
525 SELECT * FROM (VALUES(CAST(44 AS REAL)),(55));
526 } {44.0 55}
527 do_execsql_test cast-10.4 {
528 SELECT * FROM (SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55);
529 } {44.0 55}
530 do_execsql_test cast-10.5 {
531 SELECT * FROM dual CROSS JOIN (VALUES(CAST(44 AS REAL)),(55));
532 } {X 44.0 X 55}
533 do_execsql_test cast-10.6 {
534 SELECT * FROM dual CROSS JOIN (SELECT CAST(44 AS REAL) AS 'm'
535 UNION ALL SELECT 55);
536 } {X 44.0 X 55}
537 ifcapable vtab {
538 do_execsql_test cast-10.7 {
539 DROP VIEW v1;
540 CREATE VIEW v1 AS SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55;
541 SELECT name, type FROM pragma_table_info('v1');
542 } {m NUM}
543 do_execsql_test cast-10.8 {
544 CREATE VIEW v2 AS VALUES(CAST(44 AS REAL)),(55);
545 SELECT type FROM pragma_table_info('v2');
546 } {NUM}
547 do_execsql_test cast-10.9 {
548 SELECT * FROM v1;
549 } {44.0 55}
550 do_execsql_test cast-10.10 {
551 SELECT * FROM v2;
552 } {44.0 55}
553 }
554
555 finish_test