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 an SQL statement.  Append the search count to the end of the result.

    17 #

    18 proc count sql {

    19   set ::sqlite_search_count 0

    20   return [concat [execsql $sql] $::sqlite_search_count]

    21 }

    22 

    23 # This procedure sets the value of the file-format in file 'test.db'

    24 # to $newval. Also, the schema cookie is incremented.

    25 # 

    26 proc set_file_format {newval} {

    27   hexio_write test.db 44 [hexio_render_int32 $newval]

    28   set schemacookie [hexio_get_int [hexio_read test.db 40 4]]

    29   incr schemacookie

    30   hexio_write test.db 40 [hexio_render_int32 $schemacookie]

    31   return {}

    32 }

    33 

    34 do_test minmax3-1.0 {

    35   execsql {

    36     CREATE TABLE t1(x, y, z);

    37   }

    38   db close

    39   set_file_format 4

    40   sqlite3 db test.db

    41   execsql {

    42     BEGIN;

    43     INSERT INTO t1 VALUES('1', 'I',   'one');

    44     INSERT INTO t1 VALUES('2', 'IV',  'four');

    45     INSERT INTO t1 VALUES('2', NULL,  'three');

    46     INSERT INTO t1 VALUES('2', 'II',  'two');

    47     INSERT INTO t1 VALUES('2', 'V',   'five');

    48     INSERT INTO t1 VALUES('3', 'VI',  'six');

    49     COMMIT;

    50   }

    51 } {}

    52 do_test minmax3-1.1.1 {

    53   # Linear scan.

    54   count { SELECT max(y) FROM t1 WHERE x = '2'; }

    55 } {V 5}

    56 do_test minmax3-1.1.2 {

    57   # Index optimizes the WHERE x='2' constraint.

    58   execsql { CREATE INDEX i1 ON t1(x) }

    59   count   { SELECT max(y) FROM t1 WHERE x = '2'; }

    60 } {V 9}

    61 do_test minmax3-1.1.3 {

    62   # Index optimizes the WHERE x='2' constraint and the MAX(y).

    63   execsql { CREATE INDEX i2 ON t1(x,y) }

    64   count   { SELECT max(y) FROM t1 WHERE x = '2'; }

    65 } {V 1}

    66 do_test minmax3-1.1.4 {

    67   # Index optimizes the WHERE x='2' constraint and the MAX(y).

    68   execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) }

    69   count   { SELECT max(y) FROM t1 WHERE x = '2'; }

    70 } {V 1}

    71 do_test minmax3-1.1.5 {

    72   count   { SELECT max(y) FROM t1 WHERE x = '2' AND y != 'V'; }

    73 } {IV 2}

    74 do_test minmax3-1.1.6 {

    75   count   { SELECT max(y) FROM t1 WHERE x = '2' AND y < 'V'; }

    76 } {IV 1}

    77 do_test minmax3-1.1.6 {

    78   count   { SELECT max(y) FROM t1 WHERE x = '2' AND z != 'five'; }

    79 } {IV 4}

    80 

    81 do_test minmax3-1.2.1 {

    82   # Linear scan of t1.

    83   execsql { DROP INDEX i1 ; DROP INDEX i2 }

    84   count { SELECT min(y) FROM t1 WHERE x = '2'; }

    85 } {II 5}

    86 do_test minmax3-1.2.2 {

    87   # Index i1 optimizes the WHERE x='2' constraint.

    88   execsql { CREATE INDEX i1 ON t1(x) }

    89   count   { SELECT min(y) FROM t1 WHERE x = '2'; }

    90 } {II 9}

    91 do_test minmax3-1.2.3 {

    92   # Index i2 optimizes the WHERE x='2' constraint and the min(y).

    93   execsql { CREATE INDEX i2 ON t1(x,y) }

    94   count   { SELECT min(y) FROM t1 WHERE x = '2'; }

    95 } {II 1}

    96 do_test minmax3-1.2.4 {

    97   # Index optimizes the WHERE x='2' constraint and the MAX(y).

    98   execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) }

    99   count   { SELECT min(y) FROM t1 WHERE x = '2'; }

   100 } {II 1}

   101 

   102 do_test minmax3-1.3.1 {

   103   # Linear scan

   104   execsql { DROP INDEX i1 ; DROP INDEX i2 }

   105   count   { SELECT min(y) FROM t1; }

   106 } {I 5}

   107 do_test minmax3-1.3.2 {

   108   # Index i1 optimizes the min(y)

   109   execsql { CREATE INDEX i1 ON t1(y) }

   110   count   { SELECT min(y) FROM t1; }

   111 } {I 1}

   112 do_test minmax3-1.3.3 {

   113   # Index i1 optimizes the min(y)

   114   execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) }

   115   count   { SELECT min(y) FROM t1; }

   116 } {I 1}

   117 

   118 do_test minmax3-1.4.1 {

   119   # Linear scan

   120   execsql { DROP INDEX i1 }

   121   count   { SELECT max(y) FROM t1; }

   122 } {VI 5}

   123 do_test minmax3-1.4.2 {

   124   # Index i1 optimizes the max(y)

   125   execsql { CREATE INDEX i1 ON t1(y) }

   126   count   { SELECT max(y) FROM t1; }

   127 } {VI 0}

   128 do_test minmax3-1.4.3 {

   129   # Index i1 optimizes the max(y)

   130   execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) }

   131   execsql   { SELECT y from t1}

   132   count   { SELECT max(y) FROM t1; }

   133 } {VI 0}

   134 do_test minmax3-1.4.4 {

   135   execsql { DROP INDEX i1 }

   136 } {}

   137 

   138 do_test minmax3-2.1 {

   139   execsql {

   140     CREATE TABLE t2(a, b);

   141     CREATE INDEX i3 ON t2(a, b);

   142     INSERT INTO t2 VALUES(1, NULL);

   143     INSERT INTO t2 VALUES(1, 1);

   144     INSERT INTO t2 VALUES(1, 2);

   145     INSERT INTO t2 VALUES(1, 3);

   146     INSERT INTO t2 VALUES(2, NULL);

   147     INSERT INTO t2 VALUES(2, 1);

   148     INSERT INTO t2 VALUES(2, 2);

   149     INSERT INTO t2 VALUES(2, 3);

   150     INSERT INTO t2 VALUES(3, 1);

   151     INSERT INTO t2 VALUES(3, 2);

   152     INSERT INTO t2 VALUES(3, 3);

   153   }

   154 } {}

   155 do_test minmax3-2.2 {

   156   execsql { SELECT min(b) FROM t2 WHERE a = 1; }

   157 } {1}

   158 do_test minmax3-2.3 {

   159   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; }

   160 } {2}

   161 do_test minmax3-2.4 {

   162   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; }

   163 } {1}

   164 do_test minmax3-2.5 {

   165   execsql { SELECT min(b) FROM t2 WHERE a = 1; }

   166 } {1}

   167 do_test minmax3-2.6 {

   168   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; }

   169 } {1}

   170 do_test minmax3-2.7 {

   171   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; }

   172 } {{}}

   173 do_test minmax3-2.8 {

   174   execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; }

   175 } {{}}

   176 

   177 do_test minmax3-2.1 {

   178   execsql {

   179     DROP TABLE t2;

   180     CREATE TABLE t2(a, b);

   181     CREATE INDEX i3 ON t2(a, b DESC);

   182     INSERT INTO t2 VALUES(1, NULL);

   183     INSERT INTO t2 VALUES(1, 1);

   184     INSERT INTO t2 VALUES(1, 2);

   185     INSERT INTO t2 VALUES(1, 3);

   186     INSERT INTO t2 VALUES(2, NULL);

   187     INSERT INTO t2 VALUES(2, 1);

   188     INSERT INTO t2 VALUES(2, 2);

   189     INSERT INTO t2 VALUES(2, 3);

   190     INSERT INTO t2 VALUES(3, 1);

   191     INSERT INTO t2 VALUES(3, 2);

   192     INSERT INTO t2 VALUES(3, 3);

   193   }

   194 } {}

   195 do_test minmax3-2.2 {

   196   execsql { SELECT min(b) FROM t2 WHERE a = 1; }

   197 } {1}

   198 do_test minmax3-2.3 {

   199   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; }

   200 } {2}

   201 do_test minmax3-2.4 {

   202   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; }

   203 } {1}

   204 do_test minmax3-2.5 {

   205   execsql { SELECT min(b) FROM t2 WHERE a = 1; }

   206 } {1}

   207 do_test minmax3-2.6 {

   208   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; }

   209 } {1}

   210 do_test minmax3-2.7 {

   211   execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; }

   212 } {{}}

   213 do_test minmax3-2.8 {

   214   execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; }

   215 } {{}}

   216 

   217 finish_test