AryaWu/sqlite
0
1# 2005 June 252#3# The author disclaims copyright to this source code. In place of4# 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. The12# 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 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# Only run these tests if the build includes the CAST operator20ifcapable !cast {21 finish_test22 return23}24 25# Tests for the CAST( AS blob), CAST( AS text) and CAST( AS numeric) built-ins26#27ifcapable bloblit {28 do_test cast-1.1 {29 execsql {SELECT x'616263'}30 } abc31 do_test cast-1.2 {32 execsql {SELECT typeof(x'616263')}33 } blob34 do_test cast-1.3 {35 execsql {SELECT CAST(x'616263' AS text)}36 } abc37 do_test cast-1.4 {38 execsql {SELECT typeof(CAST(x'616263' AS text))}39 } text40 do_test cast-1.5 {41 execsql {SELECT CAST(x'616263' AS numeric)}42 } 043 do_test cast-1.6 {44 execsql {SELECT typeof(CAST(x'616263' AS numeric))}45 } integer46 do_test cast-1.7 {47 execsql {SELECT CAST(x'616263' AS blob)}48 } abc49 do_test cast-1.8 {50 execsql {SELECT typeof(CAST(x'616263' AS blob))}51 } blob52 do_test cast-1.9 {53 execsql {SELECT CAST(x'616263' AS integer)}54 } 055 do_test cast-1.10 {56 execsql {SELECT typeof(CAST(x'616263' AS integer))}57 } integer58}59do_test cast-1.11 {60 execsql {SELECT null}61} {{}}62do_test cast-1.12 {63 execsql {SELECT typeof(NULL)}64} null65do_test cast-1.13 {66 execsql {SELECT CAST(NULL AS text)}67} {{}}68do_test cast-1.14 {69 execsql {SELECT typeof(CAST(NULL AS text))}70} null71do_test cast-1.15 {72 execsql {SELECT CAST(NULL AS numeric)}73} {{}}74do_test cast-1.16 {75 execsql {SELECT typeof(CAST(NULL AS numeric))}76} null77do_test cast-1.17 {78 execsql {SELECT CAST(NULL AS blob)}79} {{}}80do_test cast-1.18 {81 execsql {SELECT typeof(CAST(NULL AS blob))}82} null83do_test cast-1.19 {84 execsql {SELECT CAST(NULL AS integer)}85} {{}}86do_test cast-1.20 {87 execsql {SELECT typeof(CAST(NULL AS integer))}88} null89do_test cast-1.21 {90 execsql {SELECT 123}91} {123}92do_test cast-1.22 {93 execsql {SELECT typeof(123)}94} integer95do_test cast-1.23 {96 execsql {SELECT CAST(123 AS text)}97} {123}98do_test cast-1.24 {99 execsql {SELECT typeof(CAST(123 AS text))}100} text101do_test cast-1.25 {102 execsql {SELECT CAST(123 AS numeric)}103} 123104do_test cast-1.26 {105 execsql {SELECT typeof(CAST(123 AS numeric))}106} integer107do_test cast-1.27 {108 execsql {SELECT CAST(123 AS blob)}109} {123}110do_test cast-1.28 {111 execsql {SELECT typeof(CAST(123 AS blob))}112} blob113do_test cast-1.29 {114 execsql {SELECT CAST(123 AS integer)}115} {123}116do_test cast-1.30 {117 execsql {SELECT typeof(CAST(123 AS integer))}118} integer119do_test cast-1.31 {120 execsql {SELECT 123.456}121} {123.456}122do_test cast-1.32 {123 execsql {SELECT typeof(123.456)}124} real125do_test cast-1.33 {126 execsql {SELECT CAST(123.456 AS text)}127} {123.456}128do_test cast-1.34 {129 execsql {SELECT typeof(CAST(123.456 AS text))}130} text131do_test cast-1.35 {132 execsql {SELECT CAST(123.456 AS numeric)}133} 123.456134do_test cast-1.36 {135 execsql {SELECT typeof(CAST(123.456 AS numeric))}136} real137do_test cast-1.37 {138 execsql {SELECT CAST(123.456 AS blob)}139} {123.456}140do_test cast-1.38 {141 execsql {SELECT typeof(CAST(123.456 AS blob))}142} blob143do_test cast-1.39 {144 execsql {SELECT CAST(123.456 AS integer)}145} {123}146do_test cast-1.38 {147 execsql {SELECT typeof(CAST(123.456 AS integer))}148} integer149do_test cast-1.41 {150 execsql {SELECT '123abc'}151} {123abc}152do_test cast-1.42 {153 execsql {SELECT typeof('123abc')}154} text155do_test cast-1.43 {156 execsql {SELECT CAST('123abc' AS text)}157} {123abc}158do_test cast-1.44 {159 execsql {SELECT typeof(CAST('123abc' AS text))}160} text161do_test cast-1.45 {162 execsql {SELECT CAST('123abc' AS numeric)}163} 123164do_test cast-1.46 {165 execsql {SELECT typeof(CAST('123abc' AS numeric))}166} integer167do_test cast-1.47 {168 execsql {SELECT CAST('123abc' AS blob)}169} {123abc}170do_test cast-1.48 {171 execsql {SELECT typeof(CAST('123abc' AS blob))}172} blob173do_test cast-1.49 {174 execsql {SELECT CAST('123abc' AS integer)}175} 123176do_test cast-1.50 {177 execsql {SELECT typeof(CAST('123abc' AS integer))}178} integer179do_test cast-1.51 {180 execsql {SELECT CAST('123.5abc' AS numeric)}181} 123.5182do_test cast-1.53 {183 execsql {SELECT CAST('123.5abc' AS integer)}184} 123185 186do_test cast-1.60 {187 execsql {SELECT CAST(null AS REAL)}188} {{}}189do_test cast-1.61 {190 execsql {SELECT typeof(CAST(null AS REAL))}191} {null}192do_test cast-1.62 {193 execsql {SELECT CAST(1 AS REAL)}194} {1.0}195do_test cast-1.63 {196 execsql {SELECT typeof(CAST(1 AS REAL))}197} {real}198do_test cast-1.64 {199 execsql {SELECT CAST('1' AS REAL)}200} {1.0}201do_test cast-1.65 {202 execsql {SELECT typeof(CAST('1' AS REAL))}203} {real}204do_test cast-1.66 {205 execsql {SELECT CAST('abc' AS REAL)}206} {0.0}207do_test cast-1.67 {208 execsql {SELECT typeof(CAST('abc' AS REAL))}209} {real}210do_test cast-1.68 {211 execsql {SELECT CAST(x'31' AS REAL)}212} {1.0}213do_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#220do_test cast-2.1 {221 execsql {SELECT CAST(' 123' AS integer)}222} 123223do_test cast-2.2 {224 execsql {SELECT CAST(' -123.456' AS real)}225} -123.456226 227# ticket #2364. Use full percision integers if possible when casting228# to numeric. Do not fallback to real (and the corresponding 48-bit229# mantissa) unless absolutely necessary.230#231do_test cast-3.1 {232 execsql {SELECT CAST(9223372036854774800 AS integer)}233} 9223372036854774800234do_test cast-3.2 {235 execsql {SELECT CAST(9223372036854774800 AS numeric)}236} 9223372036854774800237breakpoint238do_realnum_test cast-3.3 {239 execsql {SELECT CAST(9223372036854774800 AS real)}240} 9.22337203685477e+18241do_test cast-3.4 {242 execsql {SELECT CAST(CAST(9223372036854774800 AS real) AS integer)}243} 9223372036854774784244do_test cast-3.5 {245 execsql {SELECT CAST(-9223372036854774800 AS integer)}246} -9223372036854774800247do_test cast-3.6 {248 execsql {SELECT CAST(-9223372036854774800 AS numeric)}249} -9223372036854774800250do_realnum_test cast-3.7 {251 execsql {SELECT CAST(-9223372036854774800 AS real)}252} -9.22337203685477e+18253do_test cast-3.8 {254 execsql {SELECT CAST(CAST(-9223372036854774800 AS real) AS integer)}255} -9223372036854774784256do_test cast-3.11 {257 execsql {SELECT CAST('9223372036854774800' AS integer)}258} 9223372036854774800259do_test cast-3.12 {260 execsql {SELECT CAST('9223372036854774800' AS numeric)}261} 9223372036854774800262do_realnum_test cast-3.13 {263 execsql {SELECT CAST('9223372036854774800' AS real)}264} 9.22337203685477e+18265do_test cast-3.14 {266 execsql {SELECT CAST(CAST('9223372036854774800' AS real) AS integer)}267} 9223372036854774784268do_test cast-3.15 {269 execsql {SELECT CAST('-9223372036854774800' AS integer)}270} -9223372036854774800271do_test cast-3.16 {272 execsql {SELECT CAST('-9223372036854774800' AS numeric)}273} -9223372036854774800274do_realnum_test cast-3.17 {275 execsql {SELECT CAST('-9223372036854774800' AS real)}276} -9.22337203685477e+18277do_test cast-3.18 {278 execsql {SELECT CAST(CAST('-9223372036854774800' AS real) AS integer)}279} -9223372036854774784280if {[db eval {PRAGMA encoding}]=="UTF-8"} {281 do_test cast-3.21 {282 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS integer)}283 } 9223372036854774800284 do_test cast-3.22 {285 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS numeric)}286 } 9223372036854774800287 do_realnum_test cast-3.23 {288 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS real)}289 } 9.22337203685477e+18290 do_test cast-3.24 {291 execsql {292 SELECT CAST(CAST(x'39323233333732303336383534373734383030' AS real)293 AS integer)294 }295 } 9223372036854774784296}297do_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.303do_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 5308 sqlite3_step $::STMT309} {SQLITE_ROW}310do_test cast-3.32.2 {311 sqlite3_column_int $::STMT 0312} {12345}313do_test cast-3.32.3 {314 sqlite3_finalize $::STMT315} {SQLITE_OK}316 317 318do_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}325do_test cast-4.2 {326 db eval {327 SELECT CAST(a AS integer), a FROM t1;328 }329} {0 abc}330do_test cast-4.3 {331 db eval {332 SELECT a, CAST(a AS integer), a FROM t1;333 }334} {abc 0 abc}335do_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-26342#343# EVIDENCE-OF: R-48741-32454 If the prefix integer is greater than344# +9223372036854775807 then the result of the cast is exactly345# +9223372036854775807.346do_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 less353# than -9223372036854775808 then the result of the cast is exactly354# -9223372036854775808.355do_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 looks362# like a floating point value with an exponent, the exponent will be363# 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.366do_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#376do_execsql_test cast-6.1 {377 DROP TABLE IF EXISTS t1;378 CREATE TABLE t1(a NUMERIC);379 INSERT INTO t1 VALUES380 ('9000000000000000001'),381 ('9000000000000000001 '),382 (' 9000000000000000001'),383 (' 9000000000000000001 ');384 SELECT * FROM t1;385} {9000000000000000001 9000000000000000001 9000000000000000001 9000000000000000001}386 387# 2019-06-07388# https://sqlite.org/src/info/4c2d7639f076aa7c389do_execsql_test cast-7.1 {390 SELECT CAST('-' AS NUMERIC);391} {0}392do_execsql_test cast-7.2 {393 SELECT CAST('-0' AS NUMERIC);394} {0}395do_execsql_test cast-7.3 {396 SELECT CAST('+' AS NUMERIC);397} {0}398do_execsql_test cast-7.4 {399 SELECT CAST('/' AS NUMERIC);400} {0}401 402# 2019-06-07403# https://sqlite.org/src/info/e8bedb2a184001bb404do_execsql_test cast-7.10 {405 SELECT '' - 2851427734582196970;406} {-2851427734582196970}407do_execsql_test cast-7.11 {408 SELECT 0 - 2851427734582196970;409} {-2851427734582196970}410do_execsql_test cast-7.12 {411 SELECT '' - 1;412} {-1}413 414# 2019-06-10415# https://sqlite.org/src/info/dd6bffbfb6e61db9416#417# EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC418# yields either an INTEGER or a REAL result.419#420do_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-10428# https://sqlite.org/src/info/27de823723a41df45af3429#430do_execsql_test cast-7.30 {431 SELECT -'.';432} 0433do_execsql_test cast-7.31 {434 SELECT '.'+0;435} 0436do_execsql_test cast-7.32 {437 SELECT CAST('.' AS numeric);438} 0439do_execsql_test cast-7.33 {440 SELECT -CAST('.' AS numeric);441} 0442 443# 2019-06-12444# https://sqlite.org/src/info/674385aeba91c774445#446do_execsql_test cast-7.40 {447 SELECT CAST('-0.0' AS numeric);448} 0449do_execsql_test cast-7.41 {450 SELECT CAST('0.0' AS numeric);451} 0452do_execsql_test cast-7.42 {453 SELECT CAST('+0.0' AS numeric);454} 0455do_execsql_test cast-7.43 {456 SELECT CAST('-1.0' AS numeric);457} -1458 459ifcapable utf16 {460 reset_db461 execsql { PRAGMA encoding='utf16' }462 463 do_execsql_test cast-8.1 {464 SELECT quote(X'310032003300')==quote(substr(X'310032003300', 1))465 } 1466 do_execsql_test cast-8.2 {467 SELECT CAST(X'310032003300' AS TEXT)468 ==CAST(substr(X'310032003300', 1) AS TEXT)469 } 1470}471 472reset_db473do_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#483do_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}488do_execsql_test cast-9.2 {489 SELECT CAST(4.0 AS NUMERIC);490} {4.0}491do_execsql_test cast-9.3 {492 SELECT CAST(4.5 AS NUMERIC);493} {4.5}494do_execsql_test cast-9.4 {495 SELECT x, typeof(x) FROM (SELECT CAST(4 AS NUMERIC) AS x) JOIN dual;496} {4 integer}497do_execsql_test cast-9.5 {498 SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4 AS NUMERIC) AS x);499} {4 integer}500do_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}503do_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}506do_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}509do_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 c9ee6f9a0a8b8fefb02cf69de2a8b67ca39525c8514#515# Added a new SQLITE_AFF_FLEXNUM that does not try to convert int to real or516# real to int.517#518do_execsql_test cast-10.1 {519 VALUES(CAST(44 AS REAL)),(55);520} {44.0 55}521do_execsql_test cast-10.2 {522 SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55;523} {44.0 55}524do_execsql_test cast-10.3 {525 SELECT * FROM (VALUES(CAST(44 AS REAL)),(55));526} {44.0 55}527do_execsql_test cast-10.4 {528 SELECT * FROM (SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55);529} {44.0 55}530do_execsql_test cast-10.5 {531 SELECT * FROM dual CROSS JOIN (VALUES(CAST(44 AS REAL)),(55));532} {X 44.0 X 55}533do_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}537ifcapable 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 555finish_test556