SQLite

** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible 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.405 2009/06/10 19:33:29 drh Exp $
*/
#include "sqliteInt.h"

/*
** Trace output macros
*/
#if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)

  */
  if( (SQLITE_BIG_DBL/((double)2))<pIdxInfo->estimatedCost ){
    pCost->rCost = (SQLITE_BIG_DBL/((double)2));
  }else{
    pCost->rCost = pIdxInfo->estimatedCost;
  }
  pCost->plan.u.pVtabIdx = pIdxInfo;
  if( pIdxInfo->orderByConsumed ){
    pCost->plan.wsFlags |= WHERE_ORDERBY;
  }
  pCost->plan.nEq = 0;
  pIdxInfo->nOrderBy = nOrderBy;

  /* Try to find a more efficient access pattern by using multiple indexes
  ** to optimize an OR expression within the WHERE clause. 

        if( rev ){
          wsFlags |= WHERE_REVERSE;
        }
      }else{
        cost += cost*estLog(cost);
        WHERETRACE(("...... orderby increases cost to %.9g\n", cost));
      }
    }else if( wsFlags!=0 && (pParse->db->flags & SQLITE_ReverseOrder)!=0 ){
      /* For application testing, randomly reverse the output order for
      ** SELECT statements that omit the ORDER BY clause.  This will help
      ** to find cases where
      */
      wsFlags |= WHERE_REVERSE;
    }

# 2009 February 23
#
# 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 reverse_select_order pragma.
#
# $Id: whereA.test,v 1.3 2009/06/10 19:33:29 drh Exp $

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

do_test whereA-1.1 {
  db eval {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);

  db eval {
    CREATE TABLE t2(x);
    INSERT INTO t2 VALUES(1);
    INSERT INTO t2 VALUES(2);
    SELECT x FROM t2;
  }
} {2 1}
# Do an SQL statement.  Append the search count to the end of the result.
#
proc count sql {
  set ::sqlite_sort_count 0
  return [concat [execsql $sql] $::sqlite_sort_count]
}
do_test whereA-4.2 {   ;# Ticket #3904
  db eval {
    CREATE INDEX t2x ON t2(x);
  }
  count {
    SELECT x FROM t2;
  }
} {2 1 0}
do_test whereA-4.3 {
  count {
    SELECT x FROM t2 ORDER BY x;
  }
} {1 2 0}
do_test whereA-4.4 {
  count {
    SELECT x FROM t2 ORDER BY x DESC;
  }
} {2 1 0}
do_test whereA-4.5 {
  db eval {DROP INDEX t2x;}
  count {
    SELECT x FROM t2 ORDER BY x;
  }
} {1 2 1}
do_test whereA-4.6 {
  count {
    SELECT x FROM t2 ORDER BY x DESC;
  }
} {2 1 1}


finish_test