PageRenderTime 22ms CodeModel.GetById 18ms app.highlight 2ms RepoModel.GetById 0ms app.codeStats 0ms

/trunk/src/sqlite/test/descidx1.test

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