Debian Codegraph @

test/capi3c.test

raw
1 # 2006 November 08
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.
12 #
13 # This is a copy of the capi3.test file that has been adapted to
14 # test the new sqlite3_prepare_v2 interface.
15 #
16 # $Id: capi3c.test,v 1.23 2009/07/22 07:27:57 danielk1977 Exp $
17 #
18
19 set testdir [file dirname $argv0]
20 source $testdir/tester.tcl
21 set testprefix capi3c
22
23 # Do not use a codec for tests in this file, as the database file is
24 # manipulated directly using tcl scripts (using the [hexio_write] command).
25 #
26 do_not_use_codec
27
28 # Return the UTF-16 representation of the supplied UTF-8 string $str.
29 # If $nt is true, append two 0x00 bytes as a nul terminator.
30 proc utf16 {str {nt 1}} {
31 set r [encoding convertto unicode $str]
32 if {$nt} {
33 append r "\x00\x00"
34 }
35 return $r
36 }
37
38 # Return the UTF-8 representation of the supplied UTF-16 string $str.
39 proc utf8 {str} {
40 # If $str ends in two 0x00 0x00 bytes, knock these off before
41 # converting to UTF-8 using TCL.
42 binary scan $str \c* vals
43 if {[lindex $vals end]==0 && [lindex $vals end-1]==0} {
44 set str [binary format \c* [lrange $vals 0 end-2]]
45 }
46
47 set r [encoding convertfrom unicode $str]
48 return $r
49 }
50
51 # These tests complement those in capi2.test. They are organized
52 # as follows:
53 #
54 # capi3c-1.*: Test sqlite3_prepare_v2
55 # capi3c-2.*: Test sqlite3_prepare16_v2
56 # capi3c-3.*: Test sqlite3_open
57 # capi3c-4.*: Test sqlite3_open16
58 # capi3c-5.*: Test the various sqlite3_result_* APIs
59 # capi3c-6.*: Test that sqlite3_close fails if there are outstanding VMs.
60 #
61
62 set DB [sqlite3_connection_pointer db]
63
64 do_test capi3c-1.0 {
65 sqlite3_get_autocommit $DB
66 } 1
67 do_test capi3c-1.1 {
68 set STMT [sqlite3_prepare_v2 $DB {SELECT name FROM sqlite_master} -1 TAIL]
69 sqlite3_finalize $STMT
70 set TAIL
71 } {}
72 do_test capi3c-1.2.1 {
73 sqlite3_errcode $DB
74 } {SQLITE_OK}
75 do_test capi3c-1.2.2 {
76 sqlite3_extended_errcode $DB
77 } {SQLITE_OK}
78 do_test capi3c-1.3 {
79 sqlite3_errmsg $DB
80 } {not an error}
81 do_test capi3c-1.4 {
82 set sql {SELECT name FROM sqlite_master;SELECT 10}
83 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]
84 sqlite3_finalize $STMT
85 set TAIL
86 } {SELECT 10}
87 do_test capi3c-1.5 {
88 set sql {SELECT namex FROM sqlite_master}
89 catch {
90 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]
91 }
92 } {1}
93 do_test capi3c-1.6.1 {
94 sqlite3_errcode $DB
95 } {SQLITE_ERROR}
96 do_test capi3c-1.6.2 {
97 sqlite3_extended_errcode $DB
98 } {SQLITE_ERROR}
99 do_test capi3c-1.7 {
100 sqlite3_errmsg $DB
101 } {no such column: namex}
102
103
104 ifcapable {utf16} {
105 do_test capi3c-2.1 {
106 set sql16 [utf16 {SELECT name FROM sqlite_master}]
107 set STMT [sqlite3_prepare16_v2 $DB $sql16 -1 ::TAIL]
108 sqlite3_finalize $STMT
109 utf8 $::TAIL
110 } {}
111 do_test capi3c-2.2 {
112 set sql [utf16 {SELECT name FROM sqlite_master;SELECT 10}]
113 set STMT [sqlite3_prepare16_v2 $DB $sql -1 TAIL]
114 sqlite3_finalize $STMT
115 utf8 $TAIL
116 } {SELECT 10}
117 do_test capi3c-2.3 {
118 set sql [utf16 {SELECT namex FROM sqlite_master}]
119 catch {
120 set STMT [sqlite3_prepare16_v2 $DB $sql -1 TAIL]
121 }
122 } {1}
123 do_test capi3c-2.4.1 {
124 sqlite3_errcode $DB
125 } {SQLITE_ERROR}
126 do_test capi3c-2.4.2 {
127 sqlite3_extended_errcode $DB
128 } {SQLITE_ERROR}
129 do_test capi3c-2.5 {
130 sqlite3_errmsg $DB
131 } {no such column: namex}
132
133 ifcapable schema_pragmas {
134 do_test capi3c-2.6 {
135 execsql {CREATE TABLE tablename(x)}
136 set sql16 [utf16 {PRAGMA table_info("TableName")}]
137 set STMT [sqlite3_prepare16_v2 $DB $sql16 -1 TAIL]
138 sqlite3_step $STMT
139 } SQLITE_ROW
140 do_test capi3c-2.7 {
141 sqlite3_step $STMT
142 } SQLITE_DONE
143 do_test capi3c-2.8 {
144 sqlite3_finalize $STMT
145 } SQLITE_OK
146 }
147
148 } ;# endif utf16
149
150 # rename sqlite3_open sqlite3_open_old
151 # proc sqlite3_open {fname options} {sqlite3_open_new $fname $options}
152
153 do_test capi3c-3.1 {
154 set db2 [sqlite3_open test.db {}]
155 sqlite3_errcode $db2
156 } {SQLITE_OK}
157 # FIX ME: Should test the db handle works.
158 do_test capi3c-3.2 {
159 sqlite3_close $db2
160 } {SQLITE_OK}
161 do_test capi3c-3.3 {
162 catch {
163 set db2 [sqlite3_open /bogus/path/test.db {}]
164 }
165 sqlite3_errcode $db2
166 } {SQLITE_CANTOPEN}
167 do_test capi3c-3.4 {
168 sqlite3_errmsg $db2
169 } {unable to open database file}
170 do_test capi3c-3.5 {
171 sqlite3_close $db2
172 } {SQLITE_OK}
173 if {[clang_sanitize_address]==0} {
174 do_test capi3c-3.6.1-misuse {
175 sqlite3_close $db2
176 } {SQLITE_MISUSE}
177 do_test capi3c-3.6.2-misuse {
178 sqlite3_errmsg $db2
179 } {bad parameter or other API misuse}
180 ifcapable {utf16} {
181 do_test capi3c-3.6.3-misuse {
182 utf8 [sqlite3_errmsg16 $db2]
183 } {bad parameter or other API misuse}
184 }
185 }
186
187 # rename sqlite3_open ""
188 # rename sqlite3_open_old sqlite3_open
189
190 ifcapable {utf16} {
191 do_test capi3c-4.1 {
192 set db2 [sqlite3_open16 [utf16 test.db] {}]
193 sqlite3_errcode $db2
194 } {SQLITE_OK}
195 # FIX ME: Should test the db handle works.
196 do_test capi3c-4.2 {
197 sqlite3_close $db2
198 } {SQLITE_OK}
199 do_test capi3c-4.3 {
200 catch {
201 set db2 [sqlite3_open16 [utf16 /bogus/path/test.db] {}]
202 }
203 sqlite3_errcode $db2
204 } {SQLITE_CANTOPEN}
205 do_test capi3c-4.4 {
206 utf8 [sqlite3_errmsg16 $db2]
207 } {unable to open database file}
208 do_test capi3c-4.5 {
209 sqlite3_close $db2
210 } {SQLITE_OK}
211 } ;# utf16
212
213 # This proc is used to test the following API calls:
214 #
215 # sqlite3_column_count
216 # sqlite3_column_name
217 # sqlite3_column_name16
218 # sqlite3_column_decltype
219 # sqlite3_column_decltype16
220 #
221 # $STMT is a compiled SQL statement. $test is a prefix
222 # to use for test names within this proc. $names is a list
223 # of the column names that should be returned by $STMT.
224 # $decltypes is a list of column declaration types for $STMT.
225 #
226 # Example:
227 #
228 # set STMT [sqlite3_prepare_v2 "SELECT 1, 2, 2;" -1 DUMMY]
229 # check_header test1.1 {1 2 3} {"" "" ""}
230 #
231 proc check_header {STMT test names decltypes} {
232
233 # Use the return value of sqlite3_column_count() to build
234 # a list of column indexes. i.e. If sqlite3_column_count
235 # is 3, build the list {0 1 2}.
236 set ::idxlist [list]
237 set ::numcols [sqlite3_column_count $STMT]
238 for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i}
239
240 # Column names in UTF-8
241 do_test $test.1 {
242 set cnamelist [list]
243 foreach i $idxlist {lappend cnamelist [sqlite3_column_name $STMT $i]}
244 set cnamelist
245 } $names
246
247 # Column names in UTF-16
248 ifcapable {utf16} {
249 do_test $test.2 {
250 set cnamelist [list]
251 foreach i $idxlist {
252 lappend cnamelist [utf8 [sqlite3_column_name16 $STMT $i]]
253 }
254 set cnamelist
255 } $names
256 }
257
258 # Column names in UTF-8
259 do_test $test.3 {
260 set cnamelist [list]
261 foreach i $idxlist {lappend cnamelist [sqlite3_column_name $STMT $i]}
262 set cnamelist
263 } $names
264
265 # Column names in UTF-16
266 ifcapable {utf16} {
267 do_test $test.4 {
268 set cnamelist [list]
269 foreach i $idxlist {
270 lappend cnamelist [utf8 [sqlite3_column_name16 $STMT $i]]
271 }
272 set cnamelist
273 } $names
274 }
275
276 # Column names in UTF-8
277 do_test $test.5 {
278 set cnamelist [list]
279 foreach i $idxlist {lappend cnamelist [sqlite3_column_decltype $STMT $i]}
280 set cnamelist
281 } $decltypes
282
283 # Column declaration types in UTF-16
284 ifcapable {utf16} {
285 do_test $test.6 {
286 set cnamelist [list]
287 foreach i $idxlist {
288 lappend cnamelist [utf8 [sqlite3_column_decltype16 $STMT $i]]
289 }
290 set cnamelist
291 } $decltypes
292 }
293
294
295 # Test some out of range conditions:
296 ifcapable {utf16} {
297 do_test $test.7 {
298 list \
299 [sqlite3_column_name $STMT -1] \
300 [sqlite3_column_name16 $STMT -1] \
301 [sqlite3_column_decltype $STMT -1] \
302 [sqlite3_column_decltype16 $STMT -1] \
303 [sqlite3_column_name $STMT $numcols] \
304 [sqlite3_column_name16 $STMT $numcols] \
305 [sqlite3_column_decltype $STMT $numcols] \
306 [sqlite3_column_decltype16 $STMT $numcols]
307 } {{} {} {} {} {} {} {} {}}
308 }
309 }
310
311 # This proc is used to test the following API calls:
312 #
313 # sqlite3_column_origin_name
314 # sqlite3_column_origin_name16
315 # sqlite3_column_table_name
316 # sqlite3_column_table_name16
317 # sqlite3_column_database_name
318 # sqlite3_column_database_name16
319 #
320 # $STMT is a compiled SQL statement. $test is a prefix
321 # to use for test names within this proc. $names is a list
322 # of the column names that should be returned by $STMT.
323 # $decltypes is a list of column declaration types for $STMT.
324 #
325 # Example:
326 #
327 # set STMT [sqlite3_prepare_v2 "SELECT 1, 2, 2;" -1 DUMMY]
328 # check_header test1.1 {1 2 3} {"" "" ""}
329 #
330 proc check_origin_header {STMT test dbs tables cols} {
331 # If sqlite3_column_origin_name() and friends are not compiled into
332 # this build, this proc is a no-op.
333 ifcapable columnmetadata {
334
335 # Use the return value of sqlite3_column_count() to build
336 # a list of column indexes. i.e. If sqlite3_column_count
337 # is 3, build the list {0 1 2}.
338 set ::idxlist [list]
339 set ::numcols [sqlite3_column_count $STMT]
340 for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i}
341
342 # Database names in UTF-8
343 do_test $test.8 {
344 set cnamelist [list]
345 foreach i $idxlist {
346 lappend cnamelist [sqlite3_column_database_name $STMT $i]
347 }
348 set cnamelist
349 } $dbs
350
351 # Database names in UTF-16
352 ifcapable {utf16} {
353 do_test $test.9 {
354 set cnamelist [list]
355 foreach i $idxlist {
356 lappend cnamelist [utf8 [sqlite3_column_database_name16 $STMT $i]]
357 }
358 set cnamelist
359 } $dbs
360 }
361
362 # Table names in UTF-8
363 do_test $test.10 {
364 set cnamelist [list]
365 foreach i $idxlist {
366 lappend cnamelist [sqlite3_column_table_name $STMT $i]
367 }
368 set cnamelist
369 } $tables
370
371 # Table names in UTF-16
372 ifcapable {utf16} {
373 do_test $test.11 {
374 set cnamelist [list]
375 foreach i $idxlist {
376 lappend cnamelist [utf8 [sqlite3_column_table_name16 $STMT $i]]
377 }
378 set cnamelist
379 } $tables
380 }
381
382 # Origin names in UTF-8
383 do_test $test.12 {
384 set cnamelist [list]
385 foreach i $idxlist {
386 lappend cnamelist [sqlite3_column_origin_name $STMT $i]
387 }
388 set cnamelist
389 } $cols
390
391 # Origin declaration types in UTF-16
392 ifcapable {utf16} {
393 do_test $test.13 {
394 set cnamelist [list]
395 foreach i $idxlist {
396 lappend cnamelist [utf8 [sqlite3_column_origin_name16 $STMT $i]]
397 }
398 set cnamelist
399 } $cols
400 }
401 }
402 }
403
404 # This proc is used to test the following APIs:
405 #
406 # sqlite3_data_count
407 # sqlite3_column_type
408 # sqlite3_column_int
409 # sqlite3_column_text
410 # sqlite3_column_text16
411 # sqlite3_column_double
412 #
413 # $STMT is a compiled SQL statement for which the previous call
414 # to sqlite3_step returned SQLITE_ROW. $test is a prefix to use
415 # for test names within this proc. $types is a list of the
416 # manifest types for the current row. $ints, $doubles and $strings
417 # are lists of the integer, real and string representations of
418 # the values in the current row.
419 #
420 # Example:
421 #
422 # set STMT [sqlite3_prepare_v2 "SELECT 'hello', 1.1, NULL" -1 DUMMY]
423 # sqlite3_step $STMT
424 # check_data test1.2 {TEXT REAL NULL} {0 1 0} {0 1.1 0} {hello 1.1 {}}
425 #
426 proc check_data {STMT test types ints doubles strings} {
427
428 # Use the return value of sqlite3_column_count() to build
429 # a list of column indexes. i.e. If sqlite3_column_count
430 # is 3, build the list {0 1 2}.
431 set ::idxlist [list]
432 set numcols [sqlite3_data_count $STMT]
433 for {set i 0} {$i < $numcols} {incr i} {lappend ::idxlist $i}
434
435 # types
436 do_test $test.1 {
437 set types [list]
438 foreach i $idxlist {lappend types [sqlite3_column_type $STMT $i]}
439 set types
440 } $types
441
442 # Integers
443 do_test $test.2 {
444 set ints [list]
445 foreach i $idxlist {lappend ints [sqlite3_column_int64 $STMT $i]}
446 set ints
447 } $ints
448
449 # bytes
450 set lens [list]
451 foreach i $::idxlist {
452 lappend lens [string length [lindex $strings $i]]
453 }
454 do_test $test.3 {
455 set bytes [list]
456 set lens [list]
457 foreach i $idxlist {
458 lappend bytes [sqlite3_column_bytes $STMT $i]
459 }
460 set bytes
461 } $lens
462
463 # bytes16
464 ifcapable {utf16} {
465 set lens [list]
466 foreach i $::idxlist {
467 lappend lens [expr 2 * [string length [lindex $strings $i]]]
468 }
469 do_test $test.4 {
470 set bytes [list]
471 set lens [list]
472 foreach i $idxlist {
473 lappend bytes [sqlite3_column_bytes16 $STMT $i]
474 }
475 set bytes
476 } $lens
477 }
478
479 # Blob
480 do_test $test.5 {
481 set utf8 [list]
482 foreach i $idxlist {lappend utf8 [sqlite3_column_blob $STMT $i]}
483 set utf8
484 } $strings
485
486 # UTF-8
487 do_test $test.6 {
488 set utf8 [list]
489 foreach i $idxlist {lappend utf8 [sqlite3_column_text $STMT $i]}
490 set utf8
491 } $strings
492
493 # Floats
494 do_test $test.7 {
495 set utf8 [list]
496 foreach i $idxlist {lappend utf8 [sqlite3_column_double $STMT $i]}
497 set utf8
498 } $doubles
499
500 # UTF-16
501 ifcapable {utf16} {
502 do_test $test.8 {
503 set utf8 [list]
504 foreach i $idxlist {lappend utf8 [utf8 [sqlite3_column_text16 $STMT $i]]}
505 set utf8
506 } $strings
507 }
508
509 # Integers
510 do_test $test.9 {
511 set ints [list]
512 foreach i $idxlist {lappend ints [sqlite3_column_int $STMT $i]}
513 set ints
514 } $ints
515
516 # Floats
517 do_test $test.10 {
518 set utf8 [list]
519 foreach i $idxlist {lappend utf8 [sqlite3_column_double $STMT $i]}
520 set utf8
521 } $doubles
522
523 # UTF-8
524 do_test $test.11 {
525 set utf8 [list]
526 foreach i $idxlist {lappend utf8 [sqlite3_column_text $STMT $i]}
527 set utf8
528 } $strings
529
530 # Types
531 do_test $test.12 {
532 set types [list]
533 foreach i $idxlist {lappend types [sqlite3_column_type $STMT $i]}
534 set types
535 } $types
536
537 # Test that an out of range request returns the equivalent of NULL
538 do_test $test.13 {
539 sqlite3_column_int $STMT -1
540 } {0}
541 do_test $test.13 {
542 sqlite3_column_text $STMT -1
543 } {}
544
545 }
546
547 ifcapable !floatingpoint {
548 finish_test
549 return
550 }
551
552 do_test capi3c-5.0 {
553 execsql {
554 CREATE TABLE t1(a VARINT, b BLOB, c VARCHAR(16));
555 INSERT INTO t1 VALUES(1, 2, 3);
556 INSERT INTO t1 VALUES('one', 'two', NULL);
557 INSERT INTO t1 VALUES(1.2, 1.3, 1.4);
558 }
559 set sql "SELECT * FROM t1"
560 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]
561 sqlite3_column_count $STMT
562 } 3
563
564 check_header $STMT capi3c-5.1 {a b c} {VARINT BLOB VARCHAR(16)}
565 check_origin_header $STMT capi3c-5.1 {main main main} {t1 t1 t1} {a b c}
566 do_test capi3c-5.2 {
567 sqlite3_step $STMT
568 } SQLITE_ROW
569
570 check_header $STMT capi3c-5.3 {a b c} {VARINT BLOB VARCHAR(16)}
571 check_origin_header $STMT capi3c-5.3 {main main main} {t1 t1 t1} {a b c}
572 check_data $STMT capi3c-5.4 {INTEGER INTEGER TEXT} {1 2 3} {1.0 2.0 3.0} {1 2 3}
573
574 do_test capi3c-5.5 {
575 sqlite3_step $STMT
576 } SQLITE_ROW
577
578 check_header $STMT capi3c-5.6 {a b c} {VARINT BLOB VARCHAR(16)}
579 check_origin_header $STMT capi3c-5.6 {main main main} {t1 t1 t1} {a b c}
580 check_data $STMT capi3c-5.7 {TEXT TEXT NULL} {0 0 0} {0.0 0.0 0.0} {one two {}}
581
582 do_test capi3c-5.8 {
583 sqlite3_step $STMT
584 } SQLITE_ROW
585
586 check_header $STMT capi3c-5.9 {a b c} {VARINT BLOB VARCHAR(16)}
587 check_origin_header $STMT capi3c-5.9 {main main main} {t1 t1 t1} {a b c}
588 check_data $STMT capi3c-5.10 {FLOAT FLOAT TEXT} {1 1 1} {1.2 1.3 1.4} {1.2 1.3 1.4}
589
590 do_test capi3c-5.11 {
591 sqlite3_step $STMT
592 } SQLITE_DONE
593
594 do_test capi3c-5.12 {
595 sqlite3_finalize $STMT
596 } SQLITE_OK
597
598 do_test capi3c-5.20 {
599 set sql "SELECT a, sum(b), max(c) FROM t1 GROUP BY a"
600 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]
601 sqlite3_column_count $STMT
602 } 3
603
604 check_header $STMT capi3c-5.21 {a sum(b) max(c)} {VARINT {} {}}
605 check_origin_header $STMT capi3c-5.22 {main {} {}} {t1 {} {}} {a {} {}}
606 do_test capi3c-5.23 {
607 sqlite3_finalize $STMT
608 } SQLITE_OK
609
610
611 set ::ENC [execsql {pragma encoding}]
612 db close
613
614 do_test capi3c-6.0 {
615 sqlite3 db test.db
616 set DB [sqlite3_connection_pointer db]
617 if {[sqlite3 -has-codec]==0} { sqlite3_key $DB xyzzy }
618 set sql {SELECT a FROM t1 order by rowid}
619 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]
620 expr 0
621 } {0}
622 do_test capi3c-6.1 {
623 db cache flush
624 sqlite3_close $DB
625 } {SQLITE_BUSY}
626 do_test capi3c-6.2 {
627 sqlite3_step $STMT
628 } {SQLITE_ROW}
629 check_data $STMT capi3c-6.3 {INTEGER} {1} {1.0} {1}
630 do_test capi3c-6.3 {
631 sqlite3_finalize $STMT
632 } {SQLITE_OK}
633 if {[clang_sanitize_address]==0} {
634 do_test capi3c-6.4 {
635 db cache flush
636 sqlite3_close $DB
637 } {SQLITE_OK}
638 do_test capi3c-6.99-misuse {
639 db close
640 } {}
641 } else {
642 db close
643 }
644
645 # This procedure sets the value of the file-format in file 'test.db'
646 # to $newval. Also, the schema cookie is incremented.
647 #
648 proc set_file_format {newval} {
649 hexio_write test.db 44 [hexio_render_int32 $newval]
650 set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
651 incr schemacookie
652 hexio_write test.db 40 [hexio_render_int32 $schemacookie]
653 return {}
654 }
655
656 # This procedure returns the value of the file-format in file 'test.db'.
657 #
658 proc get_file_format {{fname test.db}} {
659 return [hexio_get_int [hexio_read $fname 44 4]]
660 }
661
662 if {![sqlite3 -has-codec]} {
663 # Test what happens when the library encounters a newer file format.
664 do_test capi3c-7.1 {
665 set_file_format 5
666 } {}
667 do_test capi3c-7.2 {
668 catch { sqlite3 db test.db }
669 catchsql {
670 SELECT * FROM sqlite_master;
671 }
672 } {1 {unsupported file format}}
673 db close
674 }
675
676 if {![sqlite3 -has-codec]} {
677 # Now test that the library correctly handles bogus entries in the
678 # sqlite_master table (schema corruption).
679 do_test capi3c-8.1 {
680 forcedelete test.db test.db-journal
681 sqlite3 db test.db
682 execsql {
683 CREATE TABLE t1(a);
684 }
685 db close
686 } {}
687 do_test capi3c-8.2 {
688 sqlite3 db test.db
689 sqlite3_db_config db DEFENSIVE 0
690 execsql {
691 PRAGMA writable_schema=ON;
692 INSERT INTO sqlite_master VALUES(NULL,NULL,NULL,NULL,NULL);
693 }
694 db close
695 } {}
696 do_test capi3c-8.3 {
697 catch { sqlite3 db test.db }
698 catchsql {
699 SELECT * FROM sqlite_master;
700 }
701 } {1 {malformed database schema (?)}}
702 do_test capi3c-8.4 {
703 # Build a 5-field row record. The first field is a string 'table', and
704 # subsequent fields are all NULL.
705 db close
706 forcedelete test.db test.db-journal
707 sqlite3 db test.db
708 sqlite3_db_config db DEFENSIVE 0
709 execsql {
710 CREATE TABLE t1(a);
711 PRAGMA writable_schema=ON;
712 INSERT INTO sqlite_master VALUES('table',NULL,NULL,NULL,NULL);
713 }
714 db close
715 } {};
716 do_test capi3c-8.5 {
717 catch { sqlite3 db test.db }
718 catchsql {
719 SELECT * FROM sqlite_master;
720 }
721 } {1 {malformed database schema (?)}}
722 db close
723 }
724 forcedelete test.db
725 forcedelete test.db-journal
726
727
728 # Test the english language string equivalents for sqlite error codes
729 set code2english [list \
730 SQLITE_OK {not an error} \
731 SQLITE_ERROR {SQL logic error} \
732 SQLITE_PERM {access permission denied} \
733 SQLITE_ABORT {query aborted} \
734 SQLITE_BUSY {database is locked} \
735 SQLITE_LOCKED {database table is locked} \
736 SQLITE_NOMEM {out of memory} \
737 SQLITE_READONLY {attempt to write a readonly database} \
738 SQLITE_INTERRUPT {interrupted} \
739 SQLITE_IOERR {disk I/O error} \
740 SQLITE_CORRUPT {database disk image is malformed} \
741 SQLITE_FULL {database or disk is full} \
742 SQLITE_CANTOPEN {unable to open database file} \
743 SQLITE_EMPTY {unknown error} \
744 SQLITE_SCHEMA {database schema has changed} \
745 SQLITE_CONSTRAINT {constraint failed} \
746 SQLITE_MISMATCH {datatype mismatch} \
747 SQLITE_MISUSE {bad parameter or other API misuse} \
748 SQLITE_AUTH {authorization denied} \
749 SQLITE_RANGE {column index out of range} \
750 SQLITE_NOTADB {file is not a database} \
751 unknownerror {unknown error} \
752 ]
753
754 set test_number 1
755 foreach {code english} $code2english {
756 do_test capi3c-9.$test_number "sqlite3_test_errstr $code" $english
757 incr test_number
758 }
759
760 # Test the error message when a "real" out of memory occurs.
761 if { [permutation] != "nofaultsim" } {
762 do_test capi3c-10-1 {
763 sqlite3 db test.db
764 set DB [sqlite3_connection_pointer db]
765 sqlite3_memdebug_fail 0
766 catchsql {
767 select * from sqlite_master;
768 }
769 } {1 {out of memory}}
770 do_test capi3c-10-2 {
771 sqlite3_errmsg $::DB
772 } {out of memory}
773 ifcapable {utf16} {
774 do_test capi3c-10-3 {
775 utf8 [sqlite3_errmsg16 $::DB]
776 } {out of memory}
777 }
778 db close
779 sqlite3_memdebug_fail -1
780 }
781
782 # The following tests - capi3c-11.* - test that a COMMIT or ROLLBACK
783 # statement issued while there are still outstanding VMs that are part of
784 # the transaction fails.
785 sqlite3 db test.db
786 set DB [sqlite3_connection_pointer db]
787 sqlite_register_test_function $DB func
788 do_test capi3c-11.1 {
789 execsql {
790 BEGIN;
791 CREATE TABLE t1(a, b);
792 INSERT INTO t1 VALUES(1, 'int');
793 INSERT INTO t1 VALUES(2, 'notatype');
794 }
795 } {}
796 do_test capi3c-11.1.1 {
797 sqlite3_get_autocommit $DB
798 } 0
799 do_test capi3c-11.2 {
800 set STMT [sqlite3_prepare_v2 $DB "SELECT func(b, a) FROM t1" -1 TAIL]
801 sqlite3_step $STMT
802 } {SQLITE_ROW}
803
804 # As of 3.6.5 a COMMIT is OK during while a query is still running -
805 # as long as it is a read-only query and not an incremental BLOB write.
806 #
807 do_test capi3-11.3.1 {
808 catchsql {
809 COMMIT;
810 }
811 } {0 {}}
812 do_test capi3-11.3.2 {
813 sqlite3_extended_errcode $DB
814 } {SQLITE_OK}
815 do_test capi3-11.3.3 {
816 sqlite3_get_autocommit $DB
817 } 1
818 do_test capi3-11.3.4 {
819 db eval {PRAGMA lock_status}
820 } {main shared temp closed}
821
822 do_test capi3c-11.4 {
823 sqlite3_step $STMT
824 } {SQLITE_ERROR}
825 do_test capi3c-11.5 {
826 sqlite3_finalize $STMT
827 } {SQLITE_ERROR}
828 do_test capi3c-11.6 {
829 catchsql {
830 SELECT * FROM t1;
831 }
832 } {0 {1 int 2 notatype}}
833 do_test capi3c-11.7 {
834 sqlite3_get_autocommit $DB
835 } 1
836 do_test capi3c-11.8 {
837 execsql {
838 CREATE TABLE t2(a);
839 INSERT INTO t2 VALUES(1);
840 INSERT INTO t2 VALUES(2);
841 BEGIN;
842 INSERT INTO t2 VALUES(3);
843 }
844 } {}
845 do_test capi3c-11.8.1 {
846 sqlite3_get_autocommit $DB
847 } 0
848 do_test capi3c-11.9 {
849 set STMT [sqlite3_prepare_v2 $DB "SELECT a FROM t2" -1 TAIL]
850 sqlite3_step $STMT
851 } {SQLITE_ROW}
852 do_test capi3c-11.9.1 {
853 sqlite3_get_autocommit $DB
854 } 0
855 do_test capi3c-11.9.2 {
856 catchsql {
857 ROLLBACK;
858 }
859 } {0 {}}
860 do_test capi3c-11.9.3 {
861 sqlite3_get_autocommit $DB
862 } 1
863 do_test capi3c-11.10 {
864 sqlite3_step $STMT
865 } {SQLITE_ROW}
866 do_test capi3c-11.11 {
867 sqlite3_step $STMT
868 } {SQLITE_DONE}
869 ifcapable !autoreset {
870 do_test capi3c-11.12armor {
871 sqlite3_step $STMT
872 sqlite3_step $STMT
873 } {SQLITE_MISUSE}
874 } else {
875 do_test capi3c-11.12 {
876 sqlite3_step $STMT
877 sqlite3_step $STMT
878 } {SQLITE_ROW}
879 }
880 do_test capi3c-11.13 {
881 sqlite3_finalize $STMT
882 } {SQLITE_OK}
883 do_test capi3c-11.14 {
884 execsql {
885 SELECT a FROM t2;
886 }
887 } {1 2}
888 do_test capi3c-11.14.1 {
889 sqlite3_get_autocommit $DB
890 } 1
891 do_test capi3c-11.15 {
892 catchsql {
893 ROLLBACK;
894 }
895 } {1 {cannot rollback - no transaction is active}}
896 do_test capi3c-11.15.1 {
897 sqlite3_get_autocommit $DB
898 } 1
899 do_test capi3c-11.16 {
900 execsql {
901 SELECT a FROM t2;
902 }
903 } {1 2}
904
905 # Sanity check on the definition of 'outstanding VM'. This means any VM
906 # that has had sqlite3_step() called more recently than sqlite3_finalize() or
907 # sqlite3_reset(). So a VM that has just been prepared or reset does not
908 # count as an active VM.
909 do_test capi3c-11.17 {
910 execsql {
911 BEGIN;
912 }
913 } {}
914 do_test capi3c-11.18 {
915 set STMT [sqlite3_prepare_v2 $DB "SELECT a FROM t1" -1 TAIL]
916 catchsql {
917 COMMIT;
918 }
919 } {0 {}}
920 do_test capi3c-11.19 {
921 sqlite3_step $STMT
922 } {SQLITE_ROW}
923 do_test capi3c-11.20 {
924 catchsql {
925 BEGIN;
926 COMMIT;
927 }
928 } {0 {}}
929 do_test capi3c-11.20 {
930 sqlite3_reset $STMT
931 catchsql {
932 COMMIT;
933 }
934 } {1 {cannot commit - no transaction is active}}
935 do_test capi3c-11.21 {
936 sqlite3_finalize $STMT
937 } {SQLITE_OK}
938
939 # The following tests - capi3c-12.* - check that its Ok to start a
940 # transaction while other VMs are active, and that its Ok to execute
941 # atomic updates in the same situation
942 #
943 do_test capi3c-12.1 {
944 set STMT [sqlite3_prepare_v2 $DB "SELECT a FROM t2" -1 TAIL]
945 sqlite3_step $STMT
946 } {SQLITE_ROW}
947 do_test capi3c-12.2 {
948 catchsql {
949 INSERT INTO t1 VALUES(3, NULL);
950 }
951 } {0 {}}
952 do_test capi3c-12.3 {
953 catchsql {
954 INSERT INTO t2 VALUES(4);
955 }
956 } {0 {}}
957 do_test capi3c-12.4 {
958 catchsql {
959 BEGIN;
960 INSERT INTO t1 VALUES(4, NULL);
961 }
962 } {0 {}}
963 do_test capi3c-12.5 {
964 sqlite3_step $STMT
965 } {SQLITE_ROW}
966 do_test capi3c-12.5.1 {
967 sqlite3_step $STMT
968 } {SQLITE_ROW}
969 do_test capi3c-12.6 {
970 sqlite3_step $STMT
971 } {SQLITE_DONE}
972 do_test capi3c-12.7 {
973 sqlite3_finalize $STMT
974 } {SQLITE_OK}
975 do_test capi3c-12.8 {
976 execsql {
977 COMMIT;
978 SELECT a FROM t1;
979 }
980 } {1 2 3 4}
981
982 # Test cases capi3c-13.* test the sqlite3_clear_bindings() and
983 # sqlite3_sleep APIs.
984 #
985 if {[llength [info commands sqlite3_clear_bindings]]>0} {
986 do_test capi3c-13.1 {
987 execsql {
988 DELETE FROM t1;
989 }
990 set STMT [sqlite3_prepare_v2 $DB "INSERT INTO t1 VALUES(?, ?)" -1 TAIL]
991 sqlite3_step $STMT
992 } {SQLITE_DONE}
993 do_test capi3c-13.2 {
994 sqlite3_reset $STMT
995 sqlite3_bind_text $STMT 1 hello 5
996 sqlite3_bind_text $STMT 2 world 5
997 sqlite3_step $STMT
998 } {SQLITE_DONE}
999 do_test capi3c-13.3 {
1000 sqlite3_reset $STMT
1001 sqlite3_clear_bindings $STMT
1002 sqlite3_step $STMT
1003 } {SQLITE_DONE}
1004 do_test capi3c-13-4 {
1005 sqlite3_finalize $STMT
1006 execsql {
1007 SELECT * FROM t1;
1008 }
1009 } {{} {} hello world {} {}}
1010 }
1011 if {[llength [info commands sqlite3_sleep]]>0} {
1012 do_test capi3c-13-5 {
1013 set ms [sqlite3_sleep 80]
1014 expr {$ms==80 || $ms==1000}
1015 } {1}
1016 }
1017
1018 # Ticket #1219: Make sure binding APIs can handle a NULL pointer.
1019 #
1020 do_test capi3c-14.1 {
1021 set rc [catch {sqlite3_bind_text 0 1 hello 5} msg]
1022 lappend rc $msg
1023 } {1 SQLITE_MISUSE}
1024
1025 # Ticket #1650: Honor the nBytes parameter to sqlite3_prepare.
1026 #
1027 do_test capi3c-15.1 {
1028 set sql {SELECT * FROM t2}
1029 set nbytes [string length $sql]
1030 append sql { WHERE a==1}
1031 set STMT [sqlite3_prepare_v2 $DB $sql $nbytes TAIL]
1032 sqlite3_step $STMT
1033 sqlite3_column_int $STMT 0
1034 } {1}
1035 do_test capi3c-15.2 {
1036 sqlite3_step $STMT
1037 sqlite3_column_int $STMT 0
1038 } {2}
1039 do_test capi3c-15.3 {
1040 sqlite3_finalize $STMT
1041 } {SQLITE_OK}
1042
1043 # Make sure code is always generated even if an IF EXISTS or
1044 # IF NOT EXISTS clause is present that the table does not or
1045 # does exists. That way we will always have a prepared statement
1046 # to expire when the schema changes.
1047 #
1048 do_test capi3c-16.1 {
1049 set sql {DROP TABLE IF EXISTS t3}
1050 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]
1051 sqlite3_finalize $STMT
1052 expr {$STMT!=""}
1053 } {1}
1054 do_test capi3c-16.2 {
1055 set sql {CREATE TABLE IF NOT EXISTS t1(x,y)}
1056 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]
1057 sqlite3_finalize $STMT
1058 expr {$STMT!=""}
1059 } {1}
1060
1061 # But still we do not generate code if there is no SQL
1062 #
1063 do_test capi3c-16.3 {
1064 set STMT [sqlite3_prepare_v2 $DB {} -1 TAIL]
1065 sqlite3_finalize $STMT
1066 expr {$STMT==""}
1067 } {1}
1068 do_test capi3c-16.4 {
1069 set STMT [sqlite3_prepare_v2 $DB {;} -1 TAIL]
1070 sqlite3_finalize $STMT
1071 expr {$STMT==""}
1072 } {1}
1073
1074 # Ticket #2154.
1075 #
1076 do_test capi3c-17.1 {
1077 set STMT [sqlite3_prepare_v2 $DB {SELECT max(a) FROM t2} -1 TAIL]
1078 sqlite3_step $STMT
1079 } SQLITE_ROW
1080 do_test capi3c-17.2 {
1081 sqlite3_column_int $STMT 0
1082 } 4
1083 do_test capi3c-17.3 {
1084 sqlite3_step $STMT
1085 } SQLITE_DONE
1086 do_test capi3c-17.4 {
1087 sqlite3_reset $STMT
1088 db eval {CREATE INDEX i2 ON t2(a)}
1089 sqlite3_step $STMT
1090 } SQLITE_ROW
1091 do_test capi3c-17.5 {
1092 sqlite3_column_int $STMT 0
1093 } 4
1094 do_test capi3c-17.6 {
1095 sqlite3_step $STMT
1096 } SQLITE_DONE
1097 do_test capi3c-17.7 {
1098 sqlite3_reset $STMT
1099 db eval {DROP INDEX i2}
1100 sqlite3_step $STMT
1101 } SQLITE_ROW
1102 do_test capi3c-17.8 {
1103 sqlite3_column_int $STMT 0
1104 } 4
1105 do_test capi3c-17.9 {
1106 sqlite3_step $STMT
1107 } SQLITE_DONE
1108 do_test capi3c-17.10 {
1109 sqlite3_finalize $STMT
1110 set STMT [sqlite3_prepare_v2 $DB {SELECT b FROM t1 WHERE a=?} -1 TAIL]
1111 sqlite3_bind_int $STMT 1 2
1112 db eval {
1113 DELETE FROM t1;
1114 INSERT INTO t1 VALUES(1,'one');
1115 INSERT INTO t1 VALUES(2,'two');
1116 INSERT INTO t1 VALUES(3,'three');
1117 INSERT INTO t1 VALUES(4,'four');
1118 }
1119 sqlite3_step $STMT
1120 } SQLITE_ROW
1121 do_test capi3c-17.11 {
1122 sqlite3_column_text $STMT 0
1123 } two
1124 do_test capi3c-17.12 {
1125 sqlite3_step $STMT
1126 } SQLITE_DONE
1127 do_test capi3c-17.13 {
1128 sqlite3_reset $STMT
1129 db eval {CREATE INDEX i1 ON t1(a)}
1130 sqlite3_step $STMT
1131 } SQLITE_ROW
1132 do_test capi3c-17.14 {
1133 sqlite3_column_text $STMT 0
1134 } two
1135 do_test capi3c-17.15 {
1136 sqlite3_step $STMT
1137 } SQLITE_DONE
1138 do_test capi3c-17.16 {
1139 sqlite3_reset $STMT
1140 db eval {DROP INDEX i1}
1141 sqlite3_step $STMT
1142 } SQLITE_ROW
1143 do_test capi3c-17.17 {
1144 sqlite3_column_text $STMT 0
1145 } two
1146 do_test capi3c-17.18 {
1147 sqlite3_step $STMT
1148 } SQLITE_DONE
1149 do_test capi3c-17.99 {
1150 sqlite3_finalize $STMT
1151 } SQLITE_OK
1152
1153 # On the mailing list it has been reported that finalizing after
1154 # an SQLITE_BUSY return leads to a segfault. Here we test that case.
1155 #
1156 do_test capi3c-18.1 {
1157 sqlite3 db2 test.db
1158 set STMT [sqlite3_prepare_v2 $DB {SELECT max(a) FROM t1} -1 TAIL]
1159 sqlite3_step $STMT
1160 } SQLITE_ROW
1161 do_test capi3c-18.2 {
1162 sqlite3_column_int $STMT 0
1163 } 4
1164 do_test capi3c-18.3 {
1165 sqlite3_reset $STMT
1166 db2 eval {BEGIN EXCLUSIVE}
1167 sqlite3_step $STMT
1168 } SQLITE_BUSY
1169 do_test capi3c-18.4 {
1170 sqlite3_finalize $STMT
1171 } SQLITE_BUSY
1172 do_test capi3c-18.5 {
1173 db2 eval {COMMIT}
1174 db2 close
1175 } {}
1176
1177 # Ticket #2158. The sqlite3_step() will still return SQLITE_SCHEMA
1178 # if the database schema changes in a way that makes the statement
1179 # no longer valid.
1180 #
1181 do_test capi3c-19.1 {
1182 db eval {
1183 CREATE TABLE t3(x,y);
1184 INSERT INTO t3 VALUES(1,2);
1185 }
1186 set STMT [sqlite3_prepare_v2 $DB {SELECT * FROM t3} -1 TAIL]
1187 sqlite3_step $STMT
1188 } SQLITE_ROW
1189 do_test capi3c-19.2 {
1190 sqlite3_column_int $STMT 0
1191 } 1
1192 do_test capi3c-19.3 {
1193 sqlite3_step $STMT
1194 } SQLITE_DONE
1195 do_test capi3c-19.4 {
1196 sqlite3_reset $STMT
1197 db eval {DROP TABLE t3}
1198 sqlite3_step $STMT
1199 } SQLITE_ERROR
1200 do_test capi3c-19.4.1 {
1201 sqlite3_errmsg $DB
1202 } {no such table: t3}
1203 ifcapable deprecated {
1204 do_test capi3c-19.4.2 {
1205 sqlite3_expired $STMT
1206 } 1
1207 }
1208 do_test capi3c-19.4.3 {
1209 sqlite3_errmsg $DB
1210 } {no such table: t3}
1211 ifcapable deprecated {
1212 do_test capi3c-19.4.4 {
1213 sqlite3_expired 0
1214 } 1
1215 }
1216 do_test capi3c-19.5 {
1217 sqlite3_reset $STMT
1218 db eval {
1219 CREATE TABLE t3(x,y);
1220 INSERT INTO t3 VALUES(1,2);
1221 }
1222 sqlite3_step $STMT
1223 } SQLITE_ROW
1224 ifcapable deprecated {
1225 do_test capi3c-19.5.2 {
1226 sqlite3_expired $STMT
1227 } 0
1228 }
1229 do_test capi3c-19.6 {
1230 sqlite3_column_int $STMT 1
1231 } 2
1232 do_test capi3c-19.99 {
1233 sqlite3_finalize $STMT
1234 } SQLITE_OK
1235
1236 # Make sure a change in a separate database connection does not
1237 # cause an SQLITE_SCHEMA return.
1238 #
1239 do_test capi3c-20.1 {
1240 set STMT [sqlite3_prepare_v2 $DB {SELECT * FROM t3} -1 TAIL]
1241 sqlite3 db2 test.db
1242 db2 eval {CREATE TABLE t4(x)}
1243 sqlite3_step $STMT
1244 } SQLITE_ROW
1245 do_test capi3c-20.2 {
1246 sqlite3_column_int $STMT 1
1247 } 2
1248 do_test capi3c-20.3 {
1249 sqlite3_step $STMT
1250 } SQLITE_DONE
1251 do_test capi3c-20.4 {
1252 db2 close
1253 sqlite3_finalize $STMT
1254 } SQLITE_OK
1255
1256 # Test that sqlite3_step() sets the database error code correctly.
1257 # See ticket #2497.
1258 #
1259 ifcapable progress {
1260 do_test capi3c-21.1 {
1261 set STMT [sqlite3_prepare_v2 $DB {SELECT * FROM t3} -1 TAIL]
1262 db progress 5 "expr 1"
1263 sqlite3_step $STMT
1264 } {SQLITE_INTERRUPT}
1265 do_test capi3c-21.2 {
1266 sqlite3_extended_errcode $DB
1267 } {SQLITE_INTERRUPT}
1268 do_test capi3c-21.3 {
1269 sqlite3_finalize $STMT
1270 } {SQLITE_INTERRUPT}
1271 do_test capi3c-21.4 {
1272 db progress
1273 set STMT [sqlite3_prepare $DB {SELECT * FROM t3} -1 TAIL]
1274 db progress 5 "expr 1"
1275 sqlite3_step $STMT
1276 } {SQLITE_ERROR}
1277 do_test capi3c-21.5 {
1278 sqlite3_errcode $DB
1279 } {SQLITE_ERROR}
1280 do_test capi3c-21.6 {
1281 sqlite3_finalize $STMT
1282 } {SQLITE_INTERRUPT}
1283 do_test capi3c-21.7 {
1284 sqlite3_errcode $DB
1285 } {SQLITE_INTERRUPT}
1286 do_test capi3c-21.8 {
1287 sqlite3_extended_errcode $DB
1288 } {SQLITE_INTERRUPT}
1289 }
1290
1291 # Make sure sqlite3_result_error_code() returns the correct error code.
1292 # See ticket #2940
1293 #
1294 do_test capi3c-22.1 {
1295 db progress 0 {}
1296 set STMT [sqlite3_prepare_v2 db {SELECT test_error('the message',3)} -1 TAIL]
1297 sqlite3_step $STMT
1298 } {SQLITE_PERM}
1299 sqlite3_finalize $STMT
1300 do_test capi3c-22.2 {
1301 set STMT [sqlite3_prepare_v2 db {SELECT test_error('the message',4)} -1 TAIL]
1302 sqlite3_step $STMT
1303 } {SQLITE_ABORT}
1304 sqlite3_finalize $STMT
1305 do_test capi3c-22.3 {
1306 set STMT [sqlite3_prepare_v2 db {SELECT test_error('the message',16)} -1 TAIL]
1307 sqlite3_step $STMT
1308 } {SQLITE_EMPTY}
1309 sqlite3_finalize $STMT
1310
1311 # For a multi-column result set where the same table column is repeated
1312 # in multiple columns of the output, verify that doing a UTF-8 to UTF-16
1313 # conversion (or vice versa) on one column does not change the value of
1314 # the second.
1315 #
1316 ifcapable utf16 {
1317 do_test capi3c-23.1 {
1318 set STMT [sqlite3_prepare_v2 db {SELECT b,b,b,b FROM t1} -1 TAIL]
1319 sqlite3_step $STMT
1320 } {SQLITE_ROW}
1321 do_test capi3c-23.2 {
1322 sqlite3_column_text16 $STMT 0
1323 sqlite3_column_text $STMT 1
1324 } {one}
1325 do_test capi3c-23.3 {
1326 sqlite3_column_text16 $STMT 2
1327 sqlite3_column_text $STMT 3
1328 } {one}
1329 sqlite3_finalize $STMT
1330 do_test capi3c-23.4 {
1331 set STMT [sqlite3_prepare_v2 db {SELECT b||'x',b,b,b FROM t1} -1 TAIL]
1332 sqlite3_step $STMT
1333 } {SQLITE_ROW}
1334 do_test capi3c-23.5 {
1335 sqlite3_column_text16 $STMT 0
1336 sqlite3_column_text $STMT 1
1337 } {one}
1338 do_test capi3c-23.6 {
1339 sqlite3_column_text16 $STMT 2
1340 sqlite3_column_text $STMT 3
1341 } {one}
1342 sqlite3_finalize $STMT
1343 }
1344
1345 # Test decltype on some SELECT statements that contain sub-selects.
1346 #
1347 proc decltype {zSql} {
1348 set ret [list]
1349 set STMT [sqlite3_prepare_v2 db $zSql -1 TAIL]
1350 for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
1351 lappend ret [sqlite3_column_decltype $STMT $i]
1352 }
1353 sqlite3_finalize $STMT
1354 return $ret
1355 }
1356 do_test capi3c-24.1 {
1357 execsql { CREATE TABLE t5(a INTEGER, b STRING, c DATETIME) }
1358 decltype {SELECT * FROM t5}
1359 } {INTEGER STRING DATETIME}
1360 do_test capi3c-24.2 {
1361 decltype {SELECT (SELECT c) FROM t5}
1362 } {DATETIME}
1363 do_test capi3c-24.3 {
1364 decltype {SELECT (SELECT * FROM (SELECT c)) FROM t5}
1365 } {DATETIME}
1366 do_test capi3c-24.4 {
1367 decltype {SELECT * FROM (SELECT * FROM t5 ORDER BY c LIMIT 1) ORDER BY b}
1368 } {INTEGER STRING DATETIME}
1369 do_test capi3c-24.5 {
1370 decltype {
1371 SELECT (SELECT x FROM (SELECT c AS x))
1372 FROM (SELECT * FROM t5 ORDER BY c LIMIT 1) ORDER BY b
1373 }
1374 } {DATETIME}
1375 do_test capi3c-24.3 {
1376 decltype {SELECT (SELECT x FROM (SELECT t5.a AS x)) FROM t5}
1377 } {INTEGER}
1378
1379
1380 # Further tests of sqlite3_column_decltype():
1381 #
1382 do_execsql_test 25.0 {
1383 CREATE TABLE t11(a VARCHAR(10), b INTEGER);
1384 CREATE TABLE t12(a VARCHAR(15), b FLOAT);
1385 }
1386
1387 foreach {tn sql} {
1388 1 "SELECT * FROM t11 UNION ALL SELECT * FROM t12"
1389 2 "SELECT * FROM t11 UNION SELECT * FROM t12"
1390 3 "SELECT * FROM t11 EXCEPT SELECT * FROM t12"
1391 4 "SELECT * FROM t11 INTERSECT SELECT * FROM t12"
1392
1393 5 "SELECT * FROM t11 UNION ALL SELECT * FROM t12 ORDER BY 1"
1394 6 "SELECT * FROM t11 UNION SELECT * FROM t12 ORDER BY 1"
1395 7 "SELECT * FROM t11 EXCEPT SELECT * FROM t12 ORDER BY 1"
1396 8 "SELECT * FROM t11 INTERSECT SELECT * FROM t12 ORDER BY 1"
1397 } {
1398 do_test 25.$tn { decltype $sql } {VARCHAR(10) INTEGER}
1399 }
1400
1401 finish_test