Annotation of embedaddon/sqlite3/test/cast.test, revision 1.1
1.1 ! misho 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_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 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to cast.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test