1# 2016-07-27
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 ORDER BY and LIMIT on an index scan.
13#
14
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# Performance regression reported at
20# http://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg98615.html
21#
22# Caused by the ORDER BY LIMIT optionation for check-in
23# https://sqlite.org/src/info/bf46179d44843769
24#
25# Fixed on approximately 2016-07-27 by changes that compute a better score
26# for index scans by taking into account WHERE clause constraints that can
27# be handled by the index and do not require a table lookup.
28#
29do_execsql_test 1.0 {
30  CREATE TABLE t1(a,b,c,d);
31  WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<100)
32  INSERT INTO t1(a,b,c,d)
33     SELECT x/10, x%10, x%19, x FROM c;
34  CREATE INDEX t1abc ON t1(a,b,c);
35  SELECT * FROM t1 WHERE c=4 ORDER BY a, b LIMIT 2;
36} {0 4 4 4 2 3 4 23}
37
38# Prior to the fix, the following EQP would show a table scan and a sort
39# rather than an index scan.
40#
41do_execsql_test 1.0eqp {
42  EXPLAIN QUERY PLAN
43  SELECT * FROM t1 WHERE c=4 ORDER BY a, b LIMIT 2;
44} {/SCAN TABLE t1 USING INDEX t1abc/}
45
46# If we change the index so that it no longer covers the WHERE clause,
47# then we should (correctly) revert to using a table scan.
48#
49do_execsql_test 1.1 {
50  DROP INDEX t1abc;
51  CREATE INDEX t1abd ON t1(a,b,d);
52  SELECT * FROM t1 WHERE c=4 ORDER BY a, b LIMIT 2;
53} {0 4 4 4 2 3 4 23}
54do_execsql_test 1.1eqp {
55  EXPLAIN QUERY PLAN
56  SELECT * FROM t1 WHERE c=4 ORDER BY a, b LIMIT 2;
57} {~/USING INDEX/}
58
59
60finish_test
61