1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
# 2016-05-20
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is testing the LIMIT in combination with ORDER BY
# and in particular, the optimizations in the inner loop that cause an
# early exit of the inner loop when the LIMIT is reached and the inner
# loop is emitting rows in ORDER BY order.
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix limit2
do_execsql_test limit2-100 {
CREATE TABLE t1(a,b);
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
INSERT INTO t1(a,b) SELECT 1, (x*17)%1000 + 1000 FROM c;
INSERT INTO t1(a,b) VALUES(2,2),(3,1006),(4,4),(5,9999);
CREATE INDEX t1ab ON t1(a,b);
}
set sqlite_search_count 0
do_execsql_test limit2-100.1 {
SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY b LIMIT 5;
} {2 2 | 4 4 | 1 1000 | 1 1001 | 1 1002 |}
set fast_count $sqlite_search_count
set sqlite_search_count 0
do_execsql_test limit2-100.2 {
SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY +b LIMIT 5;
} {2 2 | 4 4 | 1 1000 | 1 1001 | 1 1002 |}
do_test limit2-100.3 {
set slow_count $sqlite_search_count
expr {$fast_count < 0.02*$slow_count}
} {1}
do_execsql_test limit2-110 {
CREATE TABLE t2(x,y);
INSERT INTO t2(x,y) VALUES('a',1),('a',2),('a',3),('a',4);
INSERT INTO t2(x,y) VALUES('b',1),('c',2),('d',3),('e',4);
CREATE INDEX t2xy ON t2(x,y);
}
set sqlite_search_count 0
do_execsql_test limit2-110.1 {
SELECT a, b, '|' FROM t2, t1 WHERE t2.x='a' AND t1.a=t2.y ORDER BY t1.b LIMIT 5;
} {2 2 | 4 4 | 1 1000 | 1 1001 | 1 1002 |}
set fast_count $sqlite_search_count
set sqlite_search_count 0
do_execsql_test limit2-110.2 {
SELECT a, b, '|' FROM t2, t1 WHERE t2.x='a' AND t1.a=t2.y ORDER BY +t1.b LIMIT 5;
} {2 2 | 4 4 | 1 1000 | 1 1001 | 1 1002 |}
set slow_count $sqlite_search_count
do_test limit2-110.3 {
expr {$fast_count < 0.02*$slow_count}
} {1}
do_execsql_test limit2-120 {
DROP INDEX t1ab;
CREATE INDEX t1ab ON t1(a,b DESC);
}
set sqlite_search_count 0
do_execsql_test limit2-120.1 {
SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY b DESC LIMIT 5;
} {5 9999 | 1 1999 | 1 1998 | 1 1997 | 1 1996 |}
set fast_count $sqlite_search_count
set sqlite_search_count 0
do_execsql_test limit2-120.2 {
SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY +b DESC LIMIT 5;
} {5 9999 | 1 1999 | 1 1998 | 1 1997 | 1 1996 |}
do_test limit2-120.3 {
set slow_count $sqlite_search_count
expr {$fast_count < 0.02*$slow_count}
} {1}
# Bug report against the new ORDER BY LIMIT optimization just prior to
# release. (Unreleased so there is no ticket).
#
# Make sure the optimization is not applied if the inner loop can only
# provide a single row of output.
#
do_execsql_test limit2-200 {
CREATE TABLE t200(a, b);
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
INSERT INTO t200(a,b) SELECT x, x FROM c;
CREATE TABLE t201(x INTEGER PRIMARY KEY, y);
INSERT INTO t201(x,y) VALUES(2,12345);
SELECT *, '|' FROM t200, t201 WHERE x=b ORDER BY y LIMIT 3;
} {2 2 2 12345 |}
do_execsql_test limit2-210 {
SELECT *, '|' FROM t200 LEFT JOIN t201 ON x=b ORDER BY y LIMIT 3;
} {1 1 {} {} | 3 3 {} {} | 4 4 {} {} |}
# Bug in the ORDER BY LIMIT optimization reported on 2016-09-06.
# Ticket https://www.sqlite.org/src/info/559733b09e96
#
do_execsql_test limit2-300 {
CREATE TABLE t300(a,b,c);
CREATE INDEX t300x ON t300(a,b,c);
INSERT INTO t300 VALUES(0,1,99),(0,1,0),(0,0,0);
SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC;
} {0 1 99 . 0 0 0 . 0 1 0 .}
do_execsql_test limit2-310 {
SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC LIMIT 1;
} {0 1 99 .}
# Make sure the SELECT loop is ordered correctly for the direction of
# the ORDER BY
#
do_execsql_test limit2-400 {
CREATE TABLE t400(a,b);
CREATE INDEX t400_ab ON t400(a,b);
INSERT INTO t400(a,b) VALUES(1,90),(1,40),(2,80),(2,30),(3,70),(3,20);
SELECT *,'x' FROM t400 WHERE a IN (1,2,3) ORDER BY b DESC LIMIT 3;
SELECT *,'y' FROM t400 WHERE a IN (1,2,3) ORDER BY +b DESC LIMIT 3;
} {1 90 x 2 80 x 3 70 x 1 90 y 2 80 y 3 70 y}
do_execsql_test 500 {
CREATE TABLE t500(i INTEGER PRIMARY KEY, j);
INSERT INTO t500 VALUES(1, 1);
INSERT INTO t500 VALUES(2, 2);
INSERT INTO t500 VALUES(3, 3);
INSERT INTO t500 VALUES(4, 0);
INSERT INTO t500 VALUES(5, 5);
SELECT j FROM t500 WHERE i IN (1,2,3,4,5) ORDER BY j DESC LIMIT 3;
} {5 3 2}
do_execsql_test 501 {
CREATE TABLE t501(i INTEGER PRIMARY KEY, j);
INSERT INTO t501 VALUES(1, 5);
INSERT INTO t501 VALUES(2, 4);
INSERT INTO t501 VALUES(3, 3);
INSERT INTO t501 VALUES(4, 6);
INSERT INTO t501 VALUES(5, 1);
SELECT j FROM t501 WHERE i IN (1,2,3,4,5) ORDER BY j LIMIT 3;
} {1 3 4}
do_execsql_test 502 {
CREATE TABLE t502(i INT PRIMARY KEY, j);
INSERT INTO t502 VALUES(1, 5);
INSERT INTO t502 VALUES(2, 4);
INSERT INTO t502 VALUES(3, 3);
INSERT INTO t502 VALUES(4, 6);
INSERT INTO t502 VALUES(5, 1);
SELECT j FROM t502 WHERE i IN (1,2,3,4,5) ORDER BY j LIMIT 3;
} {1 3 4}
finish_test
|
