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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19# Only run these tests if the build includes the CAST operator 20ifcapable !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# 27ifcapable 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} 59do_test cast-1.11 { 60 execsql {SELECT null} 61} {{}} 62do_test cast-1.12 { 63 execsql {SELECT typeof(NULL)} 64} null 65do_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} null 71do_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} null 77do_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} null 83do_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} null 89do_test cast-1.21 { 90 execsql {SELECT 123} 91} {123} 92do_test cast-1.22 { 93 execsql {SELECT typeof(123)} 94} integer 95do_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} text 101do_test cast-1.25 { 102 execsql {SELECT CAST(123 AS numeric)} 103} 123 104do_test cast-1.26 { 105 execsql {SELECT typeof(CAST(123 AS numeric))} 106} integer 107do_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} blob 113do_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} integer 119do_test cast-1.31 { 120 execsql {SELECT 123.456} 121} {123.456} 122do_test cast-1.32 { 123 execsql {SELECT typeof(123.456)} 124} real 125do_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} text 131do_test cast-1.35 { 132 execsql {SELECT CAST(123.456 AS numeric)} 133} 123.456 134do_test cast-1.36 { 135 execsql {SELECT typeof(CAST(123.456 AS numeric))} 136} real 137do_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} blob 143do_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} integer 149do_test cast-1.41 { 150 execsql {SELECT '123abc'} 151} {123abc} 152do_test cast-1.42 { 153 execsql {SELECT typeof('123abc')} 154} text 155do_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} text 161do_test cast-1.45 { 162 execsql {SELECT CAST('123abc' AS numeric)} 163} 123 164do_test cast-1.46 { 165 execsql {SELECT typeof(CAST('123abc' AS numeric))} 166} integer 167do_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} blob 173do_test cast-1.49 { 174 execsql {SELECT CAST('123abc' AS integer)} 175} 123 176do_test cast-1.50 { 177 execsql {SELECT typeof(CAST('123abc' AS integer))} 178} integer 179do_test cast-1.51 { 180 execsql {SELECT CAST('123.5abc' AS numeric)} 181} 123.5 182do_test cast-1.53 { 183 execsql {SELECT CAST('123.5abc' AS integer)} 184} 123 185 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} 123 223do_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# 231do_test cast-3.1 { 232 execsql {SELECT CAST(9223372036854774800 AS integer)} 233} 9223372036854774800 234do_test cast-3.2 { 235 execsql {SELECT CAST(9223372036854774800 AS numeric)} 236} 9223372036854774800 237do_realnum_test cast-3.3 { 238 execsql {SELECT CAST(9223372036854774800 AS real)} 239} 9.22337203685477e+18 240do_test cast-3.4 { 241 execsql {SELECT CAST(CAST(9223372036854774800 AS real) AS integer)} 242} 9223372036854774784 243do_test cast-3.5 { 244 execsql {SELECT CAST(-9223372036854774800 AS integer)} 245} -9223372036854774800 246do_test cast-3.6 { 247 execsql {SELECT CAST(-9223372036854774800 AS numeric)} 248} -9223372036854774800 249do_realnum_test cast-3.7 { 250 execsql {SELECT CAST(-9223372036854774800 AS real)} 251} -9.22337203685477e+18 252do_test cast-3.8 { 253 execsql {SELECT CAST(CAST(-9223372036854774800 AS real) AS integer)} 254} -9223372036854774784 255do_test cast-3.11 { 256 execsql {SELECT CAST('9223372036854774800' AS integer)} 257} 9223372036854774800 258do_test cast-3.12 { 259 execsql {SELECT CAST('9223372036854774800' AS numeric)} 260} 9223372036854774800 261do_realnum_test cast-3.13 { 262 execsql {SELECT CAST('9223372036854774800' AS real)} 263} 9.22337203685477e+18 264ifcapable long_double { 265 do_test cast-3.14 { 266 execsql {SELECT CAST(CAST('9223372036854774800' AS real) AS integer)} 267 } 9223372036854774784 268} 269do_test cast-3.15 { 270 execsql {SELECT CAST('-9223372036854774800' AS integer)} 271} -9223372036854774800 272do_test cast-3.16 { 273 execsql {SELECT CAST('-9223372036854774800' AS numeric)} 274} -9223372036854774800 275do_realnum_test cast-3.17 { 276 execsql {SELECT CAST('-9223372036854774800' AS real)} 277} -9.22337203685477e+18 278ifcapable long_double { 279 do_test cast-3.18 { 280 execsql {SELECT CAST(CAST('-9223372036854774800' AS real) AS integer)} 281 } -9223372036854774784 282} 283if {[db eval {PRAGMA encoding}]=="UTF-8"} { 284 do_test cast-3.21 { 285 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS integer)} 286 } 9223372036854774800 287 do_test cast-3.22 { 288 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS numeric)} 289 } 9223372036854774800 290 do_realnum_test cast-3.23 { 291 execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS real)} 292 } 9.22337203685477e+18 293 ifcapable long_double { 294 do_test cast-3.24 { 295 execsql { 296 SELECT CAST(CAST(x'39323233333732303336383534373734383030' AS real) 297 AS integer) 298 } 299 } 9223372036854774784 300 } 301} 302do_test cast-3.31 { 303 execsql {SELECT CAST(NULL AS numeric)} 304} {{}} 305 306# Test to see if it is possible to trick SQLite into reading past 307# the end of a blob when converting it to a number. 308do_test cast-3.32.1 { 309 set blob "1234567890" 310 set DB [sqlite3_connection_pointer db] 311 set ::STMT [sqlite3_prepare $DB {SELECT CAST(? AS real)} -1 TAIL] 312 sqlite3_bind_blob -static $::STMT 1 $blob 5 313 sqlite3_step $::STMT 314} {SQLITE_ROW} 315do_test cast-3.32.2 { 316 sqlite3_column_int $::STMT 0 317} {12345} 318do_test cast-3.32.3 { 319 sqlite3_finalize $::STMT 320} {SQLITE_OK} 321 322 323do_test cast-4.1 { 324 db eval { 325 CREATE TABLE t1(a); 326 INSERT INTO t1 VALUES('abc'); 327 SELECT a, CAST(a AS integer) FROM t1; 328 } 329} {abc 0} 330do_test cast-4.2 { 331 db eval { 332 SELECT CAST(a AS integer), a FROM t1; 333 } 334} {0 abc} 335do_test cast-4.3 { 336 db eval { 337 SELECT a, CAST(a AS integer), a FROM t1; 338 } 339} {abc 0 abc} 340do_test cast-4.4 { 341 db eval { 342 SELECT CAST(a AS integer), a, CAST(a AS real), a FROM t1; 343 } 344} {0 abc 0.0 abc} 345 346# Added 2018-01-26 347# 348# EVIDENCE-OF: R-48741-32454 If the prefix integer is greater than 349# +9223372036854775807 then the result of the cast is exactly 350# +9223372036854775807. 351do_execsql_test cast-5.1 { 352 SELECT CAST('9223372036854775808' AS integer); 353 SELECT CAST(' +000009223372036854775808' AS integer); 354 SELECT CAST('12345678901234567890123' AS INTEGER); 355} {9223372036854775807 9223372036854775807 9223372036854775807} 356 357# EVIDENCE-OF: R-06028-16857 Similarly, if the prefix integer is less 358# than -9223372036854775808 then the result of the cast is exactly 359# -9223372036854775808. 360do_execsql_test cast-5.2 { 361 SELECT CAST('-9223372036854775808' AS integer); 362 SELECT CAST('-9223372036854775809' AS integer); 363 SELECT CAST('-12345678901234567890123' AS INTEGER); 364} {-9223372036854775808 -9223372036854775808 -9223372036854775808} 365 366# EVIDENCE-OF: R-33990-33527 When casting to INTEGER, if the text looks 367# like a floating point value with an exponent, the exponent will be 368# ignored because it is no part of the integer prefix. 369# EVIDENCE-OF: R-24225-46995 For example, "(CAST '123e+5' AS INTEGER)" 370# results in 123, not in 12300000. 371do_execsql_test cast-5.3 { 372 SELECT CAST('123e+5' AS INTEGER); 373 SELECT CAST('123e+5' AS NUMERIC); 374 SELECT CAST('123e+5' AS REAL); 375} {123 12300000 12300000.0} 376 377 378# The following does not have anything to do with the CAST operator, 379# but it does deal with affinity transformations. 380# 381do_execsql_test cast-6.1 { 382 DROP TABLE IF EXISTS t1; 383 CREATE TABLE t1(a NUMERIC); 384 INSERT INTO t1 VALUES 385 ('9000000000000000001'), 386 ('9000000000000000001 '), 387 (' 9000000000000000001'), 388 (' 9000000000000000001 '); 389 SELECT * FROM t1; 390} {9000000000000000001 9000000000000000001 9000000000000000001 9000000000000000001} 391 392# 2019-06-07 393# https://www.sqlite.org/src/info/4c2d7639f076aa7c 394do_execsql_test cast-7.1 { 395 SELECT CAST('-' AS NUMERIC); 396} {0} 397do_execsql_test cast-7.2 { 398 SELECT CAST('-0' AS NUMERIC); 399} {0} 400do_execsql_test cast-7.3 { 401 SELECT CAST('+' AS NUMERIC); 402} {0} 403do_execsql_test cast-7.4 { 404 SELECT CAST('/' AS NUMERIC); 405} {0} 406 407# 2019-06-07 408# https://www.sqlite.org/src/info/e8bedb2a184001bb 409do_execsql_test cast-7.10 { 410 SELECT '' - 2851427734582196970; 411} {-2851427734582196970} 412do_execsql_test cast-7.11 { 413 SELECT 0 - 2851427734582196970; 414} {-2851427734582196970} 415do_execsql_test cast-7.12 { 416 SELECT '' - 1; 417} {-1} 418 419# 2019-06-10 420# https://www.sqlite.org/src/info/dd6bffbfb6e61db9 421# 422# EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC 423# yields either an INTEGER or a REAL result. 424# 425do_execsql_test cast-7.20 { 426 DROP TABLE IF EXISTS t0; 427 CREATE TABLE t0 (c0 TEXT); 428 INSERT INTO t0(c0) VALUES ('1.0'); 429 SELECT CAST(c0 AS NUMERIC) FROM t0; 430} {1} 431 432# 2019-06-10 433# https://sqlite.org/src/info/27de823723a41df45af3 434# 435do_execsql_test cast-7.30 { 436 SELECT -'.'; 437} 0 438do_execsql_test cast-7.31 { 439 SELECT '.'+0; 440} 0 441do_execsql_test cast-7.32 { 442 SELECT CAST('.' AS numeric); 443} 0 444do_execsql_test cast-7.33 { 445 SELECT -CAST('.' AS numeric); 446} 0 447 448# 2019-06-12 449# https://www.sqlite.org/src/info/674385aeba91c774 450# 451do_execsql_test cast-7.40 { 452 SELECT CAST('-0.0' AS numeric); 453} 0 454do_execsql_test cast-7.41 { 455 SELECT CAST('0.0' AS numeric); 456} 0 457do_execsql_test cast-7.42 { 458 SELECT CAST('+0.0' AS numeric); 459} 0 460do_execsql_test cast-7.43 { 461 SELECT CAST('-1.0' AS numeric); 462} -1 463 464ifcapable utf16 { 465 reset_db 466 execsql { PRAGMA encoding='utf16' } 467 468 do_execsql_test cast-8.1 { 469 SELECT quote(X'310032003300')==quote(substr(X'310032003300', 1)) 470 } 1 471 do_execsql_test cast-8.2 { 472 SELECT CAST(X'310032003300' AS TEXT) 473 ==CAST(substr(X'310032003300', 1) AS TEXT) 474 } 1 475} 476 477reset_db 478do_execsql_test cast-9.0 { 479 CREATE TABLE t0(c0); 480 INSERT INTO t0(c0) VALUES (0); 481 CREATE VIEW v1(c0, c1) AS 482 SELECT CAST(0.0 AS NUMERIC), COUNT(*) OVER () FROM t0; 483 SELECT v1.c0 FROM v1, t0 WHERE v1.c0=0; 484} {0} 485 486 487finish_test 488