SQLite

Check-in [552bc9cb88]
Login

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

Overview
Comment:Fix a corner-case bug in table-valued functions. Update the generate_series() virtual table to increase the performance estimate penalty for being underspecified.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 552bc9cb88bbe54b4cf5fdf66d1217e7a2047110
User & Date: drh 2015-08-21 17:14:48.307
Context
2015-08-21
17:39
Reserve the SQLITE_IOERR_VNODE error code name. (check-in: 53b593fcae user: drh tags: trunk)
17:16
Merge in trunk fixes for table-valued functions. (check-in: 67375f32d9 user: drh tags: json)
17:14
Fix a corner-case bug in table-valued functions. Update the generate_series() virtual table to increase the performance estimate penalty for being underspecified. (check-in: 552bc9cb88 user: drh tags: trunk)
12:37
Fix typo in comment. No changes to code. (check-in: 7b8d17dd84 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/misc/series.c.
︙︙
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344





345
346
347
348
349

350
351
352
353
354
355
356

    pIdxInfo->aConstraintUsage[stopIdx].argvIndex = ++nArg;
    pIdxInfo->aConstraintUsage[stopIdx].omit = 1;
  }
  if( stepIdx>=0 ){
    pIdxInfo->aConstraintUsage[stepIdx].argvIndex = ++nArg;
    pIdxInfo->aConstraintUsage[stepIdx].omit = 1;
  }
  if( pIdxInfo->nOrderBy==1 ){
    if( pIdxInfo->aOrderBy[0].desc ) idxNum |= 8;
    pIdxInfo->orderByConsumed = 1;
  }
  if( (idxNum & 3)==3 ){
    /* Both start= and stop= boundaries are available.  This is the 
    ** the preferred case */
    pIdxInfo->estimatedCost = (double)1;





  }else{
    /* If either boundary is missing, we have to generate a huge span
    ** of numbers.  Make this case very expensive so that the query
    ** planner will work hard to avoid it. */
    pIdxInfo->estimatedCost = (double)2000000000;

  }
  pIdxInfo->idxNum = idxNum;
  return SQLITE_OK;
}

/*
** This following structure defines all the methods for the 








<
<
<
<




>
>
>
>
>




|
>








330
331
332
333
334
335
336




337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358

    pIdxInfo->aConstraintUsage[stopIdx].argvIndex = ++nArg;
    pIdxInfo->aConstraintUsage[stopIdx].omit = 1;
  }
  if( stepIdx>=0 ){
    pIdxInfo->aConstraintUsage[stepIdx].argvIndex = ++nArg;
    pIdxInfo->aConstraintUsage[stepIdx].omit = 1;
  }




  if( (idxNum & 3)==3 ){
    /* Both start= and stop= boundaries are available.  This is the 
    ** the preferred case */
    pIdxInfo->estimatedCost = (double)1;
    pIdxInfo->estimatedRows = 1000;
    if( pIdxInfo->nOrderBy==1 ){
      if( pIdxInfo->aOrderBy[0].desc ) idxNum |= 8;
      pIdxInfo->orderByConsumed = 1;
    }
  }else{
    /* If either boundary is missing, we have to generate a huge span
    ** of numbers.  Make this case very expensive so that the query
    ** planner will work hard to avoid it. */
    pIdxInfo->estimatedCost = (double)2147483647;
    pIdxInfo->estimatedRows = 2147483647;
  }
  pIdxInfo->idxNum = idxNum;
  return SQLITE_OK;
}

/*
** This following structure defines all the methods for the
︙︙
Changes to src/whereexpr.c.
︙︙
1277
1278
1279
1280
1281
1282
1283

1284
1285
1286
1287
1288

                      pTab->zName, j);
      return;
    }
    pColRef = sqlite3PExpr(pParse, TK_COLUMN, 0, 0, 0);
    if( pColRef==0 ) return;
    pColRef->iTable = pItem->iCursor;
    pColRef->iColumn = k++;

    pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef,
                         sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0), 0);
    whereClauseInsert(pWC, pTerm, TERM_DYNAMIC);
  }
}








>






1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289

                      pTab->zName, j);
      return;
    }
    pColRef = sqlite3PExpr(pParse, TK_COLUMN, 0, 0, 0);
    if( pColRef==0 ) return;
    pColRef->iTable = pItem->iCursor;
    pColRef->iColumn = k++;
    pColRef->pTab = pTab;
    pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef,
                         sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0), 0);
    whereClauseInsert(pWC, pTerm, TERM_DYNAMIC);
  }
}
Changes to test/tabfunc01.test.
︙︙
60
61
62
63
64
65
66




67
68
69

  INSERT INTO t1(x) VALUES(2),(3);
  SELECT *, '|' FROM t1, generate_series(1,x) ORDER BY 1, 2
} {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}

do_execsql_test tabfunc01-2.2 {
  SELECT * FROM generate_series() LIMIT 5;
} {0 1 2 3 4}






finish_test








>
>
>
>




60
61
62
63
64
65
66
67
68
69
70
71
72
73

  INSERT INTO t1(x) VALUES(2),(3);
  SELECT *, '|' FROM t1, generate_series(1,x) ORDER BY 1, 2
} {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}

do_execsql_test tabfunc01-2.2 {
  SELECT * FROM generate_series() LIMIT 5;
} {0 1 2 3 4}

do_execsql_test tabfunc01-3.1 {
  SELECT DISTINCT value FROM generate_series(1,x), t1 ORDER BY 1;
} {1 2 3}


finish_test