| 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 |