sqlite3/test/select2.test

   1 # 2001 September 15
   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 # This file implements regression tests for SQLite library.  The
  12 # focus of this file is testing the SELECT statement.
  13 #
  14 # $Id: select2.test,v 1.24 2005/01/25 04:27:55 danielk1977 Exp $
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18
  19 # Create a table with some data
  20 #
  21 execsql {CREATE TABLE tbl1(f1 int, f2 int)}
  22 execsql {BEGIN}
  23 for {set i 0} {$i<=30} {incr i} {
  24   execsql "INSERT INTO tbl1 VALUES([expr {$i%9}],[expr {$i%10}])"
  25 }
  26 execsql {COMMIT}
  27
  28 # Do a second query inside a first.
  29 #
  30 do_test select2-1.1 {
  31   set sql {SELECT DISTINCT f1 FROM tbl1 ORDER BY f1}
  32   set r {}
  33   catch {unset data}
  34   db eval $sql data {
  35     set f1 $data(f1)
  36     lappend r $f1:
  37     set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2"
  38     db eval $sql2 d2 {
  39       lappend r $d2(f2)
  40     }
  41   }
  42   set r
  43 } {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}
  44
  45 do_test select2-1.2 {
  46   set sql {SELECT DISTINCT f1 FROM tbl1 WHERE f1>3 AND f1<5}
  47   set r {}
  48   db eval $sql data {
  49     set f1 $data(f1)
  50     lappend r $f1:
  51     set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2"
  52     db eval $sql2 d2 {
  53       lappend r $d2(f2)
  54     }
  55   }
  56   set r
  57 } {4: 2 3 4}
  58
  59 # Create a largish table. Do this twice, once using the TCL cache and once
  60 # without.  Compare the performance to make sure things go faster with the
  61 # cache turned on.
  62 #
  63 ifcapable tclvar {
  64   do_test select2-2.0.1 {
  65     set t1 [time {
  66       execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int); BEGIN;}
  67       for {set i 1} {$i<=30000} {incr i} {
  68         set i2 [expr {$i*2}]
  69         set i3 [expr {$i*3}]
  70         db eval {INSERT INTO tbl2 VALUES($i,$i2,$i3)}
  71       }
  72       execsql {COMMIT}
  73     }]
  74     list
  75   } {}
  76   puts "time with cache: $::t1"
  77 }
  78 catch {execsql {DROP TABLE tbl2}}
  79 do_test select2-2.0.2 {
  80   set t2 [time {
  81     execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int); BEGIN;}
  82     for {set i 1} {$i<=30000} {incr i} {
  83       set i2 [expr {$i*2}]
  84       set i3 [expr {$i*3}]
  85       execsql "INSERT INTO tbl2 VALUES($i,$i2,$i3)"
  86     }
  87     execsql {COMMIT}
  88   }]
  89   list
  90 } {}
  91 puts "time without cache: $t2"
  92 ifcapable tclvar {
  93   do_test select2-2.0.3 {
  94     expr {[lindex $t1 0]<[lindex $t2 0]}
  95   } 1
  96 }
  97
  98 do_test select2-2.1 {
  99   execsql {SELECT count(*) FROM tbl2}
 100 } {30000}
 101 do_test select2-2.2 {
 102   execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
 103 } {29500}
 104
 105 do_test select2-3.1 {
 106   execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
 107 } {500}
 108
 109 do_test select2-3.2a {
 110   execsql {CREATE INDEX idx1 ON tbl2(f2)}
 111 } {}
 112 do_test select2-3.2b {
 113   execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
 114 } {500}
 115 do_test select2-3.2c {
 116   execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
 117 } {500}
 118 do_test select2-3.2d {
 119   set sqlite_search_count 0
 120   execsql {SELECT * FROM tbl2 WHERE 1000=f2}
 121   set sqlite_search_count
 122 } {3}
 123 do_test select2-3.2e {
 124   set sqlite_search_count 0
 125   execsql {SELECT * FROM tbl2 WHERE f2=1000}
 126   set sqlite_search_count
 127 } {3}
 128
 129 # Make sure queries run faster with an index than without
 130 #
 131 do_test select2-3.3 {
 132   execsql {DROP INDEX idx1}
 133   set sqlite_search_count 0
 134   execsql {SELECT f1 FROM tbl2 WHERE f2==2000}
 135   set sqlite_search_count
 136 } {29999}
 137
 138 # Make sure we can optimize functions in the WHERE clause that
 139 # use fields from two or more different table.  (Bug #6)
 140 #
 141 do_test select2-4.1 {
 142   execsql {
 143     CREATE TABLE aa(a);
 144     CREATE TABLE bb(b);
 145     INSERT INTO aa VALUES(1);
 146     INSERT INTO aa VALUES(3);
 147     INSERT INTO bb VALUES(2);
 148     INSERT INTO bb VALUES(4);
 149     SELECT * FROM aa, bb WHERE max(a,b)>2;
 150   }
 151 } {1 4 3 2 3 4}
 152 do_test select2-4.2 {
 153   execsql {
 154     INSERT INTO bb VALUES(0);
 155     SELECT * FROM aa, bb WHERE b;
 156   }
 157 } {1 2 1 4 3 2 3 4}
 158 do_test select2-4.3 {
 159   execsql {
 160     SELECT * FROM aa, bb WHERE NOT b;
 161   }
 162 } {1 0 3 0}
 163 do_test select2-4.4 {
 164   execsql {
 165     SELECT * FROM aa, bb WHERE min(a,b);
 166   }
 167 } {1 2 1 4 3 2 3 4}
 168 do_test select2-4.5 {
 169   execsql {
 170     SELECT * FROM aa, bb WHERE NOT min(a,b);
 171   }
 172 } {1 0 3 0}
 173 do_test select2-4.6 {
 174   execsql {
 175     SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 1 END;
 176   }
 177 } {1 2 3 4}
 178 do_test select2-4.7 {
 179   execsql {
 180     SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 0 ELSE 1 END;
 181   }
 182 } {1 4 1 0 3 2 3 0}
 183
 184 finish_test