xref: /sqlite-3.40.0/test/bestindex9.test (revision ece092e7) 
1# 2020-01-29
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
13set testdir [file dirname $argv0]
14source $testdir/tester.tcl
15set testprefix bestindex9
16
17ifcapable !vtab {
18  finish_test
19  return
20}
21
22
23proc vtab_command {method args} {
24  switch -- $method {
25    xConnect {
26      return $::create_table
27    }
28
29    xBestIndex {
30      set hdl [lindex $args 0]
31      set ::vtab_orderby [$hdl orderby]
32      set ::vtab_distinct [$hdl distinct]
33
34      return [list orderby 1]
35    }
36
37    xFilter {
38      return ""
39    }
40  }
41
42  return {}
43}
44
45proc do_bestindex9_test {tn create select orderby distinct} {
46  forcedelete test.db2
47  sqlite3 dbtest test.db2
48  register_tcl_module dbtest
49
50  set ::create_table $create
51  dbtest eval { CREATE VIRTUAL TABLE t1 USING tcl(vtab_command); }
52  dbtest eval $select
53
54  do_test $tn.orderby [list set {} $::vtab_orderby] $orderby
55  do_test $tn.distinct [list set {} $::vtab_distinct] $distinct
56
57  dbtest close
58}
59
60#-------------------------------------------------------------------------
61#
62do_bestindex9_test 1.0 {
63  CREATE TABLE x(k1, k2, v1, PRIMARY KEY(k1, k2))
64} {
65  SELECT DISTINCT k1, k2 FROM t1
66} {{column 0 desc 0} {column 1 desc 0}} 2
67
68do_bestindex9_test 1.1 {
69  CREATE TABLE x(k1, k2, v1, PRIMARY KEY(k1, k2)) WITHOUT ROWID
70} {
71  SELECT DISTINCT k1, k2 FROM t1
72} {} 0
73
74do_bestindex9_test 1.2 {
75  CREATE TABLE x(k1 NOT NULL, k2 NOT NULL, v1, PRIMARY KEY(k1, k2))
76} {
77  SELECT DISTINCT k1, k2 FROM t1
78} {} 0
79
80#-------------------------------------------------------------------------
81#
82do_bestindex9_test 2 {
83  CREATE TABLE x(c1, c2, c3);
84} {
85  SELECT DISTINCT c1 FROM t1 ORDER BY c1
86} {{column 0 desc 0}} 3
87
88#-------------------------------------------------------------------------
89#
90do_bestindex9_test 3 {
91  CREATE TABLE x(c1, c2, c3);
92} {
93  SELECT DISTINCT c1 FROM t1 GROUP BY c1
94} {{column 0 desc 0}} 1
95
96do_bestindex9_test 4 {
97  CREATE TABLE x(c1, c2, c3);
98} {
99  CREATE TABLE t2(balls);
100  SELECT DISTINCT c1 FROM t1, t2
101} {{column 0 desc 0}} 2
102
103
104finish_test
105
106
107
108
109