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