000001 # 2005 June 25
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for SQLite library. The
000012 # focus of this file is testing the CAST operator.
000013 #
000014 # $Id: cast.test,v 1.10 2008/11/06 15:33:04 drh Exp $
000015
000016 set testdir [file dirname $argv0]
000017 source $testdir/tester.tcl
000018
000019 # Only run these tests if the build includes the CAST operator
000020 ifcapable !cast {
000021 finish_test
000022 return
000023 }
000024
000025 # Tests for the CAST( AS blob), CAST( AS text) and CAST( AS numeric) built-ins
000026 #
000027 ifcapable bloblit {
000028 do_test cast-1.1 {
000029 execsql {SELECT x'616263'}
000030 } abc
000031 do_test cast-1.2 {
000032 execsql {SELECT typeof(x'616263')}
000033 } blob
000034 do_test cast-1.3 {
000035 execsql {SELECT CAST(x'616263' AS text)}
000036 } abc
000037 do_test cast-1.4 {
000038 execsql {SELECT typeof(CAST(x'616263' AS text))}
000039 } text
000040 do_test cast-1.5 {
000041 execsql {SELECT CAST(x'616263' AS numeric)}
000042 } 0
000043 do_test cast-1.6 {
000044 execsql {SELECT typeof(CAST(x'616263' AS numeric))}
000045 } integer
000046 do_test cast-1.7 {
000047 execsql {SELECT CAST(x'616263' AS blob)}
000048 } abc
000049 do_test cast-1.8 {
000050 execsql {SELECT typeof(CAST(x'616263' AS blob))}
000051 } blob
000052 do_test cast-1.9 {
000053 execsql {SELECT CAST(x'616263' AS integer)}
000054 } 0
000055 do_test cast-1.10 {
000056 execsql {SELECT typeof(CAST(x'616263' AS integer))}
000057 } integer
000058 }
000059 do_test cast-1.11 {
000060 execsql {SELECT null}
000061 } {{}}
000062 do_test cast-1.12 {
000063 execsql {SELECT typeof(NULL)}
000064 } null
000065 do_test cast-1.13 {
000066 execsql {SELECT CAST(NULL AS text)}
000067 } {{}}
000068 do_test cast-1.14 {
000069 execsql {SELECT typeof(CAST(NULL AS text))}
000070 } null
000071 do_test cast-1.15 {
000072 execsql {SELECT CAST(NULL AS numeric)}
000073 } {{}}
000074 do_test cast-1.16 {
000075 execsql {SELECT typeof(CAST(NULL AS numeric))}
000076 } null
000077 do_test cast-1.17 {
000078 execsql {SELECT CAST(NULL AS blob)}
000079 } {{}}
000080 do_test cast-1.18 {
000081 execsql {SELECT typeof(CAST(NULL AS blob))}
000082 } null
000083 do_test cast-1.19 {
000084 execsql {SELECT CAST(NULL AS integer)}
000085 } {{}}
000086 do_test cast-1.20 {
000087 execsql {SELECT typeof(CAST(NULL AS integer))}
000088 } null
000089 do_test cast-1.21 {
000090 execsql {SELECT 123}
000091 } {123}
000092 do_test cast-1.22 {
000093 execsql {SELECT typeof(123)}
000094 } integer
000095 do_test cast-1.23 {
000096 execsql {SELECT CAST(123 AS text)}
000097 } {123}
000098 do_test cast-1.24 {
000099 execsql {SELECT typeof(CAST(123 AS text))}
000100 } text
000101 do_test cast-1.25 {
000102 execsql {SELECT CAST(123 AS numeric)}
000103 } 123
000104 do_test cast-1.26 {
000105 execsql {SELECT typeof(CAST(123 AS numeric))}
000106 } integer
000107 do_test cast-1.27 {
000108 execsql {SELECT CAST(123 AS blob)}
000109 } {123}
000110 do_test cast-1.28 {
000111 execsql {SELECT typeof(CAST(123 AS blob))}
000112 } blob
000113 do_test cast-1.29 {
000114 execsql {SELECT CAST(123 AS integer)}
000115 } {123}
000116 do_test cast-1.30 {
000117 execsql {SELECT typeof(CAST(123 AS integer))}
000118 } integer
000119 do_test cast-1.31 {
000120 execsql {SELECT 123.456}
000121 } {123.456}
000122 do_test cast-1.32 {
000123 execsql {SELECT typeof(123.456)}
000124 } real
000125 do_test cast-1.33 {
000126 execsql {SELECT CAST(123.456 AS text)}
000127 } {123.456}
000128 do_test cast-1.34 {
000129 execsql {SELECT typeof(CAST(123.456 AS text))}
000130 } text
000131 do_test cast-1.35 {
000132 execsql {SELECT CAST(123.456 AS numeric)}
000133 } 123.456
000134 do_test cast-1.36 {
000135 execsql {SELECT typeof(CAST(123.456 AS numeric))}
000136 } real
000137 do_test cast-1.37 {
000138 execsql {SELECT CAST(123.456 AS blob)}
000139 } {123.456}
000140 do_test cast-1.38 {
000141 execsql {SELECT typeof(CAST(123.456 AS blob))}
000142 } blob
000143 do_test cast-1.39 {
000144 execsql {SELECT CAST(123.456 AS integer)}
000145 } {123}
000146 do_test cast-1.38 {
000147 execsql {SELECT typeof(CAST(123.456 AS integer))}
000148 } integer
000149 do_test cast-1.41 {
000150 execsql {SELECT '123abc'}
000151 } {123abc}
000152 do_test cast-1.42 {
000153 execsql {SELECT typeof('123abc')}
000154 } text
000155 do_test cast-1.43 {
000156 execsql {SELECT CAST('123abc' AS text)}
000157 } {123abc}
000158 do_test cast-1.44 {
000159 execsql {SELECT typeof(CAST('123abc' AS text))}
000160 } text
000161 do_test cast-1.45 {
000162 execsql {SELECT CAST('123abc' AS numeric)}
000163 } 123
000164 do_test cast-1.46 {
000165 execsql {SELECT typeof(CAST('123abc' AS numeric))}
000166 } integer
000167 do_test cast-1.47 {
000168 execsql {SELECT CAST('123abc' AS blob)}
000169 } {123abc}
000170 do_test cast-1.48 {
000171 execsql {SELECT typeof(CAST('123abc' AS blob))}
000172 } blob
000173 do_test cast-1.49 {
000174 execsql {SELECT CAST('123abc' AS integer)}
000175 } 123
000176 do_test cast-1.50 {
000177 execsql {SELECT typeof(CAST('123abc' AS integer))}
000178 } integer
000179 do_test cast-1.51 {
000180 execsql {SELECT CAST('123.5abc' AS numeric)}
000181 } 123.5
000182 do_test cast-1.53 {
000183 execsql {SELECT CAST('123.5abc' AS integer)}
000184 } 123
000185
000186 do_test cast-1.60 {
000187 execsql {SELECT CAST(null AS REAL)}
000188 } {{}}
000189 do_test cast-1.61 {
000190 execsql {SELECT typeof(CAST(null AS REAL))}
000191 } {null}
000192 do_test cast-1.62 {
000193 execsql {SELECT CAST(1 AS REAL)}
000194 } {1.0}
000195 do_test cast-1.63 {
000196 execsql {SELECT typeof(CAST(1 AS REAL))}
000197 } {real}
000198 do_test cast-1.64 {
000199 execsql {SELECT CAST('1' AS REAL)}
000200 } {1.0}
000201 do_test cast-1.65 {
000202 execsql {SELECT typeof(CAST('1' AS REAL))}
000203 } {real}
000204 do_test cast-1.66 {
000205 execsql {SELECT CAST('abc' AS REAL)}
000206 } {0.0}
000207 do_test cast-1.67 {
000208 execsql {SELECT typeof(CAST('abc' AS REAL))}
000209 } {real}
000210 do_test cast-1.68 {
000211 execsql {SELECT CAST(x'31' AS REAL)}
000212 } {1.0}
000213 do_test cast-1.69 {
000214 execsql {SELECT typeof(CAST(x'31' AS REAL))}
000215 } {real}
000216
000217
000218 # Ticket #1662. Ignore leading spaces in numbers when casting.
000219 #
000220 do_test cast-2.1 {
000221 execsql {SELECT CAST(' 123' AS integer)}
000222 } 123
000223 do_test cast-2.2 {
000224 execsql {SELECT CAST(' -123.456' AS real)}
000225 } -123.456
000226
000227 # ticket #2364. Use full percision integers if possible when casting
000228 # to numeric. Do not fallback to real (and the corresponding 48-bit
000229 # mantissa) unless absolutely necessary.
000230 #
000231 do_test cast-3.1 {
000232 execsql {SELECT CAST(9223372036854774800 AS integer)}
000233 } 9223372036854774800
000234 do_test cast-3.2 {
000235 execsql {SELECT CAST(9223372036854774800 AS numeric)}
000236 } 9223372036854774800
000237 do_realnum_test cast-3.3 {
000238 execsql {SELECT CAST(9223372036854774800 AS real)}
000239 } 9.22337203685477e+18
000240 do_test cast-3.4 {
000241 execsql {SELECT CAST(CAST(9223372036854774800 AS real) AS integer)}
000242 } 9223372036854774784
000243 do_test cast-3.5 {
000244 execsql {SELECT CAST(-9223372036854774800 AS integer)}
000245 } -9223372036854774800
000246 do_test cast-3.6 {
000247 execsql {SELECT CAST(-9223372036854774800 AS numeric)}
000248 } -9223372036854774800
000249 do_realnum_test cast-3.7 {
000250 execsql {SELECT CAST(-9223372036854774800 AS real)}
000251 } -9.22337203685477e+18
000252 do_test cast-3.8 {
000253 execsql {SELECT CAST(CAST(-9223372036854774800 AS real) AS integer)}
000254 } -9223372036854774784
000255 do_test cast-3.11 {
000256 execsql {SELECT CAST('9223372036854774800' AS integer)}
000257 } 9223372036854774800
000258 do_test cast-3.12 {
000259 execsql {SELECT CAST('9223372036854774800' AS numeric)}
000260 } 9223372036854774800
000261 do_realnum_test cast-3.13 {
000262 execsql {SELECT CAST('9223372036854774800' AS real)}
000263 } 9.22337203685477e+18
000264 ifcapable long_double {
000265 do_test cast-3.14 {
000266 execsql {SELECT CAST(CAST('9223372036854774800' AS real) AS integer)}
000267 } 9223372036854774784
000268 }
000269 do_test cast-3.15 {
000270 execsql {SELECT CAST('-9223372036854774800' AS integer)}
000271 } -9223372036854774800
000272 do_test cast-3.16 {
000273 execsql {SELECT CAST('-9223372036854774800' AS numeric)}
000274 } -9223372036854774800
000275 do_realnum_test cast-3.17 {
000276 execsql {SELECT CAST('-9223372036854774800' AS real)}
000277 } -9.22337203685477e+18
000278 ifcapable long_double {
000279 do_test cast-3.18 {
000280 execsql {SELECT CAST(CAST('-9223372036854774800' AS real) AS integer)}
000281 } -9223372036854774784
000282 }
000283 if {[db eval {PRAGMA encoding}]=="UTF-8"} {
000284 do_test cast-3.21 {
000285 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS integer)}
000286 } 9223372036854774800
000287 do_test cast-3.22 {
000288 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS numeric)}
000289 } 9223372036854774800
000290 do_realnum_test cast-3.23 {
000291 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS real)}
000292 } 9.22337203685477e+18
000293 ifcapable long_double {
000294 do_test cast-3.24 {
000295 execsql {
000296 SELECT CAST(CAST(x'39323233333732303336383534373734383030' AS real)
000297 AS integer)
000298 }
000299 } 9223372036854774784
000300 }
000301 }
000302 do_test cast-3.31 {
000303 execsql {SELECT CAST(NULL AS numeric)}
000304 } {{}}
000305
000306 # Test to see if it is possible to trick SQLite into reading past
000307 # the end of a blob when converting it to a number.
000308 do_test cast-3.32.1 {
000309 set blob "1234567890"
000310 set DB [sqlite3_connection_pointer db]
000311 set ::STMT [sqlite3_prepare $DB {SELECT CAST(? AS real)} -1 TAIL]
000312 sqlite3_bind_blob -static $::STMT 1 $blob 5
000313 sqlite3_step $::STMT
000314 } {SQLITE_ROW}
000315 do_test cast-3.32.2 {
000316 sqlite3_column_int $::STMT 0
000317 } {12345}
000318 do_test cast-3.32.3 {
000319 sqlite3_finalize $::STMT
000320 } {SQLITE_OK}
000321
000322
000323 do_test cast-4.1 {
000324 db eval {
000325 CREATE TABLE t1(a);
000326 INSERT INTO t1 VALUES('abc');
000327 SELECT a, CAST(a AS integer) FROM t1;
000328 }
000329 } {abc 0}
000330 do_test cast-4.2 {
000331 db eval {
000332 SELECT CAST(a AS integer), a FROM t1;
000333 }
000334 } {0 abc}
000335 do_test cast-4.3 {
000336 db eval {
000337 SELECT a, CAST(a AS integer), a FROM t1;
000338 }
000339 } {abc 0 abc}
000340 do_test cast-4.4 {
000341 db eval {
000342 SELECT CAST(a AS integer), a, CAST(a AS real), a FROM t1;
000343 }
000344 } {0 abc 0.0 abc}
000345
000346 # Added 2018-01-26
000347 #
000348 # EVIDENCE-OF: R-48741-32454 If the prefix integer is greater than
000349 # +9223372036854775807 then the result of the cast is exactly
000350 # +9223372036854775807.
000351 do_execsql_test cast-5.1 {
000352 SELECT CAST('9223372036854775808' AS integer);
000353 SELECT CAST(' +000009223372036854775808' AS integer);
000354 SELECT CAST('12345678901234567890123' AS INTEGER);
000355 } {9223372036854775807 9223372036854775807 9223372036854775807}
000356
000357 # EVIDENCE-OF: R-06028-16857 Similarly, if the prefix integer is less
000358 # than -9223372036854775808 then the result of the cast is exactly
000359 # -9223372036854775808.
000360 do_execsql_test cast-5.2 {
000361 SELECT CAST('-9223372036854775808' AS integer);
000362 SELECT CAST('-9223372036854775809' AS integer);
000363 SELECT CAST('-12345678901234567890123' AS INTEGER);
000364 } {-9223372036854775808 -9223372036854775808 -9223372036854775808}
000365
000366 # EVIDENCE-OF: R-33990-33527 When casting to INTEGER, if the text looks
000367 # like a floating point value with an exponent, the exponent will be
000368 # ignored because it is no part of the integer prefix.
000369 # EVIDENCE-OF: R-24225-46995 For example, "(CAST '123e+5' AS INTEGER)"
000370 # results in 123, not in 12300000.
000371 do_execsql_test cast-5.3 {
000372 SELECT CAST('123e+5' AS INTEGER);
000373 SELECT CAST('123e+5' AS NUMERIC);
000374 SELECT CAST('123e+5' AS REAL);
000375 } {123 12300000 12300000.0}
000376
000377
000378 # The following does not have anything to do with the CAST operator,
000379 # but it does deal with affinity transformations.
000380 #
000381 do_execsql_test cast-6.1 {
000382 DROP TABLE IF EXISTS t1;
000383 CREATE TABLE t1(a NUMERIC);
000384 INSERT INTO t1 VALUES
000385 ('9000000000000000001'),
000386 ('9000000000000000001 '),
000387 (' 9000000000000000001'),
000388 (' 9000000000000000001 ');
000389 SELECT * FROM t1;
000390 } {9000000000000000001 9000000000000000001 9000000000000000001 9000000000000000001}
000391
000392 # 2019-06-07
000393 # https://www.sqlite.org/src/info/4c2d7639f076aa7c
000394 do_execsql_test cast-7.1 {
000395 SELECT CAST('-' AS NUMERIC);
000396 } {0}
000397 do_execsql_test cast-7.2 {
000398 SELECT CAST('-0' AS NUMERIC);
000399 } {0}
000400 do_execsql_test cast-7.3 {
000401 SELECT CAST('+' AS NUMERIC);
000402 } {0}
000403 do_execsql_test cast-7.4 {
000404 SELECT CAST('/' AS NUMERIC);
000405 } {0}
000406
000407 # 2019-06-07
000408 # https://www.sqlite.org/src/info/e8bedb2a184001bb
000409 do_execsql_test cast-7.10 {
000410 SELECT '' - 2851427734582196970;
000411 } {-2851427734582196970}
000412 do_execsql_test cast-7.11 {
000413 SELECT 0 - 2851427734582196970;
000414 } {-2851427734582196970}
000415 do_execsql_test cast-7.12 {
000416 SELECT '' - 1;
000417 } {-1}
000418
000419 # 2019-06-10
000420 # https://www.sqlite.org/src/info/dd6bffbfb6e61db9
000421 #
000422 # EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC
000423 # yields either an INTEGER or a REAL result.
000424 #
000425 do_execsql_test cast-7.20 {
000426 DROP TABLE IF EXISTS t0;
000427 CREATE TABLE t0 (c0 TEXT);
000428 INSERT INTO t0(c0) VALUES ('1.0');
000429 SELECT CAST(c0 AS NUMERIC) FROM t0;
000430 } {1}
000431
000432 # 2019-06-10
000433 # https://sqlite.org/src/info/27de823723a41df45af3
000434 #
000435 do_execsql_test cast-7.30 {
000436 SELECT -'.';
000437 } 0
000438 do_execsql_test cast-7.31 {
000439 SELECT '.'+0;
000440 } 0
000441 do_execsql_test cast-7.32 {
000442 SELECT CAST('.' AS numeric);
000443 } 0
000444 do_execsql_test cast-7.33 {
000445 SELECT -CAST('.' AS numeric);
000446 } 0
000447
000448 # 2019-06-12
000449 # https://www.sqlite.org/src/info/674385aeba91c774
000450 #
000451 do_execsql_test cast-7.40 {
000452 SELECT CAST('-0.0' AS numeric);
000453 } 0
000454 do_execsql_test cast-7.41 {
000455 SELECT CAST('0.0' AS numeric);
000456 } 0
000457 do_execsql_test cast-7.42 {
000458 SELECT CAST('+0.0' AS numeric);
000459 } 0
000460 do_execsql_test cast-7.43 {
000461 SELECT CAST('-1.0' AS numeric);
000462 } -1
000463
000464 ifcapable utf16 {
000465 reset_db
000466 execsql { PRAGMA encoding='utf16' }
000467
000468 do_execsql_test cast-8.1 {
000469 SELECT quote(X'310032003300')==quote(substr(X'310032003300', 1))
000470 } 1
000471 do_execsql_test cast-8.2 {
000472 SELECT CAST(X'310032003300' AS TEXT)
000473 ==CAST(substr(X'310032003300', 1) AS TEXT)
000474 } 1
000475 }
000476
000477 reset_db
000478 do_execsql_test cast-9.0 {
000479 CREATE TABLE t0(c0);
000480 INSERT INTO t0(c0) VALUES (0);
000481 CREATE VIEW v1(c0, c1) AS
000482 SELECT CAST(0.0 AS NUMERIC), COUNT(*) OVER () FROM t0;
000483 SELECT v1.c0 FROM v1, t0 WHERE v1.c0=0;
000484 } {0.0}
000485
000486 # Set the 2022-12-10 "reopen" of ticket [https://sqlite.org/src/tktview/57c47526c3]
000487 #
000488 do_execsql_test cast-9.1 {
000489 CREATE TABLE dual(dummy TEXT);
000490 INSERT INTO dual VALUES('X');
000491 SELECT CAST(4 AS NUMERIC);
000492 } {4}
000493 do_execsql_test cast-9.2 {
000494 SELECT CAST(4.0 AS NUMERIC);
000495 } {4.0}
000496 do_execsql_test cast-9.3 {
000497 SELECT CAST(4.5 AS NUMERIC);
000498 } {4.5}
000499 do_execsql_test cast-9.4 {
000500 SELECT x, typeof(x) FROM (SELECT CAST(4 AS NUMERIC) AS x) JOIN dual;
000501 } {4 integer}
000502 do_execsql_test cast-9.5 {
000503 SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4 AS NUMERIC) AS x);
000504 } {4 integer}
000505 do_execsql_test cast-9.10 {
000506 SELECT x, typeof(x) FROM (SELECT CAST(4.0 AS NUMERIC) AS x) JOIN dual;
000507 } {4.0 real}
000508 do_execsql_test cast-9.11 {
000509 SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4.0 AS NUMERIC) AS x);
000510 } {4.0 real}
000511 do_execsql_test cast-9.12 {
000512 SELECT x, typeof(x) FROM (SELECT CAST(4.5 AS NUMERIC) AS x) JOIN dual;
000513 } {4.5 real}
000514 do_execsql_test cast-9.13 {
000515 SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4.5 AS NUMERIC) AS x);
000516 } {4.5 real}
000517
000518 # 2022-12-15 dbsqlfuzz c9ee6f9a0a8b8fefb02cf69de2a8b67ca39525c8
000519 #
000520 # Added a new SQLITE_AFF_FLEXNUM that does not try to convert int to real or
000521 # real to int.
000522 #
000523 do_execsql_test cast-10.1 {
000524 VALUES(CAST(44 AS REAL)),(55);
000525 } {44.0 55}
000526 do_execsql_test cast-10.2 {
000527 SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55;
000528 } {44.0 55}
000529 do_execsql_test cast-10.3 {
000530 SELECT * FROM (VALUES(CAST(44 AS REAL)),(55));
000531 } {44.0 55}
000532 do_execsql_test cast-10.4 {
000533 SELECT * FROM (SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55);
000534 } {44.0 55}
000535 do_execsql_test cast-10.5 {
000536 SELECT * FROM dual CROSS JOIN (VALUES(CAST(44 AS REAL)),(55));
000537 } {X 44.0 X 55}
000538 do_execsql_test cast-10.6 {
000539 SELECT * FROM dual CROSS JOIN (SELECT CAST(44 AS REAL) AS 'm'
000540 UNION ALL SELECT 55);
000541 } {X 44.0 X 55}
000542 ifcapable vtab {
000543 do_execsql_test cast-10.7 {
000544 DROP VIEW v1;
000545 CREATE VIEW v1 AS SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55;
000546 SELECT name, type FROM pragma_table_info('v1');
000547 } {m NUM}
000548 do_execsql_test cast-10.8 {
000549 CREATE VIEW v2 AS VALUES(CAST(44 AS REAL)),(55);
000550 SELECT type FROM pragma_table_info('v2');
000551 } {NUM}
000552 do_execsql_test cast-10.9 {
000553 SELECT * FROM v1;
000554 } {44.0 55}
000555 do_execsql_test cast-10.10 {
000556 SELECT * FROM v2;
000557 } {44.0 55}
000558 }
000559
000560 finish_test