/ Check-in [d30f7b2d]
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:Make sure the estimated row count for ephemeral tables is initialized so that automatic indices can be used on those tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d30f7b2deffdba373a2e0988f433c631c790a7b5
User & Date: drh 2010-10-21 22:58:25
Context
2010-10-22
13:55
Prevent an assert from failing when opening a zero-length database file with an apparently hot journal with locking_mode=exclusive set. check-in: f000ac1e user: dan tags: trunk
2010-10-21
22:58
Make sure the estimated row count for ephemeral tables is initialized so that automatic indices can be used on those tables. check-in: d30f7b2d user: drh tags: trunk
15:12
Fix some segfaults that could occur in obscure circumstances where error messages contained characters that could be mistaken for printf format specifiers. check-in: f91471e7 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

1292
1293
1294
1295
1296
1297
1298

1299
1300
1301
1302
1303
1304
1305
....
3091
3092
3093
3094
3095
3096
3097

3098
3099
3100
3101
3102
3103
3104
    return 0;
  }
  /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside
  ** is disabled */
  assert( db->lookaside.bEnabled==0 );
  pTab->nRef = 1;
  pTab->zName = 0;

  selectColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol);
  selectAddColumnTypeAndCollation(pParse, pTab->nCol, pTab->aCol, pSelect);
  pTab->iPKey = -1;
  if( db->mallocFailed ){
    sqlite3DeleteTable(db, pTab);
    return 0;
  }
................................................................................
      pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
      if( pTab==0 ) return WRC_Abort;
      pTab->nRef = 1;
      pTab->zName = sqlite3MPrintf(db, "sqlite_subquery_%p_", (void*)pTab);
      while( pSel->pPrior ){ pSel = pSel->pPrior; }
      selectColumnsFromExprList(pParse, pSel->pEList, &pTab->nCol, &pTab->aCol);
      pTab->iPKey = -1;

      pTab->tabFlags |= TF_Ephemeral;
#endif
    }else{
      /* An ordinary table or view name in the FROM clause */
      assert( pFrom->pTab==0 );
      pFrom->pTab = pTab = 
        sqlite3LocateTable(pParse,0,pFrom->zName,pFrom->zDatabase);







>







 







>







1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
....
3092
3093
3094
3095
3096
3097
3098
3099
3100
3101
3102
3103
3104
3105
3106
    return 0;
  }
  /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside
  ** is disabled */
  assert( db->lookaside.bEnabled==0 );
  pTab->nRef = 1;
  pTab->zName = 0;
  pTab->nRowEst = 1000000;
  selectColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol);
  selectAddColumnTypeAndCollation(pParse, pTab->nCol, pTab->aCol, pSelect);
  pTab->iPKey = -1;
  if( db->mallocFailed ){
    sqlite3DeleteTable(db, pTab);
    return 0;
  }
................................................................................
      pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
      if( pTab==0 ) return WRC_Abort;
      pTab->nRef = 1;
      pTab->zName = sqlite3MPrintf(db, "sqlite_subquery_%p_", (void*)pTab);
      while( pSel->pPrior ){ pSel = pSel->pPrior; }
      selectColumnsFromExprList(pParse, pSel->pEList, &pTab->nCol, &pTab->aCol);
      pTab->iPKey = -1;
      pTab->nRowEst = 1000000;
      pTab->tabFlags |= TF_Ephemeral;
#endif
    }else{
      /* An ordinary table or view name in the FROM clause */
      assert( pFrom->pTab==0 );
      pFrom->pTab = pTab = 
        sqlite3LocateTable(pParse,0,pFrom->zName,pFrom->zDatabase);

Changes to test/autoindex1.test.

160
161
162
163
164
165
166
167
168







































































169
  db eval {
    EXPLAIN QUERY PLAN
    SELECT b FROM t501
     WHERE t501.a=123
       AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
  }
} {0 0 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}}
    








































































finish_test







|

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

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
  db eval {
    EXPLAIN QUERY PLAN
    SELECT b FROM t501
     WHERE t501.a=123
       AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
  }
} {0 0 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}}


# The following code checks a performance regression reported on the
# mailing list on 2010-10-19.  The problem is that the nRowEst field
# of ephermeral tables was not being initialized correctly and so no
# automatic index was being created for the emphemeral table when it was
# used as part of a join.
#
do_test autoindex1-600 {
  db eval {
    CREATE TABLE flock_owner(
      owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
      flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
      owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
      owner_change_date TEXT, last_changed TEXT NOT NULL,
      CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
    );
    CREATE TABLE sheep (
      Sheep_No char(7) NOT NULL,
      Date_of_Birth char(8),
      Sort_DoB text,
      Flock_Book_Vol char(2),
      Breeder_No char(6),
      Breeder_Person integer,
      Originating_Flock char(6),
      Registering_Flock char(6),
      Tag_Prefix char(9),
      Tag_No char(15),
      Sort_Tag_No integer,
      Breeders_Temp_Tag char(15),
      Sex char(1),
      Sheep_Name char(32),
      Sire_No char(7),
      Dam_No char(7),
      Register_Code char(1),
      Colour char(48),
      Colour_Code char(2),
      Pattern_Code char(8),
      Horns char(1),
      Litter_Size char(1),
      Coeff_of_Inbreeding real,
      Date_of_Registration text,
      Date_Last_Changed text,
      UNIQUE(Sheep_No));
    CREATE INDEX fo_flock_no_index  
                ON flock_owner (flock_no);
    CREATE INDEX fo_owner_change_date_index  
                ON flock_owner (owner_change_date);
    CREATE INDEX fo_owner_person_id_index  
                ON flock_owner (owner_person_id);
    CREATE INDEX sheep_org_flock_index  
             ON sheep (originating_flock);
    CREATE INDEX sheep_reg_flock_index  
             ON sheep (registering_flock);
    EXPLAIN QUERY PLAN
    SELECT x.sheep_no, x.registering_flock, x.date_of_registration
     FROM sheep x LEFT JOIN
         (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
         s.date_of_registration, prev.owner_change_date
         FROM sheep s JOIN flock_owner prev ON s.registering_flock =
     prev.flock_no
         AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
         WHERE NOT EXISTS
             (SELECT 'x' FROM flock_owner later
             WHERE prev.flock_no = later.flock_no
             AND later.owner_change_date > prev.owner_change_date
             AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
         ) y ON x.sheep_no = y.sheep_no
     WHERE y.sheep_no IS NULL
     ORDER BY x.registering_flock;
  }
} {0 0 {TABLE sheep AS s} 1 1 {TABLE flock_owner AS prev WITH INDEX sqlite_autoindex_flock_owner_1} 0 0 {TABLE flock_owner AS later WITH INDEX sqlite_autoindex_flock_owner_1} 0 0 {TABLE sheep AS x WITH INDEX sheep_reg_flock_index ORDER BY} 1 1 {TABLE  AS y WITH AUTOMATIC INDEX}}

finish_test