# 2005 June 25 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CAST operator. # # $Id: cast.test,v 1.10 2008/11/06 15:33:04 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Only run these tests if the build includes the CAST operator ifcapable !cast { finish_test return } # Tests for the CAST( AS blob), CAST( AS text) and CAST( AS numeric) built-ins # ifcapable bloblit { do_test cast-1.1 { execsql {SELECT x'616263'} } abc do_test cast-1.2 { execsql {SELECT typeof(x'616263')} } blob do_test cast-1.3 { execsql {SELECT CAST(x'616263' AS text)} } abc do_test cast-1.4 { execsql {SELECT typeof(CAST(x'616263' AS text))} } text do_test cast-1.5 { execsql {SELECT CAST(x'616263' AS numeric)} } 0 do_test cast-1.6 { execsql {SELECT typeof(CAST(x'616263' AS numeric))} } integer do_test cast-1.7 { execsql {SELECT CAST(x'616263' AS blob)} } abc do_test cast-1.8 { execsql {SELECT typeof(CAST(x'616263' AS blob))} } blob do_test cast-1.9 { execsql {SELECT CAST(x'616263' AS integer)} } 0 do_test cast-1.10 { execsql {SELECT typeof(CAST(x'616263' AS integer))} } integer } do_test cast-1.11 { execsql {SELECT null} } {{}} do_test cast-1.12 { execsql {SELECT typeof(NULL)} } null do_test cast-1.13 { execsql {SELECT CAST(NULL AS text)} } {{}} do_test cast-1.14 { execsql {SELECT typeof(CAST(NULL AS text))} } null do_test cast-1.15 { execsql {SELECT CAST(NULL AS numeric)} } {{}} do_test cast-1.16 { execsql {SELECT typeof(CAST(NULL AS numeric))} } null do_test cast-1.17 { execsql {SELECT CAST(NULL AS blob)} } {{}} do_test cast-1.18 { execsql {SELECT typeof(CAST(NULL AS blob))} } null do_test cast-1.19 { execsql {SELECT CAST(NULL AS integer)} } {{}} do_test cast-1.20 { execsql {SELECT typeof(CAST(NULL AS integer))} } null do_test cast-1.21 { execsql {SELECT 123} } {123} do_test cast-1.22 { execsql {SELECT typeof(123)} } integer do_test cast-1.23 { execsql {SELECT CAST(123 AS text)} } {123} do_test cast-1.24 { execsql {SELECT typeof(CAST(123 AS text))} } text do_test cast-1.25 { execsql {SELECT CAST(123 AS numeric)} } 123 do_test cast-1.26 { execsql {SELECT typeof(CAST(123 AS numeric))} } integer do_test cast-1.27 { execsql {SELECT CAST(123 AS blob)} } {123} do_test cast-1.28 { execsql {SELECT typeof(CAST(123 AS blob))} } blob do_test cast-1.29 { execsql {SELECT CAST(123 AS integer)} } {123} do_test cast-1.30 { execsql {SELECT typeof(CAST(123 AS integer))} } integer do_test cast-1.31 { execsql {SELECT 123.456} } {123.456} do_test cast-1.32 { execsql {SELECT typeof(123.456)} } real do_test cast-1.33 { execsql {SELECT CAST(123.456 AS text)} } {123.456} do_test cast-1.34 { execsql {SELECT typeof(CAST(123.456 AS text))} } text do_test cast-1.35 { execsql {SELECT CAST(123.456 AS numeric)} } 123.456 do_test cast-1.36 { execsql {SELECT typeof(CAST(123.456 AS numeric))} } real do_test cast-1.37 { execsql {SELECT CAST(123.456 AS blob)} } {123.456} do_test cast-1.38 { execsql {SELECT typeof(CAST(123.456 AS blob))} } blob do_test cast-1.39 { execsql {SELECT CAST(123.456 AS integer)} } {123} do_test cast-1.38 { execsql {SELECT typeof(CAST(123.456 AS integer))} } integer do_test cast-1.41 { execsql {SELECT '123abc'} } {123abc} do_test cast-1.42 { execsql {SELECT typeof('123abc')} } text do_test cast-1.43 { execsql {SELECT CAST('123abc' AS text)} } {123abc} do_test cast-1.44 { execsql {SELECT typeof(CAST('123abc' AS text))} } text do_test cast-1.45 { execsql {SELECT CAST('123abc' AS numeric)} } 123 do_test cast-1.46 { execsql {SELECT typeof(CAST('123abc' AS numeric))} } integer do_test cast-1.47 { execsql {SELECT CAST('123abc' AS blob)} } {123abc} do_test cast-1.48 { execsql {SELECT typeof(CAST('123abc' AS blob))} } blob do_test cast-1.49 { execsql {SELECT CAST('123abc' AS integer)} } 123 do_test cast-1.50 { execsql {SELECT typeof(CAST('123abc' AS integer))} } integer do_test cast-1.51 { execsql {SELECT CAST('123.5abc' AS numeric)} } 123.5 do_test cast-1.53 { execsql {SELECT CAST('123.5abc' AS integer)} } 123 do_test case-1.60 { execsql {SELECT CAST(null AS REAL)} } {{}} do_test case-1.61 { execsql {SELECT typeof(CAST(null AS REAL))} } {null} do_test case-1.62 { execsql {SELECT CAST(1 AS REAL)} } {1.0} do_test case-1.63 { execsql {SELECT typeof(CAST(1 AS REAL))} } {real} do_test case-1.64 { execsql {SELECT CAST('1' AS REAL)} } {1.0} do_test case-1.65 { execsql {SELECT typeof(CAST('1' AS REAL))} } {real} do_test case-1.66 { execsql {SELECT CAST('abc' AS REAL)} } {0.0} do_test case-1.67 { execsql {SELECT typeof(CAST('abc' AS REAL))} } {real} do_test case-1.68 { execsql {SELECT CAST(x'31' AS REAL)} } {1.0} do_test case-1.69 { execsql {SELECT typeof(CAST(x'31' AS REAL))} } {real} # Ticket #1662. Ignore leading spaces in numbers when casting. # do_test cast-2.1 { execsql {SELECT CAST(' 123' AS integer)} } 123 do_test cast-2.2 { execsql {SELECT CAST(' -123.456' AS real)} } -123.456 # ticket #2364. Use full percision integers if possible when casting # to numeric. Do not fallback to real (and the corresponding 48-bit # mantissa) unless absolutely necessary. # do_test cast-3.1 { execsql {SELECT CAST(9223372036854774800 AS integer)} } 9223372036854774800 do_test cast-3.2 { execsql {SELECT CAST(9223372036854774800 AS numeric)} } 9223372036854774800 do_test cast-3.3 { execsql {SELECT CAST(9223372036854774800 AS real)} } 9.22337203685477e+18 do_test cast-3.4 { execsql {SELECT CAST(CAST(9223372036854774800 AS real) AS integer)} } 9223372036854774784 do_test cast-3.5 { execsql {SELECT CAST(-9223372036854774800 AS integer)} } -9223372036854774800 do_test cast-3.6 { execsql {SELECT CAST(-9223372036854774800 AS numeric)} } -9223372036854774800 do_test cast-3.7 { execsql {SELECT CAST(-9223372036854774800 AS real)} } -9.22337203685477e+18 do_test cast-3.8 { execsql {SELECT CAST(CAST(-9223372036854774800 AS real) AS integer)} } -9223372036854774784 do_test cast-3.11 { execsql {SELECT CAST('9223372036854774800' AS integer)} } 9223372036854774800 do_test cast-3.12 { execsql {SELECT CAST('9223372036854774800' AS numeric)} } 9223372036854774800 do_test cast-3.13 { execsql {SELECT CAST('9223372036854774800' AS real)} } 9.22337203685477e+18 ifcapable long_double { do_test cast-3.14 { execsql {SELECT CAST(CAST('9223372036854774800' AS real) AS integer)} } 9223372036854774784 } do_test cast-3.15 { execsql {SELECT CAST('-9223372036854774800' AS integer)} } -9223372036854774800 do_test cast-3.16 { execsql {SELECT CAST('-9223372036854774800' AS numeric)} } -9223372036854774800 do_test cast-3.17 { execsql {SELECT CAST('-9223372036854774800' AS real)} } -9.22337203685477e+18 ifcapable long_double { do_test cast-3.18 { execsql {SELECT CAST(CAST('-9223372036854774800' AS real) AS integer)} } -9223372036854774784 } if {[db eval {PRAGMA encoding}]=="UTF-8"} { do_test cast-3.21 { execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS integer)} } 9223372036854774800 do_test cast-3.22 { execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS numeric)} } 9223372036854774800 do_test cast-3.23 { execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS real)} } 9.22337203685477e+18 ifcapable long_double { do_test cast-3.24 { execsql { SELECT CAST(CAST(x'39323233333732303336383534373734383030' AS real) AS integer) } } 9223372036854774784 } } do_test case-3.31 { execsql {SELECT CAST(NULL AS numeric)} } {{}} # Test to see if it is possible to trick SQLite into reading past # the end of a blob when converting it to a number. do_test cast-3.32.1 { set blob "1234567890" set DB [sqlite3_connection_pointer db] set ::STMT [sqlite3_prepare $DB {SELECT CAST(? AS real)} -1 TAIL] sqlite3_bind_blob -static $::STMT 1 $blob 5 sqlite3_step $::STMT } {SQLITE_ROW} do_test cast-3.32.2 { sqlite3_column_int $::STMT 0 } {12345} do_test cast-3.32.3 { sqlite3_finalize $::STMT } {SQLITE_OK} do_test cast-4.1 { db eval { CREATE TABLE t1(a); INSERT INTO t1 VALUES('abc'); SELECT a, CAST(a AS integer) FROM t1; } } {abc 0} do_test cast-4.2 { db eval { SELECT CAST(a AS integer), a FROM t1; } } {0 abc} do_test cast-4.3 { db eval { SELECT a, CAST(a AS integer), a FROM t1; } } {abc 0 abc} do_test cast-4.4 { db eval { SELECT CAST(a AS integer), a, CAST(a AS real), a FROM t1; } } {0 abc 0.0 abc} finish_test