• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
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
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# Only run these tests if the build includes the CAST operator
20ifcapable !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#
27ifcapable 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}
59do_test cast-1.11 {
60  execsql {SELECT null}
61} {{}}
62do_test cast-1.12 {
63  execsql {SELECT typeof(NULL)}
64} null
65do_test cast-1.13 {
66  execsql {SELECT CAST(NULL AS text)}
67} {{}}
68do_test cast-1.14 {
69  execsql {SELECT typeof(CAST(NULL AS text))}
70} null
71do_test cast-1.15 {
72  execsql {SELECT CAST(NULL AS numeric)}
73} {{}}
74do_test cast-1.16 {
75  execsql {SELECT typeof(CAST(NULL AS numeric))}
76} null
77do_test cast-1.17 {
78  execsql {SELECT CAST(NULL AS blob)}
79} {{}}
80do_test cast-1.18 {
81  execsql {SELECT typeof(CAST(NULL AS blob))}
82} null
83do_test cast-1.19 {
84  execsql {SELECT CAST(NULL AS integer)}
85} {{}}
86do_test cast-1.20 {
87  execsql {SELECT typeof(CAST(NULL AS integer))}
88} null
89do_test cast-1.21 {
90  execsql {SELECT 123}
91} {123}
92do_test cast-1.22 {
93  execsql {SELECT typeof(123)}
94} integer
95do_test cast-1.23 {
96  execsql {SELECT CAST(123 AS text)}
97} {123}
98do_test cast-1.24 {
99  execsql {SELECT typeof(CAST(123 AS text))}
100} text
101do_test cast-1.25 {
102  execsql {SELECT CAST(123 AS numeric)}
103} 123
104do_test cast-1.26 {
105  execsql {SELECT typeof(CAST(123 AS numeric))}
106} integer
107do_test cast-1.27 {
108  execsql {SELECT CAST(123 AS blob)}
109} {123}
110do_test cast-1.28 {
111  execsql {SELECT typeof(CAST(123 AS blob))}
112} blob
113do_test cast-1.29 {
114  execsql {SELECT CAST(123 AS integer)}
115} {123}
116do_test cast-1.30 {
117  execsql {SELECT typeof(CAST(123 AS integer))}
118} integer
119do_test cast-1.31 {
120  execsql {SELECT 123.456}
121} {123.456}
122do_test cast-1.32 {
123  execsql {SELECT typeof(123.456)}
124} real
125do_test cast-1.33 {
126  execsql {SELECT CAST(123.456 AS text)}
127} {123.456}
128do_test cast-1.34 {
129  execsql {SELECT typeof(CAST(123.456 AS text))}
130} text
131do_test cast-1.35 {
132  execsql {SELECT CAST(123.456 AS numeric)}
133} 123.456
134do_test cast-1.36 {
135  execsql {SELECT typeof(CAST(123.456 AS numeric))}
136} real
137do_test cast-1.37 {
138  execsql {SELECT CAST(123.456 AS blob)}
139} {123.456}
140do_test cast-1.38 {
141  execsql {SELECT typeof(CAST(123.456 AS blob))}
142} blob
143do_test cast-1.39 {
144  execsql {SELECT CAST(123.456 AS integer)}
145} {123}
146do_test cast-1.38 {
147  execsql {SELECT typeof(CAST(123.456 AS integer))}
148} integer
149do_test cast-1.41 {
150  execsql {SELECT '123abc'}
151} {123abc}
152do_test cast-1.42 {
153  execsql {SELECT typeof('123abc')}
154} text
155do_test cast-1.43 {
156  execsql {SELECT CAST('123abc' AS text)}
157} {123abc}
158do_test cast-1.44 {
159  execsql {SELECT typeof(CAST('123abc' AS text))}
160} text
161do_test cast-1.45 {
162  execsql {SELECT CAST('123abc' AS numeric)}
163} 123
164do_test cast-1.46 {
165  execsql {SELECT typeof(CAST('123abc' AS numeric))}
166} integer
167do_test cast-1.47 {
168  execsql {SELECT CAST('123abc' AS blob)}
169} {123abc}
170do_test cast-1.48 {
171  execsql {SELECT typeof(CAST('123abc' AS blob))}
172} blob
173do_test cast-1.49 {
174  execsql {SELECT CAST('123abc' AS integer)}
175} 123
176do_test cast-1.50 {
177  execsql {SELECT typeof(CAST('123abc' AS integer))}
178} integer
179do_test cast-1.51 {
180  execsql {SELECT CAST('123.5abc' AS numeric)}
181} 123.5
182do_test cast-1.53 {
183  execsql {SELECT CAST('123.5abc' AS integer)}
184} 123
185
186do_test case-1.60 {
187  execsql {SELECT CAST(null AS REAL)}
188} {{}}
189do_test case-1.61 {
190  execsql {SELECT typeof(CAST(null AS REAL))}
191} {null}
192do_test case-1.62 {
193  execsql {SELECT CAST(1 AS REAL)}
194} {1.0}
195do_test case-1.63 {
196  execsql {SELECT typeof(CAST(1 AS REAL))}
197} {real}
198do_test case-1.64 {
199  execsql {SELECT CAST('1' AS REAL)}
200} {1.0}
201do_test case-1.65 {
202  execsql {SELECT typeof(CAST('1' AS REAL))}
203} {real}
204do_test case-1.66 {
205  execsql {SELECT CAST('abc' AS REAL)}
206} {0.0}
207do_test case-1.67 {
208  execsql {SELECT typeof(CAST('abc' AS REAL))}
209} {real}
210do_test case-1.68 {
211  execsql {SELECT CAST(x'31' AS REAL)}
212} {1.0}
213do_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#
220do_test cast-2.1 {
221  execsql {SELECT CAST('   123' AS integer)}
222} 123
223do_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#
231do_test cast-3.1 {
232  execsql {SELECT CAST(9223372036854774800 AS integer)}
233} 9223372036854774800
234do_test cast-3.2 {
235  execsql {SELECT CAST(9223372036854774800 AS numeric)}
236} 9223372036854774800
237do_test cast-3.3 {
238  execsql {SELECT CAST(9223372036854774800 AS real)}
239} 9.22337203685477e+18
240do_test cast-3.4 {
241  execsql {SELECT CAST(CAST(9223372036854774800 AS real) AS integer)}
242} 9223372036854774784
243do_test cast-3.5 {
244  execsql {SELECT CAST(-9223372036854774800 AS integer)}
245} -9223372036854774800
246do_test cast-3.6 {
247  execsql {SELECT CAST(-9223372036854774800 AS numeric)}
248} -9223372036854774800
249do_test cast-3.7 {
250  execsql {SELECT CAST(-9223372036854774800 AS real)}
251} -9.22337203685477e+18
252do_test cast-3.8 {
253  execsql {SELECT CAST(CAST(-9223372036854774800 AS real) AS integer)}
254} -9223372036854774784
255do_test cast-3.11 {
256  execsql {SELECT CAST('9223372036854774800' AS integer)}
257} 9223372036854774800
258do_test cast-3.12 {
259  execsql {SELECT CAST('9223372036854774800' AS numeric)}
260} 9223372036854774800
261do_test cast-3.13 {
262  execsql {SELECT CAST('9223372036854774800' AS real)}
263} 9.22337203685477e+18
264ifcapable long_double {
265  do_test cast-3.14 {
266    execsql {SELECT CAST(CAST('9223372036854774800' AS real) AS integer)}
267  } 9223372036854774784
268}
269do_test cast-3.15 {
270  execsql {SELECT CAST('-9223372036854774800' AS integer)}
271} -9223372036854774800
272do_test cast-3.16 {
273  execsql {SELECT CAST('-9223372036854774800' AS numeric)}
274} -9223372036854774800
275do_test cast-3.17 {
276  execsql {SELECT CAST('-9223372036854774800' AS real)}
277} -9.22337203685477e+18
278ifcapable long_double {
279  do_test cast-3.18 {
280    execsql {SELECT CAST(CAST('-9223372036854774800' AS real) AS integer)}
281  } -9223372036854774784
282}
283if {[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}
302do_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.
308do_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}
315do_test cast-3.32.2 {
316  sqlite3_column_int $::STMT 0
317} {12345}
318do_test cast-3.32.3 {
319  sqlite3_finalize $::STMT
320} {SQLITE_OK}
321
322
323do_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}
330do_test cast-4.2 {
331  db eval {
332    SELECT CAST(a AS integer), a FROM t1;
333  }
334} {0 abc}
335do_test cast-4.3 {
336  db eval {
337    SELECT a, CAST(a AS integer), a FROM t1;
338  }
339} {abc 0 abc}
340do_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
346finish_test
347