gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/minmax3.test (about)

     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