SQLite

Changes On Branch query-planner-debug
Login

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

Changes In Branch query-planner-debug Excluding Merge-Ins

This is equivalent to a diff from ebcd4523 to 857a1b01

2018-10-05
15:10
Changes to geopoly to silience false-positive warnings coming out of clang. (check-in: 11d9015f user: drh tags: trunk)
2018-10-04
18:17
The 0x8000 optimization flag associated with SQLITE_TESTCTRL_OPTIMIZATIONS causes a large penalty (200) to be added to all sorting costs, which encourages the query planner avoid using the sorter. This flag can be used in experiments to help come up with a more accurate estimate of the true cost of sorting. (Leaf check-in: 857a1b01 user: drh tags: query-planner-debug)
2018-10-03
18:05
Add an ALWAYS on an unreachable branch in the ALTER TABLE logic. (check-in: ebcd4523 user: drh tags: trunk)
11:13
Change a type in shell.c.in from "int" to "sqlite3_int64" in order to avoid a compiler warning and possible integer overflow. (check-in: bf0a6634 user: dan tags: trunk)

Changes to src/sqliteInt.h.

1389
1390
1391
1392
1393
1394
1395

1396
1397
1398
1399
1400
1401
1402
  signed char nextAutovac;      /* Autovac setting after VACUUM if >=0 */
  u8 suppressErr;               /* Do not issue error messages if true */
  u8 vtabOnConflict;            /* Value to return for s3_vtab_on_conflict() */
  u8 isTransactionSavepoint;    /* True if the outermost savepoint is a TS */
  u8 mTrace;                    /* zero or more SQLITE_TRACE flags */
  u8 noSharedCache;             /* True if no shared-cache backends */
  u8 nSqlExec;                  /* Number of pending OP_SqlExec opcodes */

  int nextPagesize;             /* Pagesize after VACUUM if >0 */
  u32 magic;                    /* Magic number for detect library misuse */
  int nChange;                  /* Value returned by sqlite3_changes() */
  int nTotalChange;             /* Value returned by sqlite3_total_changes() */
  int aLimit[SQLITE_N_LIMIT];   /* Limits */
  int nMaxSorterMmap;           /* Maximum size of regions mapped by sorter */
  struct sqlite3InitInfo {      /* Information used during initialization */







>







1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
  signed char nextAutovac;      /* Autovac setting after VACUUM if >=0 */
  u8 suppressErr;               /* Do not issue error messages if true */
  u8 vtabOnConflict;            /* Value to return for s3_vtab_on_conflict() */
  u8 isTransactionSavepoint;    /* True if the outermost savepoint is a TS */
  u8 mTrace;                    /* zero or more SQLITE_TRACE flags */
  u8 noSharedCache;             /* True if no shared-cache backends */
  u8 nSqlExec;                  /* Number of pending OP_SqlExec opcodes */
  LogEst iSortCost;             /* Extra cost applied to sorting */
  int nextPagesize;             /* Pagesize after VACUUM if >0 */
  u32 magic;                    /* Magic number for detect library misuse */
  int nChange;                  /* Value returned by sqlite3_changes() */
  int nTotalChange;             /* Value returned by sqlite3_total_changes() */
  int aLimit[SQLITE_N_LIMIT];   /* Limits */
  int nMaxSorterMmap;           /* Maximum size of regions mapped by sorter */
  struct sqlite3InitInfo {      /* Information used during initialization */
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586

/*
** Bits of the sqlite3.dbOptFlags field that are used by the
** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
** selectively disable various optimizations.
*/
#define SQLITE_QueryFlattener 0x0001   /* Query flattening */
                          /*  0x0002   available for reuse */
#define SQLITE_GroupByOrder   0x0004   /* GROUPBY cover of ORDERBY */
#define SQLITE_FactorOutConst 0x0008   /* Constant factoring */
#define SQLITE_DistinctOpt    0x0010   /* DISTINCT using indexes */
#define SQLITE_CoverIdxScan   0x0020   /* Covering index scans */
#define SQLITE_OrderByIdxJoin 0x0040   /* ORDER BY of joins via index */
#define SQLITE_Transitive     0x0080   /* Transitive constraints */
#define SQLITE_OmitNoopJoin   0x0100   /* Omit unused tables in joins */
#define SQLITE_CountOfView    0x0200   /* The count-of-view optimization */
#define SQLITE_CursorHints    0x0400   /* Add OP_CursorHint opcodes */
#define SQLITE_Stat34         0x0800   /* Use STAT3 or STAT4 data */
   /* TH3 expects the Stat34  ^^^^^^ value to be 0x0800.  Don't change it */
#define SQLITE_PushDown       0x1000   /* The push-down optimization */
#define SQLITE_SimplifyJoin   0x2000   /* Convert LEFT JOIN to JOIN */
#define SQLITE_SkipScan       0x4000   /* Skip-scans */
#define SQLITE_PropagateConst 0x8000   /* The constant propagation opt */
#define SQLITE_AllOpts        0xffff   /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)
#define OptimizationEnabled(db, mask)   (((db)->dbOptFlags&(mask))==0)







|














|







1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587

/*
** Bits of the sqlite3.dbOptFlags field that are used by the
** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
** selectively disable various optimizations.
*/
#define SQLITE_QueryFlattener 0x0001   /* Query flattening */
#define SQLITE_PropagateConst 0x0002   /* The constant propagation opt */
#define SQLITE_GroupByOrder   0x0004   /* GROUPBY cover of ORDERBY */
#define SQLITE_FactorOutConst 0x0008   /* Constant factoring */
#define SQLITE_DistinctOpt    0x0010   /* DISTINCT using indexes */
#define SQLITE_CoverIdxScan   0x0020   /* Covering index scans */
#define SQLITE_OrderByIdxJoin 0x0040   /* ORDER BY of joins via index */
#define SQLITE_Transitive     0x0080   /* Transitive constraints */
#define SQLITE_OmitNoopJoin   0x0100   /* Omit unused tables in joins */
#define SQLITE_CountOfView    0x0200   /* The count-of-view optimization */
#define SQLITE_CursorHints    0x0400   /* Add OP_CursorHint opcodes */
#define SQLITE_Stat34         0x0800   /* Use STAT3 or STAT4 data */
   /* TH3 expects the Stat34  ^^^^^^ value to be 0x0800.  Don't change it */
#define SQLITE_PushDown       0x1000   /* The push-down optimization */
#define SQLITE_SimplifyJoin   0x2000   /* Convert LEFT JOIN to JOIN */
#define SQLITE_SkipScan       0x4000   /* Skip-scans */
#define SQLITE_SortIfFaster   0x8000   /* ORDER BY using sorter if faster */
#define SQLITE_AllOpts        0xffff   /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)
#define OptimizationEnabled(db, mask)   (((db)->dbOptFlags&(mask))==0)

Changes to src/where.c.

4105
4106
4107
4108
4109
4110
4111
4112
4113
4114
4115
4116






4117
4118
4119
4120
4121
4122
4123
        }
        if( isOrdered>=0 && isOrdered<nOrderBy ){
          if( aSortCost[isOrdered]==0 ){
            aSortCost[isOrdered] = whereSortingCost(
                pWInfo, nRowEst, nOrderBy, isOrdered
            );
          }
          /* TUNING:  Add a small extra penalty (5) to sorting as an
          ** extra encouragment to the query planner to select a plan
          ** where the rows emerge in the correct order without any sorting
          ** required. */
          rCost = sqlite3LogEstAdd(rUnsorted, aSortCost[isOrdered]) + 5;







          WHERETRACE(0x002,
              ("---- sort cost=%-3d (%d/%d) increases cost %3d to %-3d\n",
               aSortCost[isOrdered], (nOrderBy-isOrdered), nOrderBy, 
               rUnsorted, rCost));
        }else{
          rCost = rUnsorted;







<
<
<
<
|
>
>
>
>
>
>







4105
4106
4107
4108
4109
4110
4111




4112
4113
4114
4115
4116
4117
4118
4119
4120
4121
4122
4123
4124
4125
        }
        if( isOrdered>=0 && isOrdered<nOrderBy ){
          if( aSortCost[isOrdered]==0 ){
            aSortCost[isOrdered] = whereSortingCost(
                pWInfo, nRowEst, nOrderBy, isOrdered
            );
          }




          rCost = sqlite3LogEstAdd(rUnsorted, aSortCost[isOrdered]);
          if( OptimizationDisabled(db, SQLITE_SortIfFaster) ){
            /* if the SortIfFaster optimization is disabled, then set the
            ** sort cost very high, to encourage the query to return the
            ** results in natural order, if at all possible */
            rCost += 200;
          }

          WHERETRACE(0x002,
              ("---- sort cost=%-3d (%d/%d) increases cost %3d to %-3d\n",
               aSortCost[isOrdered], (nOrderBy-isOrdered), nOrderBy, 
               rUnsorted, rCost));
        }else{
          rCost = rUnsorted;

Changes to test/where.test.

578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
    SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 10
  }
} {/1 100 4 2 99 9 3 98 16 .* nosort/}
do_test where-6.7.2 {
  cksort {
    SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 1
  }
} {1 100 4 nosort}
ifcapable subquery {
  do_test where-6.8a {
    cksort {
      SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
    }
  } {1 100 4 2 99 9 3 98 16 nosort}
  do_test where-6.8b {







|







578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
    SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 10
  }
} {/1 100 4 2 99 9 3 98 16 .* nosort/}
do_test where-6.7.2 {
  cksort {
    SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 1
  }
} {1 100 4 sort}
ifcapable subquery {
  do_test where-6.8a {
    cksort {
      SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
    }
  } {1 100 4 2 99 9 3 98 16 nosort}
  do_test where-6.8b {