Debian Codegraph @

test/wherelimit3.test

raw
1 # 2024-06-06
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 # Test cases for query plans using LIMIT
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix wherelimit3
18
19 do_execsql_test 1.0 {
20 CREATE TABLE t1(a INT, b INT);
21 WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<1000)
22 INSERT INTO t1 SELECT n, n FROM c;
23 CREATE INDEX t1a ON t1(a);
24 CREATE INDEX t1b ON t1(b);
25 ANALYZE;
26 }
27
28 do_eqp_test 1.1 {
29 SELECT * FROM t1 WHERE a>=100 AND a<300 ORDER BY b LIMIT 5;
30 } {
31 QUERY PLAN
32 |--SEARCH t1 USING INDEX t1a (a>? AND a<?)
33 `--USE TEMP B-TREE FOR ORDER BY
34 }
35 ifcapable stat4 {
36 do_eqp_test 1.2 {
37 SELECT * FROM t1 WHERE a>=100 AND a<300 ORDER BY b LIMIT -1;
38 } {
39 QUERY PLAN
40 `--SCAN t1 USING INDEX t1b
41 }
42 }
43
44 set N [expr 5]
45 do_eqp_test 1.3 {
46 SELECT * FROM t1 WHERE a>=100 AND a<300 ORDER BY b LIMIT $::N;
47 } {
48 QUERY PLAN
49 |--SEARCH t1 USING INDEX t1a (a>? AND a<?)
50 `--USE TEMP B-TREE FOR ORDER BY
51 }
52
53 ifcapable stat4 {
54 set N [expr -1]
55 do_eqp_test 1.4 {
56 SELECT * FROM t1 WHERE a>=100 AND a<300 ORDER BY b LIMIT $::N;
57 } {
58 QUERY PLAN
59 `--SCAN t1 USING INDEX t1b
60 }
61 }
62
63
64
65
66
67 finish_test