Home | History | Annotate | Download | only in 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
    186