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 db eval {PRAGMA legacy_file_format=OFF}

    21 

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

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

    24 # 

    25 proc set_file_format {newval} {

    26   hexio_write test.db 44 [hexio_render_int32 $newval]

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

    28   incr schemacookie

    29   hexio_write test.db 40 [hexio_render_int32 $schemacookie]

    30   return {}

    31 }

    32 

    33 # This procedure returns the value of the file-format in file 'test.db'.

    34 # 

    35 proc get_file_format {{fname test.db}} {

    36   return [hexio_get_int [hexio_read $fname 44 4]]

    37 }

    38 

    39 

    40 # Verify that the file format starts as 4.

    41 #

    42 do_test descidx1-1.1 {

    43   execsql {

    44     CREATE TABLE t1(a,b);

    45     CREATE INDEX i1 ON t1(b ASC);

    46   }

    47   get_file_format

    48 } {4}

    49 do_test descidx1-1.2 {

    50   execsql {

    51     CREATE INDEX i2 ON t1(a DESC);

    52   }

    53   get_file_format

    54 } {4}

    55 

    56 # Put some information in the table and verify that the descending

    57 # index actually works.

    58 #

    59 do_test descidx1-2.1 {

    60   execsql {

    61     INSERT INTO t1 VALUES(1,1);

    62     INSERT INTO t1 VALUES(2,2);

    63     INSERT INTO t1 SELECT a+2, a+2 FROM t1;

    64     INSERT INTO t1 SELECT a+4, a+4 FROM t1;

    65     SELECT b FROM t1 WHERE a>3 AND a<7;

    66   }

    67 } {6 5 4}

    68 do_test descidx1-2.2 {

    69   execsql {

    70     SELECT a FROM t1 WHERE b>3 AND b<7;

    71   }

    72 } {4 5 6}

    73 do_test descidx1-2.3 {

    74   execsql {

    75     SELECT b FROM t1 WHERE a>=3 AND a<7;

    76   }

    77 } {6 5 4 3}

    78 do_test descidx1-2.4 {

    79   execsql {

    80     SELECT b FROM t1 WHERE a>3 AND a<=7;

    81   }

    82 } {7 6 5 4}

    83 do_test descidx1-2.5 {

    84   execsql {

    85     SELECT b FROM t1 WHERE a>=3 AND a<=7;

    86   }

    87 } {7 6 5 4 3}

    88 do_test descidx1-2.6 {

    89   execsql {

    90     SELECT a FROM t1 WHERE b>=3 AND b<=7;

    91   }

    92 } {3 4 5 6 7}

    93 

    94 # This procedure executes the SQL.  Then it checks to see if the OP_Sort

    95 # opcode was executed.  If an OP_Sort did occur, then "sort" is appended

    96 # to the result.  If no OP_Sort happened, then "nosort" is appended.

    97 #

    98 # This procedure is used to check to make sure sorting is or is not

    99 # occurring as expected.

   100 #

   101 proc cksort {sql} {

   102   set ::sqlite_sort_count 0

   103   set data [execsql $sql]

   104   if {$::sqlite_sort_count} {set x sort} {set x nosort}

   105   lappend data $x

   106   return $data

   107 }

   108 

   109 # Test sorting using a descending index.

   110 #

   111 do_test descidx1-3.1 {

   112   cksort {SELECT a FROM t1 ORDER BY a}

   113 } {1 2 3 4 5 6 7 8 nosort}

   114 do_test descidx1-3.2 {

   115   cksort {SELECT a FROM t1 ORDER BY a ASC}

   116 } {1 2 3 4 5 6 7 8 nosort}

   117 do_test descidx1-3.3 {

   118   cksort {SELECT a FROM t1 ORDER BY a DESC}

   119 } {8 7 6 5 4 3 2 1 nosort}

   120 do_test descidx1-3.4 {

   121   cksort {SELECT b FROM t1 ORDER BY a}

   122 } {1 2 3 4 5 6 7 8 nosort}

   123 do_test descidx1-3.5 {

   124   cksort {SELECT b FROM t1 ORDER BY a ASC}

   125 } {1 2 3 4 5 6 7 8 nosort}

   126 do_test descidx1-3.6 {

   127   cksort {SELECT b FROM t1 ORDER BY a DESC}

   128 } {8 7 6 5 4 3 2 1 nosort}

   129 do_test descidx1-3.7 {

   130   cksort {SELECT a FROM t1 ORDER BY b}

   131 } {1 2 3 4 5 6 7 8 nosort}

   132 do_test descidx1-3.8 {

   133   cksort {SELECT a FROM t1 ORDER BY b ASC}

   134 } {1 2 3 4 5 6 7 8 nosort}

   135 do_test descidx1-3.9 {

   136   cksort {SELECT a FROM t1 ORDER BY b DESC}

   137 } {8 7 6 5 4 3 2 1 nosort}

   138 do_test descidx1-3.10 {

   139   cksort {SELECT b FROM t1 ORDER BY b}

   140 } {1 2 3 4 5 6 7 8 nosort}

   141 do_test descidx1-3.11 {

   142   cksort {SELECT b FROM t1 ORDER BY b ASC}

   143 } {1 2 3 4 5 6 7 8 nosort}

   144 do_test descidx1-3.12 {

   145   cksort {SELECT b FROM t1 ORDER BY b DESC}

   146 } {8 7 6 5 4 3 2 1 nosort}

   147 

   148 do_test descidx1-3.21 {

   149   cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}

   150 } {4 5 6 7 nosort}

   151 do_test descidx1-3.22 {

   152   cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}

   153 } {4 5 6 7 nosort}

   154 do_test descidx1-3.23 {

   155   cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}

   156 } {7 6 5 4 nosort}

   157 do_test descidx1-3.24 {

   158   cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}

   159 } {4 5 6 7 nosort}

   160 do_test descidx1-3.25 {

   161   cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}

   162 } {4 5 6 7 nosort}

   163 do_test descidx1-3.26 {

   164   cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}

   165 } {7 6 5 4 nosort}

   166 

   167 # Create a table with indices that are descending on some terms and

   168 # ascending on others.

   169 #

   170 ifcapable bloblit {

   171   do_test descidx1-4.1 {

   172     execsql {

   173       CREATE TABLE t2(a INT, b TEXT, c BLOB, d REAL);

   174       CREATE INDEX i3 ON t2(a ASC, b DESC, c ASC);

   175       CREATE INDEX i4 ON t2(b DESC, a ASC, d DESC);

   176       INSERT INTO t2 VALUES(1,'one',x'31',1.0);

   177       INSERT INTO t2 VALUES(2,'two',x'3232',2.0);

   178       INSERT INTO t2 VALUES(3,'three',x'333333',3.0);

   179       INSERT INTO t2 VALUES(4,'four',x'34343434',4.0);

   180       INSERT INTO t2 VALUES(5,'five',x'3535353535',5.0);

   181       INSERT INTO t2 VALUES(6,'six',x'363636363636',6.0);

   182       INSERT INTO t2 VALUES(2,'two',x'323232',2.1);

   183       INSERT INTO t2 VALUES(2,'zwei',x'3232',2.2);

   184       INSERT INTO t2 VALUES(2,NULL,NULL,2.3);

   185       SELECT count(*) FROM t2;

   186     }

   187   } {9}

   188   do_test descidx1-4.2 {

   189     execsql {

   190       SELECT d FROM t2 ORDER BY a;

   191     }

   192   } {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}

   193   do_test descidx1-4.3 {

   194     execsql {

   195       SELECT d FROM t2 WHERE a>=2;

   196     }

   197   } {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}

   198   do_test descidx1-4.4 {

   199     execsql {

   200       SELECT d FROM t2 WHERE a>2;

   201     }

   202   } {3.0 4.0 5.0 6.0}

   203   do_test descidx1-4.5 {

   204     execsql {

   205       SELECT d FROM t2 WHERE a=2 AND b>'two';

   206     }

   207   } {2.2}

   208   do_test descidx1-4.6 {

   209     execsql {

   210       SELECT d FROM t2 WHERE a=2 AND b>='two';

   211     }

   212   } {2.2 2.0 2.1}

   213   do_test descidx1-4.7 {

   214     execsql {

   215       SELECT d FROM t2 WHERE a=2 AND b<'two';

   216     }

   217   } {}

   218   do_test descidx1-4.8 {

   219     execsql {

   220       SELECT d FROM t2 WHERE a=2 AND b<='two';

   221     }

   222   } {2.0 2.1}

   223 }

   224 

   225 do_test descidx1-5.1 {

   226   execsql {

   227     CREATE TABLE t3(a,b,c,d);

   228     CREATE INDEX t3i1 ON t3(a DESC, b ASC, c DESC, d ASC);

   229     INSERT INTO t3 VALUES(0,0,0,0);

   230     INSERT INTO t3 VALUES(0,0,0,1);

   231     INSERT INTO t3 VALUES(0,0,1,0);

   232     INSERT INTO t3 VALUES(0,0,1,1);

   233     INSERT INTO t3 VALUES(0,1,0,0);

   234     INSERT INTO t3 VALUES(0,1,0,1);

   235     INSERT INTO t3 VALUES(0,1,1,0);

   236     INSERT INTO t3 VALUES(0,1,1,1);

   237     INSERT INTO t3 VALUES(1,0,0,0);

   238     INSERT INTO t3 VALUES(1,0,0,1);

   239     INSERT INTO t3 VALUES(1,0,1,0);

   240     INSERT INTO t3 VALUES(1,0,1,1);

   241     INSERT INTO t3 VALUES(1,1,0,0);

   242     INSERT INTO t3 VALUES(1,1,0,1);

   243     INSERT INTO t3 VALUES(1,1,1,0);

   244     INSERT INTO t3 VALUES(1,1,1,1);

   245     SELECT count(*) FROM t3;

   246   }

   247 } {16}

   248 do_test descidx1-5.2 {

   249   cksort {

   250     SELECT a||b||c||d FROM t3 ORDER BY a,b,c,d;

   251   }

   252 } {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111 sort}

   253 do_test descidx1-5.3 {

   254   cksort {

   255     SELECT a||b||c||d FROM t3 ORDER BY a DESC, b ASC, c DESC, d ASC;

   256   }

   257 } {1010 1011 1000 1001 1110 1111 1100 1101 0010 0011 0000 0001 0110 0111 0100 0101 nosort}

   258 do_test descidx1-5.4 {

   259   cksort {

   260     SELECT a||b||c||d FROM t3 ORDER BY a ASC, b DESC, c ASC, d DESC;

   261   }

   262 } {0101 0100 0111 0110 0001 0000 0011 0010 1101 1100 1111 1110 1001 1000 1011 1010 nosort}

   263 do_test descidx1-5.5 {

   264   cksort {

   265     SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b ASC, c DESC

   266   }

   267 } {101 100 111 110 001 000 011 010 nosort}

   268 do_test descidx1-5.6 {

   269   cksort {

   270     SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c ASC

   271   }

   272 } {010 011 000 001 110 111 100 101 nosort}

   273 do_test descidx1-5.7 {

   274   cksort {

   275     SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c DESC

   276   }

   277 } {011 010 001 000 111 110 101 100 sort}

   278 do_test descidx1-5.8 {

   279   cksort {

   280     SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b ASC, c ASC

   281   }

   282 } {000 001 010 011 100 101 110 111 sort}

   283 do_test descidx1-5.9 {

   284   cksort {

   285     SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b DESC, c ASC

   286   }

   287 } {110 111 100 101 010 011 000 001 sort}

   288 

   289 # Test the legacy_file_format pragma here because we have access to

   290 # the get_file_format command.

   291 #

   292 ifcapable legacyformat {

   293   do_test descidx1-6.1 {

   294     db close

   295     file delete -force test.db test.db-journal

   296     sqlite3 db test.db

   297     execsql {PRAGMA legacy_file_format}

   298   } {1}

   299 } else {

   300   do_test descidx1-6.1 {

   301     db close

   302     file delete -force test.db test.db-journal

   303     sqlite3 db test.db

   304     execsql {PRAGMA legacy_file_format}

   305   } {0}

   306 }

   307 do_test descidx1-6.2 {

   308   execsql {PRAGMA legacy_file_format=YES}

   309   execsql {PRAGMA legacy_file_format}

   310 } {1}

   311 do_test descidx1-6.3 {

   312   execsql {

   313     CREATE TABLE t1(a,b,c);

   314   }

   315   get_file_format

   316 } {1}

   317 ifcapable vacuum {

   318   # Verify that the file format is preserved across a vacuum.

   319   do_test descidx1-6.3.1 {

   320     execsql {VACUUM}

   321     get_file_format

   322   } {1}

   323 }

   324 do_test descidx1-6.4 {

   325   db close

   326   file delete -force test.db test.db-journal

   327   sqlite3 db test.db

   328   execsql {PRAGMA legacy_file_format=NO}

   329   execsql {PRAGMA legacy_file_format}

   330 } {0}

   331 do_test descidx1-6.5 {

   332   execsql {

   333     CREATE TABLE t1(a,b,c);

   334     CREATE INDEX i1 ON t1(a ASC, b DESC, c ASC);

   335     INSERT INTO t1 VALUES(1,2,3);

   336     INSERT INTO t1 VALUES(1,1,0);

   337     INSERT INTO t1 VALUES(1,2,1);

   338     INSERT INTO t1 VALUES(1,3,4);

   339   }

   340   get_file_format

   341 } {4}

   342 ifcapable vacuum {

   343   # Verify that the file format is preserved across a vacuum.

   344   do_test descidx1-6.6 {

   345     execsql {VACUUM}

   346     get_file_format

   347   } {4}

   348   do_test descidx1-6.7 {

   349     execsql {

   350       PRAGMA legacy_file_format=ON;

   351       VACUUM;

   352     }

   353     get_file_format

   354   } {4}

   355 } 

   356 

   357 

   358 

   359 finish_test