/ Changes On Branch index-scan-rate
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.

Changes In Branch index-scan-rate Excluding Merge-Ins

This is equivalent to a diff from 087af29e to de78250a

2013-10-06
22:52
Remove an incorrect debugging assert() that was accidently added during the STAT4 enhancement. check-in: 2bb7f74b user: drh tags: trunk
2013-10-05
18:16
Begin an experimental refactoring to estimate the average number of bytes in table and index rows and to use that information in query planner. Begin by renaming WhereCost to LogEst and making that type and its conversion routines available outside of where.c. check-in: 66c4a251 user: drh tags: row-size-est
02:56
In the index_list pragma, make sure the "r" column is the same on output as it was on input in the sqlite_stat1 table. Closed-Leaf check-in: de78250a user: drh tags: index-scan-rate
2013-10-04
20:39
Merge trunk changes. check-in: c6ac80ed user: drh tags: index-scan-rate
18:17
If an "INSERT INTO ... SELECT" can use the xfer optimization, pass the OPFLAG_BULKCSR hint to btree cursors used to update indices. This results in a tighter key packing. check-in: 087af29e user: dan tags: trunk
00:00
Make sure the count(*) optimization works correctly even when partial indices are present. Ticket [a5c8ed66cae]. check-in: 9f2f4c0a user: drh tags: trunk

Changes to src/analyze.c.

1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
....
1272
1273
1274
1275
1276
1277
1278

1279
1280












1281
1282
1283
1284
1285
1286
1287
....
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329

/*
** The first argument points to a nul-terminated string containing a
** list of space separated integers. Read the first nOut of these into
** the array aOut[].
*/
static void decodeIntArray(
  char *zIntArray, 
  int nOut, 
  tRowcnt *aOut, 
  int *pbUnordered
){
  char *z = zIntArray;
  int c;
  int i;
  tRowcnt v;

  assert( pbUnordered==0 || *pbUnordered==0 );

#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  if( z==0 ) z = "";
#else
  if( NEVER(z==0) ) z = "";
#endif
  for(i=0; *z && i<nOut; i++){
    v = 0;
................................................................................
    while( (c=z[0])>='0' && c<='9' ){
      v = v*10 + c - '0';
      z++;
    }
    aOut[i] = v;
    if( *z==' ' ) z++;
  }

  if( pbUnordered && strcmp(z, "unordered")==0 ){
    *pbUnordered = 1;












  }
}

/*
** This callback is invoked once for each index when reading the
** sqlite_stat1 table.  
**
................................................................................
    pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase);
  }else{
    pIndex = 0;
  }
  z = argv[2];

  if( pIndex ){
    int bUnordered = 0;
    decodeIntArray((char*)z, pIndex->nColumn+1, pIndex->aiRowEst,&bUnordered);
    if( pIndex->pPartIdxWhere==0 ) pTable->nRowEst = pIndex->aiRowEst[0];
    pIndex->bUnordered = bUnordered;
  }else{
    decodeIntArray((char*)z, 1, &pTable->nRowEst, 0);
  }

  return 0;
}








|
|
|
|






<
<







 







>
|
|
>
>
>
>
>
>
>
>
>
>
>
>







 







<
|

<







1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262


1263
1264
1265
1266
1267
1268
1269
....
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
....
1323
1324
1325
1326
1327
1328
1329

1330
1331

1332
1333
1334
1335
1336
1337
1338

/*
** The first argument points to a nul-terminated string containing a
** list of space separated integers. Read the first nOut of these into
** the array aOut[].
*/
static void decodeIntArray(
  char *zIntArray,       /* String containing int array to decode */
  int nOut,              /* Number of slots in aOut[] */
  tRowcnt *aOut,         /* Store integers here */
  Index *pIndex          /* Handle extra flags for this index, if not NULL */
){
  char *z = zIntArray;
  int c;
  int i;
  tRowcnt v;



#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  if( z==0 ) z = "";
#else
  if( NEVER(z==0) ) z = "";
#endif
  for(i=0; *z && i<nOut; i++){
    v = 0;
................................................................................
    while( (c=z[0])>='0' && c<='9' ){
      v = v*10 + c - '0';
      z++;
    }
    aOut[i] = v;
    if( *z==' ' ) z++;
  }
  if( pIndex ){
    if( strcmp(z, "unordered")==0 ){
      pIndex->bUnordered = 1;
    }else if( sqlite3_strglob("r=[0-9]*", z)==0 ){
      int v32 = 0;
      sqlite3GetInt32(z+2, &v32);
      if( v32>=200 ){
        v32 = 255;
      }else if( v32<=0 ){
        v32 = 1;
      }else{
        v32 = (128*v32)/100;
      }
      pIndex->iScanRatio = (u8)v32;
    }
  }
}

/*
** This callback is invoked once for each index when reading the
** sqlite_stat1 table.  
**
................................................................................
    pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase);
  }else{
    pIndex = 0;
  }
  z = argv[2];

  if( pIndex ){

    decodeIntArray((char*)z, pIndex->nColumn+1, pIndex->aiRowEst, pIndex);
    if( pIndex->pPartIdxWhere==0 ) pTable->nRowEst = pIndex->aiRowEst[0];

  }else{
    decodeIntArray((char*)z, 1, &pTable->nRowEst, 0);
  }

  return 0;
}

Changes to src/build.c.

1022
1023
1024
1025
1026
1027
1028

1029
1030
1031
1032
1033
1034
1035
....
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072

1073

1074
1075
1076
1077
1078

1079
1080
1081
1082
1083
1084
1085

1086
1087
1088
1089
1090
1091
1092
....
1096
1097
1098
1099
1100
1101
1102






















1103
1104
1105
1106
1107
1108
1109
....
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
....
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479

































1480
1481
1482
1483
1484
1485
1486
....
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509


1510
1511
1512
1513
1514
1515
1516
....
1521
1522
1523
1524
1525
1526
1527






1528
1529
1530
1531
1532
1533
1534
....
2480
2481
2482
2483
2484
2485
2486
2487

2488
2489

2490
2491
2492
2493
2494
2495
2496
....
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
....
2746
2747
2748
2749
2750
2751
2752



2753
2754
2755
2756
2757
2758
2759
  pCol->zName = z;
 
  /* If there is no type specified, columns have the default affinity
  ** 'NONE'. If there is a type specified, then sqlite3AddColumnType() will
  ** be called next to set pCol->affinity correctly.
  */
  pCol->affinity = SQLITE_AFF_NONE;

  p->nCol++;
}

/*
** This routine is called by the parser while in the middle of
** parsing a CREATE TABLE statement.  A "NOT NULL" constraint has
** been seen on a column.  This routine sets the notNull flag on
................................................................................
** 'REAL'        | SQLITE_AFF_REAL
** 'FLOA'        | SQLITE_AFF_REAL
** 'DOUB'        | SQLITE_AFF_REAL
**
** If none of the substrings in the above table are found,
** SQLITE_AFF_NUMERIC is returned.
*/
char sqlite3AffinityType(const char *zIn){
  u32 h = 0;
  char aff = SQLITE_AFF_NUMERIC;



  if( zIn ) while( zIn[0] ){
    h = (h<<8) + sqlite3UpperToLower[(*zIn)&0xff];
    zIn++;
    if( h==(('c'<<24)+('h'<<16)+('a'<<8)+'r') ){             /* CHAR */
      aff = SQLITE_AFF_TEXT; 

    }else if( h==(('c'<<24)+('l'<<16)+('o'<<8)+'b') ){       /* CLOB */
      aff = SQLITE_AFF_TEXT;
    }else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){       /* TEXT */
      aff = SQLITE_AFF_TEXT;
    }else if( h==(('b'<<24)+('l'<<16)+('o'<<8)+'b')          /* BLOB */
        && (aff==SQLITE_AFF_NUMERIC || aff==SQLITE_AFF_REAL) ){
      aff = SQLITE_AFF_NONE;

#ifndef SQLITE_OMIT_FLOATING_POINT
    }else if( h==(('r'<<24)+('e'<<16)+('a'<<8)+'l')          /* REAL */
        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_REAL;
    }else if( h==(('f'<<24)+('l'<<16)+('o'<<8)+'a')          /* FLOA */
        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_REAL;
................................................................................
#endif
    }else if( (h&0x00FFFFFF)==(('i'<<16)+('n'<<8)+'t') ){    /* INT */
      aff = SQLITE_AFF_INTEGER;
      break;
    }
  }























  return aff;
}

/*
** This routine is called by the parser while in the middle of
** parsing a CREATE TABLE statement.  The pFirst token is the first
** token in the sequence of tokens that describe the type of the
................................................................................
  Column *pCol;

  p = pParse->pNewTable;
  if( p==0 || NEVER(p->nCol<1) ) return;
  pCol = &p->aCol[p->nCol-1];
  assert( pCol->zType==0 );
  pCol->zType = sqlite3NameFromToken(pParse->db, pType);
  pCol->affinity = sqlite3AffinityType(pCol->zType);
}

/*
** The expression is the default value for the most recently added column
** of the table currently under construction.
**
** Default value expressions must be constant.  Raise an exception if this
................................................................................
    testcase( pCol->affinity==SQLITE_AFF_NUMERIC );
    testcase( pCol->affinity==SQLITE_AFF_INTEGER );
    testcase( pCol->affinity==SQLITE_AFF_REAL );
    
    zType = azType[pCol->affinity - SQLITE_AFF_TEXT];
    len = sqlite3Strlen30(zType);
    assert( pCol->affinity==SQLITE_AFF_NONE 
            || pCol->affinity==sqlite3AffinityType(zType) );
    memcpy(&zStmt[k], zType, len);
    k += len;
    assert( k<=n );
  }
  sqlite3_snprintf(n-k, &zStmt[k], "%s", zEnd);
  return zStmt;
}


































/*
** This routine is called to report the final ")" that terminates
** a CREATE TABLE statement.
**
** The table structure that other action routines have been building
** is added to the internal hash tables, assuming no errors have
................................................................................
*/
void sqlite3EndTable(
  Parse *pParse,          /* Parse context */
  Token *pCons,           /* The ',' token after the last column defn. */
  Token *pEnd,            /* The final ')' token in the CREATE TABLE */
  Select *pSelect         /* Select from a "CREATE ... AS SELECT" */
){
  Table *p;
  sqlite3 *db = pParse->db;
  int iDb;



  if( (pEnd==0 && pSelect==0) || db->mallocFailed ){
    return;
  }
  p = pParse->pNewTable;
  if( p==0 ) return;

................................................................................
#ifndef SQLITE_OMIT_CHECK
  /* Resolve names in all CHECK constraint expressions.
  */
  if( p->pCheck ){
    sqlite3ResolveSelfReference(pParse, p, NC_IsCheck, 0, p->pCheck);
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */







  /* If the db->init.busy is 1 it means we are reading the SQL off the
  ** "sqlite_master" or "sqlite_temp_master" table on the disk.
  ** So do not write to the disk again.  Extract the root page number
  ** for the table from the db->init.newTnum field.  (The page number
  ** should have been put there by the sqliteOpenCb routine.)
  */
................................................................................
  DbFixer sFix;        /* For assigning database names to pTable */
  int sortOrderMask;   /* 1 to honor DESC in index.  0 to ignore. */
  sqlite3 *db = pParse->db;
  Db *pDb;             /* The specific table containing the indexed database */
  int iDb;             /* Index of the database that is being written */
  Token *pName = 0;    /* Unqualified name of the index to create */
  struct ExprList_item *pListItem; /* For looping over pList */
  int nCol;

  int nExtra = 0;
  char *zExtra;


  assert( pParse->nErr==0 );      /* Never called with prior errors */
  if( db->mallocFailed || IN_DECLARE_VTAB ){
    goto exit_create_index;
  }
  if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){
    goto exit_create_index;
................................................................................
  ** more than once within the same index.  Only the first instance of
  ** the column will ever be used by the optimizer.  Note that using the
  ** same column more than once cannot be an error because that would 
  ** break backwards compatibility - it needs to be a warning.
  */
  for(i=0, pListItem=pList->a; i<pList->nExpr; i++, pListItem++){
    const char *zColName = pListItem->zName;
    Column *pTabCol;
    int requestedSortOrder;
    char *zColl;                   /* Collation sequence name */

    for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){
      if( sqlite3StrICmp(zColName, pTabCol->zName)==0 ) break;
    }
    if( j>=pTab->nCol ){
................................................................................
    }
    pIndex->azColl[i] = zColl;
    requestedSortOrder = pListItem->sortOrder & sortOrderMask;
    pIndex->aSortOrder[i] = (u8)requestedSortOrder;
    if( pTab->aCol[j].notNull==0 ) pIndex->uniqNotNull = 0;
  }
  sqlite3DefaultRowEst(pIndex);




  if( pTab==pParse->pNewTable ){
    /* This routine has been called to create an automatic index as a
    ** result of a PRIMARY KEY or UNIQUE clause on a column definition, or
    ** a PRIMARY KEY or UNIQUE clause following the column definitions.
    ** i.e. one of:
    **







>







 







|


>

>
|



|
>







>







 







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







 







|







 







|







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







 







|
|
|
>
>







 







>
>
>
>
>
>







 







|
>
|
<
>







 







<







 







>
>
>







1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
....
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
....
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
....
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
....
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
....
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
....
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
....
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557

2558
2559
2560
2561
2562
2563
2564
2565
....
2778
2779
2780
2781
2782
2783
2784

2785
2786
2787
2788
2789
2790
2791
....
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
  pCol->zName = z;
 
  /* If there is no type specified, columns have the default affinity
  ** 'NONE'. If there is a type specified, then sqlite3AddColumnType() will
  ** be called next to set pCol->affinity correctly.
  */
  pCol->affinity = SQLITE_AFF_NONE;
  pCol->szEst = 1;
  p->nCol++;
}

/*
** This routine is called by the parser while in the middle of
** parsing a CREATE TABLE statement.  A "NOT NULL" constraint has
** been seen on a column.  This routine sets the notNull flag on
................................................................................
** 'REAL'        | SQLITE_AFF_REAL
** 'FLOA'        | SQLITE_AFF_REAL
** 'DOUB'        | SQLITE_AFF_REAL
**
** If none of the substrings in the above table are found,
** SQLITE_AFF_NUMERIC is returned.
*/
char sqlite3AffinityType(const char *zIn, u8 *pszEst){
  u32 h = 0;
  char aff = SQLITE_AFF_NUMERIC;
  const char *zChar = 0;

  if( zIn==0 ) return aff;
  while( zIn[0] ){
    h = (h<<8) + sqlite3UpperToLower[(*zIn)&0xff];
    zIn++;
    if( h==(('c'<<24)+('h'<<16)+('a'<<8)+'r') ){             /* CHAR */
      aff = SQLITE_AFF_TEXT;
      zChar = zIn;
    }else if( h==(('c'<<24)+('l'<<16)+('o'<<8)+'b') ){       /* CLOB */
      aff = SQLITE_AFF_TEXT;
    }else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){       /* TEXT */
      aff = SQLITE_AFF_TEXT;
    }else if( h==(('b'<<24)+('l'<<16)+('o'<<8)+'b')          /* BLOB */
        && (aff==SQLITE_AFF_NUMERIC || aff==SQLITE_AFF_REAL) ){
      aff = SQLITE_AFF_NONE;
      if( zIn[0]=='(' ) zChar = zIn;
#ifndef SQLITE_OMIT_FLOATING_POINT
    }else if( h==(('r'<<24)+('e'<<16)+('a'<<8)+'l')          /* REAL */
        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_REAL;
    }else if( h==(('f'<<24)+('l'<<16)+('o'<<8)+'a')          /* FLOA */
        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_REAL;
................................................................................
#endif
    }else if( (h&0x00FFFFFF)==(('i'<<16)+('n'<<8)+'t') ){    /* INT */
      aff = SQLITE_AFF_INTEGER;
      break;
    }
  }

  /* If pszEst is not NULL, store an estimate of the field size.  The
  ** estimate is scaled so that the size of an integer is 1.  */
  if( pszEst ){
    *pszEst = 1;   /* default size is approx 4 bytes */
    if( aff<=SQLITE_AFF_NONE ){
      if( zChar ){
        while( zChar[0] ){
          if( sqlite3Isdigit(zChar[0]) ){
            int v;
            sqlite3GetInt32(zChar, &v);
            v = v/4 + 1;
            if( v>255 ) v = 255;
            *pszEst = v; /* BLOB(k), VARCHAR(k), CHAR(k) -> r=(k/4+1) */
            break;
          }
          zChar++;
        }
      }else{
        *pszEst = 5;   /* BLOB, TEXT, CLOB -> r=5  (approx 20 bytes)*/
      }
    }
  }
  return aff;
}

/*
** This routine is called by the parser while in the middle of
** parsing a CREATE TABLE statement.  The pFirst token is the first
** token in the sequence of tokens that describe the type of the
................................................................................
  Column *pCol;

  p = pParse->pNewTable;
  if( p==0 || NEVER(p->nCol<1) ) return;
  pCol = &p->aCol[p->nCol-1];
  assert( pCol->zType==0 );
  pCol->zType = sqlite3NameFromToken(pParse->db, pType);
  pCol->affinity = sqlite3AffinityType(pCol->zType, &pCol->szEst);
}

/*
** The expression is the default value for the most recently added column
** of the table currently under construction.
**
** Default value expressions must be constant.  Raise an exception if this
................................................................................
    testcase( pCol->affinity==SQLITE_AFF_NUMERIC );
    testcase( pCol->affinity==SQLITE_AFF_INTEGER );
    testcase( pCol->affinity==SQLITE_AFF_REAL );
    
    zType = azType[pCol->affinity - SQLITE_AFF_TEXT];
    len = sqlite3Strlen30(zType);
    assert( pCol->affinity==SQLITE_AFF_NONE 
            || pCol->affinity==sqlite3AffinityType(zType, 0) );
    memcpy(&zStmt[k], zType, len);
    k += len;
    assert( k<=n );
  }
  sqlite3_snprintf(n-k, &zStmt[k], "%s", zEnd);
  return zStmt;
}

/*
** Estimate the total row width for a table.
*/
static unsigned estimatedTableWidth(const Table *pTab){
  unsigned wTable = 0;
  const Column *pTabCol;
  int i;
  for(i=pTab->nCol, pTabCol=pTab->aCol; i>0; i--, pTabCol++){
    wTable += pTabCol->szEst;
  }
  if( pTab->iPKey<0 ) wTable++;
  return wTable;
}

/*
** Set the iScanRatio for an index based on estimates of the average
** table row width and average index row width.  Estimates are derived
** from the declared datatypes of the various columns.
*/
static void setIndexScanRatio(Index *pIdx, unsigned wTable){
  unsigned wIndex = 1;
  int i;
  const Column *aCol = pIdx->pTable->aCol;
  for(i=0; i<pIdx->nColumn; i++){
    assert( pIdx->aiColumn[i]>=0 && pIdx->aiColumn[i]<pIdx->pTable->nCol );
    wIndex += aCol[pIdx->aiColumn[i]].szEst;
  }
  assert( 100*wIndex/wTable <= 255 );
  pIdx->iScanRatio = (u8)(128*wIndex/wTable);
  /* printf("%s: wIndex=%d wTable=%d ratio=%d\n",
  ** pIdx->zName, wIndex, wTable, (100*pIdx->iScanRatio)/128); */
}

/*
** This routine is called to report the final ")" that terminates
** a CREATE TABLE statement.
**
** The table structure that other action routines have been building
** is added to the internal hash tables, assuming no errors have
................................................................................
*/
void sqlite3EndTable(
  Parse *pParse,          /* Parse context */
  Token *pCons,           /* The ',' token after the last column defn. */
  Token *pEnd,            /* The final ')' token in the CREATE TABLE */
  Select *pSelect         /* Select from a "CREATE ... AS SELECT" */
){
  Table *p;                 /* The new table */
  sqlite3 *db = pParse->db; /* The database connection */
  int iDb;                  /* Database in which the table lives */
  Index *pIdx;              /* An implied index of the table */
  unsigned wTable;          /* Estimated average width of a row in the table */

  if( (pEnd==0 && pSelect==0) || db->mallocFailed ){
    return;
  }
  p = pParse->pNewTable;
  if( p==0 ) return;

................................................................................
#ifndef SQLITE_OMIT_CHECK
  /* Resolve names in all CHECK constraint expressions.
  */
  if( p->pCheck ){
    sqlite3ResolveSelfReference(pParse, p, NC_IsCheck, 0, p->pCheck);
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* Compute the iScanRatio of implied indices */
  wTable = estimatedTableWidth(p);
  for(pIdx=p->pIndex; pIdx; pIdx=pIdx->pNext){
    setIndexScanRatio(pIdx, wTable);
  }

  /* If the db->init.busy is 1 it means we are reading the SQL off the
  ** "sqlite_master" or "sqlite_temp_master" table on the disk.
  ** So do not write to the disk again.  Extract the root page number
  ** for the table from the db->init.newTnum field.  (The page number
  ** should have been put there by the sqliteOpenCb routine.)
  */
................................................................................
  DbFixer sFix;        /* For assigning database names to pTable */
  int sortOrderMask;   /* 1 to honor DESC in index.  0 to ignore. */
  sqlite3 *db = pParse->db;
  Db *pDb;             /* The specific table containing the indexed database */
  int iDb;             /* Index of the database that is being written */
  Token *pName = 0;    /* Unqualified name of the index to create */
  struct ExprList_item *pListItem; /* For looping over pList */
  const Column *pTabCol;           /* A column in the table */
  int nCol;                        /* Number of columns */
  int nExtra = 0;                  /* Space allocated for zExtra[] */

  char *zExtra;                    /* Extra space after the Index object */

  assert( pParse->nErr==0 );      /* Never called with prior errors */
  if( db->mallocFailed || IN_DECLARE_VTAB ){
    goto exit_create_index;
  }
  if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){
    goto exit_create_index;
................................................................................
  ** more than once within the same index.  Only the first instance of
  ** the column will ever be used by the optimizer.  Note that using the
  ** same column more than once cannot be an error because that would 
  ** break backwards compatibility - it needs to be a warning.
  */
  for(i=0, pListItem=pList->a; i<pList->nExpr; i++, pListItem++){
    const char *zColName = pListItem->zName;

    int requestedSortOrder;
    char *zColl;                   /* Collation sequence name */

    for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){
      if( sqlite3StrICmp(zColName, pTabCol->zName)==0 ) break;
    }
    if( j>=pTab->nCol ){
................................................................................
    }
    pIndex->azColl[i] = zColl;
    requestedSortOrder = pListItem->sortOrder & sortOrderMask;
    pIndex->aSortOrder[i] = (u8)requestedSortOrder;
    if( pTab->aCol[j].notNull==0 ) pIndex->uniqNotNull = 0;
  }
  sqlite3DefaultRowEst(pIndex);
  if( pParse->pNewTable==0 ){
    setIndexScanRatio(pIndex, estimatedTableWidth(pTab));
  }

  if( pTab==pParse->pNewTable ){
    /* This routine has been called to create an automatic index as a
    ** result of a PRIMARY KEY or UNIQUE clause on a column definition, or
    ** a PRIMARY KEY or UNIQUE clause following the column definitions.
    ** i.e. one of:
    **

Changes to src/expr.c.

37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
....
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
....
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
  if( op==TK_SELECT ){
    assert( pExpr->flags&EP_xIsSelect );
    return sqlite3ExprAffinity(pExpr->x.pSelect->pEList->a[0].pExpr);
  }
#ifndef SQLITE_OMIT_CAST
  if( op==TK_CAST ){
    assert( !ExprHasProperty(pExpr, EP_IntValue) );
    return sqlite3AffinityType(pExpr->u.zToken);
  }
#endif
  if( (op==TK_AGG_COLUMN || op==TK_COLUMN || op==TK_REGISTER) 
   && pExpr->pTab!=0
  ){
    /* op==TK_REGISTER && pExpr->pTab!=0 happens when pExpr was originally
    ** a TK_COLUMN but was previously evaluated and cached in a register */
................................................................................
    }
#ifndef SQLITE_OMIT_CAST
    case TK_CAST: {
      /* Expressions of the form:   CAST(pLeft AS token) */
      int aff, to_op;
      inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
      assert( !ExprHasProperty(pExpr, EP_IntValue) );
      aff = sqlite3AffinityType(pExpr->u.zToken);
      to_op = aff - SQLITE_AFF_TEXT + OP_ToText;
      assert( to_op==OP_ToText    || aff!=SQLITE_AFF_TEXT    );
      assert( to_op==OP_ToBlob    || aff!=SQLITE_AFF_NONE    );
      assert( to_op==OP_ToNumeric || aff!=SQLITE_AFF_NUMERIC );
      assert( to_op==OP_ToInt     || aff!=SQLITE_AFF_INTEGER );
      assert( to_op==OP_ToReal    || aff!=SQLITE_AFF_REAL    );
      testcase( to_op==OP_ToText );
................................................................................
      sqlite3ExplainExpr(pOut, pExpr->pLeft);
      break;
    }
#ifndef SQLITE_OMIT_CAST
    case TK_CAST: {
      /* Expressions of the form:   CAST(pLeft AS token) */
      const char *zAff = "unk";
      switch( sqlite3AffinityType(pExpr->u.zToken) ){
        case SQLITE_AFF_TEXT:    zAff = "TEXT";     break;
        case SQLITE_AFF_NONE:    zAff = "NONE";     break;
        case SQLITE_AFF_NUMERIC: zAff = "NUMERIC";  break;
        case SQLITE_AFF_INTEGER: zAff = "INTEGER";  break;
        case SQLITE_AFF_REAL:    zAff = "REAL";     break;
      }
      sqlite3ExplainPrintf(pOut, "CAST-%s(", zAff);







|







 







|







 







|







37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
....
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
....
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
  if( op==TK_SELECT ){
    assert( pExpr->flags&EP_xIsSelect );
    return sqlite3ExprAffinity(pExpr->x.pSelect->pEList->a[0].pExpr);
  }
#ifndef SQLITE_OMIT_CAST
  if( op==TK_CAST ){
    assert( !ExprHasProperty(pExpr, EP_IntValue) );
    return sqlite3AffinityType(pExpr->u.zToken, 0);
  }
#endif
  if( (op==TK_AGG_COLUMN || op==TK_COLUMN || op==TK_REGISTER) 
   && pExpr->pTab!=0
  ){
    /* op==TK_REGISTER && pExpr->pTab!=0 happens when pExpr was originally
    ** a TK_COLUMN but was previously evaluated and cached in a register */
................................................................................
    }
#ifndef SQLITE_OMIT_CAST
    case TK_CAST: {
      /* Expressions of the form:   CAST(pLeft AS token) */
      int aff, to_op;
      inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
      assert( !ExprHasProperty(pExpr, EP_IntValue) );
      aff = sqlite3AffinityType(pExpr->u.zToken, 0);
      to_op = aff - SQLITE_AFF_TEXT + OP_ToText;
      assert( to_op==OP_ToText    || aff!=SQLITE_AFF_TEXT    );
      assert( to_op==OP_ToBlob    || aff!=SQLITE_AFF_NONE    );
      assert( to_op==OP_ToNumeric || aff!=SQLITE_AFF_NUMERIC );
      assert( to_op==OP_ToInt     || aff!=SQLITE_AFF_INTEGER );
      assert( to_op==OP_ToReal    || aff!=SQLITE_AFF_REAL    );
      testcase( to_op==OP_ToText );
................................................................................
      sqlite3ExplainExpr(pOut, pExpr->pLeft);
      break;
    }
#ifndef SQLITE_OMIT_CAST
    case TK_CAST: {
      /* Expressions of the form:   CAST(pLeft AS token) */
      const char *zAff = "unk";
      switch( sqlite3AffinityType(pExpr->u.zToken, 0) ){
        case SQLITE_AFF_TEXT:    zAff = "TEXT";     break;
        case SQLITE_AFF_NONE:    zAff = "NONE";     break;
        case SQLITE_AFF_NUMERIC: zAff = "NUMERIC";  break;
        case SQLITE_AFF_INTEGER: zAff = "INTEGER";  break;
        case SQLITE_AFF_REAL:    zAff = "REAL";     break;
      }
      sqlite3ExplainPrintf(pOut, "CAST-%s(", zAff);

Changes to src/pragma.c.

1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463

1464
1465
1466
1467

1468
1469
1470
1471
1472
1473
1474
1475
    Table *pTab;
    pTab = sqlite3FindTable(db, zRight, zDb);
    if( pTab ){
      v = sqlite3GetVdbe(pParse);
      pIdx = pTab->pIndex;
      if( pIdx ){
        int i = 0; 
        sqlite3VdbeSetNumCols(v, 3);
        pParse->nMem = 3;
        sqlite3CodeVerifySchema(pParse, iDb);
        sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "seq", SQLITE_STATIC);
        sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "name", SQLITE_STATIC);
        sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "unique", SQLITE_STATIC);

        while(pIdx){
          sqlite3VdbeAddOp2(v, OP_Integer, i, 1);
          sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, pIdx->zName, 0);
          sqlite3VdbeAddOp2(v, OP_Integer, pIdx->onError!=OE_None, 3);

          sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 3);
          ++i;
          pIdx = pIdx->pNext;
        }
      }
    }
  }
  break;







|
|




>




>
|







1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
    Table *pTab;
    pTab = sqlite3FindTable(db, zRight, zDb);
    if( pTab ){
      v = sqlite3GetVdbe(pParse);
      pIdx = pTab->pIndex;
      if( pIdx ){
        int i = 0; 
        sqlite3VdbeSetNumCols(v, 4);
        pParse->nMem = 4;
        sqlite3CodeVerifySchema(pParse, iDb);
        sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "seq", SQLITE_STATIC);
        sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "name", SQLITE_STATIC);
        sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "unique", SQLITE_STATIC);
        sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "r", SQLITE_STATIC);
        while(pIdx){
          sqlite3VdbeAddOp2(v, OP_Integer, i, 1);
          sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, pIdx->zName, 0);
          sqlite3VdbeAddOp2(v, OP_Integer, pIdx->onError!=OE_None, 3);
          sqlite3VdbeAddOp2(v, OP_Integer, (pIdx->iScanRatio*100+127)/128, 4);
          sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4);
          ++i;
          pIdx = pIdx->pNext;
        }
      }
    }
  }
  break;

Changes to src/select.c.

4593
4594
4595
4596
4597
4598
4599
4600
4601
4602
4603
4604
4605
4606
4607
4608
4609
4610
4611
4612
4613
4614
4615
4616

4617
4618
4619
4620
4621
4622
4623
4624
4625
4626
4627
4628
4629
4630
        KeyInfo *pKeyInfo = 0;               /* Keyinfo for scanned index */
        Index *pBest = 0;                    /* Best index found so far */
        int iRoot = pTab->tnum;              /* Root page of scanned b-tree */

        sqlite3CodeVerifySchema(pParse, iDb);
        sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);

        /* Search for the index that has the least amount of columns. If
        ** there is such an index, and it has less columns than the table
        ** does, then we can assume that it consumes less space on disk and
        ** will therefore be cheaper to scan to determine the query result.
        ** In this case set iRoot to the root page number of the index b-tree
        ** and pKeyInfo to the KeyInfo structure required to navigate the
        ** index.
        **
        ** (2011-04-15) Do not do a full scan of an unordered index.
        **
        ** (2013-10-03) Do not count the entires in a partial index.
        **
        ** In practice the KeyInfo structure will not be used. It is only 
        ** passed to keep OP_OpenRead happy.
        */
        for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          if( pIdx->bUnordered==0

           && pIdx->pPartIdxWhere==0
           && (!pBest || pIdx->nColumn<pBest->nColumn)
          ){
            pBest = pIdx;
          }
        }
        if( pBest && pBest->nColumn<pTab->nCol ){
          iRoot = pBest->tnum;
          pKeyInfo = sqlite3IndexKeyinfo(pParse, pBest);
        }

        /* Open a read-only cursor, execute the OP_Count, close the cursor. */
        sqlite3VdbeAddOp3(v, OP_OpenRead, iCsr, iRoot, iDb);
        if( pKeyInfo ){







|
<
<
<
<
<
<










>

|




|







4593
4594
4595
4596
4597
4598
4599
4600






4601
4602
4603
4604
4605
4606
4607
4608
4609
4610
4611
4612
4613
4614
4615
4616
4617
4618
4619
4620
4621
4622
4623
4624
4625
        KeyInfo *pKeyInfo = 0;               /* Keyinfo for scanned index */
        Index *pBest = 0;                    /* Best index found so far */
        int iRoot = pTab->tnum;              /* Root page of scanned b-tree */

        sqlite3CodeVerifySchema(pParse, iDb);
        sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);

        /* Search for the index that has the lowest scan cost.






        **
        ** (2011-04-15) Do not do a full scan of an unordered index.
        **
        ** (2013-10-03) Do not count the entires in a partial index.
        **
        ** In practice the KeyInfo structure will not be used. It is only 
        ** passed to keep OP_OpenRead happy.
        */
        for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          if( pIdx->bUnordered==0
           && pIdx->iScanRatio<128
           && pIdx->pPartIdxWhere==0
           && (!pBest || pIdx->iScanRatio<pBest->iScanRatio)
          ){
            pBest = pIdx;
          }
        }
        if( pBest ){
          iRoot = pBest->tnum;
          pKeyInfo = sqlite3IndexKeyinfo(pParse, pBest);
        }

        /* Open a read-only cursor, execute the OP_Count, close the cursor. */
        sqlite3VdbeAddOp3(v, OP_OpenRead, iCsr, iRoot, iDb);
        if( pKeyInfo ){

Changes to src/sqliteInt.h.

1193
1194
1195
1196
1197
1198
1199

1200
1201
1202
1203
1204
1205
1206
1207
....
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
....
1556
1557
1558
1559
1560
1561
1562

1563
1564
1565
1566
1567
1568
1569
1570
....
3077
3078
3079
3080
3081
3082
3083
3084
3085
3086
3087
3088
3089
3090
3091
  char *zName;     /* Name of this column */
  Expr *pDflt;     /* Default value of this column */
  char *zDflt;     /* Original text of the default value */
  char *zType;     /* Data type for this column */
  char *zColl;     /* Collating sequence.  If NULL, use the default */
  u8 notNull;      /* An OE_ code for handling a NOT NULL constraint */
  char affinity;   /* One of the SQLITE_AFF_... values */

  u16 colFlags;    /* Boolean properties.  See COLFLAG_ defines below */
};

/* Allowed values for Column.colFlags:
*/
#define COLFLAG_PRIMKEY  0x0001    /* Column is part of the primary key */
#define COLFLAG_HIDDEN   0x0002    /* A hidden column in a virtual table */

................................................................................
#define OE_Replace  5   /* Delete existing record, then do INSERT or UPDATE */

#define OE_Restrict 6   /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */
#define OE_SetNull  7   /* Set the foreign key value to NULL */
#define OE_SetDflt  8   /* Set the foreign key value to its default */
#define OE_Cascade  9   /* Cascade the changes */

#define OE_Default  99  /* Do whatever the default action is */


/*
** An instance of the following structure is passed as the first
** argument to sqlite3VdbeKeyCompare and is used to control the 
** comparison of the two index keys.
**
................................................................................
  Index *pNext;            /* The next index associated with the same table */
  Schema *pSchema;         /* Schema containing this index */
  u8 *aSortOrder;          /* for each column: True==DESC, False==ASC */
  char **azColl;           /* Array of collation sequence names for index */
  Expr *pPartIdxWhere;     /* WHERE clause for partial indices */
  int tnum;                /* DB Page containing root of this index */
  u16 nColumn;             /* Number of columns in table used by this index */

  u8 onError;              /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  unsigned autoIndex:2;    /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */
  unsigned bUnordered:1;   /* Use this index for == or IN queries only */
  unsigned uniqNotNull:1;  /* True if UNIQUE and NOT NULL for all columns */
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  int nSample;             /* Number of elements in aSample[] */
  int nSampleCol;          /* Size of IndexSample.anEq[] and so on */
  tRowcnt *aAvgEq;         /* Average nEq values for keys not in aSample */
................................................................................
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*);
int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*);
char sqlite3AffinityType(const char*);
void sqlite3Analyze(Parse*, Token*, Token*);
int sqlite3InvokeBusyHandler(BusyHandler*);
int sqlite3FindDb(sqlite3*, Token*);
int sqlite3FindDbName(sqlite3 *, const char *);
int sqlite3AnalysisLoad(sqlite3*,int iDB);
void sqlite3DeleteIndexSamples(sqlite3*,Index*);
void sqlite3DefaultRowEst(Index*);







>
|







 







|







 







>
|







 







|







1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
....
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
....
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
....
3079
3080
3081
3082
3083
3084
3085
3086
3087
3088
3089
3090
3091
3092
3093
  char *zName;     /* Name of this column */
  Expr *pDflt;     /* Default value of this column */
  char *zDflt;     /* Original text of the default value */
  char *zType;     /* Data type for this column */
  char *zColl;     /* Collating sequence.  If NULL, use the default */
  u8 notNull;      /* An OE_ code for handling a NOT NULL constraint */
  char affinity;   /* One of the SQLITE_AFF_... values */
  u8 szEst;        /* Estimated size of this column.  INT==1 */
  u8 colFlags;     /* Boolean properties.  See COLFLAG_ defines below */
};

/* Allowed values for Column.colFlags:
*/
#define COLFLAG_PRIMKEY  0x0001    /* Column is part of the primary key */
#define COLFLAG_HIDDEN   0x0002    /* A hidden column in a virtual table */

................................................................................
#define OE_Replace  5   /* Delete existing record, then do INSERT or UPDATE */

#define OE_Restrict 6   /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */
#define OE_SetNull  7   /* Set the foreign key value to NULL */
#define OE_SetDflt  8   /* Set the foreign key value to its default */
#define OE_Cascade  9   /* Cascade the changes */

#define OE_Default  10  /* Do whatever the default action is */


/*
** An instance of the following structure is passed as the first
** argument to sqlite3VdbeKeyCompare and is used to control the 
** comparison of the two index keys.
**
................................................................................
  Index *pNext;            /* The next index associated with the same table */
  Schema *pSchema;         /* Schema containing this index */
  u8 *aSortOrder;          /* for each column: True==DESC, False==ASC */
  char **azColl;           /* Array of collation sequence names for index */
  Expr *pPartIdxWhere;     /* WHERE clause for partial indices */
  int tnum;                /* DB Page containing root of this index */
  u16 nColumn;             /* Number of columns in table used by this index */
  u8 iScanRatio;           /* Scan rate relative to the main table */
  unsigned onError:4;      /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  unsigned autoIndex:2;    /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */
  unsigned bUnordered:1;   /* Use this index for == or IN queries only */
  unsigned uniqNotNull:1;  /* True if UNIQUE and NOT NULL for all columns */
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  int nSample;             /* Number of elements in aSample[] */
  int nSampleCol;          /* Size of IndexSample.anEq[] and so on */
  tRowcnt *aAvgEq;         /* Average nEq values for keys not in aSample */
................................................................................
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*);
int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*);
char sqlite3AffinityType(const char*, u8*);
void sqlite3Analyze(Parse*, Token*, Token*);
int sqlite3InvokeBusyHandler(BusyHandler*);
int sqlite3FindDb(sqlite3*, Token*);
int sqlite3FindDbName(sqlite3 *, const char *);
int sqlite3AnalysisLoad(sqlite3*,int iDB);
void sqlite3DeleteIndexSamples(sqlite3*,Index*);
void sqlite3DefaultRowEst(Index*);

Changes to src/where.c.

4665
4666
4667
4668
4669
4670
4671
4672
4673
4674
4675
4676
4677
4678
4679
4680
4681
4682
4683
4684
4685
4686
4687
4688

4689
4690
4691
4692
4693
4694
4695
4696

4697
4698
4699
4700
4701
4702
4703
4704
4705
4706
4707
4708
4709
4710
4711
    if( pProbe->tnum<=0 ){
      /* Integer primary key index */
      pNew->wsFlags = WHERE_IPK;

      /* Full table scan */
      pNew->iSortIdx = b ? iSortIdx : 0;
      /* TUNING: Cost of full table scan is 3*(N + log2(N)).
      **  +  The extra 3 factor is to encourage the use of indexed lookups
      **     over full scans.  A smaller constant 2 is used for covering
      **     index scans so that a covering index scan will be favored over
      **     a table scan. */
      pNew->rRun = whereCostAdd(rSize,rLogSize) + 16;
      whereLoopOutputAdjust(pWC, pNew, pSrc->iCursor);
      rc = whereLoopInsert(pBuilder, pNew);
      pNew->nOut = rSize;
      if( rc ) break;
    }else{
      Bitmask m = pSrc->colUsed & ~columnsInIndex(pProbe);
      pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;

      /* Full scan via index */
      if( b
       || ( m==0
         && pProbe->bUnordered==0

         && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
         && sqlite3GlobalConfig.bUseCis
         && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
          )
      ){
        pNew->iSortIdx = b ? iSortIdx : 0;
        if( m==0 ){
          /* TUNING: Cost of a covering index scan is 2*(N + log2(N)).

          **  +  The extra 2 factor is to encourage the use of indexed lookups
          **     over index scans.  A table scan uses a factor of 3 so that
          **     index scans are favored over table scans.
          **  +  If this covering index might also help satisfy the ORDER BY
          **     clause, then the cost is fudged down slightly so that this
          **     index is favored above other indices that have no hope of
          **     helping with the ORDER BY. */
          pNew->rRun = 10 + whereCostAdd(rSize,rLogSize) - b;
        }else{
          assert( b!=0 ); 
          /* TUNING: Cost of scanning a non-covering index is (N+1)*log2(N)
          ** which we will simplify to just N*log2(N) */
          pNew->rRun = rSize + rLogSize;
        }
        whereLoopOutputAdjust(pWC, pNew, pSrc->iCursor);







|
|
<
<













>







|
>
|
<
|
|
<
<
<
|







4665
4666
4667
4668
4669
4670
4671
4672
4673


4674
4675
4676
4677
4678
4679
4680
4681
4682
4683
4684
4685
4686
4687
4688
4689
4690
4691
4692
4693
4694
4695
4696
4697

4698
4699



4700
4701
4702
4703
4704
4705
4706
4707
    if( pProbe->tnum<=0 ){
      /* Integer primary key index */
      pNew->wsFlags = WHERE_IPK;

      /* Full table scan */
      pNew->iSortIdx = b ? iSortIdx : 0;
      /* TUNING: Cost of full table scan is 3*(N + log2(N)).
      **  +  The extra 4 factor is to encourage the use of indexed lookups
      **     over full scans. */


      pNew->rRun = whereCostAdd(rSize,rLogSize) + 16;
      whereLoopOutputAdjust(pWC, pNew, pSrc->iCursor);
      rc = whereLoopInsert(pBuilder, pNew);
      pNew->nOut = rSize;
      if( rc ) break;
    }else{
      Bitmask m = pSrc->colUsed & ~columnsInIndex(pProbe);
      pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;

      /* Full scan via index */
      if( b
       || ( m==0
         && pProbe->bUnordered==0
         && pProbe->iScanRatio<128
         && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
         && sqlite3GlobalConfig.bUseCis
         && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
          )
      ){
        pNew->iSortIdx = b ? iSortIdx : 0;
        if( m==0 ){
          /* TUNING: Cost of a covering index scan is K*(N + log2(N)).
          **  +  The extra factor K of between 1.0 and 3.0 is added to
          **     encourage the use of indexed lookups.  The value of K

          **     depends on the iScanRatio value for the index.
          */



          pNew->rRun = whereCostAdd(rSize,rLogSize) + pProbe->iScanRatio/9 + 1;
        }else{
          assert( b!=0 ); 
          /* TUNING: Cost of scanning a non-covering index is (N+1)*log2(N)
          ** which we will simplify to just N*log2(N) */
          pNew->rRun = rSize + rLogSize;
        }
        whereLoopOutputAdjust(pWC, pNew, pSrc->iCursor);

Changes to test/analyze6.test.

26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

proc eqp {sql {db db}} {
  uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
}

do_test analyze6-1.0 {
  db eval {
    CREATE TABLE cat(x INT);
    CREATE UNIQUE INDEX catx ON cat(x);
    /* Give cat 16 unique integers */
    INSERT INTO cat VALUES(1);
    INSERT INTO cat VALUES(2);
    INSERT INTO cat SELECT x+2 FROM cat;
    INSERT INTO cat SELECT x+4 FROM cat;
    INSERT INTO cat SELECT x+8 FROM cat;

    CREATE TABLE ev(y INT);
    CREATE INDEX evy ON ev(y);
    /* ev will hold 32 copies of 16 integers found in cat */
    INSERT INTO ev SELECT x FROM cat;
    INSERT INTO ev SELECT x FROM cat;
    INSERT INTO ev SELECT y FROM ev;







|


|
|
|
|
|







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

proc eqp {sql {db db}} {
  uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
}

do_test analyze6-1.0 {
  db eval {
    CREATE TABLE cat(x INT, yz TEXT);
    CREATE UNIQUE INDEX catx ON cat(x);
    /* Give cat 16 unique integers */
    INSERT INTO cat(x) VALUES(1);
    INSERT INTO cat(x) VALUES(2);
    INSERT INTO cat(x) SELECT x+2 FROM cat;
    INSERT INTO cat(x) SELECT x+4 FROM cat;
    INSERT INTO cat(x) SELECT x+8 FROM cat;

    CREATE TABLE ev(y INT);
    CREATE INDEX evy ON ev(y);
    /* ev will hold 32 copies of 16 integers found in cat */
    INSERT INTO ev SELECT x FROM cat;
    INSERT INTO ev SELECT x FROM cat;
    INSERT INTO ev SELECT y FROM ev;

Changes to test/eqp.test.

29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
...
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
...
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
...
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
...
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
...
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
...
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
# ...
# eqp-7.*:        "SELECT count(*) FROM tbl" statements (VDBE code OP_Count).
#

proc det {args} { uplevel do_eqp_test $args }

do_execsql_test 1.1 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a);
  CREATE INDEX i2 ON t1(b);
  CREATE TABLE t2(a, b);
  CREATE TABLE t3(a, b);
}

do_eqp_test 1.2 {
  SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
} {
  0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 
  0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)} 
................................................................................
}

#-------------------------------------------------------------------------
# Test cases eqp-2.* - tests for single select statements.
#
drop_all_tables
do_execsql_test 2.1 {
  CREATE TABLE t1(x, y);

  CREATE TABLE t2(x, y);
  CREATE INDEX t2i1 ON t2(x);
}

det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" {
  0 0 0 {SCAN TABLE t1}
  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
................................................................................
#
drop_all_tables

# EVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b
# FROM t1 WHERE a=1;
# 0|0|0|SCAN TABLE t1
#
do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) }
det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {
  0 0 0 {SCAN TABLE t1}
}

# EVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SEARCH TABLE t1 USING INDEX i1
................................................................................
}

# EVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN
# SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
# 0|1|1|SCAN TABLE t2
#
do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)}
det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
  0 1 1 {SCAN TABLE t2}
}

# EVIDENCE-OF: R-33626-61085 sqlite> EXPLAIN QUERY PLAN
# SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
# 0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
# 0|1|0|SCAN TABLE t2
#
det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {
  0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
  0 1 0 {SCAN TABLE t2}
}

# EVIDENCE-OF: R-04002-25654 sqlite> CREATE INDEX i3 ON t1(b);
# sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)
#
do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}
det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
  0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)}
}

# EVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN
# SELECT c, d FROM t2 ORDER BY c;
# 0|0|0|SCAN TABLE t2
................................................................................
}

# EVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN
# SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;
# 0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?)
# 0|1|1|SCAN TABLE t1
#
det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" {
  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)}
  0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2}
}

# EVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN
# SELECT a FROM t1 UNION SELECT c FROM t2;
# 1|0|0|SCAN TABLE t1
# 2|0|0|SCAN TABLE t2
# 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
#
det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" {
  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
  2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4}
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
}

# EVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN
# SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
# 2|0|0|SCAN TABLE t2 2|0|0|USE TEMP B-TREE FOR ORDER BY
# 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
#
det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {
  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
  2 0 0 {SCAN TABLE t2}
  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
}


#-------------------------------------------------------------------------
................................................................................
    set data [read $fd]
    close $fd
    set data
  }] [list $res]
}

do_peqp_test 6.1 {
  SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1
} [string trimleft {
1 0 0 SCAN TABLE t1 USING COVERING INDEX i2
2 0 0 SCAN TABLE t2
2 0 0 USE TEMP B-TREE FOR ORDER BY
0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
}]

................................................................................
#-------------------------------------------------------------------------
# The following tests - eqp-7.* - test that queries that use the OP_Count
# optimization return something sensible with EQP.
#
drop_all_tables

do_execsql_test 7.0 {
  CREATE TABLE t1(a, b);
  CREATE TABLE t2(a, b);
  CREATE INDEX i1 ON t2(a);
}

det 7.1 "SELECT count(*) FROM t1" {
  0 0 0 {SCAN TABLE t1}
}

det 7.2 "SELECT count(*) FROM t2" {
  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1}
}

do_execsql_test 7.3 {
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(3, 4);

  INSERT INTO t2 VALUES(1, 2);
  INSERT INTO t2 VALUES(3, 4);
  INSERT INTO t2 VALUES(5, 6);
 
  ANALYZE;
}

db close
sqlite3 db test.db








|


|
|







 







|

|







 







|







 







|
|









|










|







 







|










|












|







 







|







 







|
|












|
|

|
|
|







29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
...
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
...
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
...
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
...
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
...
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
...
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
# ...
# eqp-7.*:        "SELECT count(*) FROM tbl" statements (VDBE code OP_Count).
#

proc det {args} { uplevel do_eqp_test $args }

do_execsql_test 1.1 {
  CREATE TABLE t1(a INT, b INT, ex TEXT);
  CREATE INDEX i1 ON t1(a);
  CREATE INDEX i2 ON t1(b);
  CREATE TABLE t2(a INT, b INT, ex TEXT);
  CREATE TABLE t3(a INT, b INT, ex TEXT);
}

do_eqp_test 1.2 {
  SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
} {
  0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 
  0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)} 
................................................................................
}

#-------------------------------------------------------------------------
# Test cases eqp-2.* - tests for single select statements.
#
drop_all_tables
do_execsql_test 2.1 {
  CREATE TABLE t1(x INT, y INT, ex TEXT);

  CREATE TABLE t2(x INT, y INT, ex TEXT);
  CREATE INDEX t2i1 ON t2(x);
}

det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" {
  0 0 0 {SCAN TABLE t1}
  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
................................................................................
#
drop_all_tables

# EVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b
# FROM t1 WHERE a=1;
# 0|0|0|SCAN TABLE t1
#
do_execsql_test 5.1.0 { CREATE TABLE t1(a INT, b INT, ex TEXT) }
det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {
  0 0 0 {SCAN TABLE t1}
}

# EVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SEARCH TABLE t1 USING INDEX i1
................................................................................
}

# EVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN
# SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
# 0|1|1|SCAN TABLE t2
#
do_execsql_test 5.4.0 {CREATE TABLE t2(c INT, d INT, ex TEXT)}
det 5.4.1 "SELECT t1.a, t2.c FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
  0 1 1 {SCAN TABLE t2}
}

# EVIDENCE-OF: R-33626-61085 sqlite> EXPLAIN QUERY PLAN
# SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
# 0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
# 0|1|0|SCAN TABLE t2
#
det 5.5 "SELECT t1.a, t2.c FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {
  0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
  0 1 0 {SCAN TABLE t2}
}

# EVIDENCE-OF: R-04002-25654 sqlite> CREATE INDEX i3 ON t1(b);
# sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)
#
do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}
det 5.6.1 "SELECT a, b FROM t1 WHERE a=1 OR b=2" {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
  0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)}
}

# EVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN
# SELECT c, d FROM t2 ORDER BY c;
# 0|0|0|SCAN TABLE t2
................................................................................
}

# EVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN
# SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;
# 0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?)
# 0|1|1|SCAN TABLE t1
#
det 5.11 "SELECT a, b FROM (SELECT * FROM t2 WHERE c=1), t1" {
  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)}
  0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2}
}

# EVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN
# SELECT a FROM t1 UNION SELECT c FROM t2;
# 1|0|0|SCAN TABLE t1
# 2|0|0|SCAN TABLE t2
# 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
#
det 5.12 "SELECT a,b FROM t1 UNION SELECT c, 99 FROM t2" {
  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
  2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4}
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
}

# EVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN
# SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
# 2|0|0|SCAN TABLE t2 2|0|0|USE TEMP B-TREE FOR ORDER BY
# 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
#
det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {
  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}
  2 0 0 {SCAN TABLE t2}
  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
}


#-------------------------------------------------------------------------
................................................................................
    set data [read $fd]
    close $fd
    set data
  }] [list $res]
}

do_peqp_test 6.1 {
  SELECT a, b FROM t1 EXCEPT SELECT d, 99 FROM t2 ORDER BY 1
} [string trimleft {
1 0 0 SCAN TABLE t1 USING COVERING INDEX i2
2 0 0 SCAN TABLE t2
2 0 0 USE TEMP B-TREE FOR ORDER BY
0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
}]

................................................................................
#-------------------------------------------------------------------------
# The following tests - eqp-7.* - test that queries that use the OP_Count
# optimization return something sensible with EQP.
#
drop_all_tables

do_execsql_test 7.0 {
  CREATE TABLE t1(a INT, b INT, ex CHAR(100));
  CREATE TABLE t2(a INT, b INT, ex CHAR(100));
  CREATE INDEX i1 ON t2(a);
}

det 7.1 "SELECT count(*) FROM t1" {
  0 0 0 {SCAN TABLE t1}
}

det 7.2 "SELECT count(*) FROM t2" {
  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1}
}

do_execsql_test 7.3 {
  INSERT INTO t1(a,b) VALUES(1, 2);
  INSERT INTO t1(a,b) VALUES(3, 4);

  INSERT INTO t2(a,b) VALUES(1, 2);
  INSERT INTO t2(a,b) VALUES(3, 4);
  INSERT INTO t2(a,b) VALUES(5, 6);
 
  ANALYZE;
}

db close
sqlite3 db test.db

Changes to test/pragma.test.

570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
...
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
....
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
      pragma foreign_key_list(t5);
    }
  } {}
  do_test pragma-6.4 {
    execsql {
      pragma index_list(t3);
    }
  } {0 sqlite_autoindex_t3_1 1}
}
ifcapable {!foreignkey} {
  execsql {CREATE TABLE t3(a,b UNIQUE)}
}
do_test pragma-6.5.1 {
  execsql {
    CREATE INDEX t3i1 ON t3(a,b);
................................................................................
do_test pragma-7.1.1 {
  # Make sure a pragma knows to read the schema if it needs to
  db close
  sqlite3 db test.db
  execsql {
    pragma index_list(t3);
  }
} {0 t3i1 0 1 sqlite_autoindex_t3_1 1}
do_test pragma-7.1.2 {
  execsql {
    pragma index_list(t3_bogus);
  }
} {}
} ;# ifcapable schema_pragmas
ifcapable {utf16} {
................................................................................
  db2 eval {PRAGMA index_info(i2)}
} {0 2 c 1 3 d 2 1 b}
do_test 23.3 {
  db eval {
    CREATE INDEX i3 ON t1(d,b,c);
  }
  db2 eval {PRAGMA index_list(t1)}
} {0 i3 0 1 i2 0 2 i1 0}
do_test 23.4 {
  db eval {
    ALTER TABLE t1 ADD COLUMN e;
  }
  db2 eval {
    PRAGMA table_info(t1);
  }







|







 







|







 







|







570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
...
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
....
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
      pragma foreign_key_list(t5);
    }
  } {}
  do_test pragma-6.4 {
    execsql {
      pragma index_list(t3);
    }
  } {/0 sqlite_autoindex_t3_1 1 \d+/}
}
ifcapable {!foreignkey} {
  execsql {CREATE TABLE t3(a,b UNIQUE)}
}
do_test pragma-6.5.1 {
  execsql {
    CREATE INDEX t3i1 ON t3(a,b);
................................................................................
do_test pragma-7.1.1 {
  # Make sure a pragma knows to read the schema if it needs to
  db close
  sqlite3 db test.db
  execsql {
    pragma index_list(t3);
  }
} {/0 t3i1 0 \d+ 1 sqlite_autoindex_t3_1 1 \d+/}
do_test pragma-7.1.2 {
  execsql {
    pragma index_list(t3_bogus);
  }
} {}
} ;# ifcapable schema_pragmas
ifcapable {utf16} {
................................................................................
  db2 eval {PRAGMA index_info(i2)}
} {0 2 c 1 3 d 2 1 b}
do_test 23.3 {
  db eval {
    CREATE INDEX i3 ON t1(d,b,c);
  }
  db2 eval {PRAGMA index_list(t1)}
} {/0 i3 0 \d+ 1 i2 0 \d+ 2 i1 0 \d+/}
do_test 23.4 {
  db eval {
    ALTER TABLE t1 ADD COLUMN e;
  }
  db2 eval {
    PRAGMA table_info(t1);
  }

Changes to test/subquery.test.

241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
} {10.0}
do_test subquery-2.5.3.2 {
  # Verify that the t4i index was not used in the previous query
  execsql {
    EXPLAIN QUERY PLAN
    SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
  }
} {/SCAN TABLE t4 /}
do_test subquery-2.5.4 {
  execsql {
    DROP TABLE t3;
    DROP TABLE t4;
  }
} {}








|







241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
} {10.0}
do_test subquery-2.5.3.2 {
  # Verify that the t4i index was not used in the previous query
  execsql {
    EXPLAIN QUERY PLAN
    SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
  }
} {~/t4i/}
do_test subquery-2.5.4 {
  execsql {
    DROP TABLE t3;
    DROP TABLE t4;
  }
} {}

Changes to test/tkt-78e04e52ea.test.

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
#

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

do_test tkt-78e04-1.0 {
  execsql {
    CREATE TABLE ""("" UNIQUE);
    CREATE TABLE t2(x);
    INSERT INTO "" VALUES(1);
    INSERT INTO t2 VALUES(2);
    SELECT * FROM "", t2;
  }
} {1 2}
do_test tkt-78e04-1.1 {
  catchsql {
    INSERT INTO "" VALUES(1);
  }
} {1 {column  is not unique}}
do_test tkt-78e04-1.2 {
  execsql {
    PRAGMA table_info("");
  }
} {0 {} {} 0 {} 0}
do_test tkt-78e04-1.3 {
  execsql {
    CREATE INDEX i1 ON ""("" COLLATE nocase);
  }
} {}
do_test tkt-78e04-1.4 {
  execsql {
    EXPLAIN QUERY PLAN SELECT * FROM "" WHERE "" LIKE 'abc%';
  }
} {0 0 0 {SCAN TABLE  USING COVERING INDEX i1}}
do_test tkt-78e04-1.5 {
  execsql {
    DROP TABLE "";
    SELECT name FROM sqlite_master;
  }







|

|



|


|






|







|







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
#

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

do_test tkt-78e04-1.0 {
  execsql {
    CREATE TABLE ""("" UNIQUE, x CHAR(100));
    CREATE TABLE t2(x);
    INSERT INTO ""("") VALUES(1);
    INSERT INTO t2 VALUES(2);
    SELECT * FROM "", t2;
  }
} {1 {} 2}
do_test tkt-78e04-1.1 {
  catchsql {
    INSERT INTO ""("") VALUES(1);
  }
} {1 {column  is not unique}}
do_test tkt-78e04-1.2 {
  execsql {
    PRAGMA table_info("");
  }
} {0 {} {} 0 {} 0 1 x CHAR(100) 0 {} 0}
do_test tkt-78e04-1.3 {
  execsql {
    CREATE INDEX i1 ON ""("" COLLATE nocase);
  }
} {}
do_test tkt-78e04-1.4 {
  execsql {
    EXPLAIN QUERY PLAN SELECT "" FROM "" WHERE "" LIKE 'abc%';
  }
} {0 0 0 {SCAN TABLE  USING COVERING INDEX i1}}
do_test tkt-78e04-1.5 {
  execsql {
    DROP TABLE "";
    SELECT name FROM sqlite_master;
  }

Changes to test/where.test.

1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
#
# When optimizing out ORDER BY clauses, make sure that trailing terms
# of the ORDER BY clause do not reference other tables in a join.
#
if {[permutation] != "no_optimization"} {
do_test where-14.1 {
  execsql {
    CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE);
    INSERT INTO t8 VALUES(1,'one');
    INSERT INTO t8 VALUES(4,'four');
  }
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
  } 
} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.2 {
  cksort {







|
|
|







1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
#
# When optimizing out ORDER BY clauses, make sure that trailing terms
# of the ORDER BY clause do not reference other tables in a join.
#
if {[permutation] != "no_optimization"} {
do_test where-14.1 {
  execsql {
    CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE, c CHAR(100));
    INSERT INTO t8(a,b) VALUES(1,'one');
    INSERT INTO t8(a,b) VALUES(4,'four');
  }
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
  } 
} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.2 {
  cksort {

Changes to test/where2.test.

310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
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
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430

431
432
433
434
435

436
437
438
439
440

441
442
443
444
445
446
447
448
...
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
if {[permutation] != "no_optimization"} {

# Ticket #2249.  Make sure the OR optimization is not attempted if
# comparisons between columns of different affinities are needed.
#
do_test where2-6.7 {
  execsql {
    CREATE TABLE t2249a(a TEXT UNIQUE);
    CREATE TABLE t2249b(b INTEGER);
    INSERT INTO t2249a VALUES('0123');
    INSERT INTO t2249b VALUES(123);
  }
  queryplan {
    -- Because a is type TEXT and b is type INTEGER, both a and b
    -- will attempt to convert to NUMERIC before the comparison.
    -- They will thus compare equal.
    --
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b;
  }
} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.9 {
  queryplan {
    -- The + operator removes affinity from the rhs.  No conversions
    -- occur and the comparison is false.  The result is an empty set.
    --
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b;
  }
} {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.9.2 {
  # The same thing but with the expression flipped around.
  queryplan {
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a
  }
} {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.10 {
  queryplan {
    -- Use + on both sides of the comparison to disable indices
    -- completely.  Make sure we get the same result.
    --
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE +a=+b;
  }
} {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.11 {
  # This will not attempt the OR optimization because of the a=b
  # comparison.
  queryplan {
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello';
  }
} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.11.2 {
  # Permutations of the expression terms.
  queryplan {
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello';
  }
} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.11.3 {
  # Permutations of the expression terms.
  queryplan {
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a;
  }
} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.11.4 {
  # Permutations of the expression terms.
  queryplan {
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a;
  }
} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
ifcapable explain&&subquery {
  # These tests are not run if subquery support is not included in the
  # build. This is because these tests test the "a = 1 OR a = 2" to
  # "a IN (1, 2)" optimisation transformation, which is not enabled if
  # subqueries and the IN operator is not available.
  #
  do_test where2-6.12 {
    # In this case, the +b disables the affinity conflict and allows
    # the OR optimization to be used again.  The result is now an empty
    # set, the same as in where2-6.9.
    queryplan {
      SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello';
    }
  } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
  do_test where2-6.12.2 {
    # In this case, the +b disables the affinity conflict and allows
    # the OR optimization to be used again.  The result is now an empty
    # set, the same as in where2-6.9.
    queryplan {
      SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a;
    }
  } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
  do_test where2-6.12.3 {
    # In this case, the +b disables the affinity conflict and allows
    # the OR optimization to be used again.  The result is now an empty
    # set, the same as in where2-6.9.
    queryplan {
      SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello';
    }
  } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
  do_test where2-6.13 {
    # The addition of +a on the second term disabled the OR optimization.
    # But we should still get the same empty-set result as in where2-6.9.
    queryplan {
      SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello';
    }
  } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
}

# Variations on the order of terms in a WHERE clause in order
# to make sure the OR optimizer can recognize them all.
do_test where2-6.20 {
  queryplan {
    SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a
  }
} {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1}
ifcapable explain&&subquery {
  # These tests are not run if subquery support is not included in the
  # build. This is because these tests test the "a = 1 OR a = 2" to
  # "a IN (1, 2)" optimisation transformation, which is not enabled if
  # subqueries and the IN operator is not available.
  #
  do_test where2-6.21 {
    queryplan {

      SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a OR y.a='hello'
    }
  } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1}
  do_test where2-6.22 {
    queryplan {

      SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a=x.a OR y.a='hello'
    }
  } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1}
  do_test where2-6.23 {
    queryplan {

      SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a='hello' OR x.a=y.a
    }
  } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1}
}

# Unique queries (queries that are guaranteed to return only a single
# row of result) do not call the sorter.  But all tables must give
# a unique result.  If any one table in the join does not give a unique
................................................................................
  }
} {4 8 10}

# Verify that the OR clause is used in an outer loop even when
# the OR clause scores slightly better on an inner loop.
if {[permutation] != "no_optimization"} {
do_execsql_test where2-12.1 {
  CREATE TABLE t12(x INTEGER PRIMARY KEY, y);
  CREATE INDEX t12y ON t12(y);
  EXPLAIN QUERY PLAN
    SELECT a.x, b.x
      FROM t12 AS a JOIN t12 AS b ON a.y=b.x
     WHERE (b.x=$abc OR b.y=$abc);
} {/.*SEARCH TABLE t12 AS b .*SEARCH TABLE t12 AS b .*/}
}


finish_test







|

|







|







|





|







|






|





|





|





|













|







|







|






|








|










>
|




>
|




>
|







 







|










310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
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
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
...
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
if {[permutation] != "no_optimization"} {

# Ticket #2249.  Make sure the OR optimization is not attempted if
# comparisons between columns of different affinities are needed.
#
do_test where2-6.7 {
  execsql {
    CREATE TABLE t2249a(a TEXT UNIQUE, x CHAR(100));
    CREATE TABLE t2249b(b INTEGER);
    INSERT INTO t2249a(a) VALUES('0123');
    INSERT INTO t2249b VALUES(123);
  }
  queryplan {
    -- Because a is type TEXT and b is type INTEGER, both a and b
    -- will attempt to convert to NUMERIC before the comparison.
    -- They will thus compare equal.
    --
    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b;
  }
} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.9 {
  queryplan {
    -- The + operator removes affinity from the rhs.  No conversions
    -- occur and the comparison is false.  The result is an empty set.
    --
    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b;
  }
} {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.9.2 {
  # The same thing but with the expression flipped around.
  queryplan {
    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a
  }
} {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.10 {
  queryplan {
    -- Use + on both sides of the comparison to disable indices
    -- completely.  Make sure we get the same result.
    --
    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +a=+b;
  }
} {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.11 {
  # This will not attempt the OR optimization because of the a=b
  # comparison.
  queryplan {
    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello';
  }
} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.11.2 {
  # Permutations of the expression terms.
  queryplan {
    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello';
  }
} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.11.3 {
  # Permutations of the expression terms.
  queryplan {
    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a;
  }
} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
do_test where2-6.11.4 {
  # Permutations of the expression terms.
  queryplan {
    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a;
  }
} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
ifcapable explain&&subquery {
  # These tests are not run if subquery support is not included in the
  # build. This is because these tests test the "a = 1 OR a = 2" to
  # "a IN (1, 2)" optimisation transformation, which is not enabled if
  # subqueries and the IN operator is not available.
  #
  do_test where2-6.12 {
    # In this case, the +b disables the affinity conflict and allows
    # the OR optimization to be used again.  The result is now an empty
    # set, the same as in where2-6.9.
    queryplan {
      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello';
    }
  } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
  do_test where2-6.12.2 {
    # In this case, the +b disables the affinity conflict and allows
    # the OR optimization to be used again.  The result is now an empty
    # set, the same as in where2-6.9.
    queryplan {
      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a;
    }
  } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
  do_test where2-6.12.3 {
    # In this case, the +b disables the affinity conflict and allows
    # the OR optimization to be used again.  The result is now an empty
    # set, the same as in where2-6.9.
    queryplan {
      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello';
    }
  } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
  do_test where2-6.13 {
    # The addition of +a on the second term disabled the OR optimization.
    # But we should still get the same empty-set result as in where2-6.9.
    queryplan {
      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello';
    }
  } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
}

# Variations on the order of terms in a WHERE clause in order
# to make sure the OR optimizer can recognize them all.
do_test where2-6.20 {
  queryplan {
    SELECT x.a, y.a FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a
  }
} {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1}
ifcapable explain&&subquery {
  # These tests are not run if subquery support is not included in the
  # build. This is because these tests test the "a = 1 OR a = 2" to
  # "a IN (1, 2)" optimisation transformation, which is not enabled if
  # subqueries and the IN operator is not available.
  #
  do_test where2-6.21 {
    queryplan {
      SELECT x.a,y.a FROM t2249a x CROSS JOIN t2249a y
       WHERE x.a=y.a OR y.a='hello'
    }
  } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1}
  do_test where2-6.22 {
    queryplan {
      SELECT x.a,y.a FROM t2249a x CROSS JOIN t2249a y
       WHERE y.a=x.a OR y.a='hello'
    }
  } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1}
  do_test where2-6.23 {
    queryplan {
      SELECT x.a,y.a FROM t2249a x CROSS JOIN t2249a y
       WHERE y.a='hello' OR x.a=y.a
    }
  } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1}
}

# Unique queries (queries that are guaranteed to return only a single
# row of result) do not call the sorter.  But all tables must give
# a unique result.  If any one table in the join does not give a unique
................................................................................
  }
} {4 8 10}

# Verify that the OR clause is used in an outer loop even when
# the OR clause scores slightly better on an inner loop.
if {[permutation] != "no_optimization"} {
do_execsql_test where2-12.1 {
  CREATE TABLE t12(x INTEGER PRIMARY KEY, y INT, z CHAR(100));
  CREATE INDEX t12y ON t12(y);
  EXPLAIN QUERY PLAN
    SELECT a.x, b.x
      FROM t12 AS a JOIN t12 AS b ON a.y=b.x
     WHERE (b.x=$abc OR b.y=$abc);
} {/.*SEARCH TABLE t12 AS b .*SEARCH TABLE t12 AS b .*/}
}


finish_test