/ Check-in [78a391dc]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Do not let the reverse_unordered_selects pragma force the use of an index that would not otherwise be used. Ticket #3904. Also: remove an test which is always true. (CVS 6745)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 78a391dca05dbe3ad1d8124b80b31bc2ce75778f
User & Date: drh 2009-06-10 19:33:29
Context
2009-06-11
00:47
Changes to reenable codec operation and to handle memory allocation failures within a codec. (CVS 6746) check-in: 43a6ca98 user: drh tags: trunk
2009-06-10
19:33
Do not let the reverse_unordered_selects pragma force the use of an index that would not otherwise be used. Ticket #3904. Also: remove an test which is always true. (CVS 6745) check-in: 78a391dc user: drh tags: trunk
11:07
Remove a NEVER() that can sometimes occur on an OOM error. (CVS 6744) check-in: c27f23bb user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
....
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
** 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.404 2009/06/08 19:44:37 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 && 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( pParse->db->flags & SQLITE_ReverseOrder ){
      /* 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;
    }








|







 







|







 







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
....
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
** 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;
    }

Changes to test/whereA.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
82
83
84
85
86
87
88

89



90































91
#    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.2 2009/04/06 12:26:58 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}





































finish_test







|







 







>
|
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
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
#    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