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.1.1.1 2012/02/21 17:04:16 misho 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