sl@0: # 2005 December 21
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #*************************************************************************
sl@0: # This file implements regression tests for SQLite library.  The
sl@0: # focus of this script is descending indices.
sl@0: #
sl@0: # $Id: descidx2.test,v 1.5 2008/03/19 00:21:31 drh Exp $
sl@0: #
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: db eval {PRAGMA legacy_file_format=OFF}
sl@0: 
sl@0: # This procedure sets the value of the file-format in file 'test.db'
sl@0: # to $newval. Also, the schema cookie is incremented.
sl@0: # 
sl@0: proc set_file_format {newval} {
sl@0:   hexio_write test.db 44 [hexio_render_int32 $newval]
sl@0:   set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
sl@0:   incr schemacookie
sl@0:   hexio_write test.db 40 [hexio_render_int32 $schemacookie]
sl@0:   return {}
sl@0: }
sl@0: 
sl@0: # This procedure returns the value of the file-format in file 'test.db'.
sl@0: # 
sl@0: proc get_file_format {{fname test.db}} {
sl@0:   return [hexio_get_int [hexio_read $fname 44 4]]
sl@0: }
sl@0: 
sl@0: 
sl@0: # Verify that the file format starts as 4
sl@0: #
sl@0: do_test descidx2-1.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t1(a,b);
sl@0:     CREATE INDEX i1 ON t1(b ASC);
sl@0:   }
sl@0:   get_file_format
sl@0: } {4}
sl@0: do_test descidx2-1.2 {
sl@0:   execsql {
sl@0:     CREATE INDEX i2 ON t1(a DESC);
sl@0:   }
sl@0:   get_file_format
sl@0: } {4}
sl@0: 
sl@0: # Before adding any information to the database, set the file format
sl@0: # back to three.  Then close and reopen the database.  With the file
sl@0: # format set to three, SQLite should ignore the DESC argument on the
sl@0: # index.
sl@0: #
sl@0: do_test descidx2-2.0 {
sl@0:   set_file_format 3
sl@0:   db close
sl@0:   sqlite3 db test.db
sl@0:   get_file_format
sl@0: } {3}
sl@0: 
sl@0: # Put some information in the table and verify that the DESC
sl@0: # on the index is ignored.
sl@0: #
sl@0: do_test descidx2-2.1 {
sl@0:   execsql {
sl@0:     INSERT INTO t1 VALUES(1,1);
sl@0:     INSERT INTO t1 VALUES(2,2);
sl@0:     INSERT INTO t1 SELECT a+2, a+2 FROM t1;
sl@0:     INSERT INTO t1 SELECT a+4, a+4 FROM t1;
sl@0:     SELECT b FROM t1 WHERE a>3 AND a<7;
sl@0:   }
sl@0: } {4 5 6}
sl@0: do_test descidx2-2.2 {
sl@0:   execsql {
sl@0:     SELECT a FROM t1 WHERE b>3 AND b<7;
sl@0:   }
sl@0: } {4 5 6}
sl@0: do_test descidx2-2.3 {
sl@0:   execsql {
sl@0:     SELECT b FROM t1 WHERE a>=3 AND a<7;
sl@0:   }
sl@0: } {3 4 5 6}
sl@0: do_test descidx2-2.4 {
sl@0:   execsql {
sl@0:     SELECT b FROM t1 WHERE a>3 AND a<=7;
sl@0:   }
sl@0: } {4 5 6 7}
sl@0: do_test descidx2-2.5 {
sl@0:   execsql {
sl@0:     SELECT b FROM t1 WHERE a>=3 AND a<=7;
sl@0:   }
sl@0: } {3 4 5 6 7}
sl@0: do_test descidx2-2.6 {
sl@0:   execsql {
sl@0:     SELECT a FROM t1 WHERE b>=3 AND b<=7;
sl@0:   }
sl@0: } {3 4 5 6 7}
sl@0: 
sl@0: # This procedure executes the SQL.  Then it checks to see if the OP_Sort
sl@0: # opcode was executed.  If an OP_Sort did occur, then "sort" is appended
sl@0: # to the result.  If no OP_Sort happened, then "nosort" is appended.
sl@0: #
sl@0: # This procedure is used to check to make sure sorting is or is not
sl@0: # occurring as expected.
sl@0: #
sl@0: proc cksort {sql} {
sl@0:   set ::sqlite_sort_count 0
sl@0:   set data [execsql $sql]
sl@0:   if {$::sqlite_sort_count} {set x sort} {set x nosort}
sl@0:   lappend data $x
sl@0:   return $data
sl@0: }
sl@0: 
sl@0: # Test sorting using a descending index.
sl@0: #
sl@0: do_test descidx2-3.1 {
sl@0:   cksort {SELECT a FROM t1 ORDER BY a}
sl@0: } {1 2 3 4 5 6 7 8 nosort}
sl@0: do_test descidx2-3.2 {
sl@0:   cksort {SELECT a FROM t1 ORDER BY a ASC}
sl@0: } {1 2 3 4 5 6 7 8 nosort}
sl@0: do_test descidx2-3.3 {
sl@0:   cksort {SELECT a FROM t1 ORDER BY a DESC}
sl@0: } {8 7 6 5 4 3 2 1 nosort}
sl@0: do_test descidx2-3.4 {
sl@0:   cksort {SELECT b FROM t1 ORDER BY a}
sl@0: } {1 2 3 4 5 6 7 8 nosort}
sl@0: do_test descidx2-3.5 {
sl@0:   cksort {SELECT b FROM t1 ORDER BY a ASC}
sl@0: } {1 2 3 4 5 6 7 8 nosort}
sl@0: do_test descidx2-3.6 {
sl@0:   cksort {SELECT b FROM t1 ORDER BY a DESC}
sl@0: } {8 7 6 5 4 3 2 1 nosort}
sl@0: do_test descidx2-3.7 {
sl@0:   cksort {SELECT a FROM t1 ORDER BY b}
sl@0: } {1 2 3 4 5 6 7 8 nosort}
sl@0: do_test descidx2-3.8 {
sl@0:   cksort {SELECT a FROM t1 ORDER BY b ASC}
sl@0: } {1 2 3 4 5 6 7 8 nosort}
sl@0: do_test descidx2-3.9 {
sl@0:   cksort {SELECT a FROM t1 ORDER BY b DESC}
sl@0: } {8 7 6 5 4 3 2 1 nosort}
sl@0: do_test descidx2-3.10 {
sl@0:   cksort {SELECT b FROM t1 ORDER BY b}
sl@0: } {1 2 3 4 5 6 7 8 nosort}
sl@0: do_test descidx2-3.11 {
sl@0:   cksort {SELECT b FROM t1 ORDER BY b ASC}
sl@0: } {1 2 3 4 5 6 7 8 nosort}
sl@0: do_test descidx2-3.12 {
sl@0:   cksort {SELECT b FROM t1 ORDER BY b DESC}
sl@0: } {8 7 6 5 4 3 2 1 nosort}
sl@0: 
sl@0: do_test descidx2-3.21 {
sl@0:   cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
sl@0: } {4 5 6 7 nosort}
sl@0: do_test descidx2-3.22 {
sl@0:   cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
sl@0: } {4 5 6 7 nosort}
sl@0: do_test descidx2-3.23 {
sl@0:   cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
sl@0: } {7 6 5 4 nosort}
sl@0: do_test descidx2-3.24 {
sl@0:   cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
sl@0: } {4 5 6 7 nosort}
sl@0: do_test descidx2-3.25 {
sl@0:   cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
sl@0: } {4 5 6 7 nosort}
sl@0: do_test descidx2-3.26 {
sl@0:   cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
sl@0: } {7 6 5 4 nosort}
sl@0: 
sl@0: finish_test