/ Check-in [5638a11e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Bug fixes and additional testing of descending indices. (CVS 2841)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:5638a11ed5618dd833d3daffc1715951091d72b2
User & Date: drh 2005-12-21 18:36:46
Context
2005-12-22
13:47
Make genericAllocationSize work on NULL pointers. (CVS 2842) check-in: 326fc9cc user: drh tags: trunk
2005-12-21
18:36
Bug fixes and additional testing of descending indices. (CVS 2841) check-in: 5638a11e user: drh tags: trunk
14:43
Basic functionality for descending indices is in place. Lots more testing needed. (CVS 2840) check-in: 7064433e user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
....
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
....
5813
5814
5815
5816
5817
5818
5819
5820
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** $Id: btree.c,v 1.275 2005/12/16 15:24:29 danielk1977 Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.
................................................................................
}

/* Move the cursor so that it points to an entry near pKey/nKey.
** Return a success code.
**
** For INTKEY tables, only the nKey parameter is used.  pKey is
** ignored.  For other tables, nKey is the number of bytes of data
** in nKey.  The comparison function specified when the cursor was
** created is used to compare keys.
**
** If an exact match is not found, then the cursor is always
** left pointing at a leaf page which would hold the entry if it
** were present.  The cursor might point to an entry that comes
** before or after the key.
**
................................................................................
    }
    return sqlite3pager_sync(pBt->pPager, zMaster, nTrunc);
#endif
    return sqlite3pager_sync(pBt->pPager, zMaster, 0);
  }
  return SQLITE_OK;
}








|







 







|







 







<
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
....
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
....
5813
5814
5815
5816
5817
5818
5819

** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** $Id: btree.c,v 1.276 2005/12/21 18:36:46 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.
................................................................................
}

/* Move the cursor so that it points to an entry near pKey/nKey.
** Return a success code.
**
** For INTKEY tables, only the nKey parameter is used.  pKey is
** ignored.  For other tables, nKey is the number of bytes of data
** in pKey.  The comparison function specified when the cursor was
** created is used to compare keys.
**
** If an exact match is not found, then the cursor is always
** left pointing at a leaf page which would hold the entry if it
** were present.  The cursor might point to an entry that comes
** before or after the key.
**
................................................................................
    }
    return sqlite3pager_sync(pBt->pPager, zMaster, nTrunc);
#endif
    return sqlite3pager_sync(pBt->pPager, zMaster, 0);
  }
  return SQLITE_OK;
}

Changes to src/build.c.

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.360 2005/12/21 14:43:12 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.







|







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.361 2005/12/21 18:36:46 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.

Changes to src/vdbe.c.

39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
....
2759
2760
2761
2762
2763
2764
2765

2766
2767
2768
2769
2770
2771
2772
2773
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.507 2005/12/21 14:43:12 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
      rc = sqlite3BtreeMoveto(pC->pCursor, 0, (u64)iKey, &res);
      if( rc!=SQLITE_OK ){
        goto abort_due_to_error;
      }
      pC->lastRowid = pTos->i;
      pC->rowidIsValid = res==0;
    }else{

      Stringify(pTos, db->enc);
      rc = sqlite3BtreeMoveto(pC->pCursor, pTos->z, pTos->n, &res);
      if( rc!=SQLITE_OK ){
        goto abort_due_to_error;
      }
      pC->rowidIsValid = 0;
    }
    pC->deferredMoveto = 0;







|







 







>
|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
....
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.508 2005/12/21 18:36:46 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
      rc = sqlite3BtreeMoveto(pC->pCursor, 0, (u64)iKey, &res);
      if( rc!=SQLITE_OK ){
        goto abort_due_to_error;
      }
      pC->lastRowid = pTos->i;
      pC->rowidIsValid = res==0;
    }else{
      assert( pTos->flags & MEM_Blob );
      /* Stringify(pTos, db->enc); */
      rc = sqlite3BtreeMoveto(pC->pCursor, pTos->z, pTos->n, &res);
      if( rc!=SQLITE_OK ){
        goto abort_due_to_error;
      }
      pC->rowidIsValid = 0;
    }
    pC->deferredMoveto = 0;

Changes to src/vdbeaux.c.

1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
    if( pKeyInfo->incrKey ){
      rc = -1;
    }else if( d1<nKey1 ){
      rc = 1;
    }else if( d2<nKey2 ){
      rc = -1;
    }
  }

  if( pKeyInfo->aSortOrder && i<pKeyInfo->nField && pKeyInfo->aSortOrder[i] ){
    rc = -rc;
  }

  return rc;
}

/*







|
|
<







1734
1735
1736
1737
1738
1739
1740
1741
1742

1743
1744
1745
1746
1747
1748
1749
    if( pKeyInfo->incrKey ){
      rc = -1;
    }else if( d1<nKey1 ){
      rc = 1;
    }else if( d2<nKey2 ){
      rc = -1;
    }
  }else if( pKeyInfo->aSortOrder && i<pKeyInfo->nField
               && pKeyInfo->aSortOrder[i] ){

    rc = -rc;
  }

  return rc;
}

/*

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1715
1716
1717
1718
1719
1720
1721

1722
1723
1724
1725
1726
1727
1728
....
1736
1737
1738
1739
1740
1741
1742

1743
1744
1745
1746
1747
1748
1749
....
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.188 2005/12/21 03:16:43 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
      */
      int start;
      int nEq = pLevel->nEq;
      int topEq=0;        /* True if top limit uses ==. False is strictly < */
      int btmEq=0;        /* True if btm limit uses ==. False if strictly > */
      int topOp, btmOp;   /* Operators for the top and bottom search bounds */
      int testOp;

      int topLimit = (pLevel->flags & WHERE_TOP_LIMIT)!=0;
      int btmLimit = (pLevel->flags & WHERE_BTM_LIMIT)!=0;

      /* Generate code to evaluate all constraint terms using == or IN
      ** and level the values of those terms on the stack.
      */
      codeAllEqualityTerms(pParse, pLevel, &wc, notReady, brk);
................................................................................
      }

      /* Figure out what comparison operators to use for top and bottom 
      ** search bounds. For an ascending index, the bottom bound is a > or >=
      ** operator and the top bound is a < or <= operator.  For a descending
      ** index the operators are reversed.
      */

      if( pIdx->keyInfo.aSortOrder[nEq]==SQLITE_SO_ASC ){
        topOp = WO_LT|WO_LE;
        btmOp = WO_GT|WO_GE;
      }else{
        topOp = WO_GT|WO_GE;
        btmOp = WO_LT|WO_LE;
        SWAP(int, topLimit, btmLimit);
................................................................................
        sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqlite3VdbeAddOp(v, testOp, iIdxCur, brk);
        if( (topEq && !bRev) || (!btmEq && bRev) ){
          sqlite3VdbeChangeP3(v, -1, "+", P3_STATIC);
        }
      }
      sqlite3VdbeAddOp(v, OP_RowKey, iIdxCur, 0);
      sqlite3VdbeAddOp(v, OP_IdxIsNull, nEq + topLimit, cont);
      if( !omitTable ){
        sqlite3VdbeAddOp(v, OP_IdxRowid, iIdxCur, 0);
        sqlite3VdbeAddOp(v, OP_MoveGe, iCur, 0);
      }

      /* Record the instruction used to terminate the loop.
      */







|







 







>







 







>







 







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
....
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
....
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.189 2005/12/21 18:36:46 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
      */
      int start;
      int nEq = pLevel->nEq;
      int topEq=0;        /* True if top limit uses ==. False is strictly < */
      int btmEq=0;        /* True if btm limit uses ==. False if strictly > */
      int topOp, btmOp;   /* Operators for the top and bottom search bounds */
      int testOp;
      int nNotNull;       /* Number of rows of index that must be non-NULL */
      int topLimit = (pLevel->flags & WHERE_TOP_LIMIT)!=0;
      int btmLimit = (pLevel->flags & WHERE_BTM_LIMIT)!=0;

      /* Generate code to evaluate all constraint terms using == or IN
      ** and level the values of those terms on the stack.
      */
      codeAllEqualityTerms(pParse, pLevel, &wc, notReady, brk);
................................................................................
      }

      /* Figure out what comparison operators to use for top and bottom 
      ** search bounds. For an ascending index, the bottom bound is a > or >=
      ** operator and the top bound is a < or <= operator.  For a descending
      ** index the operators are reversed.
      */
      nNotNull = nEq + topLimit;
      if( pIdx->keyInfo.aSortOrder[nEq]==SQLITE_SO_ASC ){
        topOp = WO_LT|WO_LE;
        btmOp = WO_GT|WO_GE;
      }else{
        topOp = WO_GT|WO_GE;
        btmOp = WO_LT|WO_LE;
        SWAP(int, topLimit, btmLimit);
................................................................................
        sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqlite3VdbeAddOp(v, testOp, iIdxCur, brk);
        if( (topEq && !bRev) || (!btmEq && bRev) ){
          sqlite3VdbeChangeP3(v, -1, "+", P3_STATIC);
        }
      }
      sqlite3VdbeAddOp(v, OP_RowKey, iIdxCur, 0);
      sqlite3VdbeAddOp(v, OP_IdxIsNull, nNotNull, cont);
      if( !omitTable ){
        sqlite3VdbeAddOp(v, OP_IdxRowid, iIdxCur, 0);
        sqlite3VdbeAddOp(v, OP_MoveGe, iCur, 0);
      }

      /* Record the instruction used to terminate the loop.
      */

Changes to test/descidx1.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
163
164
165
166
167
168
169
170
























































































































171
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is descending indices.
#
# $Id: descidx1.test,v 1.1 2005/12/21 14:43:12 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# This procedure sets the value of the file-format in file 'test.db'
# to $newval. Also, the schema cookie is incremented.
................................................................................
} {4 5 6 7 nosort}
do_test descidx1-3.25 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
} {4 5 6 7 nosort}
do_test descidx1-3.26 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
} {7 6 5 4 nosort}

























































































































finish_test







|







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is descending indices.
#
# $Id: descidx1.test,v 1.2 2005/12/21 18:36:46 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# This procedure sets the value of the file-format in file 'test.db'
# to $newval. Also, the schema cookie is incremented.
................................................................................
} {4 5 6 7 nosort}
do_test descidx1-3.25 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
} {4 5 6 7 nosort}
do_test descidx1-3.26 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
} {7 6 5 4 nosort}

# Create a table with indices that are descending on some terms and
# ascending on others.
#
do_test descidx1-4.1 {
  execsql {
    CREATE TABLE t2(a INT, b TEXT, c BLOB, d REAL);
    CREATE INDEX i3 ON t2(a ASC, b DESC, c ASC);
    CREATE INDEX i4 ON t2(b DESC, a ASC, d DESC);
    INSERT INTO t2 VALUES(1,'one',x'31',1.0);
    INSERT INTO t2 VALUES(2,'two',x'3232',2.0);
    INSERT INTO t2 VALUES(3,'three',x'333333',3.0);
    INSERT INTO t2 VALUES(4,'four',x'34343434',4.0);
    INSERT INTO t2 VALUES(5,'five',x'3535353535',5.0);
    INSERT INTO t2 VALUES(6,'six',x'363636363636',6.0);
    INSERT INTO t2 VALUES(2,'two',x'323232',2.1);
    INSERT INTO t2 VALUES(2,'zwei',x'3232',2.2);
    INSERT INTO t2 VALUES(2,NULL,NULL,2.3);
    SELECT count(*) FROM t2;
  }
} {9}
do_test descidx1-4.2 {
  execsql {
    SELECT d FROM t2 ORDER BY a;
  }
} {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
do_test descidx1-4.3 {
  execsql {
    SELECT d FROM t2 WHERE a>=2;
  }
} {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
do_test descidx1-4.4 {
  execsql {
    SELECT d FROM t2 WHERE a>2;
  }
} {3.0 4.0 5.0 6.0}
do_test descidx1-4.5 {
  execsql {
    SELECT d FROM t2 WHERE a=2 AND b>'two';
  }
} {2.2}
do_test descidx1-4.6 {
  execsql {
    SELECT d FROM t2 WHERE a=2 AND b>='two';
  }
} {2.2 2.0 2.1}
do_test descidx1-4.7 {
  execsql {
    SELECT d FROM t2 WHERE a=2 AND b<'two';
  }
} {}
do_test descidx1-4.8 {
  execsql {
    SELECT d FROM t2 WHERE a=2 AND b<='two';
  }
} {2.0 2.1}

do_test descidx1-5.1 {
  execsql {
    CREATE TABLE t3(a,b,c,d);
    CREATE INDEX t3i1 ON t3(a DESC, b ASC, c DESC, d ASC);
    INSERT INTO t3 VALUES(0,0,0,0);
    INSERT INTO t3 VALUES(0,0,0,1);
    INSERT INTO t3 VALUES(0,0,1,0);
    INSERT INTO t3 VALUES(0,0,1,1);
    INSERT INTO t3 VALUES(0,1,0,0);
    INSERT INTO t3 VALUES(0,1,0,1);
    INSERT INTO t3 VALUES(0,1,1,0);
    INSERT INTO t3 VALUES(0,1,1,1);
    INSERT INTO t3 VALUES(1,0,0,0);
    INSERT INTO t3 VALUES(1,0,0,1);
    INSERT INTO t3 VALUES(1,0,1,0);
    INSERT INTO t3 VALUES(1,0,1,1);
    INSERT INTO t3 VALUES(1,1,0,0);
    INSERT INTO t3 VALUES(1,1,0,1);
    INSERT INTO t3 VALUES(1,1,1,0);
    INSERT INTO t3 VALUES(1,1,1,1);
    SELECT count(*) FROM t3;
  }
} {16}
do_test descidx1-5.2 {
  cksort {
    SELECT a||b||c||d FROM t3 ORDER BY a,b,c,d;
  }
} {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111 sort}
do_test descidx1-5.3 {
  cksort {
    SELECT a||b||c||d FROM t3 ORDER BY a DESC, b ASC, c DESC, d ASC;
  }
} {1010 1011 1000 1001 1110 1111 1100 1101 0010 0011 0000 0001 0110 0111 0100 0101 nosort}
do_test descidx1-5.4 {
  cksort {
    SELECT a||b||c||d FROM t3 ORDER BY a ASC, b DESC, c ASC, d DESC;
  }
} {0101 0100 0111 0110 0001 0000 0011 0010 1101 1100 1111 1110 1001 1000 1011 1010 nosort}
do_test descidx1-5.5 {
  cksort {
    SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b ASC, c DESC
  }
} {101 100 111 110 001 000 011 010 nosort}
do_test descidx1-5.6 {
  cksort {
    SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c ASC
  }
} {010 011 000 001 110 111 100 101 nosort}
do_test descidx1-5.7 {
  cksort {
    SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c DESC
  }
} {011 010 001 000 111 110 101 100 sort}
do_test descidx1-5.8 {
  cksort {
    SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b ASC, c ASC
  }
} {000 001 010 011 100 101 110 111 sort}
do_test descidx1-5.9 {
  cksort {
    SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b DESC, c ASC
  }
} {110 111 100 101 010 011 000 001 sort}

finish_test

Added test/descidx2.test.















































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
# 2005 December 21
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is descending indices.
#
# $Id: descidx2.test,v 1.1 2005/12/21 18:36:46 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# This procedure sets the value of the file-format in file 'test.db'
# to $newval. Also, the schema cookie is incremented.
# 
proc set_file_format {newval} {
  set bt [btree_open test.db 10 0]
  btree_begin_transaction $bt
  set meta [btree_get_meta $bt]
  lset meta 2 $newval                    ;# File format
  lset meta 1 [expr [lindex $meta 1]+1]  ;# Schema cookie
  eval "btree_update_meta $bt $meta"
  btree_commit $bt
  btree_close $bt
}

# This procedure returns the value of the file-format in file 'test.db'.
# 
proc get_file_format {{fname test.db}} {
  set bt [btree_open $fname 10 0]
  set meta [btree_get_meta $bt]
  btree_close $bt
  lindex $meta 2
}

# Verify that the file format jumps to (at least) 4 as soon as a
# descending index is created.
#
do_test descidx2-1.1 {
  execsql {
    CREATE TABLE t1(a,b);
    CREATE INDEX i1 ON t1(b ASC);
  }
  get_file_format
} {1}
do_test descidx2-1.2 {
  execsql {
    CREATE INDEX i2 ON t1(a DESC);
  }
  get_file_format
} {4}

# Before adding any information to the database, set the file format
# back to three.  Then close and reopen the database.  With the file
# format set to three, SQLite should ignore the DESC argument on the
# index.
#
do_test descidx2-2.0 {
  set_file_format 3
  db close
  sqlite3 db test.db
  get_file_format
} {3}

# Put some information in the table and verify that the DESC
# on the index is ignored.
#
do_test descidx2-2.1 {
  execsql {
    INSERT INTO t1 VALUES(1,1);
    INSERT INTO t1 VALUES(2,2);
    INSERT INTO t1 SELECT a+2, a+2 FROM t1;
    INSERT INTO t1 SELECT a+4, a+4 FROM t1;
    SELECT b FROM t1 WHERE a>3 AND a<7;
  }
} {4 5 6}
do_test descidx2-2.2 {
  execsql {
    SELECT a FROM t1 WHERE b>3 AND b<7;
  }
} {4 5 6}
do_test descidx2-2.3 {
  execsql {
    SELECT b FROM t1 WHERE a>=3 AND a<7;
  }
} {3 4 5 6}
do_test descidx2-2.4 {
  execsql {
    SELECT b FROM t1 WHERE a>3 AND a<=7;
  }
} {4 5 6 7}
do_test descidx2-2.5 {
  execsql {
    SELECT b FROM t1 WHERE a>=3 AND a<=7;
  }
} {3 4 5 6 7}
do_test descidx2-2.6 {
  execsql {
    SELECT a FROM t1 WHERE b>=3 AND b<=7;
  }
} {3 4 5 6 7}

# This procedure executes the SQL.  Then it checks to see if the OP_Sort
# opcode was executed.  If an OP_Sort did occur, then "sort" is appended
# to the result.  If no OP_Sort happened, then "nosort" is appended.
#
# This procedure is used to check to make sure sorting is or is not
# occurring as expected.
#
proc cksort {sql} {
  set ::sqlite_sort_count 0
  set data [execsql $sql]
  if {$::sqlite_sort_count} {set x sort} {set x nosort}
  lappend data $x
  return $data
}

# Test sorting using a descending index.
#
do_test descidx2-3.1 {
  cksort {SELECT a FROM t1 ORDER BY a}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.2 {
  cksort {SELECT a FROM t1 ORDER BY a ASC}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.3 {
  cksort {SELECT a FROM t1 ORDER BY a DESC}
} {8 7 6 5 4 3 2 1 nosort}
do_test descidx2-3.4 {
  cksort {SELECT b FROM t1 ORDER BY a}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.5 {
  cksort {SELECT b FROM t1 ORDER BY a ASC}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.6 {
  cksort {SELECT b FROM t1 ORDER BY a DESC}
} {8 7 6 5 4 3 2 1 nosort}
do_test descidx2-3.7 {
  cksort {SELECT a FROM t1 ORDER BY b}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.8 {
  cksort {SELECT a FROM t1 ORDER BY b ASC}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.9 {
  cksort {SELECT a FROM t1 ORDER BY b DESC}
} {8 7 6 5 4 3 2 1 nosort}
do_test descidx2-3.10 {
  cksort {SELECT b FROM t1 ORDER BY b}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.11 {
  cksort {SELECT b FROM t1 ORDER BY b ASC}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.12 {
  cksort {SELECT b FROM t1 ORDER BY b DESC}
} {8 7 6 5 4 3 2 1 nosort}

do_test descidx2-3.21 {
  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
} {4 5 6 7 nosort}
do_test descidx2-3.22 {
  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
} {4 5 6 7 nosort}
do_test descidx2-3.23 {
  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
} {7 6 5 4 nosort}
do_test descidx2-3.24 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
} {4 5 6 7 nosort}
do_test descidx2-3.25 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
} {4 5 6 7 nosort}
do_test descidx2-3.26 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
} {7 6 5 4 nosort}

finish_test