SQLite

Check-in [f8ff021212]
Login

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

Overview
Comment:Fix a bug in where.c causing a malfunction when an INDEXED BY clause specified an unusable index on other than the leftmost table in the FROM clause. Ticket #3560. (CVS 6076)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f8ff0212129ce602a1e1e9ad702c47b9302f2f21
User & Date: danielk1977 2008-12-30 09:45:46.000
Context
2008-12-30
12:00
Add a couple of extra tests for the "WHERE ... OR" optimization. (CVS 6077) (check-in: 35c87585b8 user: danielk1977 tags: trunk)
09:45
Fix a bug in where.c causing a malfunction when an INDEXED BY clause specified an unusable index on other than the leftmost table in the FROM clause. Ticket #3560. (CVS 6076) (check-in: f8ff021212 user: danielk1977 tags: trunk)
06:36
Fix a bug in README.tokenizers. Ticket #3559. (CVS 6075) (check-in: b8898d132e user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** 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.350 2008/12/29 23:45:07 drh Exp $
*/
#include "sqliteInt.h"

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







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** 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.351 2008/12/30 09:45:46 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Trace output macros
*/
#if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
3100
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110
3111
3112
3113


3114
3115
3116
3117
3118
3119
3120
        }
      }else 
#endif
      {
        bestIndex(pParse, pWC, pTabItem, notReady,
                  (i==0 && ppOrderBy) ? *ppOrderBy : 0, &sCost);
      }
      if( sCost.rCost<bestPlan.rCost ){
        once = 1;
        bestPlan = sCost;
        bestJ = j;
      }
      if( doNotReorder ) break;
    }


    WHERETRACE(("*** Optimizer selects table %d for loop %d\n", bestJ,
           pLevel-pWInfo->a));
    if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){
      *ppOrderBy = 0;
    }
    andFlags &= bestPlan.plan.wsFlags;
    pLevel->plan = bestPlan.plan;







|






>
>







3100
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110
3111
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
        }
      }else 
#endif
      {
        bestIndex(pParse, pWC, pTabItem, notReady,
                  (i==0 && ppOrderBy) ? *ppOrderBy : 0, &sCost);
      }
      if( once==0 || sCost.rCost<bestPlan.rCost ){
        once = 1;
        bestPlan = sCost;
        bestJ = j;
      }
      if( doNotReorder ) break;
    }
    assert( once );
    assert( notReady & getMask(pMaskSet, pTabList->a[bestJ].iCursor) );
    WHERETRACE(("*** Optimizer selects table %d for loop %d\n", bestJ,
           pLevel-pWInfo->a));
    if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){
      *ppOrderBy = 0;
    }
    andFlags &= bestPlan.plan.wsFlags;
    pLevel->plan = bestPlan.plan;
Changes to test/indexedby.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 2008 October 4
#
# 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: indexedby.test,v 1.3 2008/10/06 16:18:40 danielk1977 Exp $

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

# Create a schema with some indexes.
#
do_test indexedby-1.1 {











|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 2008 October 4
#
# 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: indexedby.test,v 1.4 2008/12/30 09:45:46 danielk1977 Exp $

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

# Create a schema with some indexes.
#
do_test indexedby-1.1 {
200
201
202
203
204
205
206





















207
208
do_test indexedby-8.5 {
  EQP { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10}
} {0 0 {TABLE t1 WITH INDEX i2}}
do_test indexedby-8.6 {
  catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
} {1 {cannot use index: i2}}






















finish_test








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


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
do_test indexedby-8.5 {
  EQP { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10}
} {0 0 {TABLE t1 WITH INDEX i2}}
do_test indexedby-8.6 {
  catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
} {1 {cannot use index: i2}}

# Test that bug #3560 is fixed.
#
do_test indexedby-9.1 {
  execsql {
    CREATE TABLE maintable( id integer);
    CREATE TABLE joinme(id_int integer, id_text text);
    CREATE INDEX joinme_id_text_idx on joinme(id_text);
    CREATE INDEX joinme_id_int_idx on joinme(id_int);
  }
} {}
do_test indexedby-9.2 {
  catchsql {
    select * from maintable as m inner join
    joinme as j indexed by joinme_id_text_idx
    on ( m.id  = j.id_int)
  }
} {1 {cannot use index: joinme_id_text_idx}}
do_test indexedby-9.3 {
  catchsql { select * from maintable, joinme INDEXED by joinme_id_text_idx }
} {1 {cannot use index: joinme_id_text_idx}}

finish_test