/ Changes On Branch autoindex-improvements
Login

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

Changes In Branch autoindex-improvements Excluding Merge-Ins

This is equivalent to a diff from fc918f7d33 to 4ece839d44

2014-06-18
15:11
Prevent an automatic index from taking the place of a declared index. (check-in: 0a52bddd9d user: drh tags: trunk)
2014-06-17
20:16
Prevent an automatic index from taking the place of a declared index. (Closed-Leaf check-in: 4ece839d44 user: drh tags: autoindex-improvements)
15:53
Improvements to query planning, especially in regards to estimating the cost and benefit of automatic indexes. (check-in: 1272fb8991 user: drh tags: trunk)
13:23
Add the autoindex2.test testing module. (check-in: ffe3fea47b user: drh tags: autoindex-improvements)
02:46
Do not attempt to create an automatic index on a constant constraint, as doing so is pointless. (check-in: d6883e960f user: drh tags: autoindex-improvements)
2014-06-16
22:45
Fix CSV import issue, reported via the mailing list, in the shell when the file to be imported ends with an empty line. (check-in: fc918f7d33 user: mistachkin tags: trunk)
21:30
In the "wheretrace" logic (disabled for normal builds) expand the print width of the flags field to be wide enough for all 17 bits. (check-in: 3181d2069a user: drh tags: trunk)

Changes to ext/rtree/rtree6.test.

88
89
90
91
92
93
94
95






96
97
98
99
100
101
102
do_eqp_test rtree6.2.3 {
  SELECT * FROM t1,t2 WHERE k=ii
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:} 
  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
}

do_eqp_test rtree6.2.4 {






  SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1} 
  0 1 1 {SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)}
}

do_eqp_test rtree6.2.5 {







|
>
>
>
>
>
>







88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
do_eqp_test rtree6.2.3 {
  SELECT * FROM t1,t2 WHERE k=ii
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:} 
  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
}

do_eqp_test rtree6.2.4.1 {
  SELECT * FROM t1,t2 WHERE v=+ii and x1<10 and x2>10
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1} 
  0 1 1 {SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)}
}
do_eqp_test rtree6.2.4.2 {
  SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1} 
  0 1 1 {SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)}
}

do_eqp_test rtree6.2.5 {

Changes to src/where.c.

3918
3919
3920
3921
3922
3923
3924











3925
3926
3927
3928
3929
3930
3931
    assert( p->rSetup==0 || pTemplate->rSetup==0 
                 || p->rSetup==pTemplate->rSetup );

    /* whereLoopAddBtree() always generates and inserts the automatic index
    ** case first.  Hence compatible candidate WhereLoops never have a larger
    ** rSetup. Call this SETUP-INVARIANT */
    assert( p->rSetup>=pTemplate->rSetup );












    /* If existing WhereLoop p is better than pTemplate, pTemplate can be
    ** discarded.  WhereLoop p is better if:
    **   (1)  p has no more dependencies than pTemplate, and
    **   (2)  p has an equal or lower cost than pTemplate
    */
    if( (p->prereq & pTemplate->prereq)==p->prereq    /* (1)  */







>
>
>
>
>
>
>
>
>
>
>







3918
3919
3920
3921
3922
3923
3924
3925
3926
3927
3928
3929
3930
3931
3932
3933
3934
3935
3936
3937
3938
3939
3940
3941
3942
    assert( p->rSetup==0 || pTemplate->rSetup==0 
                 || p->rSetup==pTemplate->rSetup );

    /* whereLoopAddBtree() always generates and inserts the automatic index
    ** case first.  Hence compatible candidate WhereLoops never have a larger
    ** rSetup. Call this SETUP-INVARIANT */
    assert( p->rSetup>=pTemplate->rSetup );

    /* Any loop using an appliation-defined index (or PRIMARY KEY or
    ** UNIQUE constraint) with one or more == constraints is better
    ** than an automatic index. */
    if( (p->wsFlags & WHERE_AUTO_INDEX)!=0
     && (pTemplate->wsFlags & WHERE_INDEXED)!=0
     && (pTemplate->wsFlags & WHERE_COLUMN_EQ)!=0
     && (p->prereq & pTemplate->prereq)==pTemplate->prereq
    ){
      break;
    }

    /* If existing WhereLoop p is better than pTemplate, pTemplate can be
    ** discarded.  WhereLoop p is better if:
    **   (1)  p has no more dependencies than pTemplate, and
    **   (2)  p has an equal or lower cost than pTemplate
    */
    if( (p->prereq & pTemplate->prereq)==p->prereq    /* (1)  */
4043
4044
4045
4046
4047
4048
4049
4050
4051
4052
4053
4054
4055
4056
4057
    /* We will be overwriting WhereLoop p[].  But before we do, first
    ** go through the rest of the list and delete any other entries besides
    ** p[] that are also supplated by pTemplate */
    WhereLoop **ppTail = &p->pNextLoop;
    WhereLoop *pToDel;
    while( *ppTail ){
      ppTail = whereLoopFindLesser(ppTail, pTemplate);
      if( NEVER(ppTail==0) ) break;
      pToDel = *ppTail;
      if( pToDel==0 ) break;
      *ppTail = pToDel->pNextLoop;
#if WHERETRACE_ENABLED /* 0x8 */
      if( sqlite3WhereTrace & 0x8 ){
        sqlite3DebugPrintf("ins-del: ");
        whereLoopPrint(pToDel, pBuilder->pWC);







|







4054
4055
4056
4057
4058
4059
4060
4061
4062
4063
4064
4065
4066
4067
4068
    /* We will be overwriting WhereLoop p[].  But before we do, first
    ** go through the rest of the list and delete any other entries besides
    ** p[] that are also supplated by pTemplate */
    WhereLoop **ppTail = &p->pNextLoop;
    WhereLoop *pToDel;
    while( *ppTail ){
      ppTail = whereLoopFindLesser(ppTail, pTemplate);
      if( ppTail==0 ) break;
      pToDel = *ppTail;
      if( pToDel==0 ) break;
      *ppTail = pToDel->pNextLoop;
#if WHERETRACE_ENABLED /* 0x8 */
      if( sqlite3WhereTrace & 0x8 ){
        sqlite3DebugPrintf("ins-del: ");
        whereLoopPrint(pToDel, pBuilder->pWC);
5274
5275
5276
5277
5278
5279
5280
5281
5282
5283
5284
5285
5286
5287
5288
  int iLoop;                /* Loop counter over the terms of the join */
  int ii, jj;               /* Loop counters */
  int mxI = 0;              /* Index of next entry to replace */
  int nOrderBy;             /* Number of ORDER BY clause terms */
  LogEst rCost;             /* Cost of a path */
  LogEst nOut;              /* Number of outputs */
  LogEst mxCost = 0;        /* Maximum cost of a set of paths */
  LogEst mxOut = 0;         /* Maximum nOut value on the set of paths */
  int nTo, nFrom;           /* Number of valid entries in aTo[] and aFrom[] */
  WherePath *aFrom;         /* All nFrom paths at the previous level */
  WherePath *aTo;           /* The nTo best paths at the current level */
  WherePath *pFrom;         /* An element of aFrom[] that we are working on */
  WherePath *pTo;           /* An element of aTo[] that we are working on */
  WhereLoop *pWLoop;        /* One of the WhereLoop objects */
  WhereLoop **pX;           /* Used to divy up the pSpace memory */







<







5285
5286
5287
5288
5289
5290
5291

5292
5293
5294
5295
5296
5297
5298
  int iLoop;                /* Loop counter over the terms of the join */
  int ii, jj;               /* Loop counters */
  int mxI = 0;              /* Index of next entry to replace */
  int nOrderBy;             /* Number of ORDER BY clause terms */
  LogEst rCost;             /* Cost of a path */
  LogEst nOut;              /* Number of outputs */
  LogEst mxCost = 0;        /* Maximum cost of a set of paths */

  int nTo, nFrom;           /* Number of valid entries in aTo[] and aFrom[] */
  WherePath *aFrom;         /* All nFrom paths at the previous level */
  WherePath *aTo;           /* The nTo best paths at the current level */
  WherePath *pFrom;         /* An element of aFrom[] that we are working on */
  WherePath *pTo;           /* An element of aTo[] that we are working on */
  WhereLoop *pWLoop;        /* One of the WhereLoop objects */
  WhereLoop **pX;           /* Used to divy up the pSpace memory */
5384
5385
5386
5387
5388
5389
5390
5391
5392
5393
5394
5395
5396
5397
5398
5399
        }else{
          revMask = pFrom->revLoop;
        }
        /* Check to see if pWLoop should be added to the mxChoice best so far */
        for(jj=0, pTo=aTo; jj<nTo; jj++, pTo++){
          if( pTo->maskLoop==maskNew
           && ((pTo->isOrdered^isOrdered)&80)==0
           && ((pTo->rCost<=rCost && pTo->nRow<=nOut) ||
                (pTo->rCost>=rCost && pTo->nRow>=nOut))
          ){
            testcase( jj==nTo-1 );
            break;
          }
        }
        if( jj>=nTo ){
          if( nTo>=mxChoice && rCost>=mxCost ){







<
<







5394
5395
5396
5397
5398
5399
5400


5401
5402
5403
5404
5405
5406
5407
        }else{
          revMask = pFrom->revLoop;
        }
        /* Check to see if pWLoop should be added to the mxChoice best so far */
        for(jj=0, pTo=aTo; jj<nTo; jj++, pTo++){
          if( pTo->maskLoop==maskNew
           && ((pTo->isOrdered^isOrdered)&80)==0


          ){
            testcase( jj==nTo-1 );
            break;
          }
        }
        if( jj>=nTo ){
          if( nTo>=mxChoice && rCost>=mxCost ){
5419
5420
5421
5422
5423
5424
5425
5426
5427
5428
5429
5430
5431
5432
5433
          if( sqlite3WhereTrace&0x4 ){
            sqlite3DebugPrintf("New    %s cost=%-3d,%3d order=%c\n",
                wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
                isOrdered>=0 ? isOrdered+'0' : '?');
          }
#endif
        }else{
          if( pTo->rCost<=rCost && pTo->nRow<=nOut ){
#ifdef WHERETRACE_ENABLED /* 0x4 */
            if( sqlite3WhereTrace&0x4 ){
              sqlite3DebugPrintf(
                  "Skip   %s cost=%-3d,%3d order=%c",
                  wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
                  isOrdered>=0 ? isOrdered+'0' : '?');
              sqlite3DebugPrintf("   vs %s cost=%-3d,%d order=%c\n",







|







5427
5428
5429
5430
5431
5432
5433
5434
5435
5436
5437
5438
5439
5440
5441
          if( sqlite3WhereTrace&0x4 ){
            sqlite3DebugPrintf("New    %s cost=%-3d,%3d order=%c\n",
                wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
                isOrdered>=0 ? isOrdered+'0' : '?');
          }
#endif
        }else{
          if( pTo->rCost<=rCost ){
#ifdef WHERETRACE_ENABLED /* 0x4 */
            if( sqlite3WhereTrace&0x4 ){
              sqlite3DebugPrintf(
                  "Skip   %s cost=%-3d,%3d order=%c",
                  wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
                  isOrdered>=0 ? isOrdered+'0' : '?');
              sqlite3DebugPrintf("   vs %s cost=%-3d,%d order=%c\n",
5459
5460
5461
5462
5463
5464
5465
5466
5467
5468
5469
5470
5471
5472
5473
5474
5475
5476
5477
        pTo->rCost = rCost;
        pTo->isOrdered = isOrdered;
        memcpy(pTo->aLoop, pFrom->aLoop, sizeof(WhereLoop*)*iLoop);
        pTo->aLoop[iLoop] = pWLoop;
        if( nTo>=mxChoice ){
          mxI = 0;
          mxCost = aTo[0].rCost;
          mxOut = aTo[0].nRow;
          for(jj=1, pTo=&aTo[1]; jj<mxChoice; jj++, pTo++){
            if( pTo->rCost>mxCost || (pTo->rCost==mxCost && pTo->nRow>mxOut) ){
              mxCost = pTo->rCost;
              mxOut = pTo->nRow;
              mxI = jj;
            }
          }
        }
      }
    }








<

|

<







5467
5468
5469
5470
5471
5472
5473

5474
5475
5476

5477
5478
5479
5480
5481
5482
5483
        pTo->rCost = rCost;
        pTo->isOrdered = isOrdered;
        memcpy(pTo->aLoop, pFrom->aLoop, sizeof(WhereLoop*)*iLoop);
        pTo->aLoop[iLoop] = pWLoop;
        if( nTo>=mxChoice ){
          mxI = 0;
          mxCost = aTo[0].rCost;

          for(jj=1, pTo=&aTo[1]; jj<mxChoice; jj++, pTo++){
            if( pTo->rCost>mxCost ){
              mxCost = pTo->rCost;

              mxI = jj;
            }
          }
        }
      }
    }

Added test/autoindex2.test.































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
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
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
# 2014-06-17
#
# 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 script is testing automatic index creation logic.
#
# This file contains a single real-world test case that was giving
# suboptimal performance because of over-use of automatic indexes.
#

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


do_execsql_test autoindex2-100 {
  CREATE TABLE t1(
    t1_id largeint,
    did char(9),
    ptime largeint,
    exbyte char(4),
    pe_id int,
    field_id int,
    mass float,
    param10 float,
    param11 float,
    exmass float,
    deviation float,
    trange float,
    vstatus int,
    commit_status int,
    formula char(329),
    tier int DEFAULT 2,
    ssid int DEFAULT 0,
    last_operation largeint DEFAULT 0,
    admin_uuid int DEFAULT 0,
    previous_value float,
    job_id largeint,
    last_t1 largeint DEFAULT 0,
    data_t1 int,
    previous_date largeint DEFAULT 0,
    flg8 int DEFAULT 1,
    failed_fields char(100)
  );
  CREATE INDEX t1x0 on t1 (t1_id);
  CREATE INDEX t1x1 on t1 (ptime, vstatus);
  CREATE INDEX t1x2 on t1 (did, ssid, ptime, vstatus, exbyte, t1_id);
  CREATE INDEX t1x3 on t1 (job_id);
  
  CREATE TABLE t2(
    did char(9),
    client_did char(30),
    description char(49),
    uid int,
    tzid int,
    privilege int,
    param2 int,
    type char(30),
    subtype char(32),
    dparam1 char(7) DEFAULT '',
    param5 char(3) DEFAULT '',
    notional float DEFAULT 0.000000,
    create_time largeint,
    sample_time largeint DEFAULT 0,
    param6 largeint,
    frequency int,
    expiration largeint,
    uw_status int,
    next_sample largeint,
    last_sample largeint,
    reserve1 char(29) DEFAULT '',
    reserve2 char(29) DEFAULT '',
    reserve3 char(29) DEFAULT '',
    bxcdr char(19) DEFAULT 'XY',
    ssid int DEFAULT 1,
    last_t1_id largeint,
    reserve4 char(29) DEFAULT '',
    reserve5 char(29) DEFAULT '',
    param12 int DEFAULT 0,
    long_did char(100) DEFAULT '',
    gr_code int DEFAULT 0,
    drx char(100) DEFAULT '',
    parent_id char(9) DEFAULT '',
    param13 int DEFAULT 0,
    position float DEFAULT 1.000000,
    client_did3 char(100) DEFAULT '',
    client_did4 char(100) DEFAULT '',
    dlib_id char(9) DEFAULT ''
  );
  CREATE INDEX t2x0 on t2 (did);
  CREATE INDEX t2x1 on t2 (client_did);
  CREATE INDEX t2x2 on t2 (long_did);
  CREATE INDEX t2x3 on t2 (uid);
  CREATE INDEX t2x4 on t2 (param2);
  CREATE INDEX t2x5 on t2 (type);
  CREATE INDEX t2x6 on t2 (subtype);
  CREATE INDEX t2x7 on t2 (last_sample);
  CREATE INDEX t2x8 on t2 (param6);
  CREATE INDEX t2x9 on t2 (frequency);
  CREATE INDEX t2x10 on t2 (privilege);
  CREATE INDEX t2x11 on t2 (sample_time);
  CREATE INDEX t2x12 on t2 (notional);
  CREATE INDEX t2x13 on t2 (tzid);
  CREATE INDEX t2x14 on t2 (gr_code);
  CREATE INDEX t2x15 on t2 (parent_id);
  
  CREATE TABLE t3(
    uid int,
    param3 int,
    uuid int,
    acc_id int,
    cust_num int,
    numerix_id int,
    pfy char(29),
    param4 char(29),
    param15 int DEFAULT 0,
    flg7 int DEFAULT 0,
    param21 int DEFAULT 0,
    bxcdr char(2) DEFAULT 'PC',
    c31 int DEFAULT 0,
    c33 int DEFAULT 0,
    c35 int DEFAULT 0,
    c37 int,
    mgr_uuid int,
    back_up_uuid int,
    priv_mars int DEFAULT 0,
    is_qc int DEFAULT 0,
    c41 int DEFAULT 0,
    deleted int DEFAULT 0,
    c47 int DEFAULT 1
  );
  CREATE INDEX t3x0 on t3 (uid);
  CREATE INDEX t3x1 on t3 (param3);
  CREATE INDEX t3x2 on t3 (uuid);
  CREATE INDEX t3x3 on t3 (acc_id);
  CREATE INDEX t3x4 on t3 (param4);
  CREATE INDEX t3x5 on t3 (pfy);
  CREATE INDEX t3x6 on t3 (is_qc);
  SELECT count(*) FROM sqlite_master;
} {30}
do_execsql_test autoindex2-110 {
  ANALYZE sqlite_master;
  INSERT INTO sqlite_stat1 VALUES('t1','t1x3','10747267 260');
  INSERT INTO sqlite_stat1 VALUES('t1','t1x2','10747267 121 113 2 2 2 1');
  INSERT INTO sqlite_stat1 VALUES('t1','t1x1','10747267 50 40');
  INSERT INTO sqlite_stat1 VALUES('t1','t1x0','10747267 1');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x15','39667 253');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x14','39667 19834');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x13','39667 13223');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x12','39667 7');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x11','39667 17');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x10','39667 19834');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x9','39667 7934');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x8','39667 11');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x7','39667 5');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x6','39667 242');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x5','39667 1984');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x4','39667 4408');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x3','39667 81');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x2','39667 551');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x1','39667 2');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x0','39667 1');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x6','569 285');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x5','569 2');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x4','569 2');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x3','569 5');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x2','569 3');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x1','569 6');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x0','569 1');
  ANALYZE sqlite_master;
} {}
do_execsql_test autoindex2-120 {
  EXPLAIN QUERY PLAN
  SELECT
     t1_id,
     t1.did,
     param2,
     param3,
     t1.ptime,
     t1.trange,
     t1.exmass,
     t1.mass,
     t1.vstatus,
     type,
     subtype,
     t1.deviation,
     t1.formula,
     dparam1,
     reserve1,
     reserve2,
     param4,
     t1.last_operation,
     t1.admin_uuid,
     t1.previous_value,
     t1.job_id,
     client_did, 
     t1.last_t1,
     t1.data_t1,
     t1.previous_date,
     param5,
     param6,
     mgr_uuid
  FROM
     t1,
     t2,
     t3
  WHERE
     t1.ptime > 1393520400
     AND param3<>9001
     AND t3.flg7 = 1
     AND t1.did = t2.did
     AND t2.uid = t3.uid
  ORDER BY t1.ptime desc LIMIT 500;
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1x1 (ptime>?)} 0 1 1 {SEARCH TABLE t2 USING INDEX t2x0 (did=?)} 0 2 2 {SEARCH TABLE t3 USING INDEX t3x0 (uid=?)}}
#
# ^^^--- Before being fixed, the above was using an automatic covering
# on t3 and reordering the tables so that t3 was in the outer loop and
# implementing the ORDER BY clause using a B-Tree.

do_execsql_test autoindex2-120 {
  EXPLAIN QUERY PLAN
  SELECT
     t1_id,
     t1.did,
     param2,
     param3,
     t1.ptime,
     t1.trange,
     t1.exmass,
     t1.mass,
     t1.vstatus,
     type,
     subtype,
     t1.deviation,
     t1.formula,
     dparam1,
     reserve1,
     reserve2,
     param4,
     t1.last_operation,
     t1.admin_uuid,
     t1.previous_value,
     t1.job_id,
     client_did, 
     t1.last_t1,
     t1.data_t1,
     t1.previous_date,
     param5,
     param6,
     mgr_uuid
  FROM
     t3,
     t2,
     t1
  WHERE
     t1.ptime > 1393520400
     AND param3<>9001
     AND t3.flg7 = 1
     AND t1.did = t2.did
     AND t2.uid = t3.uid
  ORDER BY t1.ptime desc LIMIT 500;
} {0 0 2 {SEARCH TABLE t1 USING INDEX t1x1 (ptime>?)} 0 1 1 {SEARCH TABLE t2 USING INDEX t2x0 (did=?)} 0 2 0 {SEARCH TABLE t3 USING INDEX t3x0 (uid=?)}}

finish_test

Added test/autoindex3.test.





















































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
# 2014-06-17
#
# 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 script is testing automatic index creation logic,
# and specifically that an automatic index will not be created that
# shadows a declared index.
#

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

# The t1b and t2d indexes are not very selective.  It used to be that
# the autoindex mechanism would create automatic indexes on t1(b) or
# t2(d), make assumptions that they were reasonably selective, and use
# them instead of t1b or t2d.  But that would be cheating, because the
# automatic index cannot be any more selective than the real index.
#
# This test verifies that the cheat is no longer allowed.
#
do_execsql_test autoindex3-100 {
  CREATE TABLE t1(a,b,x);
  CREATE TABLE t2(c,d,y);
  CREATE INDEX t1b ON t1(b);
  CREATE INDEX t2d ON t2(d);
  ANALYZE sqlite_master;
  INSERT INTO sqlite_stat1 VALUES('t1','t1b','10000 500');
  INSERT INTO sqlite_stat1 VALUES('t2','t2d','10000 500');
  ANALYZE sqlite_master;
  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d=b;
} {~/AUTO/}

# Automatic indexes can still be used if existing indexes do not
# participate in == constraints.
#
do_execsql_test autoindex3-110 {
  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d>b AND x=y;
} {/AUTO/}
do_execsql_test autoindex3-120 {
  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d<b AND x=y;
} {/AUTO/}
do_execsql_test autoindex3-130 {
  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IS NULL AND x=y;
} {/AUTO/}
do_execsql_test autoindex3-140 {
  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IN (5,b) AND x=y;
} {/AUTO/}


finish_test

Changes to test/tpch01.test.

164
165
166
167
168
169
170
171



172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187


               o_year
       order by
               o_year;}]
  set ::eqpres
} {/0 0 0 {SEARCH TABLE part USING INDEX bootleg_pti .P_TYPE=..} 0 1 2 {SEARCH TABLE lineitem USING INDEX lpki2 .L_PARTKEY=..}.*/}
do_test tpch01-1.1b {
  set ::eqpres
} {/.* customer .* nation AS n1 .* nation AS n2 .*/}




do_eqp_test tpch01-1.2 {
select
    c_custkey,    c_name,    sum(l_extendedprice * (1 - l_discount)) as revenue,
    c_acctbal,    n_name,    c_address,    c_phone,    c_comment
from
    customer,    orders,    lineitem,    nation
where
    c_custkey = o_custkey    and l_orderkey = o_orderkey
    and o_orderdate >=  '1994-08-01'    and o_orderdate < date('1994-08-01', '+3 month')
    and l_returnflag = 'R'    and c_nationkey = n_nationkey
group by
    c_custkey,    c_name,    c_acctbal,    c_phone,    n_name, c_address,    c_comment
order by
    revenue desc;
} {0 0 1 {SEARCH TABLE orders USING INDEX odi (O_ORDERDATE>? AND O_ORDERDATE<?)} 0 1 0 {SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?)} 0 2 3 {SEARCH TABLE nation USING INDEX npki (N_NATIONKEY=?)} 0 3 2 {SEARCH TABLE lineitem USING INDEX lpki (L_ORDERKEY=?)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}}









|
>
>
>
















>
>
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
               o_year
       order by
               o_year;}]
  set ::eqpres
} {/0 0 0 {SEARCH TABLE part USING INDEX bootleg_pti .P_TYPE=..} 0 1 2 {SEARCH TABLE lineitem USING INDEX lpki2 .L_PARTKEY=..}.*/}
do_test tpch01-1.1b {
  set ::eqpres
} {/.* customer .* nation AS n1 .*/}
do_test tpch01-1.1c {
  set ::eqpres
} {/.* supplier .* nation AS n2 .*/}

do_eqp_test tpch01-1.2 {
select
    c_custkey,    c_name,    sum(l_extendedprice * (1 - l_discount)) as revenue,
    c_acctbal,    n_name,    c_address,    c_phone,    c_comment
from
    customer,    orders,    lineitem,    nation
where
    c_custkey = o_custkey    and l_orderkey = o_orderkey
    and o_orderdate >=  '1994-08-01'    and o_orderdate < date('1994-08-01', '+3 month')
    and l_returnflag = 'R'    and c_nationkey = n_nationkey
group by
    c_custkey,    c_name,    c_acctbal,    c_phone,    n_name, c_address,    c_comment
order by
    revenue desc;
} {0 0 1 {SEARCH TABLE orders USING INDEX odi (O_ORDERDATE>? AND O_ORDERDATE<?)} 0 1 0 {SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?)} 0 2 3 {SEARCH TABLE nation USING INDEX npki (N_NATIONKEY=?)} 0 3 2 {SEARCH TABLE lineitem USING INDEX lpki (L_ORDERKEY=?)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}}

finish_test