Debian Codegraph @

test/carray02.test

raw
1 # 2025-10-07
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 #
12 # This file implements tests for CARRAY extension
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix carray02
18
19 ifcapable !vtab {
20 finish_test
21 return
22 }
23
24 proc run_stmt {stmt} {
25 set r {}
26 while {[sqlite3_step $stmt]=="SQLITE_ROW"} {
27 for {set i 0} {$i<[sqlite3_data_count $stmt]} {incr i} {
28 lappend r [sqlite3_column_text $stmt $i]
29 }
30 }
31 sqlite3_reset $stmt
32 return $r
33 }
34
35 # Bind some text values using sqlite3_carray_bind(). Including a NULL pointer.
36 #
37 set STMT [
38 sqlite3_prepare_v2 db "SELECT value, value IS NULL FROM carray(?)" -1 TAIL
39 ]
40 do_test 1.0 {
41 sqlite3_carray_bind -transient -text $STMT 1 "abc" NULL "def"
42 run_stmt $STMT
43 } {abc 0 {} 1 def 0}
44 sqlite3_finalize $STMT
45
46 # Pass bad values as the "flags" parameter to sqlite3_carray_bind().
47 #
48 set STMT [sqlite3_prepare_v2 db "SELECT value FROM carray(?)" -1 TAIL]
49 do_test 2.0 {
50 list [catch {sqlite3_carray_bind -flags 5 -int32 $STMT 1 1 2 3 4} msg] $msg
51 } {1 {SQL logic error}}
52 do_test 2.1 {
53 list [catch {sqlite3_carray_bind -flags -1 -int32 $STMT 1 1 2 3 4} msg] $msg
54 } {1 {SQL logic error}}
55 sqlite3_finalize $STMT
56
57 # In each of the following, carray(?) contains integer values 1 to 5, bound
58 # using sqlite3_carray_bind().
59 #
60 foreach {tn sql res} {
61 1 { SELECT value FROM carray(?) WHERE value>2 } {3 4 5}
62 2 {
63 WITH s(i) AS ( VALUES(1) UNION ALL VALUES(2) )
64 SELECT i, value FROM s, carray(?) WHERE i=value;
65 } {1 1 2 2}
66 } {
67 do_test 2.2.$tn {
68 set STMT [sqlite3_prepare_v2 db $sql -1 TAIL]
69 sqlite3_carray_bind -int32 $STMT 1 1 2 3 4 5
70 set r [run_stmt $STMT]
71 sqlite3_finalize $STMT
72 set r
73 } $res
74 }
75
76 # In each of the following, ? is bound to an int array containing 1 to 5.
77 # Bound using C code like:
78 #
79 # sqlite3_bind_pointer(pStmt, 1, aInt, "carray", SQLITE_TRANSIENT)
80 #
81 foreach {tn sql res} {
82 1 { SELECT value FROM carray(?, 5) } {1 2 3 4 5}
83 2 { SELECT value FROM carray(?, 3, 'int32') } {1 2 3}
84 3 { SELECT value, pointer, count, ctype FROM carray(?, 5, 'int32') }
85 {1 {} 5 int32 2 {} 5 int32 3 {} 5 int32 4 {} 5 int32 5 {} 5 int32}
86 4 { SELECT rowid, value FROM carray(?, 5, 'int32') }
87 {1 1 2 2 3 3 4 4 5 5}
88 } {
89 do_test 2.3.$tn {
90 set STMT [sqlite3_prepare_v2 db $sql -1 TAIL]
91 bind_carray_intptr $STMT 1 1 2 3 4 5
92 set r [run_stmt $STMT]
93 sqlite3_finalize $STMT
94 set r
95 } $res
96 }
97
98 # In each of the following. Both carray(?1) and carray(?2) contain integer
99 # values 1 to 5. Bound by sqlite3_carray_bind().
100 #
101 foreach {tn sql res} {
102 1 {
103 SELECT * FROM carray(?1) AS a, carray(?2) AS b
104 WHERE a.value=b.value
105 } {1 1 2 2 3 3 4 4 5 5}
106
107 2 {
108 SELECT * FROM carray(?1) AS a, carray(?2) AS b
109 WHERE a.value=b.value AND a.value<3 AND b.value<3
110 } {1 1 2 2 3 3}
111
112 3 {
113 SELECT * FROM carray(?1) AS a, carray(?2) AS b
114 WHERE a.value<3 AND b.value<3 AND a.value=b.value
115 } {1 1 2 2 3 3}
116
117 4 {
118 SELECT * FROM carray(?1) AS a, carray(?2, a.value) AS b
119 WHERE a.value=b.value
120 } {1 1 2 2 3 3}
121
122 } {
123 do_test 2.4.$tn {
124 set STMT [sqlite3_prepare_v2 db {
125 SELECT * FROM carray(?1) AS a, carray(?2) AS b WHERE a.value=b.value
126 } -1 TAIL]
127 sqlite3_carray_bind $STMT 1 1 2 3 4 5
128 sqlite3_carray_bind $STMT 2 1 2 3 4 5
129 set r [run_stmt $STMT]
130 sqlite3_finalize $STMT
131 set r
132 } {1 1 2 2 3 3 4 4 5 5}
133 }
134
135 # Test that not binding any pointer, or passing a value that is not a bound
136 # pointer to carray() produces no rows of output.
137 #
138 do_execsql_test 3.0.0 {
139 SELECT * FROM carray
140 } {}
141 do_execsql_test 3.0.1 {
142 SELECT * FROM carray('0xFFFF', 5)
143 } {}
144 do_execsql_test 3.0.2 {
145 SELECT * FROM carray('0xFFFF')
146 } {}
147 do_execsql_test 3.0.3 {
148 CREATE TABLE t1(x);
149 INSERT INTO t1 VALUES('0xFFFF');
150 SELECT * FROM t1, carray WHERE carray.pointer = t1.x;
151 } {}
152
153 # Test passing an unknown datatype.
154 #
155 do_test 3.1 {
156 set STMT [sqlite3_prepare_v2 db {SELECT * FROM carray(?, 5, 'apples')} -1 T]
157 sqlite3_carray_bind $STMT 1 1 2 3 4 5
158 sqlite3_step $STMT
159 list [sqlite3_finalize $STMT] [sqlite3_errmsg db]
160 } {SQLITE_ERROR {unknown datatype: 'apples'}}
161
162 finish_test