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 breakpoint
000238 do_realnum_test cast-3.3 {
000239 execsql {SELECT CAST(9223372036854774800 AS real)}
000240 } 9.22337203685477e+18
000241 do_test cast-3.4 {
000242 execsql {SELECT CAST(CAST(9223372036854774800 AS real) AS integer)}
000243 } 9223372036854774784
000244 do_test cast-3.5 {
000245 execsql {SELECT CAST(-9223372036854774800 AS integer)}
000246 } -9223372036854774800
000247 do_test cast-3.6 {
000248 execsql {SELECT CAST(-9223372036854774800 AS numeric)}
000249 } -9223372036854774800
000250 do_realnum_test cast-3.7 {
000251 execsql {SELECT CAST(-9223372036854774800 AS real)}
000252 } -9.22337203685477e+18
000253 do_test cast-3.8 {
000254 execsql {SELECT CAST(CAST(-9223372036854774800 AS real) AS integer)}
000255 } -9223372036854774784
000256 do_test cast-3.11 {
000257 execsql {SELECT CAST('9223372036854774800' AS integer)}
000258 } 9223372036854774800
000259 do_test cast-3.12 {
000260 execsql {SELECT CAST('9223372036854774800' AS numeric)}
000261 } 9223372036854774800
000262 do_realnum_test cast-3.13 {
000263 execsql {SELECT CAST('9223372036854774800' AS real)}
000264 } 9.22337203685477e+18
000265 do_test cast-3.14 {
000266 execsql {SELECT CAST(CAST('9223372036854774800' AS real) AS integer)}
000267 } 9223372036854774784
000268 do_test cast-3.15 {
000269 execsql {SELECT CAST('-9223372036854774800' AS integer)}
000270 } -9223372036854774800
000271 do_test cast-3.16 {
000272 execsql {SELECT CAST('-9223372036854774800' AS numeric)}
000273 } -9223372036854774800
000274 do_realnum_test cast-3.17 {
000275 execsql {SELECT CAST('-9223372036854774800' AS real)}
000276 } -9.22337203685477e+18
000277 do_test cast-3.18 {
000278 execsql {SELECT CAST(CAST('-9223372036854774800' AS real) AS integer)}
000279 } -9223372036854774784
000280 if {[db eval {PRAGMA encoding}]=="UTF-8"} {
000281 do_test cast-3.21 {
000282 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS integer)}
000283 } 9223372036854774800
000284 do_test cast-3.22 {
000285 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS numeric)}
000286 } 9223372036854774800
000287 do_realnum_test cast-3.23 {
000288 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS real)}
000289 } 9.22337203685477e+18
000290 do_test cast-3.24 {
000291 execsql {
000292 SELECT CAST(CAST(x'39323233333732303336383534373734383030' AS real)
000293 AS integer)
000294 }
000295 } 9223372036854774784
000296 }
000297 do_test cast-3.31 {
000298 execsql {SELECT CAST(NULL AS numeric)}
000299 } {{}}
000300
000301 # Test to see if it is possible to trick SQLite into reading past
000302 # the end of a blob when converting it to a number.
000303 do_test cast-3.32.1 {
000304 set blob "1234567890"
000305 set DB [sqlite3_connection_pointer db]
000306 set ::STMT [sqlite3_prepare $DB {SELECT CAST(? AS real)} -1 TAIL]
000307 sqlite3_bind_blob -static $::STMT 1 $blob 5
000308 sqlite3_step $::STMT
000309 } {SQLITE_ROW}
000310 do_test cast-3.32.2 {
000311 sqlite3_column_int $::STMT 0
000312 } {12345}
000313 do_test cast-3.32.3 {
000314 sqlite3_finalize $::STMT
000315 } {SQLITE_OK}
000316
000317
000318 do_test cast-4.1 {
000319 db eval {
000320 CREATE TABLE t1(a);
000321 INSERT INTO t1 VALUES('abc');
000322 SELECT a, CAST(a AS integer) FROM t1;
000323 }
000324 } {abc 0}
000325 do_test cast-4.2 {
000326 db eval {
000327 SELECT CAST(a AS integer), a FROM t1;
000328 }
000329 } {0 abc}
000330 do_test cast-4.3 {
000331 db eval {
000332 SELECT a, CAST(a AS integer), a FROM t1;
000333 }
000334 } {abc 0 abc}
000335 do_test cast-4.4 {
000336 db eval {
000337 SELECT CAST(a AS integer), a, CAST(a AS real), a FROM t1;
000338 }
000339 } {0 abc 0.0 abc}
000340
000341 # Added 2018-01-26
000342 #
000343 # EVIDENCE-OF: R-48741-32454 If the prefix integer is greater than
000344 # +9223372036854775807 then the result of the cast is exactly
000345 # +9223372036854775807.
000346 do_execsql_test cast-5.1 {
000347 SELECT CAST('9223372036854775808' AS integer);
000348 SELECT CAST(' +000009223372036854775808' AS integer);
000349 SELECT CAST('12345678901234567890123' AS INTEGER);
000350 } {9223372036854775807 9223372036854775807 9223372036854775807}
000351
000352 # EVIDENCE-OF: R-06028-16857 Similarly, if the prefix integer is less
000353 # than -9223372036854775808 then the result of the cast is exactly
000354 # -9223372036854775808.
000355 do_execsql_test cast-5.2 {
000356 SELECT CAST('-9223372036854775808' AS integer);
000357 SELECT CAST('-9223372036854775809' AS integer);
000358 SELECT CAST('-12345678901234567890123' AS INTEGER);
000359 } {-9223372036854775808 -9223372036854775808 -9223372036854775808}
000360
000361 # EVIDENCE-OF: R-33990-33527 When casting to INTEGER, if the text looks
000362 # like a floating point value with an exponent, the exponent will be
000363 # ignored because it is no part of the integer prefix.
000364 # EVIDENCE-OF: R-24225-46995 For example, "(CAST '123e+5' AS INTEGER)"
000365 # results in 123, not in 12300000.
000366 do_execsql_test cast-5.3 {
000367 SELECT CAST('123e+5' AS INTEGER);
000368 SELECT CAST('123e+5' AS NUMERIC);
000369 SELECT CAST('123e+5' AS REAL);
000370 } {123 12300000 12300000.0}
000371
000372
000373 # The following does not have anything to do with the CAST operator,
000374 # but it does deal with affinity transformations.
000375 #
000376 do_execsql_test cast-6.1 {
000377 DROP TABLE IF EXISTS t1;
000378 CREATE TABLE t1(a NUMERIC);
000379 INSERT INTO t1 VALUES
000380 ('9000000000000000001'),
000381 ('9000000000000000001 '),
000382 (' 9000000000000000001'),
000383 (' 9000000000000000001 ');
000384 SELECT * FROM t1;
000385 } {9000000000000000001 9000000000000000001 9000000000000000001 9000000000000000001}
000386
000387 # 2019-06-07
000388 # https://www.sqlite.org/src/info/4c2d7639f076aa7c
000389 do_execsql_test cast-7.1 {
000390 SELECT CAST('-' AS NUMERIC);
000391 } {0}
000392 do_execsql_test cast-7.2 {
000393 SELECT CAST('-0' AS NUMERIC);
000394 } {0}
000395 do_execsql_test cast-7.3 {
000396 SELECT CAST('+' AS NUMERIC);
000397 } {0}
000398 do_execsql_test cast-7.4 {
000399 SELECT CAST('/' AS NUMERIC);
000400 } {0}
000401
000402 # 2019-06-07
000403 # https://www.sqlite.org/src/info/e8bedb2a184001bb
000404 do_execsql_test cast-7.10 {
000405 SELECT '' - 2851427734582196970;
000406 } {-2851427734582196970}
000407 do_execsql_test cast-7.11 {
000408 SELECT 0 - 2851427734582196970;
000409 } {-2851427734582196970}
000410 do_execsql_test cast-7.12 {
000411 SELECT '' - 1;
000412 } {-1}
000413
000414 # 2019-06-10
000415 # https://www.sqlite.org/src/info/dd6bffbfb6e61db9
000416 #
000417 # EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC
000418 # yields either an INTEGER or a REAL result.
000419 #
000420 do_execsql_test cast-7.20 {
000421 DROP TABLE IF EXISTS t0;
000422 CREATE TABLE t0 (c0 TEXT);
000423 INSERT INTO t0(c0) VALUES ('1.0');
000424 SELECT CAST(c0 AS NUMERIC) FROM t0;
000425 } {1}
000426
000427 # 2019-06-10
000428 # https://sqlite.org/src/info/27de823723a41df45af3
000429 #
000430 do_execsql_test cast-7.30 {
000431 SELECT -'.';
000432 } 0
000433 do_execsql_test cast-7.31 {
000434 SELECT '.'+0;
000435 } 0
000436 do_execsql_test cast-7.32 {
000437 SELECT CAST('.' AS numeric);
000438 } 0
000439 do_execsql_test cast-7.33 {
000440 SELECT -CAST('.' AS numeric);
000441 } 0
000442
000443 # 2019-06-12
000444 # https://www.sqlite.org/src/info/674385aeba91c774
000445 #
000446 do_execsql_test cast-7.40 {
000447 SELECT CAST('-0.0' AS numeric);
000448 } 0
000449 do_execsql_test cast-7.41 {
000450 SELECT CAST('0.0' AS numeric);
000451 } 0
000452 do_execsql_test cast-7.42 {
000453 SELECT CAST('+0.0' AS numeric);
000454 } 0
000455 do_execsql_test cast-7.43 {
000456 SELECT CAST('-1.0' AS numeric);
000457 } -1
000458
000459 ifcapable utf16 {
000460 reset_db
000461 execsql { PRAGMA encoding='utf16' }
000462
000463 do_execsql_test cast-8.1 {
000464 SELECT quote(X'310032003300')==quote(substr(X'310032003300', 1))
000465 } 1
000466 do_execsql_test cast-8.2 {
000467 SELECT CAST(X'310032003300' AS TEXT)
000468 ==CAST(substr(X'310032003300', 1) AS TEXT)
000469 } 1
000470 }
000471
000472 reset_db
000473 do_execsql_test cast-9.0 {
000474 CREATE TABLE t0(c0);
000475 INSERT INTO t0(c0) VALUES (0);
000476 CREATE VIEW v1(c0, c1) AS
000477 SELECT CAST(0.0 AS NUMERIC), COUNT(*) OVER () FROM t0;
000478 SELECT v1.c0 FROM v1, t0 WHERE v1.c0=0;
000479 } {0.0}
000480
000481 # Set the 2022-12-10 "reopen" of ticket [https://sqlite.org/src/tktview/57c47526c3]
000482 #
000483 do_execsql_test cast-9.1 {
000484 CREATE TABLE dual(dummy TEXT);
000485 INSERT INTO dual VALUES('X');
000486 SELECT CAST(4 AS NUMERIC);
000487 } {4}
000488 do_execsql_test cast-9.2 {
000489 SELECT CAST(4.0 AS NUMERIC);
000490 } {4.0}
000491 do_execsql_test cast-9.3 {
000492 SELECT CAST(4.5 AS NUMERIC);
000493 } {4.5}
000494 do_execsql_test cast-9.4 {
000495 SELECT x, typeof(x) FROM (SELECT CAST(4 AS NUMERIC) AS x) JOIN dual;
000496 } {4 integer}
000497 do_execsql_test cast-9.5 {
000498 SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4 AS NUMERIC) AS x);
000499 } {4 integer}
000500 do_execsql_test cast-9.10 {
000501 SELECT x, typeof(x) FROM (SELECT CAST(4.0 AS NUMERIC) AS x) JOIN dual;
000502 } {4.0 real}
000503 do_execsql_test cast-9.11 {
000504 SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4.0 AS NUMERIC) AS x);
000505 } {4.0 real}
000506 do_execsql_test cast-9.12 {
000507 SELECT x, typeof(x) FROM (SELECT CAST(4.5 AS NUMERIC) AS x) JOIN dual;
000508 } {4.5 real}
000509 do_execsql_test cast-9.13 {
000510 SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4.5 AS NUMERIC) AS x);
000511 } {4.5 real}
000512
000513 # 2022-12-15 dbsqlfuzz c9ee6f9a0a8b8fefb02cf69de2a8b67ca39525c8
000514 #
000515 # Added a new SQLITE_AFF_FLEXNUM that does not try to convert int to real or
000516 # real to int.
000517 #
000518 do_execsql_test cast-10.1 {
000519 VALUES(CAST(44 AS REAL)),(55);
000520 } {44.0 55}
000521 do_execsql_test cast-10.2 {
000522 SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55;
000523 } {44.0 55}
000524 do_execsql_test cast-10.3 {
000525 SELECT * FROM (VALUES(CAST(44 AS REAL)),(55));
000526 } {44.0 55}
000527 do_execsql_test cast-10.4 {
000528 SELECT * FROM (SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55);
000529 } {44.0 55}
000530 do_execsql_test cast-10.5 {
000531 SELECT * FROM dual CROSS JOIN (VALUES(CAST(44 AS REAL)),(55));
000532 } {X 44.0 X 55}
000533 do_execsql_test cast-10.6 {
000534 SELECT * FROM dual CROSS JOIN (SELECT CAST(44 AS REAL) AS 'm'
000535 UNION ALL SELECT 55);
000536 } {X 44.0 X 55}
000537 ifcapable vtab {
000538 do_execsql_test cast-10.7 {
000539 DROP VIEW v1;
000540 CREATE VIEW v1 AS SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55;
000541 SELECT name, type FROM pragma_table_info('v1');
000542 } {m NUM}
000543 do_execsql_test cast-10.8 {
000544 CREATE VIEW v2 AS VALUES(CAST(44 AS REAL)),(55);
000545 SELECT type FROM pragma_table_info('v2');
000546 } {NUM}
000547 do_execsql_test cast-10.9 {
000548 SELECT * FROM v1;
000549 } {44.0 55}
000550 do_execsql_test cast-10.10 {
000551 SELECT * FROM v2;
000552 } {44.0 55}
000553 }
000554
000555 finish_test