1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/cast.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,322 @@
1.4 +# 2005 June 25
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing the CAST operator.
1.16 +#
1.17 +# $Id: cast.test,v 1.9 2008/01/19 20:11:26 drh Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# Only run these tests if the build includes the CAST operator
1.23 +ifcapable !cast {
1.24 + finish_test
1.25 + return
1.26 +}
1.27 +
1.28 +# Tests for the CAST( AS blob), CAST( AS text) and CAST( AS numeric) built-ins
1.29 +#
1.30 +ifcapable bloblit {
1.31 + do_test cast-1.1 {
1.32 + execsql {SELECT x'616263'}
1.33 + } abc
1.34 + do_test cast-1.2 {
1.35 + execsql {SELECT typeof(x'616263')}
1.36 + } blob
1.37 + do_test cast-1.3 {
1.38 + execsql {SELECT CAST(x'616263' AS text)}
1.39 + } abc
1.40 + do_test cast-1.4 {
1.41 + execsql {SELECT typeof(CAST(x'616263' AS text))}
1.42 + } text
1.43 + do_test cast-1.5 {
1.44 + execsql {SELECT CAST(x'616263' AS numeric)}
1.45 + } 0
1.46 + do_test cast-1.6 {
1.47 + execsql {SELECT typeof(CAST(x'616263' AS numeric))}
1.48 + } integer
1.49 + do_test cast-1.7 {
1.50 + execsql {SELECT CAST(x'616263' AS blob)}
1.51 + } abc
1.52 + do_test cast-1.8 {
1.53 + execsql {SELECT typeof(CAST(x'616263' AS blob))}
1.54 + } blob
1.55 + do_test cast-1.9 {
1.56 + execsql {SELECT CAST(x'616263' AS integer)}
1.57 + } 0
1.58 + do_test cast-1.10 {
1.59 + execsql {SELECT typeof(CAST(x'616263' AS integer))}
1.60 + } integer
1.61 +}
1.62 +do_test cast-1.11 {
1.63 + execsql {SELECT null}
1.64 +} {{}}
1.65 +do_test cast-1.12 {
1.66 + execsql {SELECT typeof(NULL)}
1.67 +} null
1.68 +do_test cast-1.13 {
1.69 + execsql {SELECT CAST(NULL AS text)}
1.70 +} {{}}
1.71 +do_test cast-1.14 {
1.72 + execsql {SELECT typeof(CAST(NULL AS text))}
1.73 +} null
1.74 +do_test cast-1.15 {
1.75 + execsql {SELECT CAST(NULL AS numeric)}
1.76 +} {{}}
1.77 +do_test cast-1.16 {
1.78 + execsql {SELECT typeof(CAST(NULL AS numeric))}
1.79 +} null
1.80 +do_test cast-1.17 {
1.81 + execsql {SELECT CAST(NULL AS blob)}
1.82 +} {{}}
1.83 +do_test cast-1.18 {
1.84 + execsql {SELECT typeof(CAST(NULL AS blob))}
1.85 +} null
1.86 +do_test cast-1.19 {
1.87 + execsql {SELECT CAST(NULL AS integer)}
1.88 +} {{}}
1.89 +do_test cast-1.20 {
1.90 + execsql {SELECT typeof(CAST(NULL AS integer))}
1.91 +} null
1.92 +do_test cast-1.21 {
1.93 + execsql {SELECT 123}
1.94 +} {123}
1.95 +do_test cast-1.22 {
1.96 + execsql {SELECT typeof(123)}
1.97 +} integer
1.98 +do_test cast-1.23 {
1.99 + execsql {SELECT CAST(123 AS text)}
1.100 +} {123}
1.101 +do_test cast-1.24 {
1.102 + execsql {SELECT typeof(CAST(123 AS text))}
1.103 +} text
1.104 +do_test cast-1.25 {
1.105 + execsql {SELECT CAST(123 AS numeric)}
1.106 +} 123
1.107 +do_test cast-1.26 {
1.108 + execsql {SELECT typeof(CAST(123 AS numeric))}
1.109 +} integer
1.110 +do_test cast-1.27 {
1.111 + execsql {SELECT CAST(123 AS blob)}
1.112 +} {123}
1.113 +do_test cast-1.28 {
1.114 + execsql {SELECT typeof(CAST(123 AS blob))}
1.115 +} blob
1.116 +do_test cast-1.29 {
1.117 + execsql {SELECT CAST(123 AS integer)}
1.118 +} {123}
1.119 +do_test cast-1.30 {
1.120 + execsql {SELECT typeof(CAST(123 AS integer))}
1.121 +} integer
1.122 +do_test cast-1.31 {
1.123 + execsql {SELECT 123.456}
1.124 +} {123.456}
1.125 +do_test cast-1.32 {
1.126 + execsql {SELECT typeof(123.456)}
1.127 +} real
1.128 +do_test cast-1.33 {
1.129 + execsql {SELECT CAST(123.456 AS text)}
1.130 +} {123.456}
1.131 +do_test cast-1.34 {
1.132 + execsql {SELECT typeof(CAST(123.456 AS text))}
1.133 +} text
1.134 +do_test cast-1.35 {
1.135 + execsql {SELECT CAST(123.456 AS numeric)}
1.136 +} 123.456
1.137 +do_test cast-1.36 {
1.138 + execsql {SELECT typeof(CAST(123.456 AS numeric))}
1.139 +} real
1.140 +do_test cast-1.37 {
1.141 + execsql {SELECT CAST(123.456 AS blob)}
1.142 +} {123.456}
1.143 +do_test cast-1.38 {
1.144 + execsql {SELECT typeof(CAST(123.456 AS blob))}
1.145 +} blob
1.146 +do_test cast-1.39 {
1.147 + execsql {SELECT CAST(123.456 AS integer)}
1.148 +} {123}
1.149 +do_test cast-1.38 {
1.150 + execsql {SELECT typeof(CAST(123.456 AS integer))}
1.151 +} integer
1.152 +do_test cast-1.41 {
1.153 + execsql {SELECT '123abc'}
1.154 +} {123abc}
1.155 +do_test cast-1.42 {
1.156 + execsql {SELECT typeof('123abc')}
1.157 +} text
1.158 +do_test cast-1.43 {
1.159 + execsql {SELECT CAST('123abc' AS text)}
1.160 +} {123abc}
1.161 +do_test cast-1.44 {
1.162 + execsql {SELECT typeof(CAST('123abc' AS text))}
1.163 +} text
1.164 +do_test cast-1.45 {
1.165 + execsql {SELECT CAST('123abc' AS numeric)}
1.166 +} 123
1.167 +do_test cast-1.46 {
1.168 + execsql {SELECT typeof(CAST('123abc' AS numeric))}
1.169 +} integer
1.170 +do_test cast-1.47 {
1.171 + execsql {SELECT CAST('123abc' AS blob)}
1.172 +} {123abc}
1.173 +do_test cast-1.48 {
1.174 + execsql {SELECT typeof(CAST('123abc' AS blob))}
1.175 +} blob
1.176 +do_test cast-1.49 {
1.177 + execsql {SELECT CAST('123abc' AS integer)}
1.178 +} 123
1.179 +do_test cast-1.50 {
1.180 + execsql {SELECT typeof(CAST('123abc' AS integer))}
1.181 +} integer
1.182 +do_test cast-1.51 {
1.183 + execsql {SELECT CAST('123.5abc' AS numeric)}
1.184 +} 123.5
1.185 +do_test cast-1.53 {
1.186 + execsql {SELECT CAST('123.5abc' AS integer)}
1.187 +} 123
1.188 +
1.189 +do_test case-1.60 {
1.190 + execsql {SELECT CAST(null AS REAL)}
1.191 +} {{}}
1.192 +do_test case-1.61 {
1.193 + execsql {SELECT typeof(CAST(null AS REAL))}
1.194 +} {null}
1.195 +do_test case-1.62 {
1.196 + execsql {SELECT CAST(1 AS REAL)}
1.197 +} {1.0}
1.198 +do_test case-1.63 {
1.199 + execsql {SELECT typeof(CAST(1 AS REAL))}
1.200 +} {real}
1.201 +do_test case-1.64 {
1.202 + execsql {SELECT CAST('1' AS REAL)}
1.203 +} {1.0}
1.204 +do_test case-1.65 {
1.205 + execsql {SELECT typeof(CAST('1' AS REAL))}
1.206 +} {real}
1.207 +do_test case-1.66 {
1.208 + execsql {SELECT CAST('abc' AS REAL)}
1.209 +} {0.0}
1.210 +do_test case-1.67 {
1.211 + execsql {SELECT typeof(CAST('abc' AS REAL))}
1.212 +} {real}
1.213 +do_test case-1.68 {
1.214 + execsql {SELECT CAST(x'31' AS REAL)}
1.215 +} {1.0}
1.216 +do_test case-1.69 {
1.217 + execsql {SELECT typeof(CAST(x'31' AS REAL))}
1.218 +} {real}
1.219 +
1.220 +
1.221 +# Ticket #1662. Ignore leading spaces in numbers when casting.
1.222 +#
1.223 +do_test cast-2.1 {
1.224 + execsql {SELECT CAST(' 123' AS integer)}
1.225 +} 123
1.226 +do_test cast-2.2 {
1.227 + execsql {SELECT CAST(' -123.456' AS real)}
1.228 +} -123.456
1.229 +
1.230 +# ticket #2364. Use full percision integers if possible when casting
1.231 +# to numeric. Do not fallback to real (and the corresponding 48-bit
1.232 +# mantissa) unless absolutely necessary.
1.233 +#
1.234 +do_test cast-3.1 {
1.235 + execsql {SELECT CAST(9223372036854774800 AS integer)}
1.236 +} 9223372036854774800
1.237 +do_test cast-3.2 {
1.238 + execsql {SELECT CAST(9223372036854774800 AS numeric)}
1.239 +} 9223372036854774800
1.240 +do_test cast-3.3 {
1.241 + execsql {SELECT CAST(9223372036854774800 AS real)}
1.242 +} 9.22337203685477e+18
1.243 +do_test cast-3.4 {
1.244 + execsql {SELECT CAST(CAST(9223372036854774800 AS real) AS integer)}
1.245 +} 9223372036854774784
1.246 +do_test cast-3.5 {
1.247 + execsql {SELECT CAST(-9223372036854774800 AS integer)}
1.248 +} -9223372036854774800
1.249 +do_test cast-3.6 {
1.250 + execsql {SELECT CAST(-9223372036854774800 AS numeric)}
1.251 +} -9223372036854774800
1.252 +do_test cast-3.7 {
1.253 + execsql {SELECT CAST(-9223372036854774800 AS real)}
1.254 +} -9.22337203685477e+18
1.255 +do_test cast-3.8 {
1.256 + execsql {SELECT CAST(CAST(-9223372036854774800 AS real) AS integer)}
1.257 +} -9223372036854774784
1.258 +do_test cast-3.11 {
1.259 + execsql {SELECT CAST('9223372036854774800' AS integer)}
1.260 +} 9223372036854774800
1.261 +do_test cast-3.12 {
1.262 + execsql {SELECT CAST('9223372036854774800' AS numeric)}
1.263 +} 9223372036854774800
1.264 +do_test cast-3.13 {
1.265 + execsql {SELECT CAST('9223372036854774800' AS real)}
1.266 +} 9.22337203685477e+18
1.267 +ifcapable long_double {
1.268 + do_test cast-3.14 {
1.269 + execsql {SELECT CAST(CAST('9223372036854774800' AS real) AS integer)}
1.270 + } 9223372036854774784
1.271 +}
1.272 +do_test cast-3.15 {
1.273 + execsql {SELECT CAST('-9223372036854774800' AS integer)}
1.274 +} -9223372036854774800
1.275 +do_test cast-3.16 {
1.276 + execsql {SELECT CAST('-9223372036854774800' AS numeric)}
1.277 +} -9223372036854774800
1.278 +do_test cast-3.17 {
1.279 + execsql {SELECT CAST('-9223372036854774800' AS real)}
1.280 +} -9.22337203685477e+18
1.281 +ifcapable long_double {
1.282 + do_test cast-3.18 {
1.283 + execsql {SELECT CAST(CAST('-9223372036854774800' AS real) AS integer)}
1.284 + } -9223372036854774784
1.285 +}
1.286 +if {[db eval {PRAGMA encoding}]=="UTF-8"} {
1.287 + do_test cast-3.21 {
1.288 + execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS integer)}
1.289 + } 9223372036854774800
1.290 + do_test cast-3.22 {
1.291 + execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS numeric)}
1.292 + } 9223372036854774800
1.293 + do_test cast-3.23 {
1.294 + execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS real)}
1.295 + } 9.22337203685477e+18
1.296 + ifcapable long_double {
1.297 + do_test cast-3.24 {
1.298 + execsql {
1.299 + SELECT CAST(CAST(x'39323233333732303336383534373734383030' AS real)
1.300 + AS integer)
1.301 + }
1.302 + } 9223372036854774784
1.303 + }
1.304 +}
1.305 +do_test case-3.31 {
1.306 + execsql {SELECT CAST(NULL AS numeric)}
1.307 +} {{}}
1.308 +
1.309 +# Test to see if it is possible to trick SQLite into reading past
1.310 +# the end of a blob when converting it to a number.
1.311 +do_test cast-3.32.1 {
1.312 + set blob "1234567890"
1.313 + set DB [sqlite3_connection_pointer db]
1.314 + set ::STMT [sqlite3_prepare $DB {SELECT CAST(? AS real)} -1 TAIL]
1.315 + sqlite3_bind_blob -static $::STMT 1 $blob 5
1.316 + sqlite3_step $::STMT
1.317 +} {SQLITE_ROW}
1.318 +do_test cast-3.32.2 {
1.319 + sqlite3_column_int $::STMT 0
1.320 +} {12345}
1.321 +do_test cast-3.32.3 {
1.322 + sqlite3_finalize $::STMT
1.323 +} {SQLITE_OK}
1.324 +
1.325 +finish_test