summaryrefslogtreecommitdiff
path: root/test/cast.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/cast.test')
-rw-r--r--test/cast.test346
1 files changed, 346 insertions, 0 deletions
diff --git a/test/cast.test b/test/cast.test
new file mode 100644
index 0000000..f47f4bb
--- /dev/null
+++ b/test/cast.test
@@ -0,0 +1,346 @@
+# 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_realnum_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_realnum_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_realnum_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_realnum_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_realnum_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
generated by cgit v1.2.3 (git 2.25.1) at 2024-06-08 19:28:22 +0000