AryaWu/sqlite

1# 2025-10-072#3# The author disclaims copyright to this source code.  In place of4# 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 extension13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix carray0218 19ifcapable !vtab {20  finish_test21  return22}23 24proc 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 $stmt32  return $r33}34 35# Bind some text values using sqlite3_carray_bind(). Including a NULL pointer.36#37set STMT [38  sqlite3_prepare_v2 db "SELECT value, value IS NULL FROM carray(?)" -1 TAIL39]40do_test 1.0 {41  sqlite3_carray_bind -transient -text $STMT 1 "abc" NULL "def"42  run_stmt $STMT43} {abc 0 {} 1 def 0}44sqlite3_finalize $STMT45 46# Pass bad values as the "flags" parameter to sqlite3_carray_bind().47#48set STMT [sqlite3_prepare_v2 db "SELECT value FROM carray(?)" -1 TAIL]49do_test 2.0 {50  list [catch {sqlite3_carray_bind -flags 5 -int32 $STMT 1 1 2 3 4} msg] $msg51} {1 {SQL logic error}}52do_test 2.1 {53  list [catch {sqlite3_carray_bind -flags -1 -int32 $STMT 1 1 2 3 4} msg] $msg54} {1 {SQL logic error}}55sqlite3_finalize $STMT56 57# In each of the following, carray(?) contains integer values 1 to 5, bound58# using sqlite3_carray_bind().59#60foreach {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 570    set r [run_stmt $STMT]71    sqlite3_finalize $STMT72    set r73  } $res74}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#81foreach {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 592    set r [run_stmt $STMT]93    sqlite3_finalize $STMT94    set r95  } $res96}97 98# In each of the following. Both carray(?1) and carray(?2) contain integer99# values 1 to 5. Bound by sqlite3_carray_bind().100#101foreach {tn sql res} {102  1 { 103    SELECT * FROM carray(?1) AS a, carray(?2) AS b 104    WHERE a.value=b.value105  } {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<3110  } {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.value115  } {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.value120  } {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.value126    } -1 TAIL]127    sqlite3_carray_bind $STMT 1   1 2 3 4 5128    sqlite3_carray_bind $STMT 2   1 2 3 4 5129    set r [run_stmt $STMT]130    sqlite3_finalize $STMT131    set r132  } {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 bound136# pointer to carray() produces no rows of output.137#138do_execsql_test 3.0.0 {139  SELECT * FROM carray140} {}141do_execsql_test 3.0.1 {142  SELECT * FROM carray('0xFFFF', 5)143} {}144do_execsql_test 3.0.2 {145  SELECT * FROM carray('0xFFFF')146} {}147do_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#155do_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 5158  sqlite3_step $STMT159  list [sqlite3_finalize $STMT] [sqlite3_errmsg db]160} {SQLITE_ERROR {unknown datatype: 'apples'}}161 162finish_test163