SQLite

/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** $Id: btree.c,v 1.75 2002/12/04 22:29:28 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.

**
** The result of comparing the key with the entry to which the
** cursor is left pointing is stored in pCur->iMatch.  The same
** value is also written to *pRes if pRes!=NULL.  The meaning of
** this value is as follows:
**
**     *pRes<0      The cursor is left pointing at an entry that
**                  is smaller than pKey or if the table is empty
**                  and the cursor is therefore left point to nothing.
**
**     *pRes==0     The cursor is left pointing at an entry that
**                  exactly matches pKey.
**
**     *pRes>0      The cursor is left pointing at an entry that
**                  is larger than pKey.
*/
int sqliteBtreeMoveto(BtCursor *pCur, const void *pKey, int nKey, int *pRes){
  int rc;
  if( pCur->pPage==0 ) return SQLITE_ABORT;
  pCur->eSkip = SKIP_NONE;
  rc = moveToRoot(pCur);
  if( rc ) return rc;
  for(;;){
    int lwr, upr;
    Pgno chldPg;
    MemPage *pPage = pCur->pPage;
    int c = -1;  /* pRes return if table is empty must be -1 */
    lwr = 0;
    upr = pPage->nCell-1;
    while( lwr<=upr ){
      pCur->idx = (lwr+upr)/2;
      rc = sqliteBtreeKeyCompare(pCur, pKey, nKey, 0, &c);
      if( rc ) return rc;
      if( c==0 ){

**
** 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.187 2002/12/04 22:29:29 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The makefile scans this source file and creates the following
** array of string constants which are the names of all VDBE opcodes.

    oc = pOp->opcode;
    if( oc==OP_MoveTo && res<0 ){
      sqliteBtreeNext(pC->pCursor, &res);
      pC->recnoIsValid = 0;
      if( res && pOp->p2>0 ){
        pc = pOp->p2 - 1;
      }
    }else if( oc==OP_MoveLt ){
      if( res>=0 ){
        sqliteBtreePrevious(pC->pCursor, &res);
        pC->recnoIsValid = 0;
      }else{
        /* res might be negative because the table is empty.  Check to
        ** see if this is the case.
        */
        int keysize;
        res = sqliteBtreeKeySize(pC->pCursor,&keysize)!=0 || keysize==0;
      }
      if( res && pOp->p2>0 ){
        pc = pOp->p2 - 1;
      }
    }
  }
  POPSTACK;
  break;

# 2001 September 15
#
# 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 file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.13 2002/12/04 22:29:29 drh Exp $

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

# Build some test data
#
do_test where-1.0 {

  }
} {11 12 13 nosort}
do_test where-7.14 {
  cksort {
    SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y;
  }
} {10 11 12 nosort}
do_test where-7.15 {
  cksort {
    SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y;
  }
} {nosort}
do_test where-7.16 {
  cksort {
    SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y;
  }
} {8 nosort}
do_test where-7.17 {
  cksort {
    SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y;
  }
} {nosort}
do_test where-7.18 {
  cksort {
    SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y;
  }
} {15 nosort}
do_test where-7.19 {
  cksort {
    SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC;
  }
} {nosort}
do_test where-7.20 {
  cksort {
    SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC;
  }
} {8 nosort}
do_test where-7.21 {
  cksort {
    SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC;
  }
} {nosort}
do_test where-7.22 {
  cksort {
    SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC;
  }
} {15 nosort}
do_test where-7.23 {
  cksort {
    SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y;
  }
} {nosort}
do_test where-7.24 {
  cksort {
    SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y;
  }
} {1 nosort}
do_test where-7.25 {
  cksort {
    SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y;
  }
} {nosort}
do_test where-7.26 {
  cksort {
    SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y;
  }
} {100 nosort}
do_test where-7.27 {
  cksort {
    SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC;
  }
} {nosort}
do_test where-7.28 {
  cksort {
    SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC;
  }
} {1 nosort}
do_test where-7.29 {
  cksort {
    SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC;
  }
} {nosort}
do_test where-7.30 {
  cksort {
    SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC;
  }
} {100 nosort}

do_test where-8.1 {
  execsql {
    CREATE TABLE t4 AS SELECT * FROM t1;
    CREATE INDEX i4xy ON t4(x,y);
  }
  cksort {
    SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
  }
} {30 29 28 nosort}
do_test where-8.2 {
  execsql {
    DELETE FROM t4;
  }
  cksort {
    SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
  }
} {nosort}


finish_test