AryaWu/sqlite

1# 2005 December 212#3# The author disclaims copyright to this source code.  In place of4# 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.  The12# focus of this script is descending indices.13#14# $Id: descidx2.test,v 1.5 2008/03/19 00:21:31 drh Exp $15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20# Do not use a codec for tests in this file, as the database file is21# manipulated directly using tcl scripts (using the [hexio_write] command).22#23do_not_use_codec24 25 26#db eval {PRAGMA legacy_file_format=OFF}27sqlite3_db_config db LEGACY_FILE_FORMAT 028 29# This procedure sets the value of the file-format in file 'test.db'30# to $newval. Also, the schema cookie is incremented.31# 32proc set_file_format {newval} {33  hexio_write test.db 44 [hexio_render_int32 $newval]34  set schemacookie [hexio_get_int [hexio_read test.db 40 4]]35  incr schemacookie36  hexio_write test.db 40 [hexio_render_int32 $schemacookie]37  return {}38}39 40# This procedure returns the value of the file-format in file 'test.db'.41# 42proc get_file_format {{fname test.db}} {43  return [hexio_get_int [hexio_read $fname 44 4]]44}45 46 47# Verify that the file format starts as 448#49do_test descidx2-1.1 {50  execsql {51    CREATE TABLE t1(a,b);52    CREATE INDEX i1 ON t1(b ASC);53  }54  get_file_format55} {4}56do_test descidx2-1.2 {57  execsql {58    CREATE INDEX i2 ON t1(a DESC);59  }60  get_file_format61} {4}62 63# Before adding any information to the database, set the file format64# back to three.  Then close and reopen the database.  With the file65# format set to three, SQLite should ignore the DESC argument on the66# index.67#68do_test descidx2-2.0 {69  set_file_format 370  db close71  sqlite3 db test.db72  get_file_format73} {3}74 75# Put some information in the table and verify that the DESC76# on the index is ignored.77#78do_test descidx2-2.1 {79  execsql {80    INSERT INTO t1 VALUES(1,1);81    INSERT INTO t1 VALUES(2,2);82    INSERT INTO t1 SELECT a+2, a+2 FROM t1;83    INSERT INTO t1 SELECT a+4, a+4 FROM t1;84    SELECT b FROM t1 WHERE a>3 AND a<7;85  }86} {4 5 6}87do_test descidx2-2.2 {88  execsql {89    SELECT a FROM t1 WHERE b>3 AND b<7;90  }91} {4 5 6}92do_test descidx2-2.3 {93  execsql {94    SELECT b FROM t1 WHERE a>=3 AND a<7;95  }96} {3 4 5 6}97do_test descidx2-2.4 {98  execsql {99    SELECT b FROM t1 WHERE a>3 AND a<=7;100  }101} {4 5 6 7}102do_test descidx2-2.5 {103  execsql {104    SELECT b FROM t1 WHERE a>=3 AND a<=7;105  }106} {3 4 5 6 7}107do_test descidx2-2.6 {108  execsql {109    SELECT a FROM t1 WHERE b>=3 AND b<=7;110  }111} {3 4 5 6 7}112 113# This procedure executes the SQL.  Then it checks to see if the OP_Sort114# opcode was executed.  If an OP_Sort did occur, then "sort" is appended115# to the result.  If no OP_Sort happened, then "nosort" is appended.116#117# This procedure is used to check to make sure sorting is or is not118# occurring as expected.119#120proc cksort {sql} {121  set ::sqlite_sort_count 0122  set data [execsql $sql]123  if {$::sqlite_sort_count} {set x sort} {set x nosort}124  lappend data $x125  return $data126}127 128# Test sorting using a descending index.129#130do_test descidx2-3.1 {131  cksort {SELECT a FROM t1 ORDER BY a}132} {1 2 3 4 5 6 7 8 nosort}133do_test descidx2-3.2 {134  cksort {SELECT a FROM t1 ORDER BY a ASC}135} {1 2 3 4 5 6 7 8 nosort}136do_test descidx2-3.3 {137  cksort {SELECT a FROM t1 ORDER BY a DESC}138} {8 7 6 5 4 3 2 1 nosort}139do_test descidx2-3.4 {140  cksort {SELECT b FROM t1 ORDER BY a}141} {1 2 3 4 5 6 7 8 nosort}142do_test descidx2-3.5 {143  cksort {SELECT b FROM t1 ORDER BY a ASC}144} {1 2 3 4 5 6 7 8 nosort}145do_test descidx2-3.6 {146  cksort {SELECT b FROM t1 ORDER BY a DESC}147} {8 7 6 5 4 3 2 1 nosort}148do_test descidx2-3.7 {149  cksort {SELECT a FROM t1 ORDER BY b}150} {1 2 3 4 5 6 7 8 nosort}151do_test descidx2-3.8 {152  cksort {SELECT a FROM t1 ORDER BY b ASC}153} {1 2 3 4 5 6 7 8 nosort}154do_test descidx2-3.9 {155  cksort {SELECT a FROM t1 ORDER BY b DESC}156} {8 7 6 5 4 3 2 1 nosort}157do_test descidx2-3.10 {158  cksort {SELECT b FROM t1 ORDER BY b}159} {1 2 3 4 5 6 7 8 nosort}160do_test descidx2-3.11 {161  cksort {SELECT b FROM t1 ORDER BY b ASC}162} {1 2 3 4 5 6 7 8 nosort}163do_test descidx2-3.12 {164  cksort {SELECT b FROM t1 ORDER BY b DESC}165} {8 7 6 5 4 3 2 1 nosort}166 167do_test descidx2-3.21 {168  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}169} {4 5 6 7 nosort}170do_test descidx2-3.22 {171  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}172} {4 5 6 7 nosort}173do_test descidx2-3.23 {174  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}175} {7 6 5 4 nosort}176do_test descidx2-3.24 {177  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}178} {4 5 6 7 nosort}179do_test descidx2-3.25 {180  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}181} {4 5 6 7 nosort}182do_test descidx2-3.26 {183  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}184} {7 6 5 4 nosort}185 186finish_test187