xref: /sqlite-3.40.0/test/select2.test (revision c87fa69a) 
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.11 2001/08/19 18:19:46 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 {COPY tbl2 FROM './testdata1.txt'}
82  file delete -force ./testdata1.txt
83} {}
84
85do_test select2-2.1 {
86  execsql {SELECT count(*) FROM tbl2}
87} {30000}
88do_test select2-2.2 {
89  execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
90} {29500}
91
92do_test select2-3.1 {
93  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
94} {500}
95
96do_test select2-3.2a {
97  execsql {CREATE INDEX idx1 ON tbl2(f2)}
98} {}
99
100do_test select2-3.2b {
101  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
102} {500}
103do_test select2-3.2c {
104  execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
105} {500}
106do_test select2-3.2d {
107  execsql {SELECT fcnt() FROM tbl2 WHERE 1000=f2}
108} {1}
109do_test select2-3.2e {
110  execsql {SELECT fcnt() FROM tbl2 WHERE f2=1000}
111} {1}
112
113# omit the time-dependent tests
114#
115testif gdbm:
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