xref: /sqlite-3.40.0/test/select2.test (revision 3fc190cc) 
1# Copyright (c) 1999, 2000 D. Richard Hipp
2#
3# This program is free software; you can redistribute it and/or
4# modify it under the terms of the GNU General Public
5# License as published by the Free Software Foundation; either
6# version 2 of the License, or (at your option) any later version.
7#
8# This program is distributed in the hope that it will be useful,
9# but WITHOUT ANY WARRANTY; without even the implied warranty of
10# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
11# General Public License for more details.
12#
13# You should have received a copy of the GNU General Public
14# License along with this library; if not, write to the
15# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
16# Boston, MA  02111-1307, USA.
17#
18# Author contact information:
19#   [email protected]
20#   http://www.hwaci.com/drh/
21#
22#***********************************************************************
23# This file implements regression tests for SQLite library.  The
24# focus of this file is testing the SELECT statement.
25#
26# $Id: select2.test,v 1.13 2001/09/14 03:24:25 drh Exp $
27
28set testdir [file dirname $argv0]
29source $testdir/tester.tcl
30
31# Create a table with some data
32#
33execsql {CREATE TABLE tbl1(f1 int, f2 int)}
34set f [open ./testdata1.txt w]
35for {set i 0} {$i<=30} {incr i} {
36  puts $f "[expr {$i%9}]\t[expr {$i%10}]"
37}
38close $f
39execsql {COPY tbl1 FROM './testdata1.txt'}
40file delete -force ./testdata1.txt
41
42# Do a second query inside a first.
43#
44do_test select2-1.1 {
45  set sql {SELECT DISTINCT f1 FROM tbl1 ORDER BY f1}
46  set r {}
47  db eval $sql data {
48    set f1 $data(f1)
49    lappend r $f1:
50    set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2"
51    db eval $sql2 d2 {
52      lappend r $d2(f2)
53    }
54  }
55  set r
56} {0: 0 7 8 9 1: 0 1 8 9 2: 0 1 2 9 3: 0 1 2 3 4: 2 3 4 5: 3 4 5 6: 4 5 6 7: 5 6 7 8: 6 7 8}
57
58do_test select2-1.2 {
59  set sql {SELECT DISTINCT f1 FROM tbl1 WHERE f1>3 AND f1<5}
60  set r {}
61  db eval $sql data {
62    set f1 $data(f1)
63    lappend r $f1:
64    set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2"
65    db eval $sql2 d2 {
66      lappend r $d2(f2)
67    }
68  }
69  set r
70} {4: 2 3 4}
71
72# Create a largish table
73#
74do_test select2-2.0 {
75  execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int)}
76  set f [open ./testdata1.txt w]
77  for {set i 1} {$i<=30000} {incr i} {
78    puts $f "$i\t[expr {$i*2}]\t[expr {$i*3}]"
79  }
80  close $f
81  # execsql {--vdbe-trace-on--}
82  execsql {COPY tbl2 FROM './testdata1.txt'}
83  file delete -force ./testdata1.txt
84} {}
85
86do_test select2-2.1 {
87  execsql {SELECT count(*) FROM tbl2}
88} {30000}
89do_test select2-2.2 {
90  execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
91} {29500}
92
93do_test select2-3.1 {
94  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
95} {500}
96
97do_test select2-3.2a {
98  execsql {CREATE INDEX idx1 ON tbl2(f2)}
99} {}
100
101do_test select2-3.2b {
102  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
103} {500}
104do_test select2-3.2c {
105  execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
106} {500}
107do_test select2-3.2d {
108  execsql {SELECT fcnt() FROM tbl2 WHERE 1000=f2}
109} {1}
110do_test select2-3.2e {
111  execsql {SELECT fcnt() FROM tbl2 WHERE f2=1000}
112} {1}
113
114# omit the time-dependent tests
115#
116do_probtest select2-3.2f {
117  set t1 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}} 1] 0]
118  set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2=1000}} 1] 0]
119  expr {$t1*0.7<$t2 && $t2*0.7<$t1}
120} {1}
121
122# Make sure queries run faster with an index than without
123#
124do_probtest select2-3.3 {
125  set t1 [lindex [time {execsql {SELECT f1 from tbl2 WHERE f2==2000}} 1] 0]
126  execsql {DROP INDEX idx1}
127  set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2==2000}} 1] 0]
128  expr {$t1*10 < $t2}
129} {1}
130do_probtest select2-3.4 {
131  expr {[execsql {SELECT fcnt() FROM tbl2 WHERE f2==2000}]>10}
132} {1}
133
134finish_test
135