/ Check-in [f8ff0212]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:f8ff0212129ce602a1e1e9ad702c47b9302f2f21
User & Date: danielk1977 2008-12-30 09:45:46
Context
2008-12-30
12:00
Add a couple of extra tests for the "WHERE ... OR" optimization. (CVS 6077) check-in: 35c87585 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: f8ff0212 user: danielk1977 tags: trunk
06:36
Fix a bug in README.tokenizers. Ticket #3559. (CVS 6075) check-in: b8898d13 user: danielk1977 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
....
3100
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110
3111
3112
3113


3114
3115
3116
3117
3118
3119
3120
** 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)
................................................................................
        }
      }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;







|







 







|






>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
3100
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110
3111
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
** 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)
................................................................................
        }
      }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.

5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
...
200
201
202
203
204
205
206





















207
208
#
#    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 {
................................................................................
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








|







 







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


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