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 case-1.60 { 187 execsql {SELECT CAST(null AS REAL)} 188 } {{}} 189 do_test case-1.61 { 190 execsql {SELECT typeof(CAST(null AS REAL))} 191 } {null} 192 do_test case-1.62 { 193 execsql {SELECT CAST(1 AS REAL)} 194 } {1.0} 195 do_test case-1.63 { 196 execsql {SELECT typeof(CAST(1 AS REAL))} 197 } {real} 198 do_test case-1.64 { 199 execsql {SELECT CAST('1' AS REAL)} 200 } {1.0} 201 do_test case-1.65 { 202 execsql {SELECT typeof(CAST('1' AS REAL))} 203 } {real} 204 do_test case-1.66 { 205 execsql {SELECT CAST('abc' AS REAL)} 206 } {0.0} 207 do_test case-1.67 { 208 execsql {SELECT typeof(CAST('abc' AS REAL))} 209 } {real} 210 do_test case-1.68 { 211 execsql {SELECT CAST(x'31' AS REAL)} 212 } {1.0} 213 do_test case-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_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_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_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_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_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 case-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 finish_test 347
