test/descidx1.test

   1 # 2005 December 21
   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 script is descending indices.
  13 #
  14 # $Id: descidx1.test,v 1.10 2008/03/19 00:21:31 drh Exp $
  15 #
  16
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19
  20 # Do not use a codec for tests in this file, as the database file is
  21 # manipulated directly using tcl scripts (using the [hexio_write] command).
  22 #
  23 do_not_use_codec
  24
  25 #db eval {PRAGMA legacy_file_format=OFF}
  26 sqlite3_db_config db LEGACY_FILE_FORMAT 0
  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 # This procedure returns the value of the file-format in file 'test.db'.
  40 #
  41 proc get_file_format {{fname test.db}} {
  42   return [hexio_get_int [hexio_read $fname 44 4]]
  43 }
  44
  45
  46 # Verify that the file format starts as 4.
  47 #
  48 do_test descidx1-1.1 {
  49   execsql {
  50     CREATE TABLE t1(a,b);
  51     CREATE INDEX i1 ON t1(b ASC);
  52   }
  53   get_file_format
  54 } {4}
  55 do_test descidx1-1.2 {
  56   execsql {
  57     CREATE INDEX i2 ON t1(a DESC);
  58   }
  59   get_file_format
  60 } {4}
  61
  62 # Put some information in the table and verify that the descending
  63 # index actually works.
  64 #
  65 do_test descidx1-2.1 {
  66   execsql {
  67     INSERT INTO t1 VALUES(1,1);
  68     INSERT INTO t1 VALUES(2,2);
  69     INSERT INTO t1 SELECT a+2, a+2 FROM t1;
  70     INSERT INTO t1 SELECT a+4, a+4 FROM t1;
  71     SELECT b FROM t1 WHERE a>3 AND a<7;
  72   }
  73 } {6 5 4}
  74 do_test descidx1-2.2 {
  75   execsql {
  76     SELECT a FROM t1 WHERE b>3 AND b<7;
  77   }
  78 } {4 5 6}
  79 do_test descidx1-2.3 {
  80   execsql {
  81     SELECT b FROM t1 WHERE a>=3 AND a<7;
  82   }
  83 } {6 5 4 3}
  84 do_test descidx1-2.4 {
  85   execsql {
  86     SELECT b FROM t1 WHERE a>3 AND a<=7;
  87   }
  88 } {7 6 5 4}
  89 do_test descidx1-2.5 {
  90   execsql {
  91     SELECT b FROM t1 WHERE a>=3 AND a<=7;
  92   }
  93 } {7 6 5 4 3}
  94 do_test descidx1-2.6 {
  95   execsql {
  96     SELECT a FROM t1 WHERE b>=3 AND b<=7;
  97   }
  98 } {3 4 5 6 7}
  99
 100 # This procedure executes the SQL.  Then it checks to see if the OP_Sort
 101 # opcode was executed.  If an OP_Sort did occur, then "sort" is appended
 102 # to the result.  If no OP_Sort happened, then "nosort" is appended.
 103 #
 104 # This procedure is used to check to make sure sorting is or is not
 105 # occurring as expected.
 106 #
 107 proc cksort {sql} {
 108   set ::sqlite_sort_count 0
 109   set data [execsql $sql]
 110   if {$::sqlite_sort_count} {set x sort} {set x nosort}
 111   lappend data $x
 112   return $data
 113 }
 114
 115 # Test sorting using a descending index.
 116 #
 117 do_test descidx1-3.1 {
 118   cksort {SELECT a FROM t1 ORDER BY a}
 119 } {1 2 3 4 5 6 7 8 nosort}
 120 do_test descidx1-3.2 {
 121   cksort {SELECT a FROM t1 ORDER BY a ASC}
 122 } {1 2 3 4 5 6 7 8 nosort}
 123 do_test descidx1-3.3 {
 124   cksort {SELECT a FROM t1 ORDER BY a DESC}
 125 } {8 7 6 5 4 3 2 1 nosort}
 126 do_test descidx1-3.4 {
 127   cksort {SELECT b FROM t1 ORDER BY a}
 128 } {1 2 3 4 5 6 7 8 nosort}
 129 do_test descidx1-3.5 {
 130   cksort {SELECT b FROM t1 ORDER BY a ASC}
 131 } {1 2 3 4 5 6 7 8 nosort}
 132 do_test descidx1-3.6 {
 133   cksort {SELECT b FROM t1 ORDER BY a DESC}
 134 } {8 7 6 5 4 3 2 1 nosort}
 135 do_test descidx1-3.7 {
 136   cksort {SELECT a FROM t1 ORDER BY b}
 137 } {1 2 3 4 5 6 7 8 nosort}
 138 do_test descidx1-3.8 {
 139   cksort {SELECT a FROM t1 ORDER BY b ASC}
 140 } {1 2 3 4 5 6 7 8 nosort}
 141 do_test descidx1-3.9 {
 142   cksort {SELECT a FROM t1 ORDER BY b DESC}
 143 } {8 7 6 5 4 3 2 1 nosort}
 144 do_test descidx1-3.10 {
 145   cksort {SELECT b FROM t1 ORDER BY b}
 146 } {1 2 3 4 5 6 7 8 nosort}
 147 do_test descidx1-3.11 {
 148   cksort {SELECT b FROM t1 ORDER BY b ASC}
 149 } {1 2 3 4 5 6 7 8 nosort}
 150 do_test descidx1-3.12 {
 151   cksort {SELECT b FROM t1 ORDER BY b DESC}
 152 } {8 7 6 5 4 3 2 1 nosort}
 153
 154 do_test descidx1-3.21 {
 155   cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
 156 } {4 5 6 7 nosort}
 157 do_test descidx1-3.22 {
 158   cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
 159 } {4 5 6 7 nosort}
 160 do_test descidx1-3.23 {
 161   cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
 162 } {7 6 5 4 nosort}
 163 do_test descidx1-3.24 {
 164   cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
 165 } {4 5 6 7 nosort}
 166 do_test descidx1-3.25 {
 167   cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
 168 } {4 5 6 7 nosort}
 169 do_test descidx1-3.26 {
 170   cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
 171 } {7 6 5 4 nosort}
 172
 173 # Create a table with indices that are descending on some terms and
 174 # ascending on others.
 175 #
 176 ifcapable bloblit {
 177   do_test descidx1-4.1 {
 178     execsql {
 179       CREATE TABLE t2(a INT, b TEXT, c BLOB, d REAL);
 180       CREATE INDEX i3 ON t2(a ASC, b DESC, c ASC);
 181       CREATE INDEX i4 ON t2(b DESC, a ASC, d DESC);
 182       INSERT INTO t2 VALUES(1,'one',x'31',1.0);
 183       INSERT INTO t2 VALUES(2,'two',x'3232',2.0);
 184       INSERT INTO t2 VALUES(3,'three',x'333333',3.0);
 185       INSERT INTO t2 VALUES(4,'four',x'34343434',4.0);
 186       INSERT INTO t2 VALUES(5,'five',x'3535353535',5.0);
 187       INSERT INTO t2 VALUES(6,'six',x'363636363636',6.0);
 188       INSERT INTO t2 VALUES(2,'two',x'323232',2.1);
 189       INSERT INTO t2 VALUES(2,'zwei',x'3232',2.2);
 190       INSERT INTO t2 VALUES(2,NULL,NULL,2.3);
 191       SELECT count(*) FROM t2;
 192     }
 193   } {9}
 194   do_test descidx1-4.2 {
 195     execsql {
 196       SELECT d FROM t2 ORDER BY a;
 197     }
 198   } {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
 199   do_test descidx1-4.3 {
 200     execsql {
 201       SELECT d FROM t2 WHERE a>=2 ORDER BY a;
 202     }
 203   } {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
 204   do_test descidx1-4.4 {
 205     execsql {
 206       SELECT d FROM t2 WHERE a>2 ORDER BY a;
 207     }
 208   } {3.0 4.0 5.0 6.0}
 209   do_test descidx1-4.5 {
 210     execsql {
 211       SELECT d FROM t2 WHERE a=2 AND b>'two';
 212     }
 213   } {2.2}
 214   do_test descidx1-4.6 {
 215     execsql {
 216       SELECT d FROM t2 WHERE a=2 AND b>='two';
 217     }
 218   } {2.2 2.0 2.1}
 219   do_test descidx1-4.7 {
 220     execsql {
 221       SELECT d FROM t2 WHERE a=2 AND b<'two';
 222     }
 223   } {}
 224   do_test descidx1-4.8 {
 225     execsql {
 226       SELECT d FROM t2 WHERE a=2 AND b<='two';
 227     }
 228   } {2.0 2.1}
 229 }
 230
 231 do_test descidx1-5.1 {
 232   execsql {
 233     CREATE TABLE t3(a,b,c,d);
 234     CREATE INDEX t3i1 ON t3(a DESC, b ASC, c DESC, d ASC);
 235     INSERT INTO t3 VALUES(0,0,0,0);
 236     INSERT INTO t3 VALUES(0,0,0,1);
 237     INSERT INTO t3 VALUES(0,0,1,0);
 238     INSERT INTO t3 VALUES(0,0,1,1);
 239     INSERT INTO t3 VALUES(0,1,0,0);
 240     INSERT INTO t3 VALUES(0,1,0,1);
 241     INSERT INTO t3 VALUES(0,1,1,0);
 242     INSERT INTO t3 VALUES(0,1,1,1);
 243     INSERT INTO t3 VALUES(1,0,0,0);
 244     INSERT INTO t3 VALUES(1,0,0,1);
 245     INSERT INTO t3 VALUES(1,0,1,0);
 246     INSERT INTO t3 VALUES(1,0,1,1);
 247     INSERT INTO t3 VALUES(1,1,0,0);
 248     INSERT INTO t3 VALUES(1,1,0,1);
 249     INSERT INTO t3 VALUES(1,1,1,0);
 250     INSERT INTO t3 VALUES(1,1,1,1);
 251     SELECT count(*) FROM t3;
 252   }
 253 } {16}
 254 do_test descidx1-5.2 {
 255   cksort {
 256     SELECT a||b||c||d FROM t3 ORDER BY a,b,c,d;
 257   }
 258 } {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111 sort}
 259 do_test descidx1-5.3 {
 260   cksort {
 261     SELECT a||b||c||d FROM t3 ORDER BY a DESC, b ASC, c DESC, d ASC;
 262   }
 263 } {1010 1011 1000 1001 1110 1111 1100 1101 0010 0011 0000 0001 0110 0111 0100 0101 nosort}
 264 do_test descidx1-5.4 {
 265   cksort {
 266     SELECT a||b||c||d FROM t3 ORDER BY a ASC, b DESC, c ASC, d DESC;
 267   }
 268 } {0101 0100 0111 0110 0001 0000 0011 0010 1101 1100 1111 1110 1001 1000 1011 1010 nosort}
 269 do_test descidx1-5.5 {
 270   cksort {
 271     SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b ASC, c DESC
 272   }
 273 } {101 100 111 110 001 000 011 010 nosort}
 274 do_test descidx1-5.6 {
 275   cksort {
 276     SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c ASC
 277   }
 278 } {010 011 000 001 110 111 100 101 nosort}
 279 do_test descidx1-5.7 {
 280   cksort {
 281     SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c DESC
 282   }
 283 } {011 010 001 000 111 110 101 100 sort}
 284 do_test descidx1-5.8 {
 285   cksort {
 286     SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b ASC, c ASC
 287   }
 288 } {000 001 010 011 100 101 110 111 sort}
 289 do_test descidx1-5.9 {
 290   cksort {
 291     SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b DESC, c ASC
 292   }
 293 } {110 111 100 101 010 011 000 001 sort}
 294
 295 # Test the legacy_file_format pragma here because we have access to
 296 # the get_file_format command.
 297 #
 298 ifcapable legacyformat {
 299   do_test descidx1-6.1 {
 300     db close
 301     forcedelete test.db test.db-journal
 302     sqlite3 db test.db
 303     sqlite3_db_config db LEGACY_FILE_FORMAT
 304   } {1}
 305 } else {
 306   do_test descidx1-6.1 {
 307     db close
 308     forcedelete test.db test.db-journal
 309     sqlite3 db test.db
 310     sqlite3_db_config db LEGACY_FILE_FORMAT
 311   } {0}
 312 }
 313 do_test descidx1-6.2 {
 314   sqlite3_db_config db LEGACY_FILE_FORMAT 1
 315   sqlite3_db_config db LEGACY_FILE_FORMAT
 316 } {1}
 317 do_test descidx1-6.3 {
 318   execsql {
 319     CREATE TABLE t1(a,b,c);
 320   }
 321   get_file_format
 322 } {1}
 323 ifcapable vacuum {
 324   # Verify that the file format is preserved across a vacuum.
 325   do_test descidx1-6.3.1 {
 326     execsql {VACUUM}
 327     get_file_format
 328   } {1}
 329 }
 330 do_test descidx1-6.4 {
 331   db close
 332   forcedelete test.db test.db-journal
 333   sqlite3 db test.db
 334   sqlite3_db_config db LEGACY_FILE_FORMAT 0
 335   sqlite3_db_config db LEGACY_FILE_FORMAT
 336 } {0}
 337 do_test descidx1-6.5 {
 338   execsql {
 339     CREATE TABLE t1(a,b,c);
 340     CREATE INDEX i1 ON t1(a ASC, b DESC, c ASC);
 341     INSERT INTO t1 VALUES(1,2,3);
 342     INSERT INTO t1 VALUES(1,1,0);
 343     INSERT INTO t1 VALUES(1,2,1);
 344     INSERT INTO t1 VALUES(1,3,4);
 345   }
 346   get_file_format
 347 } {4}
 348 ifcapable vacuum {
 349   # Verify that the file format is preserved across a vacuum.
 350   do_test descidx1-6.6 {
 351     execsql {VACUUM}
 352     get_file_format
 353   } {4}
 354   do_test descidx1-6.7 {
 355     sqlite3_db_config db LEGACY_FILE_FORMAT 1
 356     execsql {
 357       VACUUM;
 358     }
 359     get_file_format
 360   } {4}
 361 }
 362
 363
 364
 365 finish_test