github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/cast.test (about) 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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 # Only run these tests if the build includes the CAST operator 20 ifcapable !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 # 27 ifcapable 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 } 59 do_test cast-1.11 { 60 execsql {SELECT null} 61 } {{}} 62 do_test cast-1.12 { 63 execsql {SELECT typeof(NULL)} 64 } null 65 do_test cast-1.13 { 66 execsql {SELECT CAST(NULL AS text)} 67 } {{}} 68 do_test cast-1.14 { 69 execsql {SELECT typeof(CAST(NULL AS text))} 70 } null 71 do_test cast-1.15 { 72 execsql {SELECT CAST(NULL AS numeric)} 73 } {{}} 74 do_test cast-1.16 { 75 execsql {SELECT typeof(CAST(NULL AS numeric))} 76 } null 77 do_test cast-1.17 { 78 execsql {SELECT CAST(NULL AS blob)} 79 } {{}} 80 do_test cast-1.18 { 81 execsql {SELECT typeof(CAST(NULL AS blob))} 82 } null 83 do_test cast-1.19 { 84 execsql {SELECT CAST(NULL AS integer)} 85 } {{}} 86 do_test cast-1.20 { 87 execsql {SELECT typeof(CAST(NULL AS integer))} 88 } null 89 do_test cast-1.21 { 90 execsql {SELECT 123} 91 } {123} 92 do_test cast-1.22 { 93 execsql {SELECT typeof(123)} 94 } integer 95 do_test cast-1.23 { 96 execsql {SELECT CAST(123 AS text)} 97 } {123} 98 do_test cast-1.24 { 99 execsql {SELECT typeof(CAST(123 AS text))} 100 } text 101 do_test cast-1.25 { 102 execsql {SELECT CAST(123 AS numeric)} 103 } 123 104 do_test cast-1.26 { 105 execsql {SELECT typeof(CAST(123 AS numeric))} 106 } integer 107 do_test cast-1.27 { 108 execsql {SELECT CAST(123 AS blob)} 109 } {123} 110 do_test cast-1.28 { 111 execsql {SELECT typeof(CAST(123 AS blob))} 112 } blob 113 do_test cast-1.29 { 114 execsql {SELECT CAST(123 AS integer)} 115 } {123} 116 do_test cast-1.30 { 117 execsql {SELECT typeof(CAST(123 AS integer))} 118 } integer 119 do_test cast-1.31 { 120 execsql {SELECT 123.456} 121 } {123.456} 122 do_test cast-1.32 { 123 execsql {SELECT typeof(123.456)} 124 } real 125 do_test cast-1.33 { 126 execsql {SELECT CAST(123.456 AS text)} 127 } {123.456} 128 do_test cast-1.34 { 129 execsql {SELECT typeof(CAST(123.456 AS text))} 130 } text 131 do_test cast-1.35 { 132 execsql {SELECT CAST(123.456 AS numeric)} 133 } 123.456 134 do_test cast-1.36 { 135 execsql {SELECT typeof(CAST(123.456 AS numeric))} 136 } real 137 do_test cast-1.37 { 138 execsql {SELECT CAST(123.456 AS blob)} 139 } {123.456} 140 do_test cast-1.38 { 141 execsql {SELECT typeof(CAST(123.456 AS blob))} 142 } blob 143 do_test cast-1.39 { 144 execsql {SELECT CAST(123.456 AS integer)} 145 } {123} 146 do_test cast-1.38 { 147 execsql {SELECT typeof(CAST(123.456 AS integer))} 148 } integer 149 do_test cast-1.41 { 150 execsql {SELECT '123abc'} 151 } {123abc} 152 do_test cast-1.42 { 153 execsql {SELECT typeof('123abc')} 154 } text 155 do_test cast-1.43 { 156 execsql {SELECT CAST('123abc' AS text)} 157 } {123abc} 158 do_test cast-1.44 { 159 execsql {SELECT typeof(CAST('123abc' AS text))} 160 } text 161 do_test cast-1.45 { 162 execsql {SELECT CAST('123abc' AS numeric)} 163 } 123 164 do_test cast-1.46 { 165 execsql {SELECT typeof(CAST('123abc' AS numeric))} 166 } integer 167 do_test cast-1.47 { 168 execsql {SELECT CAST('123abc' AS blob)} 169 } {123abc} 170 do_test cast-1.48 { 171 execsql {SELECT typeof(CAST('123abc' AS blob))} 172 } blob 173 do_test cast-1.49 { 174 execsql {SELECT CAST('123abc' AS integer)} 175 } 123 176 do_test cast-1.50 { 177 execsql {SELECT typeof(CAST('123abc' AS integer))} 178 } integer 179 do_test cast-1.51 { 180 execsql {SELECT CAST('123.5abc' AS numeric)} 181 } 123.5 182 do_test cast-1.53 { 183 execsql {SELECT CAST('123.5abc' AS integer)} 184 } 123 185 186 do_test cast-1.60 { 187 execsql {SELECT CAST(null AS REAL)} 188 } {{}} 189 do_test cast-1.61 { 190 execsql {SELECT typeof(CAST(null AS REAL))} 191 } {null} 192 do_test cast-1.62 { 193 execsql {SELECT CAST(1 AS REAL)} 194 } {1.0} 195 do_test cast-1.63 { 196 execsql {SELECT typeof(CAST(1 AS REAL))} 197 } {real} 198 do_test cast-1.64 { 199 execsql {SELECT CAST('1' AS REAL)} 200 } {1.0} 201 do_test cast-1.65 { 202 execsql {SELECT typeof(CAST('1' AS REAL))} 203 } {real} 204 do_test cast-1.66 { 205 execsql {SELECT CAST('abc' AS REAL)} 206 } {0.0} 207 do_test cast-1.67 { 208 execsql {SELECT typeof(CAST('abc' AS REAL))} 209 } {real} 210 do_test cast-1.68 { 211 execsql {SELECT CAST(x'31' AS REAL)} 212 } {1.0} 213 do_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 # 220 do_test cast-2.1 { 221 execsql {SELECT CAST(' 123' AS integer)} 222 } 123 223 do_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 # 231 do_test cast-3.1 { 232 execsql {SELECT CAST(9223372036854774800 AS integer)} 233 } 9223372036854774800 234 do_test cast-3.2 { 235 execsql {SELECT CAST(9223372036854774800 AS numeric)} 236 } 9223372036854774800 237 do_realnum_test cast-3.3 { 238 execsql {SELECT CAST(9223372036854774800 AS real)} 239 } 9.22337203685477e+18 240 do_test cast-3.4 { 241 execsql {SELECT CAST(CAST(9223372036854774800 AS real) AS integer)} 242 } 9223372036854774784 243 do_test cast-3.5 { 244 execsql {SELECT CAST(-9223372036854774800 AS integer)} 245 } -9223372036854774800 246 do_test cast-3.6 { 247 execsql {SELECT CAST(-9223372036854774800 AS numeric)} 248 } -9223372036854774800 249 do_realnum_test cast-3.7 { 250 execsql {SELECT CAST(-9223372036854774800 AS real)} 251 } -9.22337203685477e+18 252 do_test cast-3.8 { 253 execsql {SELECT CAST(CAST(-9223372036854774800 AS real) AS integer)} 254 } -9223372036854774784 255 do_test cast-3.11 { 256 execsql {SELECT CAST('9223372036854774800' AS integer)} 257 } 9223372036854774800 258 do_test cast-3.12 { 259 execsql {SELECT CAST('9223372036854774800' AS numeric)} 260 } 9223372036854774800 261 do_realnum_test cast-3.13 { 262 execsql {SELECT CAST('9223372036854774800' AS real)} 263 } 9.22337203685477e+18 264 ifcapable long_double { 265 do_test cast-3.14 { 266 execsql {SELECT CAST(CAST('9223372036854774800' AS real) AS integer)} 267 } 9223372036854774784 268 } 269 do_test cast-3.15 { 270 execsql {SELECT CAST('-9223372036854774800' AS integer)} 271 } -9223372036854774800 272 do_test cast-3.16 { 273 execsql {SELECT CAST('-9223372036854774800' AS numeric)} 274 } -9223372036854774800 275 do_realnum_test cast-3.17 { 276 execsql {SELECT CAST('-9223372036854774800' AS real)} 277 } -9.22337203685477e+18 278 ifcapable long_double { 279 do_test cast-3.18 { 280 execsql {SELECT CAST(CAST('-9223372036854774800' AS real) AS integer)} 281 } -9223372036854774784 282 } 283 if {[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 } 302 do_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. 308 do_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} 315 do_test cast-3.32.2 { 316 sqlite3_column_int $::STMT 0 317 } {12345} 318 do_test cast-3.32.3 { 319 sqlite3_finalize $::STMT 320 } {SQLITE_OK} 321 322 323 do_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} 330 do_test cast-4.2 { 331 db eval { 332 SELECT CAST(a AS integer), a FROM t1; 333 } 334 } {0 abc} 335 do_test cast-4.3 { 336 db eval { 337 SELECT a, CAST(a AS integer), a FROM t1; 338 } 339 } {abc 0 abc} 340 do_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. 351 do_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. 360 do_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. 371 do_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 # 381 do_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 394 do_execsql_test cast-7.1 { 395 SELECT CAST('-' AS NUMERIC); 396 } {0} 397 do_execsql_test cast-7.2 { 398 SELECT CAST('-0' AS NUMERIC); 399 } {0} 400 do_execsql_test cast-7.3 { 401 SELECT CAST('+' AS NUMERIC); 402 } {0} 403 do_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 409 do_execsql_test cast-7.10 { 410 SELECT '' - 2851427734582196970; 411 } {-2851427734582196970} 412 do_execsql_test cast-7.11 { 413 SELECT 0 - 2851427734582196970; 414 } {-2851427734582196970} 415 do_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 # 425 do_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 # 435 do_execsql_test cast-7.30 { 436 SELECT -'.'; 437 } 0 438 do_execsql_test cast-7.31 { 439 SELECT '.'+0; 440 } 0 441 do_execsql_test cast-7.32 { 442 SELECT CAST('.' AS numeric); 443 } 0 444 do_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 # 451 do_execsql_test cast-7.40 { 452 SELECT CAST('-0.0' AS numeric); 453 } 0 454 do_execsql_test cast-7.41 { 455 SELECT CAST('0.0' AS numeric); 456 } 0 457 do_execsql_test cast-7.42 { 458 SELECT CAST('+0.0' AS numeric); 459 } 0 460 do_execsql_test cast-7.43 { 461 SELECT CAST('-1.0' AS numeric); 462 } -1 463 464 ifcapable 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 477 reset_db 478 do_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.0} 485 486 487 finish_test