AryaWu/sqlite
0
1# 2022 December 52#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 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set testprefix scanstatus216 17ifcapable !scanstatus {18 finish_test19 return20}21 22sqlite3_db_config db STMT_SCANSTATUS 123 24do_execsql_test 1.0 {25 CREATE TABLE t1(a, b);26 CREATE TABLE t2(x, y);27 INSERT INTO t1 VALUES(1, 2);28 INSERT INTO t1 VALUES(3, 4);29 INSERT INTO t2 VALUES('a', 'b');30 INSERT INTO t2 VALUES('c', 'd');31 INSERT INTO t2 VALUES('e', 'f');32}33 34proc do_zexplain_test {v2 tn sql res} {35 db eval $sql36 set stmt [db version -last-stmt-ptr]37 set idx 038 set ret [list]39 40 set cmd sqlite3_stmt_scanstatus41 set f [list]42 if {$v2} { lappend f complex }43 44 while {1} {45 set r [sqlite3_stmt_scanstatus -flags $f $stmt $idx]46 if {[llength $r]==0} break47 lappend ret [dict get $r zExplain]48 incr idx49 }50 uplevel [list do_test $tn [list set {} $ret] [list {*}$res]]51}52 53proc get_cycles {stmt} {54 set r [sqlite3_stmt_scanstatus $stmt -1]55 dict get $r nCycle56}57 58proc foreach_scan {varname stmt body {debug 0}} {59 upvar $varname var60 for {set ii 0} {1} {incr ii} {61 set f "complex"62 if {$debug} { set f "complex debug" }63 set r [sqlite3_stmt_scanstatus -flags $f $stmt $ii]64 if {[llength $r]==0} break65 array set var $r66 uplevel $body67 }68}69 70proc get_eqp_graph {stmt iPar nIndent} {71 set res ""72 foreach_scan A $stmt {73 if {$A(iParentId)==$iPar} {74 set txt $A(zExplain)75 if {$A(nCycle)>=0} {76 append txt " (nCycle=$A(nCycle))"77 }78 append res "[string repeat - $nIndent]$txt\n"79 append res [get_eqp_graph $stmt $A(iSelectId) [expr $nIndent+2]]80 }81 }82 set res83}84 85proc get_graph {stmt} {86 set nCycle [get_cycles $stmt]87 set res "QUERY (nCycle=$nCycle)\n"88 append res [get_eqp_graph $stmt 0 2]89}90 91proc do_graph_test {tn sql res} {92 db eval $sql93 set stmt [db version -last-stmt-ptr]94 set graph [string trim [get_graph $stmt]]95 96 set graph [regsub -all {nCycle=[0-9]+} $graph nCycle=nnn]97 uplevel [list do_test $tn [list set {} $graph] [string trim $res]]98}99 100proc puts_graph {sql} {101 db eval $sql102 set stmt [db version -last-stmt-ptr]103 puts [string trim [get_graph $stmt]]104}105 106proc puts_debug_info {sql} {107 db eval $sql108 set stmt [db version -last-stmt-ptr]109 foreach_scan X $stmt {110 puts -nonewline "$X(debug_explain) $X(zExplain): "111 puts -nonewline "loop=$X(debug_loop) visit=$X(debug_visit) "112 puts "csr=$X(debug_csr) range=$X(debug_range)"113 } 1114}115 116do_zexplain_test 0 1.1 {117 SELECT (SELECT a FROM t1 WHERE b=x) FROM t2 WHERE y=2118} {119 {SCAN t2}120 {SCAN t1}121}122do_zexplain_test 1 1.2 {123 SELECT (SELECT a FROM t1 WHERE b=x) FROM t2 WHERE y=2124} {125 {SCAN t2}126 {CORRELATED SCALAR SUBQUERY 1}127 {SCAN t1}128}129 130do_graph_test 1.3 {131 SELECT (SELECT a FROM t1 WHERE b=x) FROM t2 WHERE y=2132} {133QUERY (nCycle=nnn)134--SCAN t2 (nCycle=nnn)135--CORRELATED SCALAR SUBQUERY 1 (nCycle=nnn)136----SCAN t1 (nCycle=nnn)137}138 139do_graph_test 1.4 {140 WITH v2(x,y) AS MATERIALIZED (141 SELECT x,y FROM t2142 )143 SELECT * FROM t1, v2 ORDER BY y;144} {145QUERY (nCycle=nnn)146--MATERIALIZE v2 (nCycle=nnn)147----SCAN t2 (nCycle=nnn)148--SCAN t1 (nCycle=nnn)149--SCAN v2 (nCycle=nnn)150--USE TEMP B-TREE FOR ORDER BY (nCycle=nnn)151}152 153#-------------------------------------------------------------------------154ifcapable fts5 {155 reset_db156 sqlite3_db_config db STMT_SCANSTATUS 1157 do_execsql_test 2.0 {158 CREATE VIRTUAL TABLE ft USING fts5(a);159 INSERT INTO ft VALUES('abc');160 INSERT INTO ft VALUES('def');161 INSERT INTO ft VALUES('ghi');162 }163 164 do_graph_test 2.1 {165 SELECT * FROM ft('def')166 } {167QUERY (nCycle=nnn)168--SCAN ft VIRTUAL TABLE INDEX 0:M1 (nCycle=nnn)169 }170}171 172#-------------------------------------------------------------------------173reset_db174sqlite3_db_config db STMT_SCANSTATUS 1175do_execsql_test 3.0 {176 CREATE TABLE x1(a, b);177 CREATE TABLE x2(c, d);178 179 WITH s(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000)180 INSERT INTO x1 SELECT i, i FROM s;181 INSERT INTO x2 SELECT a, b FROM x1;182}183 184do_graph_test 2.1 {185 SELECT * FROM x1, x2 WHERE c=+a;186} {187QUERY (nCycle=nnn)188--SCAN x1 (nCycle=nnn)189--CREATE AUTOMATIC INDEX ON x2(c, d) (nCycle=nnn)190--BLOOM FILTER ON x2 (c=?)191--SEARCH x2 USING AUTOMATIC COVERING INDEX (c=?) (nCycle=nnn)192}193 194#-------------------------------------------------------------------------195reset_db196sqlite3_db_config db STMT_SCANSTATUS 1197do_execsql_test 4.0 {198 CREATE TABLE rt1 (id INTEGER PRIMARY KEY, x1, x2);199 CREATE TABLE rt2 (id, x1, x2);200}201 202do_graph_test 4.1 {203 SELECT * FROM rt1, rt2 WHERE rt1.id%2 AND rt2.x1=rt1.x1;204} {205QUERY (nCycle=nnn)206--SCAN rt1 (nCycle=nnn)207--CREATE AUTOMATIC INDEX ON rt2(x1, id, x2) (nCycle=nnn)208--BLOOM FILTER ON rt2 (x1=?)209--SEARCH rt2 USING AUTOMATIC COVERING INDEX (x1=?) (nCycle=nnn)210}211 212do_graph_test 4.2 {213 SELECT rt2.id FROM rt1, rt2 WHERE rt1.id%2 AND rt2.x1=rt1.x1;214} {215QUERY (nCycle=nnn)216--SCAN rt1 (nCycle=nnn)217--CREATE AUTOMATIC INDEX ON rt2(x1, id) (nCycle=nnn)218--BLOOM FILTER ON rt2 (x1=?)219--SEARCH rt2 USING AUTOMATIC COVERING INDEX (x1=?) (nCycle=nnn)220}221 222do_graph_test 4.3 {223 SELECT rt2.id FROM rt1, rt2 WHERE rt1.id%2 AND (rt2.x1+1)=(rt1.x1+1);224} {225QUERY (nCycle=nnn)226--SCAN rt1 (nCycle=nnn)227--SCAN rt2 (nCycle=nnn)228}229 230do_graph_test 4.4 {231 SELECT rt2.id FROM rt1, rt2 WHERE rt1.id%2 AND rt2.x1=(rt1.x1+1) AND rt2.id>5;232} {233QUERY (nCycle=nnn)234--SCAN rt1 (nCycle=nnn)235--CREATE AUTOMATIC INDEX ON rt2(x1, id) WHERE <expr> (nCycle=nnn)236--BLOOM FILTER ON rt2 (x1=?)237--SEARCH rt2 USING AUTOMATIC PARTIAL COVERING INDEX (x1=?) (nCycle=nnn)238}239 240do_graph_test 4.5 {241 SELECT v1.cnt FROM rt1, (242 SELECT count(*) AS cnt, rt2.x1 AS x1 FROM rt2 GROUP BY x1243 ) AS v1 WHERE rt1.x1=v1.x1244} {245QUERY (nCycle=nnn)246--CO-ROUTINE v1247----SCAN rt2 (nCycle=nnn)248----USE TEMP B-TREE FOR GROUP BY (nCycle=nnn)249--SCAN rt1 (nCycle=nnn)250--CREATE AUTOMATIC INDEX ON v1(x1, cnt) (nCycle=nnn)251--BLOOM FILTER ON v1 (x1=?)252--SEARCH v1 USING AUTOMATIC COVERING INDEX (x1=?) (nCycle=nnn)253}254 255#-------------------------------------------------------------------------256reset_db257 258ifcapable trace { 259 do_execsql_test 5.0 {260 CREATE TABLE t1(x, y);261 CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN262 SELECT 1;263 END;264 INSERT INTO t1 VALUES(1, 2);265 }266 267 proc trace {stmt sql} {268 array set A [sqlite3_stmt_scanstatus -flags complex [format %llx $stmt] 0]269 lappend ::trace_explain $A(zExplain)270 }271 db trace_v2 trace 272 273 set ::trace_explain [list]274 do_execsql_test 5.1 {275 DELETE FROM t1 WHERE x=1;276 }277 278 do_test 5.2 {279 set ::trace_explain280 } {{SCAN t1} {SCAN t1} {SCAN t1}}281}282 283#-------------------------------------------------------------------------284reset_db285sqlite3_db_config db STMT_SCANSTATUS 1286 287do_execsql_test 6.0 {288 CREATE TABLE t1(a, b);289 INSERT INTO t1 VALUES(1, 'one');290 INSERT INTO t1 VALUES(2, 'two');291 INSERT INTO t1 VALUES(3, 'three');292 INSERT INTO t1 VALUES(4, 'four');293 INSERT INTO t1 VALUES(5, 'five');294 INSERT INTO t1 VALUES(6, 'six');295 INSERT INTO t1 VALUES(7, 'seven');296 INSERT INTO t1 VALUES(8, 'eight');297}298 299do_graph_test 6.1 {300 SELECT (a % 2), group_concat(b) FROM t1 GROUP BY 1301} {302QUERY (nCycle=nnn)303--SCAN t1 (nCycle=nnn)304--USE TEMP B-TREE FOR GROUP BY (nCycle=nnn)305}306 307set sql {308 WITH xy(x, y) AS ( SELECT (a % 2), group_concat(b) FROM t1 GROUP BY 1)309 SELECT * FROM xy WHERE x=1310}311do_graph_test 6.2 $sql {312QUERY (nCycle=nnn)313--CO-ROUTINE xy314----SCAN t1 (nCycle=nnn)315----USE TEMP B-TREE FOR GROUP BY (nCycle=nnn)316--SCAN xy (nCycle=nnn)317}318 319do_graph_test 6.3 {320 WITH xy(x, y) AS ( SELECT (a % 2), group_concat(b) FROM t1 GROUP BY 1)321 SELECT * FROM xy, xy AS xy2322} {323QUERY (nCycle=nnn)324--MATERIALIZE xy (nCycle=nnn)325----SCAN t1 (nCycle=nnn)326----USE TEMP B-TREE FOR GROUP BY (nCycle=nnn)327--SCAN xy (nCycle=nnn)328--SCAN xy2 (nCycle=nnn)329}330 331#-------------------------------------------------------------------------332reset_db333 334# Check that an OOB parameter (45) does not cause asan or valgrind errors.335#336do_test 7.0 {337 db eval {SELECT * FROM sqlite_schema}338 set stmt [db version -last-stmt-ptr]339 sqlite3_stmt_scanstatus -flags complex $stmt 1000000340} {}341 342#explain_i { SELECT (a % 2), group_concat(b) FROM t1 GROUP BY 1 }343#puts_debug_info { SELECT (a % 2), group_concat(b) FROM t1 GROUP BY 1 }344 345finish_test346