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.28 2009/01/15 15:23:59 drh 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 unset data
  59
  60 # Create a largish table. Do this twice, once using the TCL cache and once
  61 # without.  Compare the performance to make sure things go faster with the
  62 # cache turned on.
  63 #
  64 ifcapable tclvar {
  65   do_test select2-2.0.1 {
  66     set t1 [time {
  67       execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int); BEGIN;}
  68       for {set i 1} {$i<=30000} {incr i} {
  69         set i2 [expr {$i*2}]
  70         set i3 [expr {$i*3}]
  71         db eval {INSERT INTO tbl2 VALUES($i,$i2,$i3)}
  72       }
  73       execsql {COMMIT}
  74     }]
  75     list
  76   } {}
  77   puts "time with cache: $::t1"
  78 }
  79 catch {execsql {DROP TABLE tbl2}}
  80 do_test select2-2.0.2 {
  81   set t2 [time {
  82     execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int); BEGIN;}
  83     for {set i 1} {$i<=30000} {incr i} {
  84       set i2 [expr {$i*2}]
  85       set i3 [expr {$i*3}]
  86       execsql "INSERT INTO tbl2 VALUES($i,$i2,$i3)"
  87     }
  88     execsql {COMMIT}
  89   }]
  90   list
  91 } {}
  92 puts "time without cache: $t2"
  93 #ifcapable tclvar {
  94 #  do_test select2-2.0.3 {
  95 #    expr {[lindex $t1 0]<[lindex $t2 0]}
  96 #  } 1
  97 #}
  98
  99 do_test select2-2.1 {
 100   execsql {SELECT count(*) FROM tbl2}
 101 } {30000}
 102 do_test select2-2.2 {
 103   execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
 104 } {29500}
 105
 106 do_test select2-3.1 {
 107   execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
 108 } {500}
 109
 110 do_test select2-3.2a {
 111   execsql {CREATE INDEX idx1 ON tbl2(f2)}
 112 } {}
 113 do_test select2-3.2b {
 114   execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
 115 } {500}
 116 do_test select2-3.2c {
 117   execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
 118 } {500}
 119 do_test select2-3.2d {
 120   set sqlite_search_count 0
 121   execsql {SELECT * FROM tbl2 WHERE 1000=f2}
 122   set sqlite_search_count
 123 } {3}
 124 do_test select2-3.2e {
 125   set sqlite_search_count 0
 126   execsql {SELECT * FROM tbl2 WHERE f2=1000}
 127   set sqlite_search_count
 128 } {3}
 129
 130 # Make sure queries run faster with an index than without
 131 #
 132 do_test select2-3.3 {
 133   execsql {DROP INDEX idx1}
 134   set sqlite_search_count 0
 135   execsql {SELECT f1 FROM tbl2 WHERE f2==2000}
 136   set sqlite_search_count
 137 } {29999}
 138
 139 # Make sure we can optimize functions in the WHERE clause that
 140 # use fields from two or more different table.  (Bug #6)
 141 #
 142 do_test select2-4.1 {
 143   execsql {
 144     CREATE TABLE aa(a);
 145     CREATE TABLE bb(b);
 146     INSERT INTO aa VALUES(1);
 147     INSERT INTO aa VALUES(3);
 148     INSERT INTO bb VALUES(2);
 149     INSERT INTO bb VALUES(4);
 150     SELECT * FROM aa, bb WHERE max(a,b)>2;
 151   }
 152 } {1 4 3 2 3 4}
 153 do_test select2-4.2 {
 154   execsql {
 155     INSERT INTO bb VALUES(0);
 156     SELECT * FROM aa CROSS JOIN bb WHERE b;
 157   }
 158 } {1 2 1 4 3 2 3 4}
 159 do_test select2-4.3 {
 160   execsql {
 161     SELECT * FROM aa CROSS JOIN bb WHERE NOT b;
 162   }
 163 } {1 0 3 0}
 164 do_test select2-4.4 {
 165   execsql {
 166     SELECT * FROM aa, bb WHERE min(a,b);
 167   }
 168 } {1 2 1 4 3 2 3 4}
 169 do_test select2-4.5 {
 170   execsql {
 171     SELECT * FROM aa, bb WHERE NOT min(a,b);
 172   }
 173 } {1 0 3 0}
 174 do_test select2-4.6 {
 175   execsql {
 176     SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 1 END;
 177   }
 178 } {1 2 3 4}
 179 do_test select2-4.7 {
 180   execsql {
 181     SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 0 ELSE 1 END;
 182   }
 183 } {1 4 1 0 3 2 3 0}
 184
 185 finish_test