3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the CAST operator.
14 # $Id: cast.test,v 1.10 2008/11/06 15:33:04 drh Exp $
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 # Only run these tests if the build includes the CAST operator
25 # Tests for the CAST( AS blob), CAST( AS text) and CAST( AS numeric) built-ins
29 execsql {SELECT x'616263'}
32 execsql {SELECT typeof(x'616263')}
35 execsql {SELECT CAST(x'616263' AS text)}
38 execsql {SELECT typeof(CAST(x'616263' AS text))}
41 execsql {SELECT CAST(x'616263' AS numeric)}
44 execsql {SELECT typeof(CAST(x'616263' AS numeric))}
47 execsql {SELECT CAST(x'616263' AS blob)}
50 execsql {SELECT typeof(CAST(x'616263' AS blob))}
53 execsql {SELECT CAST(x'616263' AS integer)}
56 execsql {SELECT typeof(CAST(x'616263' AS integer))}
63 execsql {SELECT typeof(NULL)}
66 execsql {SELECT CAST(NULL AS text)}
69 execsql {SELECT typeof(CAST(NULL AS text))}
72 execsql {SELECT CAST(NULL AS numeric)}
75 execsql {SELECT typeof(CAST(NULL AS numeric))}
78 execsql {SELECT CAST(NULL AS blob)}
81 execsql {SELECT typeof(CAST(NULL AS blob))}
84 execsql {SELECT CAST(NULL AS integer)}
87 execsql {SELECT typeof(CAST(NULL AS integer))}
93 execsql {SELECT typeof(123)}
96 execsql {SELECT CAST(123 AS text)}
99 execsql {SELECT typeof(CAST(123 AS text))}
102 execsql {SELECT CAST(123 AS numeric)}
105 execsql {SELECT typeof(CAST(123 AS numeric))}
108 execsql {SELECT CAST(123 AS blob)}
111 execsql {SELECT typeof(CAST(123 AS blob))}
114 execsql {SELECT CAST(123 AS integer)}
117 execsql {SELECT typeof(CAST(123 AS integer))}
120 execsql {SELECT 123.456}
123 execsql {SELECT typeof(123.456)}
126 execsql {SELECT CAST(123.456 AS text)}
129 execsql {SELECT typeof(CAST(123.456 AS text))}
132 execsql {SELECT CAST(123.456 AS numeric)}
135 execsql {SELECT typeof(CAST(123.456 AS numeric))}
138 execsql {SELECT CAST(123.456 AS blob)}
141 execsql {SELECT typeof(CAST(123.456 AS blob))}
144 execsql {SELECT CAST(123.456 AS integer)}
147 execsql {SELECT typeof(CAST(123.456 AS integer))}
150 execsql {SELECT '123abc'}
153 execsql {SELECT typeof('123abc')}
156 execsql {SELECT CAST('123abc' AS text)}
159 execsql {SELECT typeof(CAST('123abc' AS text))}
162 execsql {SELECT CAST('123abc' AS numeric)}
165 execsql {SELECT typeof(CAST('123abc' AS numeric))}
168 execsql {SELECT CAST('123abc' AS blob)}
171 execsql {SELECT typeof(CAST('123abc' AS blob))}
174 execsql {SELECT CAST('123abc' AS integer)}
177 execsql {SELECT typeof(CAST('123abc' AS integer))}
180 execsql {SELECT CAST('123.5abc' AS numeric)}
183 execsql {SELECT CAST('123.5abc' AS integer)}
187 execsql {SELECT CAST(null AS REAL)}
190 execsql {SELECT typeof(CAST(null AS REAL))}
193 execsql {SELECT CAST(1 AS REAL)}
196 execsql {SELECT typeof(CAST(1 AS REAL))}
199 execsql {SELECT CAST('1' AS REAL)}
202 execsql {SELECT typeof(CAST('1' AS REAL))}
205 execsql {SELECT CAST('abc' AS REAL)}
208 execsql {SELECT typeof(CAST('abc' AS REAL))}
211 execsql {SELECT CAST(x'31' AS REAL)}
214 execsql {SELECT typeof(CAST(x'31' AS REAL))}
218 # Ticket #1662. Ignore leading spaces in numbers when casting.
221 execsql {SELECT CAST(' 123' AS integer)}
224 execsql {SELECT CAST(' -123.456' AS real)}
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.
232 execsql {SELECT CAST(9223372036854774800 AS integer)}
233 } 9223372036854774800
235 execsql {SELECT CAST(9223372036854774800 AS numeric)}
236 } 9223372036854774800
238 do_realnum_test cast-3.3 {
239 execsql {SELECT CAST(9223372036854774800 AS real)}
240 } 9.22337203685477e+18
242 execsql {SELECT CAST(CAST(9223372036854774800 AS real) AS integer)}
243 } 9223372036854774784
245 execsql {SELECT CAST(-9223372036854774800 AS integer)}
246 } -9223372036854774800
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
254 execsql {SELECT CAST(CAST(-9223372036854774800 AS real) AS integer)}
255 } -9223372036854774784
257 execsql {SELECT CAST('9223372036854774800' AS integer)}
258 } 9223372036854774800
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 ifcapable long_double {
267 execsql {SELECT CAST(CAST('9223372036854774800' AS real) AS integer)}
268 } 9223372036854774784
271 execsql {SELECT CAST('-9223372036854774800' AS integer)}
272 } -9223372036854774800
274 execsql {SELECT CAST('-9223372036854774800' AS numeric)}
275 } -9223372036854774800
276 do_realnum_test cast-3.17 {
277 execsql {SELECT CAST('-9223372036854774800' AS real)}
278 } -9.22337203685477e+18
279 ifcapable long_double {
281 execsql {SELECT CAST(CAST('-9223372036854774800' AS real) AS integer)}
282 } -9223372036854774784
284 if {[db eval {PRAGMA encoding}]=="UTF-8"} {
286 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS integer)}
287 } 9223372036854774800
289 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS numeric)}
290 } 9223372036854774800
291 do_realnum_test cast-3.23 {
292 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS real)}
293 } 9.22337203685477e+18
294 ifcapable long_double {
297 SELECT CAST(CAST(x'39323233333732303336383534373734383030' AS real)
300 } 9223372036854774784
304 execsql {SELECT CAST(NULL AS numeric)}
307 # Test to see if it is possible to trick SQLite into reading past
308 # the end of a blob when converting it to a number.
309 do_test cast-3.32.1 {
310 set blob "1234567890"
311 set DB [sqlite3_connection_pointer db]
312 set ::STMT [sqlite3_prepare $DB {SELECT CAST(? AS real)} -1 TAIL]
313 sqlite3_bind_blob -static $::STMT 1 $blob 5
316 do_test cast-3.32.2 {
317 sqlite3_column_int $::STMT 0
319 do_test cast-3.32.3 {
320 sqlite3_finalize $::STMT
327 INSERT INTO t1 VALUES('abc');
328 SELECT a, CAST(a AS integer) FROM t1;
333 SELECT CAST(a AS integer), a FROM t1;
338 SELECT a, CAST(a AS integer), a FROM t1;
343 SELECT CAST(a AS integer), a, CAST(a AS real), a FROM t1;
349 # EVIDENCE-OF: R-48741-32454 If the prefix integer is greater than
350 # +9223372036854775807 then the result of the cast is exactly
351 # +9223372036854775807.
352 do_execsql_test cast-5.1 {
353 SELECT CAST('9223372036854775808' AS integer);
354 SELECT CAST(' +000009223372036854775808' AS integer);
355 SELECT CAST('12345678901234567890123' AS INTEGER);
356 } {9223372036854775807 9223372036854775807 9223372036854775807}
358 # EVIDENCE-OF: R-06028-16857 Similarly, if the prefix integer is less
359 # than -9223372036854775808 then the result of the cast is exactly
360 # -9223372036854775808.
361 do_execsql_test cast-5.2 {
362 SELECT CAST('-9223372036854775808' AS integer);
363 SELECT CAST('-9223372036854775809' AS integer);
364 SELECT CAST('-12345678901234567890123' AS INTEGER);
365 } {-9223372036854775808 -9223372036854775808 -9223372036854775808}
367 # EVIDENCE-OF: R-33990-33527 When casting to INTEGER, if the text looks
368 # like a floating point value with an exponent, the exponent will be
369 # ignored because it is no part of the integer prefix.
370 # EVIDENCE-OF: R-24225-46995 For example, "(CAST '123e+5' AS INTEGER)"
371 # results in 123, not in 12300000.
372 do_execsql_test cast-5.3 {
373 SELECT CAST('123e+5' AS INTEGER);
374 SELECT CAST('123e+5' AS NUMERIC);
375 SELECT CAST('123e+5' AS REAL);
376 } {123 12300000 12300000.0}
379 # The following does not have anything to do with the CAST operator,
380 # but it does deal with affinity transformations.
382 do_execsql_test cast-6.1 {
383 DROP TABLE IF EXISTS t1;
384 CREATE TABLE t1(a NUMERIC);
385 INSERT INTO t1 VALUES
386 ('9000000000000000001'),
387 ('9000000000000000001 '),
388 (' 9000000000000000001'),
389 (' 9000000000000000001 ');
391 } {9000000000000000001 9000000000000000001 9000000000000000001 9000000000000000001}
394 # https://www.sqlite.org/src/info/4c2d7639f076aa7c
395 do_execsql_test cast-7.1 {
396 SELECT CAST('-' AS NUMERIC);
398 do_execsql_test cast-7.2 {
399 SELECT CAST('-0' AS NUMERIC);
401 do_execsql_test cast-7.3 {
402 SELECT CAST('+' AS NUMERIC);
404 do_execsql_test cast-7.4 {
405 SELECT CAST('/' AS NUMERIC);
409 # https://www.sqlite.org/src/info/e8bedb2a184001bb
410 do_execsql_test cast-7.10 {
411 SELECT '' - 2851427734582196970;
412 } {-2851427734582196970}
413 do_execsql_test cast-7.11 {
414 SELECT 0 - 2851427734582196970;
415 } {-2851427734582196970}
416 do_execsql_test cast-7.12 {
421 # https://www.sqlite.org/src/info/dd6bffbfb6e61db9
423 # EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC
424 # yields either an INTEGER or a REAL result.
426 do_execsql_test cast-7.20 {
427 DROP TABLE IF EXISTS t0;
428 CREATE TABLE t0 (c0 TEXT);
429 INSERT INTO t0(c0) VALUES ('1.0');
430 SELECT CAST(c0 AS NUMERIC) FROM t0;
434 # https://sqlite.org/src/info/27de823723a41df45af3
436 do_execsql_test cast-7.30 {
439 do_execsql_test cast-7.31 {
442 do_execsql_test cast-7.32 {
443 SELECT CAST('.' AS numeric);
445 do_execsql_test cast-7.33 {
446 SELECT -CAST('.' AS numeric);
450 # https://www.sqlite.org/src/info/674385aeba91c774
452 do_execsql_test cast-7.40 {
453 SELECT CAST('-0.0' AS numeric);
455 do_execsql_test cast-7.41 {
456 SELECT CAST('0.0' AS numeric);
458 do_execsql_test cast-7.42 {
459 SELECT CAST('+0.0' AS numeric);
461 do_execsql_test cast-7.43 {
462 SELECT CAST('-1.0' AS numeric);
467 execsql { PRAGMA encoding='utf16' }
469 do_execsql_test cast-8.1 {
470 SELECT quote(X'310032003300')==quote(substr(X'310032003300', 1))
472 do_execsql_test cast-8.2 {
473 SELECT CAST(X'310032003300' AS TEXT)
474 ==CAST(substr(X'310032003300', 1) AS TEXT)
479 do_execsql_test cast-9.0 {
481 INSERT INTO t0(c0) VALUES (0);
482 CREATE VIEW v1(c0, c1) AS
483 SELECT CAST(0.0 AS NUMERIC), COUNT(*) OVER () FROM t0;
484 SELECT v1.c0 FROM v1, t0 WHERE v1.c0=0;
487 # Set the 2022-12-10 "reopen" of ticket [https://sqlite.org/src/tktview/57c47526c3]
489 do_execsql_test cast-9.1 {
490 CREATE TABLE dual(dummy TEXT);
491 INSERT INTO dual VALUES('X');
492 SELECT CAST(4 AS NUMERIC);
494 do_execsql_test cast-9.2 {
495 SELECT CAST(4.0 AS NUMERIC);
497 do_execsql_test cast-9.3 {
498 SELECT CAST(4.5 AS NUMERIC);
500 do_execsql_test cast-9.4 {
501 SELECT x, typeof(x) FROM (SELECT CAST(4 AS NUMERIC) AS x) JOIN dual;
503 do_execsql_test cast-9.5 {
504 SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4 AS NUMERIC) AS x);
506 do_execsql_test cast-9.10 {
507 SELECT x, typeof(x) FROM (SELECT CAST(4.0 AS NUMERIC) AS x) JOIN dual;
509 do_execsql_test cast-9.11 {
510 SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4.0 AS NUMERIC) AS x);
512 do_execsql_test cast-9.12 {
513 SELECT x, typeof(x) FROM (SELECT CAST(4.5 AS NUMERIC) AS x) JOIN dual;
515 do_execsql_test cast-9.13 {
516 SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4.5 AS NUMERIC) AS x);
519 # 2022-12-15 dbsqlfuzz c9ee6f9a0a8b8fefb02cf69de2a8b67ca39525c8
521 # Added a new SQLITE_AFF_FLEXNUM that does not try to convert int to real or
524 do_execsql_test cast-10.1 {
525 VALUES(CAST(44 AS REAL)),(55);
527 do_execsql_test cast-10.2 {
528 SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55;
530 do_execsql_test cast-10.3 {
531 SELECT * FROM (VALUES(CAST(44 AS REAL)),(55));
533 do_execsql_test cast-10.4 {
534 SELECT * FROM (SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55);
536 do_execsql_test cast-10.5 {
537 SELECT * FROM dual CROSS JOIN (VALUES(CAST(44 AS REAL)),(55));
539 do_execsql_test cast-10.6 {
540 SELECT * FROM dual CROSS JOIN (SELECT CAST(44 AS REAL) AS 'm'
541 UNION ALL SELECT 55);
544 do_execsql_test cast-10.7 {
546 CREATE VIEW v1 AS SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55;
547 SELECT name, type FROM pragma_table_info('v1');
549 do_execsql_test cast-10.8 {
550 CREATE VIEW v2 AS VALUES(CAST(44 AS REAL)),(55);
551 SELECT type FROM pragma_table_info('v2');
553 do_execsql_test cast-10.9 {
556 do_execsql_test cast-10.10 {