Home | History | Annotate | Download | only in test 
      1 # 2008 January 5
      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 # $Id: minmax3.test,v 1.5 2008/07/12 14:52:20 drh Exp $
     12 
     13 set testdir [file dirname $argv0]
     14 source $testdir/tester.tcl
     15 
     16 # Do not use a codec for tests in this file, as the database file is
     17 # manipulated directly using tcl scripts (using the [hexio_write] command).
     18 #
     19 do_not_use_codec
     20 
     21 # Do an SQL statement.  Append the search count to the end of the result.
     22 #
     23 proc count sql {
     24   set ::sqlite_search_count 0
     25   return [concat [execsql $sql] $::sqlite_search_count]
     26 }
     27 
     28 # This procedure sets the value of the file-format in file 'test.db'
     29 # to $newval. Also, the schema cookie is incremented.
     30 # 
     31 proc set_file_format {newval} {
     32   hexio_write test.db 44 [hexio_render_int32 $newval]
     33   set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
     34   incr schemacookie
     35   hexio_write test.db 40 [hexio_render_int32 $schemacookie]
     36   return {}
     37 }
     38 
     39 do_test minmax3-1.0 {
     40   execsql {
     41     CREATE TABLE t1(x, y, z);
     42   }
     43   db close
     44   set_file_format 4
     45   sqlite3 db test.db
     46   execsql {
     47     BEGIN;
     48     INSERT INTO t1 VALUES('1', 'I',   'one');
     49     INSERT INTO t1 VALUES('2', 'IV',  'four');
     50     INSERT INTO t1 VALUES('2', NULL,  'three');
     51     INSERT INTO t1 VALUES('2', 'II',  'two');
     52     INSERT INTO t1 VALUES('2', 'V',   'five');
     53     INSERT INTO t1 VALUES('3', 'VI',  'six');
     54     COMMIT;
     55     PRAGMA automatic_index=OFF;
     56   }
     57 } {}
     58 do_test minmax3-1.1.1 {
     59   # Linear scan.
     60   count { SELECT max(y) FROM t1 WHERE x = '2'; }
     61 } {V 5}
     62 do_test minmax3-1.1.2 {
     63   # Index optimizes the WHERE x='2' constraint.
     64   execsql { CREATE INDEX i1 ON t1(x) }
     65   count   { SELECT max(y) FROM t1 WHERE x = '2'; }
     66 } {V 9}
     67 do_test minmax3-1.1.3 {
     68   # Index optimizes the WHERE x='2' constraint and the MAX(y).
     69   execsql { CREATE INDEX i2 ON t1(x,y) }
     70   count   { SELECT max(y) FROM t1 WHERE x = '2'; }
     71 } {V 1}
     72 do_test minmax3-1.1.4 {
     73   # Index optimizes the WHERE x='2' constraint and the MAX(y).
     74   execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) }
     75   count   { SELECT max(y) FROM t1 WHERE x = '2'; }
     76 } {V 1}
     77 do_test minmax3-1.1.5 {
     78   count   { SELECT max(y) FROM t1 WHERE x = '2' AND y != 'V'; }
     79 } {IV 2}
     80 do_test minmax3-1.1.6 {
     81   count   { SELECT max(y) FROM t1 WHERE x = '2' AND y < 'V'; }
     82 } {IV 1}
     83 do_test minmax3-1.1.6 {
     84   count   { SELECT max(y) FROM t1 WHERE x = '2' AND z != 'five'; }
     85 } {IV 4}
     86 
     87 do_test minmax3-1.2.1 {
     88   # Linear scan of t1.
     89   execsql { DROP INDEX i1 ; DROP INDEX i2 }
     90   count { SELECT min(y) FROM t1 WHERE x = '2'; }
     91 } {II 5}
     92 do_test minmax3-1.2.2 {
     93   # Index i1 optimizes the WHERE x='2' constraint.
     94   execsql { CREATE INDEX i1 ON t1(x) }
     95   count   { SELECT min(y) FROM t1 WHERE x = '2'; }
     96 } {II 9}
     97 do_test minmax3-1.2.3 {
     98   # Index i2 optimizes the WHERE x='2' constraint and the min(y).
     99   execsql { CREATE INDEX i2 ON t1(x,y) }
    100   count   { SELECT min(y) FROM t1 WHERE x = '2'; }
    101 } {II 1}
    102 do_test minmax3-1.2.4 {
    103   # Index optimizes the WHERE x='2' constraint and the MAX(y).
    104   execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) }
    105   count   { SELECT min(y) FROM t1 WHERE x = '2'; }
    106 } {II 1}
    107 
    108 do_test minmax3-1.3.1 {
    109   # Linear scan
    110   execsql { DROP INDEX i1 ; DROP INDEX i2 }
    111   count   { SELECT min(y) FROM t1; }
    112 } {I 5}
    113 do_test minmax3-1.3.2 {
    114   # Index i1 optimizes the min(y)
    115   execsql { CREATE INDEX i1 ON t1(y) }
    116   count   { SELECT min(y) FROM t1; }
    117 } {I 1}
    118 do_test minmax3-1.3.3 {
    119   # Index i1 optimizes the min(y)
    120   execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) }
    121   count   { SELECT min(y) FROM t1; }
    122 } {I 1}
    123 
    124 do_test minmax3-1.4.1 {
    125   # Linear scan
    126   execsql { DROP INDEX i1 }
    127   count   { SELECT max(y) FROM t1; }
    128 } {VI 5}
    129 do_test minmax3-1.4.2 {
    130   # Index i1 optimizes the max(y)
    131   execsql { CREATE INDEX i1 ON t1(y) }
    132   count   { SELECT max(y) FROM t1; }
    133 } {VI 0}
    134 do_test minmax3-1.4.3 {
    135   # Index i1 optimizes the max(y)
    136   execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) }
    137   execsql   { SELECT y from t1}
    138   count   { SELECT max(y) FROM t1; }
    139 } {VI 0}
    140 do_test minmax3-1.4.4 {
    141   execsql { DROP INDEX i1 }
    142 } {}
    143 
    144 do_test minmax3-2.1 {
    145   execsql {
    146     CREATE TABLE t2(a, b);
    147     CREATE INDEX i3 ON t2(a, b);
    148     INSERT INTO t2 VALUES(1, NULL);
    149     INSERT INTO t2 VALUES(1, 1);
    150     INSERT INTO t2 VALUES(1, 2);
    151     INSERT INTO t2 VALUES(1, 3);
    152     INSERT INTO t2 VALUES(2, NULL);
    153     INSERT INTO t2 VALUES(2, 1);
    154     INSERT INTO t2 VALUES(2, 2);
    155     INSERT INTO t2 VALUES(2, 3);
    156     INSERT INTO t2 VALUES(3, 1);
    157     INSERT INTO t2 VALUES(3, 2);
    158     INSERT INTO t2 VALUES(3, 3);
    159   }
    160 } {}
    161 do_test minmax3-2.2 {
    162   execsql { SELECT min(b) FROM t2 WHERE a = 1; }
    163 } {1}
    164 do_test minmax3-2.3 {
    165   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; }
    166 } {2}
    167 do_test minmax3-2.4 {
    168   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; }
    169 } {1}
    170 do_test minmax3-2.5 {
    171   execsql { SELECT min(b) FROM t2 WHERE a = 1; }
    172 } {1}
    173 do_test minmax3-2.6 {
    174   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; }
    175 } {1}
    176 do_test minmax3-2.7 {
    177   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; }
    178 } {{}}
    179 do_test minmax3-2.8 {
    180   execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; }
    181 } {{}}
    182 
    183 do_test minmax3-3.1 {
    184   execsql {
    185     DROP TABLE t2;
    186     CREATE TABLE t2(a, b);
    187     CREATE INDEX i3 ON t2(a, b DESC);
    188     INSERT INTO t2 VALUES(1, NULL);
    189     INSERT INTO t2 VALUES(1, 1);
    190     INSERT INTO t2 VALUES(1, 2);
    191     INSERT INTO t2 VALUES(1, 3);
    192     INSERT INTO t2 VALUES(2, NULL);
    193     INSERT INTO t2 VALUES(2, 1);
    194     INSERT INTO t2 VALUES(2, 2);
    195     INSERT INTO t2 VALUES(2, 3);
    196     INSERT INTO t2 VALUES(3, 1);
    197     INSERT INTO t2 VALUES(3, 2);
    198     INSERT INTO t2 VALUES(3, 3);
    199   }
    200 } {}
    201 do_test minmax3-3.2 {
    202   execsql { SELECT min(b) FROM t2 WHERE a = 1; }
    203 } {1}
    204 do_test minmax3-3.3 {
    205   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; }
    206 } {2}
    207 do_test minmax3-3.4 {
    208   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; }
    209 } {1}
    210 do_test minmax3-3.5 {
    211   execsql { SELECT min(b) FROM t2 WHERE a = 1; }
    212 } {1}
    213 do_test minmax3-3.6 {
    214   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; }
    215 } {1}
    216 do_test minmax3-3.7 {
    217   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; }
    218 } {{}}
    219 do_test minmax3-3.8 {
    220   execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; }
    221 } {{}}
    222 
    223 do_test minmax3-4.1 {
    224   execsql {
    225     CREATE TABLE t4(x);
    226     INSERT INTO t4 VALUES('abc');
    227     INSERT INTO t4 VALUES('BCD');
    228     SELECT max(x) FROM t4;
    229   }
    230 } {abc}
    231 do_test minmax3-4.2 {
    232   execsql {
    233     SELECT max(x COLLATE nocase) FROM t4;
    234   }
    235 } {BCD}
    236 do_test minmax3-4.3 {
    237   execsql {
    238     SELECT max(x), max(x COLLATE nocase) FROM t4;
    239   }
    240 } {abc BCD}
    241 do_test minmax3-4.4 {
    242   execsql {
    243     SELECT max(x COLLATE binary), max(x COLLATE nocase) FROM t4;
    244   }
    245 } {abc BCD}
    246 do_test minmax3-4.5 {
    247   execsql {
    248     SELECT max(x COLLATE nocase), max(x COLLATE rtrim) FROM t4;
    249   }
    250 } {BCD abc}
    251 do_test minmax3-4.6 {
    252   execsql {
    253     SELECT max(x COLLATE nocase), max(x) FROM t4;
    254   }
    255 } {BCD abc}
    256 do_test minmax3-4.10 {
    257   execsql {
    258     SELECT min(x) FROM t4;
    259   }
    260 } {BCD}
    261 do_test minmax3-4.11 {
    262   execsql {
    263     SELECT min(x COLLATE nocase) FROM t4;
    264   }
    265 } {abc}
    266 do_test minmax3-4.12 {
    267   execsql {
    268     SELECT min(x), min(x COLLATE nocase) FROM t4;
    269   }
    270 } {BCD abc}
    271 do_test minmax3-4.13 {
    272   execsql {
    273     SELECT min(x COLLATE binary), min(x COLLATE nocase) FROM t4;
    274   }
    275 } {BCD abc}
    276 do_test minmax3-4.14 {
    277   execsql {
    278     SELECT min(x COLLATE nocase), min(x COLLATE rtrim) FROM t4;
    279   }
    280 } {abc BCD}
    281 do_test minmax3-4.15 {
    282   execsql {
    283     SELECT min(x COLLATE nocase), min(x) FROM t4;
    284   }
    285 } {abc BCD}
    286 
    287 
    288 finish_test
    289