xref: /titanic_51/usr/src/lib/libsqlite/test/select2.test (revision 791a814c934fcd4deb13b26c1f116ff283272a0d)
1
2#pragma ident	"%Z%%M%	%I%	%E% SMI"
3
4# 2001 September 15
5#
6# The author disclaims copyright to this source code.  In place of
7# a legal notice, here is a blessing:
8#
9#    May you do good and not evil.
10#    May you find forgiveness for yourself and forgive others.
11#    May you share freely, never taking more than you give.
12#
13#***********************************************************************
14# This file implements regression tests for SQLite library.  The
15# focus of this file is testing the SELECT statement.
16#
17# $Id: select2.test,v 1.18 2002/04/02 13:26:11 drh Exp $
18
19set testdir [file dirname $argv0]
20source $testdir/tester.tcl
21
22# Create a table with some data
23#
24execsql {CREATE TABLE tbl1(f1 int, f2 int)}
25set f [open ./testdata1.txt w]
26for {set i 0} {$i<=30} {incr i} {
27  puts $f "[expr {$i%9}]\t[expr {$i%10}]"
28}
29close $f
30execsql {COPY tbl1 FROM './testdata1.txt'}
31file delete -force ./testdata1.txt
32catch {unset data}
33
34# Do a second query inside a first.
35#
36do_test select2-1.1 {
37  set sql {SELECT DISTINCT f1 FROM tbl1 ORDER BY f1}
38  set r {}
39  db eval $sql data {
40    set f1 $data(f1)
41    lappend r $f1:
42    set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2"
43    db eval $sql2 d2 {
44      lappend r $d2(f2)
45    }
46  }
47  set r
48} {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}
49
50do_test select2-1.2 {
51  set sql {SELECT DISTINCT f1 FROM tbl1 WHERE f1>3 AND f1<5}
52  set r {}
53  db eval $sql data {
54    set f1 $data(f1)
55    lappend r $f1:
56    set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2"
57    db eval $sql2 d2 {
58      lappend r $d2(f2)
59    }
60  }
61  set r
62} {4: 2 3 4}
63
64# Create a largish table
65#
66do_test select2-2.0 {
67  execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int)}
68  set f [open ./testdata1.txt w]
69  for {set i 1} {$i<=30000} {incr i} {
70    puts $f "$i\t[expr {$i*2}]\t[expr {$i*3}]"
71  }
72  close $f
73  # execsql {--vdbe-trace-on--}
74  execsql {COPY tbl2 FROM './testdata1.txt'}
75  file delete -force ./testdata1.txt
76} {}
77
78do_test select2-2.1 {
79  execsql {SELECT count(*) FROM tbl2}
80} {30000}
81do_test select2-2.2 {
82  execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
83} {29500}
84
85do_test select2-3.1 {
86  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
87} {500}
88
89do_test select2-3.2a {
90  execsql {CREATE INDEX idx1 ON tbl2(f2)}
91} {}
92
93do_test select2-3.2b {
94  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
95} {500}
96do_test select2-3.2c {
97  execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
98} {500}
99do_test select2-3.2d {
100  set sqlite_search_count 0
101  execsql {SELECT * FROM tbl2 WHERE 1000=f2}
102  set sqlite_search_count
103} {3}
104do_test select2-3.2e {
105  set sqlite_search_count 0
106  execsql {SELECT * FROM tbl2 WHERE f2=1000}
107  set sqlite_search_count
108} {3}
109
110# Make sure queries run faster with an index than without
111#
112do_test select2-3.3 {
113  execsql {DROP INDEX idx1}
114  set sqlite_search_count 0
115  execsql {SELECT f1 FROM tbl2 WHERE f2==2000}
116  set sqlite_search_count
117} {29999}
118
119# Make sure we can optimize functions in the WHERE clause that
120# use fields from two or more different table.  (Bug #6)
121#
122do_test select2-4.1 {
123  execsql {
124    CREATE TABLE aa(a);
125    CREATE TABLE bb(b);
126    INSERT INTO aa VALUES(1);
127    INSERT INTO aa VALUES(3);
128    INSERT INTO bb VALUES(2);
129    INSERT INTO bb VALUES(4);
130    SELECT * FROM aa, bb WHERE max(a,b)>2;
131  }
132} {1 4 3 2 3 4}
133do_test select2-4.2 {
134  execsql {
135    INSERT INTO bb VALUES(0);
136    SELECT * FROM aa, bb WHERE b;
137  }
138} {1 2 1 4 3 2 3 4}
139do_test select2-4.3 {
140  execsql {
141    SELECT * FROM aa, bb WHERE NOT b;
142  }
143} {1 0 3 0}
144do_test select2-4.4 {
145  execsql {
146    SELECT * FROM aa, bb WHERE min(a,b);
147  }
148} {1 2 1 4 3 2 3 4}
149do_test select2-4.5 {
150  execsql {
151    SELECT * FROM aa, bb WHERE NOT min(a,b);
152  }
153} {1 0 3 0}
154do_test select2-4.6 {
155  execsql {
156    SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 1 END;
157  }
158} {1 2 3 4}
159do_test select2-4.7 {
160  execsql {
161    SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 0 ELSE 1 END;
162  }
163} {1 4 1 0 3 2 3 0}
164
165finish_test
166