/ Check-in [14ef7543]
Login

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

Overview
Comment:Merge latest trunk changes with this branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions-subtype-fix
Files: files | file ages | folders
SHA3-256: 14ef7543465dd14d8fa141fcceb1950b5c2d265a3e862323969d747b39c0cd8c
User & Date: dan 2019-09-13 16:19:53
Context
2019-09-13
17:36
Change the meaning of the SQLITE_SUBTYPE flag so that it indicates that the function may use sqlite3_value_subtype() on its arguments. Closed-Leaf check-in: 7f424ec9 user: dan tags: window-functions-subtype-fix
17:05
Change the meaning of the SQLITE_SUBTYPE flag so that it indicates that the user-defined function cares about the subtypes of its arguments. Closed-Leaf check-in: af1bc20f user: dan tags: window-functions-subtype-fix2
16:19
Merge latest trunk changes with this branch. check-in: 14ef7543 user: dan tags: window-functions-subtype-fix
13:23
Ensure that the idxStr for FTS5 is always zero-terminated. Fix for check-in [9d418a7a491761ee] check-in: 090cd07d user: drh tags: trunk
2019-09-07
18:20
Add the SQLITE_SUBTYPE flag, which can be passed to sqlite3_create_function() and similar to indicate to the core that a user function is likely to use sqlite3_result_subtype(). check-in: 6aa438ce user: dan tags: window-functions-subtype-fix
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts5/fts5Int.h.

   691    691   */
   692    692   int sqlite3Fts5ExprFirst(Fts5Expr*, Fts5Index *pIdx, i64 iMin, int bDesc);
   693    693   int sqlite3Fts5ExprNext(Fts5Expr*, i64 iMax);
   694    694   int sqlite3Fts5ExprEof(Fts5Expr*);
   695    695   i64 sqlite3Fts5ExprRowid(Fts5Expr*);
   696    696   
   697    697   void sqlite3Fts5ExprFree(Fts5Expr*);
          698  +int sqlite3Fts5ExprAnd(Fts5Expr **pp1, Fts5Expr *p2);
   698    699   
   699    700   /* Called during startup to register a UDF with SQLite */
   700    701   int sqlite3Fts5ExprInit(Fts5Global*, sqlite3*);
   701    702   
   702    703   int sqlite3Fts5ExprPhraseCount(Fts5Expr*);
   703    704   int sqlite3Fts5ExprPhraseSize(Fts5Expr*, int iPhrase);
   704    705   int sqlite3Fts5ExprPoslist(Fts5Expr*, int, const u8 **);

Changes to ext/fts5/fts5_config.c.

   679    679     );
   680    680   
   681    681     assert( zSql || rc==SQLITE_NOMEM );
   682    682     if( zSql ){
   683    683       rc = sqlite3_declare_vtab(pConfig->db, zSql);
   684    684       sqlite3_free(zSql);
   685    685     }
   686         -  
          686  + 
   687    687     return rc;
   688    688   }
   689    689   
   690    690   /*
   691    691   ** Tokenize the text passed via the second and third arguments.
   692    692   **
   693    693   ** The callback is invoked once for each token in the input text. The

Changes to ext/fts5/fts5_expr.c.

   304    304   void sqlite3Fts5ExprFree(Fts5Expr *p){
   305    305     if( p ){
   306    306       sqlite3Fts5ParseNodeFree(p->pRoot);
   307    307       sqlite3_free(p->apExprPhrase);
   308    308       sqlite3_free(p);
   309    309     }
   310    310   }
          311  +
          312  +int sqlite3Fts5ExprAnd(Fts5Expr **pp1, Fts5Expr *p2){
          313  +  Fts5Parse sParse;
          314  +  memset(&sParse, 0, sizeof(sParse));
          315  +
          316  +  if( *pp1 ){
          317  +    Fts5Expr *p1 = *pp1;
          318  +    int nPhrase = p1->nPhrase + p2->nPhrase;
          319  +
          320  +    p1->pRoot = sqlite3Fts5ParseNode(&sParse, FTS5_AND, p1->pRoot, p2->pRoot,0);
          321  +    p2->pRoot = 0;
          322  +
          323  +    if( sParse.rc==SQLITE_OK ){
          324  +      Fts5ExprPhrase **ap = (Fts5ExprPhrase**)sqlite3_realloc(
          325  +          p1->apExprPhrase, nPhrase * sizeof(Fts5ExprPhrase*)
          326  +      );
          327  +      if( ap==0 ){
          328  +        sParse.rc = SQLITE_NOMEM;
          329  +      }else{
          330  +        int i;
          331  +        memmove(&ap[p2->nPhrase], ap, p1->nPhrase*sizeof(Fts5ExprPhrase*));
          332  +        for(i=0; i<p2->nPhrase; i++){
          333  +          ap[i] = p2->apExprPhrase[i];
          334  +        }
          335  +        p1->nPhrase = nPhrase;
          336  +        p1->apExprPhrase = ap;
          337  +      }
          338  +    }
          339  +    sqlite3_free(p2->apExprPhrase);
          340  +    sqlite3_free(p2);
          341  +  }else{
          342  +    *pp1 = p2;
          343  +  }
          344  +
          345  +  return sParse.rc;
          346  +}
   311    347   
   312    348   /*
   313    349   ** Argument pTerm must be a synonym iterator. Return the current rowid
   314    350   ** that it points to.
   315    351   */
   316    352   static i64 fts5ExprSynonymRowid(Fts5ExprTerm *pTerm, int bDesc, int *pbEof){
   317    353     i64 iRet = 0;

Changes to ext/fts5/fts5_main.c.

   461    461   #endif
   462    462   }
   463    463   
   464    464   /*
   465    465   ** Implementation of the xBestIndex method for FTS5 tables. Within the 
   466    466   ** WHERE constraint, it searches for the following:
   467    467   **
   468         -**   1. A MATCH constraint against the special column.
          468  +**   1. A MATCH constraint against the table column.
   469    469   **   2. A MATCH constraint against the "rank" column.
   470         -**   3. An == constraint against the rowid column.
   471         -**   4. A < or <= constraint against the rowid column.
   472         -**   5. A > or >= constraint against the rowid column.
          470  +**   3. A MATCH constraint against some other column.
          471  +**   4. An == constraint against the rowid column.
          472  +**   5. A < or <= constraint against the rowid column.
          473  +**   6. A > or >= constraint against the rowid column.
   473    474   **
   474         -** Within the ORDER BY, either:
          475  +** Within the ORDER BY, the following are supported:
   475    476   **
   476    477   **   5. ORDER BY rank [ASC|DESC]
   477    478   **   6. ORDER BY rowid [ASC|DESC]
          479  +**
          480  +** Information for the xFilter call is passed via both the idxNum and 
          481  +** idxStr variables. Specifically, idxNum is a bitmask of the following
          482  +** flags used to encode the ORDER BY clause:
          483  +**
          484  +**     FTS5_BI_ORDER_RANK
          485  +**     FTS5_BI_ORDER_ROWID
          486  +**     FTS5_BI_ORDER_DESC
          487  +**
          488  +** idxStr is used to encode data from the WHERE clause. For each argument
          489  +** passed to the xFilter method, the following is appended to idxStr:
          490  +**
          491  +**   Match against table column:            "m"
          492  +**   Match against rank column:             "r"
          493  +**   Match against other column:            "<column-number>"
          494  +**   Equality constraint against the rowid: "="
          495  +**   A < or <= against the rowid:           "<"
          496  +**   A > or >= against the rowid:           ">"
          497  +**
          498  +** This function ensures that there is at most one "r" or "=". And that if
          499  +** there exists an "=" then there is no "<" or ">".
   478    500   **
   479    501   ** Costs are assigned as follows:
   480    502   **
   481    503   **  a) If an unusable MATCH operator is present in the WHERE clause, the
   482    504   **     cost is unconditionally set to 1e50 (a really big number).
   483    505   **
   484    506   **  a) If a MATCH operator is present, the cost depends on the other
................................................................................
   499    521   ** Costs are not modified by the ORDER BY clause.
   500    522   */
   501    523   static int fts5BestIndexMethod(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){
   502    524     Fts5Table *pTab = (Fts5Table*)pVTab;
   503    525     Fts5Config *pConfig = pTab->pConfig;
   504    526     const int nCol = pConfig->nCol;
   505    527     int idxFlags = 0;               /* Parameter passed through to xFilter() */
   506         -  int bHasMatch;
   507         -  int iNext;
   508    528     int i;
   509    529   
   510         -  struct Constraint {
   511         -    int op;                       /* Mask against sqlite3_index_constraint.op */
   512         -    int fts5op;                   /* FTS5 mask for idxFlags */
   513         -    int iCol;                     /* 0==rowid, 1==tbl, 2==rank */
   514         -    int omit;                     /* True to omit this if found */
   515         -    int iConsIndex;               /* Index in pInfo->aConstraint[] */
   516         -  } aConstraint[] = {
   517         -    {SQLITE_INDEX_CONSTRAINT_MATCH|SQLITE_INDEX_CONSTRAINT_EQ, 
   518         -                                    FTS5_BI_MATCH,    1, 1, -1},
   519         -    {SQLITE_INDEX_CONSTRAINT_MATCH|SQLITE_INDEX_CONSTRAINT_EQ, 
   520         -                                    FTS5_BI_RANK,     2, 1, -1},
   521         -    {SQLITE_INDEX_CONSTRAINT_EQ,    FTS5_BI_ROWID_EQ, 0, 0, -1},
   522         -    {SQLITE_INDEX_CONSTRAINT_LT|SQLITE_INDEX_CONSTRAINT_LE, 
   523         -                                    FTS5_BI_ROWID_LE, 0, 0, -1},
   524         -    {SQLITE_INDEX_CONSTRAINT_GT|SQLITE_INDEX_CONSTRAINT_GE, 
   525         -                                    FTS5_BI_ROWID_GE, 0, 0, -1},
   526         -  };
          530  +  char *idxStr;
          531  +  int iIdxStr = 0;
          532  +  int iCons = 0;
   527    533   
   528         -  int aColMap[3];
   529         -  aColMap[0] = -1;
   530         -  aColMap[1] = nCol;
   531         -  aColMap[2] = nCol+1;
          534  +  int bSeenEq = 0;
          535  +  int bSeenGt = 0;
          536  +  int bSeenLt = 0;
          537  +  int bSeenMatch = 0;
          538  +  int bSeenRank = 0;
          539  +
   532    540   
   533    541     assert( SQLITE_INDEX_CONSTRAINT_EQ<SQLITE_INDEX_CONSTRAINT_MATCH );
   534    542     assert( SQLITE_INDEX_CONSTRAINT_GT<SQLITE_INDEX_CONSTRAINT_MATCH );
   535    543     assert( SQLITE_INDEX_CONSTRAINT_LE<SQLITE_INDEX_CONSTRAINT_MATCH );
   536    544     assert( SQLITE_INDEX_CONSTRAINT_GE<SQLITE_INDEX_CONSTRAINT_MATCH );
   537    545     assert( SQLITE_INDEX_CONSTRAINT_LE<SQLITE_INDEX_CONSTRAINT_MATCH );
   538    546   
................................................................................
   539    547     if( pConfig->bLock ){
   540    548       pTab->base.zErrMsg = sqlite3_mprintf(
   541    549           "recursively defined fts5 content table"
   542    550       );
   543    551       return SQLITE_ERROR;
   544    552     }
   545    553   
   546         -  /* Set idxFlags flags for all WHERE clause terms that will be used. */
          554  +  idxStr = (char*)sqlite3_malloc(pInfo->nConstraint * 6 + 1);
          555  +  if( idxStr==0 ) return SQLITE_NOMEM;
          556  +  pInfo->idxStr = idxStr;
          557  +  pInfo->needToFreeIdxStr = 1;
          558  +
   547    559     for(i=0; i<pInfo->nConstraint; i++){
   548    560       struct sqlite3_index_constraint *p = &pInfo->aConstraint[i];
   549    561       int iCol = p->iColumn;
   550         -
   551         -    if( (p->op==SQLITE_INDEX_CONSTRAINT_MATCH && iCol>=0 && iCol<=nCol)
   552         -     || (p->op==SQLITE_INDEX_CONSTRAINT_EQ && iCol==nCol)
          562  +    if( p->op==SQLITE_INDEX_CONSTRAINT_MATCH
          563  +     || (p->op==SQLITE_INDEX_CONSTRAINT_EQ && iCol>=nCol)
   553    564       ){
   554    565         /* A MATCH operator or equivalent */
   555         -      if( p->usable ){
   556         -        idxFlags = (idxFlags & 0xFFFF) | FTS5_BI_MATCH | (iCol << 16);
   557         -        aConstraint[0].iConsIndex = i;
   558         -      }else{
          566  +      if( p->usable==0 || iCol<0 ){
   559    567           /* As there exists an unusable MATCH constraint this is an 
   560    568           ** unusable plan. Set a prohibitively high cost. */
   561    569           pInfo->estimatedCost = 1e50;
          570  +        assert( iIdxStr < pInfo->nConstraint*6 + 1 );
          571  +        idxStr[iIdxStr] = 0;
   562    572           return SQLITE_OK;
   563         -      }
   564         -    }else if( p->op<=SQLITE_INDEX_CONSTRAINT_MATCH ){
   565         -      int j;
   566         -      for(j=1; j<ArraySize(aConstraint); j++){
   567         -        struct Constraint *pC = &aConstraint[j];
   568         -        if( iCol==aColMap[pC->iCol] && (p->op & pC->op) && p->usable ){
   569         -          pC->iConsIndex = i;
   570         -          idxFlags |= pC->fts5op;
   571         -        }
   572         -      }
   573         -    }
   574         -  }
          573  +      }else{
          574  +        if( iCol==nCol+1 ){
          575  +          if( bSeenRank ) continue;
          576  +          idxStr[iIdxStr++] = 'r';
          577  +          bSeenRank = 1;
          578  +        }else{
          579  +          bSeenMatch = 1;
          580  +          idxStr[iIdxStr++] = 'm';
          581  +          if( iCol<nCol ){
          582  +            sqlite3_snprintf(6, &idxStr[iIdxStr], "%d", iCol);
          583  +            idxStr += strlen(&idxStr[iIdxStr]);
          584  +            assert( idxStr[iIdxStr]=='\0' );
          585  +          }
          586  +        }
          587  +        pInfo->aConstraintUsage[i].argvIndex = ++iCons;
          588  +        pInfo->aConstraintUsage[i].omit = 1;
          589  +      }
          590  +    }
          591  +    else if( p->usable && bSeenEq==0 
          592  +      && p->op==SQLITE_INDEX_CONSTRAINT_EQ && iCol<0 
          593  +    ){
          594  +      idxStr[iIdxStr++] = '=';
          595  +      bSeenEq = 1;
          596  +      pInfo->aConstraintUsage[i].argvIndex = ++iCons;
          597  +    }
          598  +  }
          599  +
          600  +  if( bSeenEq==0 ){
          601  +    for(i=0; i<pInfo->nConstraint; i++){
          602  +      struct sqlite3_index_constraint *p = &pInfo->aConstraint[i];
          603  +      if( p->iColumn<0 && p->usable ){
          604  +        int op = p->op;
          605  +        if( op==SQLITE_INDEX_CONSTRAINT_LT || op==SQLITE_INDEX_CONSTRAINT_LE ){
          606  +          if( bSeenLt ) continue;
          607  +          idxStr[iIdxStr++] = '<';
          608  +          pInfo->aConstraintUsage[i].argvIndex = ++iCons;
          609  +          bSeenLt = 1;
          610  +        }else
          611  +        if( op==SQLITE_INDEX_CONSTRAINT_GT || op==SQLITE_INDEX_CONSTRAINT_GE ){
          612  +          if( bSeenGt ) continue;
          613  +          idxStr[iIdxStr++] = '>';
          614  +          pInfo->aConstraintUsage[i].argvIndex = ++iCons;
          615  +          bSeenGt = 1;
          616  +        }
          617  +      }
          618  +    }
          619  +  }
          620  +  idxStr[iIdxStr] = '\0';
   575    621   
   576    622     /* Set idxFlags flags for the ORDER BY clause */
   577    623     if( pInfo->nOrderBy==1 ){
   578    624       int iSort = pInfo->aOrderBy[0].iColumn;
   579         -    if( iSort==(pConfig->nCol+1) && BitFlagTest(idxFlags, FTS5_BI_MATCH) ){
          625  +    if( iSort==(pConfig->nCol+1) && bSeenMatch ){
   580    626         idxFlags |= FTS5_BI_ORDER_RANK;
   581    627       }else if( iSort==-1 ){
   582    628         idxFlags |= FTS5_BI_ORDER_ROWID;
   583    629       }
   584    630       if( BitFlagTest(idxFlags, FTS5_BI_ORDER_RANK|FTS5_BI_ORDER_ROWID) ){
   585    631         pInfo->orderByConsumed = 1;
   586    632         if( pInfo->aOrderBy[0].desc ){
   587    633           idxFlags |= FTS5_BI_ORDER_DESC;
   588    634         }
   589    635       }
   590    636     }
   591    637   
   592    638     /* Calculate the estimated cost based on the flags set in idxFlags. */
   593         -  bHasMatch = BitFlagTest(idxFlags, FTS5_BI_MATCH);
   594         -  if( BitFlagTest(idxFlags, FTS5_BI_ROWID_EQ) ){
   595         -    pInfo->estimatedCost = bHasMatch ? 100.0 : 10.0;
   596         -    if( bHasMatch==0 ) fts5SetUniqueFlag(pInfo);
   597         -  }else if( BitFlagAllTest(idxFlags, FTS5_BI_ROWID_LE|FTS5_BI_ROWID_GE) ){
   598         -    pInfo->estimatedCost = bHasMatch ? 500.0 : 250000.0;
   599         -  }else if( BitFlagTest(idxFlags, FTS5_BI_ROWID_LE|FTS5_BI_ROWID_GE) ){
   600         -    pInfo->estimatedCost = bHasMatch ? 750.0 : 750000.0;
          639  +  if( bSeenEq ){
          640  +    pInfo->estimatedCost = bSeenMatch ? 100.0 : 10.0;
          641  +    if( bSeenMatch==0 ) fts5SetUniqueFlag(pInfo);
          642  +  }else if( bSeenLt && bSeenGt ){
          643  +    pInfo->estimatedCost = bSeenMatch ? 500.0 : 250000.0;
          644  +  }else if( bSeenLt || bSeenGt ){
          645  +    pInfo->estimatedCost = bSeenMatch ? 750.0 : 750000.0;
   601    646     }else{
   602         -    pInfo->estimatedCost = bHasMatch ? 1000.0 : 1000000.0;
   603         -  }
   604         -
   605         -  /* Assign argvIndex values to each constraint in use. */
   606         -  iNext = 1;
   607         -  for(i=0; i<ArraySize(aConstraint); i++){
   608         -    struct Constraint *pC = &aConstraint[i];
   609         -    if( pC->iConsIndex>=0 ){
   610         -      pInfo->aConstraintUsage[pC->iConsIndex].argvIndex = iNext++;
   611         -      pInfo->aConstraintUsage[pC->iConsIndex].omit = (unsigned char)pC->omit;
   612         -    }
          647  +    pInfo->estimatedCost = bSeenMatch ? 1000.0 : 1000000.0;
   613    648     }
   614    649   
   615    650     pInfo->idxNum = idxFlags;
   616    651     return SQLITE_OK;
   617    652   }
   618    653   
   619    654   static int fts5NewTransaction(Fts5FullTable *pTab){
................................................................................
  1128   1163   **   1. Full-text search using a MATCH operator.
  1129   1164   **   2. A by-rowid lookup.
  1130   1165   **   3. A full-table scan.
  1131   1166   */
  1132   1167   static int fts5FilterMethod(
  1133   1168     sqlite3_vtab_cursor *pCursor,   /* The cursor used for this query */
  1134   1169     int idxNum,                     /* Strategy index */
  1135         -  const char *zUnused,            /* Unused */
         1170  +  const char *idxStr,             /* Unused */
  1136   1171     int nVal,                       /* Number of elements in apVal */
  1137   1172     sqlite3_value **apVal           /* Arguments for the indexing scheme */
  1138   1173   ){
  1139   1174     Fts5FullTable *pTab = (Fts5FullTable*)(pCursor->pVtab);
  1140   1175     Fts5Config *pConfig = pTab->p.pConfig;
  1141   1176     Fts5Cursor *pCsr = (Fts5Cursor*)pCursor;
  1142   1177     int rc = SQLITE_OK;             /* Error code */
  1143         -  int iVal = 0;                   /* Counter for apVal[] */
  1144   1178     int bDesc;                      /* True if ORDER BY [rank|rowid] DESC */
  1145   1179     int bOrderByRank;               /* True if ORDER BY rank */
  1146         -  sqlite3_value *pMatch = 0;      /* <tbl> MATCH ? expression (or NULL) */
  1147   1180     sqlite3_value *pRank = 0;       /* rank MATCH ? expression (or NULL) */
  1148   1181     sqlite3_value *pRowidEq = 0;    /* rowid = ? expression (or NULL) */
  1149   1182     sqlite3_value *pRowidLe = 0;    /* rowid <= ? expression (or NULL) */
  1150   1183     sqlite3_value *pRowidGe = 0;    /* rowid >= ? expression (or NULL) */
  1151   1184     int iCol;                       /* Column on LHS of MATCH operator */
  1152   1185     char **pzErrmsg = pConfig->pzErrmsg;
  1153         -
  1154         -  UNUSED_PARAM(zUnused);
  1155         -  UNUSED_PARAM(nVal);
         1186  +  int i;
         1187  +  int iIdxStr = 0;
         1188  +  Fts5Expr *pExpr = 0;
  1156   1189   
  1157   1190     if( pCsr->ePlan ){
  1158   1191       fts5FreeCursorComponents(pCsr);
  1159   1192       memset(&pCsr->ePlan, 0, sizeof(Fts5Cursor) - ((u8*)&pCsr->ePlan-(u8*)pCsr));
  1160   1193     }
  1161   1194   
  1162   1195     assert( pCsr->pStmt==0 );
  1163   1196     assert( pCsr->pExpr==0 );
  1164   1197     assert( pCsr->csrflags==0 );
  1165   1198     assert( pCsr->pRank==0 );
  1166   1199     assert( pCsr->zRank==0 );
  1167   1200     assert( pCsr->zRankArgs==0 );
         1201  +  assert( pTab->pSortCsr==0 || nVal==0 );
  1168   1202   
  1169   1203     assert( pzErrmsg==0 || pzErrmsg==&pTab->p.base.zErrMsg );
  1170   1204     pConfig->pzErrmsg = &pTab->p.base.zErrMsg;
  1171   1205   
  1172         -  /* Decode the arguments passed through to this function.
  1173         -  **
  1174         -  ** Note: The following set of if(...) statements must be in the same
  1175         -  ** order as the corresponding entries in the struct at the top of
  1176         -  ** fts5BestIndexMethod().  */
  1177         -  if( BitFlagTest(idxNum, FTS5_BI_MATCH) ) pMatch = apVal[iVal++];
  1178         -  if( BitFlagTest(idxNum, FTS5_BI_RANK) ) pRank = apVal[iVal++];
  1179         -  if( BitFlagTest(idxNum, FTS5_BI_ROWID_EQ) ) pRowidEq = apVal[iVal++];
  1180         -  if( BitFlagTest(idxNum, FTS5_BI_ROWID_LE) ) pRowidLe = apVal[iVal++];
  1181         -  if( BitFlagTest(idxNum, FTS5_BI_ROWID_GE) ) pRowidGe = apVal[iVal++];
  1182         -  iCol = (idxNum>>16);
  1183         -  assert( iCol>=0 && iCol<=pConfig->nCol );
  1184         -  assert( iVal==nVal );
         1206  +  /* Decode the arguments passed through to this function. */
         1207  +  for(i=0; i<nVal; i++){
         1208  +    switch( idxStr[iIdxStr++] ){
         1209  +      case 'r':
         1210  +        pRank = apVal[i];
         1211  +        break;
         1212  +      case 'm': {
         1213  +        const char *zText = (const char*)sqlite3_value_text(apVal[i]);
         1214  +        if( zText==0 ) zText = "";
         1215  +
         1216  +        if( idxStr[iIdxStr]>='0' && idxStr[iIdxStr]<='9' ){
         1217  +          iCol = 0;
         1218  +          do{
         1219  +            iCol = iCol*10 + (idxStr[iIdxStr]-'0');
         1220  +            iIdxStr++;
         1221  +          }while( idxStr[iIdxStr]>='0' && idxStr[iIdxStr]<='9' );
         1222  +        }else{
         1223  +          iCol = pConfig->nCol;
         1224  +        }
         1225  +
         1226  +        if( zText[0]=='*' ){
         1227  +          /* The user has issued a query of the form "MATCH '*...'". This
         1228  +          ** indicates that the MATCH expression is not a full text query,
         1229  +          ** but a request for an internal parameter.  */
         1230  +          rc = fts5SpecialMatch(pTab, pCsr, &zText[1]);
         1231  +          goto filter_out;
         1232  +        }else{
         1233  +          char **pzErr = &pTab->p.base.zErrMsg;
         1234  +          rc = sqlite3Fts5ExprNew(pConfig, iCol, zText, &pExpr, pzErr);
         1235  +          if( rc==SQLITE_OK ){
         1236  +            rc = sqlite3Fts5ExprAnd(&pCsr->pExpr, pExpr);
         1237  +            pExpr = 0;
         1238  +          }
         1239  +          if( rc!=SQLITE_OK ) goto filter_out;
         1240  +        }
         1241  +
         1242  +        break;
         1243  +      }
         1244  +      case '=':
         1245  +        pRowidEq = apVal[i];
         1246  +        break;
         1247  +      case '<':
         1248  +        pRowidLe = apVal[i];
         1249  +        break;
         1250  +      default: assert( idxStr[iIdxStr-1]=='>' );
         1251  +        pRowidGe = apVal[i];
         1252  +        break;
         1253  +    }
         1254  +  }
  1185   1255     bOrderByRank = ((idxNum & FTS5_BI_ORDER_RANK) ? 1 : 0);
  1186   1256     pCsr->bDesc = bDesc = ((idxNum & FTS5_BI_ORDER_DESC) ? 1 : 0);
  1187   1257   
  1188   1258     /* Set the cursor upper and lower rowid limits. Only some strategies 
  1189   1259     ** actually use them. This is ok, as the xBestIndex() method leaves the
  1190   1260     ** sqlite3_index_constraint.omit flag clear for range constraints
  1191   1261     ** on the rowid field.  */
................................................................................
  1204   1274       /* If pSortCsr is non-NULL, then this call is being made as part of 
  1205   1275       ** processing for a "... MATCH <expr> ORDER BY rank" query (ePlan is
  1206   1276       ** set to FTS5_PLAN_SORTED_MATCH). pSortCsr is the cursor that will
  1207   1277       ** return results to the user for this query. The current cursor 
  1208   1278       ** (pCursor) is used to execute the query issued by function 
  1209   1279       ** fts5CursorFirstSorted() above.  */
  1210   1280       assert( pRowidEq==0 && pRowidLe==0 && pRowidGe==0 && pRank==0 );
  1211         -    assert( nVal==0 && pMatch==0 && bOrderByRank==0 && bDesc==0 );
         1281  +    assert( nVal==0 && bOrderByRank==0 && bDesc==0 );
  1212   1282       assert( pCsr->iLastRowid==LARGEST_INT64 );
  1213   1283       assert( pCsr->iFirstRowid==SMALLEST_INT64 );
  1214   1284       if( pTab->pSortCsr->bDesc ){
  1215   1285         pCsr->iLastRowid = pTab->pSortCsr->iFirstRowid;
  1216   1286         pCsr->iFirstRowid = pTab->pSortCsr->iLastRowid;
  1217   1287       }else{
  1218   1288         pCsr->iLastRowid = pTab->pSortCsr->iLastRowid;
  1219   1289         pCsr->iFirstRowid = pTab->pSortCsr->iFirstRowid;
  1220   1290       }
  1221   1291       pCsr->ePlan = FTS5_PLAN_SOURCE;
  1222   1292       pCsr->pExpr = pTab->pSortCsr->pExpr;
  1223   1293       rc = fts5CursorFirst(pTab, pCsr, bDesc);
  1224         -  }else if( pMatch ){
  1225         -    const char *zExpr = (const char*)sqlite3_value_text(apVal[0]);
  1226         -    if( zExpr==0 ) zExpr = "";
  1227         -
         1294  +  }else if( pCsr->pExpr ){
  1228   1295       rc = fts5CursorParseRank(pConfig, pCsr, pRank);
  1229   1296       if( rc==SQLITE_OK ){
  1230         -      if( zExpr[0]=='*' ){
  1231         -        /* The user has issued a query of the form "MATCH '*...'". This
  1232         -        ** indicates that the MATCH expression is not a full text query,
  1233         -        ** but a request for an internal parameter.  */
  1234         -        rc = fts5SpecialMatch(pTab, pCsr, &zExpr[1]);
         1297  +      if( bOrderByRank ){
         1298  +        pCsr->ePlan = FTS5_PLAN_SORTED_MATCH;
         1299  +        rc = fts5CursorFirstSorted(pTab, pCsr, bDesc);
  1235   1300         }else{
  1236         -        char **pzErr = &pTab->p.base.zErrMsg;
  1237         -        rc = sqlite3Fts5ExprNew(pConfig, iCol, zExpr, &pCsr->pExpr, pzErr);
  1238         -        if( rc==SQLITE_OK ){
  1239         -          if( bOrderByRank ){
  1240         -            pCsr->ePlan = FTS5_PLAN_SORTED_MATCH;
  1241         -            rc = fts5CursorFirstSorted(pTab, pCsr, bDesc);
  1242         -          }else{
  1243         -            pCsr->ePlan = FTS5_PLAN_MATCH;
  1244         -            rc = fts5CursorFirst(pTab, pCsr, bDesc);
  1245         -          }
  1246         -        }
         1301  +        pCsr->ePlan = FTS5_PLAN_MATCH;
         1302  +        rc = fts5CursorFirst(pTab, pCsr, bDesc);
  1247   1303         }
  1248   1304       }
  1249   1305     }else if( pConfig->zContent==0 ){
  1250   1306       *pConfig->pzErrmsg = sqlite3_mprintf(
  1251   1307           "%s: table does not support scanning", pConfig->zName
  1252   1308       );
  1253   1309       rc = SQLITE_ERROR;
................................................................................
  1256   1312       ** by rowid (ePlan==FTS5_PLAN_ROWID).  */
  1257   1313       pCsr->ePlan = (pRowidEq ? FTS5_PLAN_ROWID : FTS5_PLAN_SCAN);
  1258   1314       rc = sqlite3Fts5StorageStmt(
  1259   1315           pTab->pStorage, fts5StmtType(pCsr), &pCsr->pStmt, &pTab->p.base.zErrMsg
  1260   1316       );
  1261   1317       if( rc==SQLITE_OK ){
  1262   1318         if( pCsr->ePlan==FTS5_PLAN_ROWID ){
  1263         -        sqlite3_bind_value(pCsr->pStmt, 1, apVal[0]);
         1319  +        sqlite3_bind_value(pCsr->pStmt, 1, pRowidEq);
  1264   1320         }else{
  1265   1321           sqlite3_bind_int64(pCsr->pStmt, 1, pCsr->iFirstRowid);
  1266   1322           sqlite3_bind_int64(pCsr->pStmt, 2, pCsr->iLastRowid);
  1267   1323         }
  1268   1324         rc = fts5NextMethod(pCursor);
  1269   1325       }
  1270   1326     }
  1271   1327   
         1328  + filter_out:
         1329  +  sqlite3Fts5ExprFree(pExpr);
  1272   1330     pConfig->pzErrmsg = pzErrmsg;
  1273   1331     return rc;
  1274   1332   }
  1275   1333   
  1276   1334   /* 
  1277   1335   ** This is the xEof method of the virtual table. SQLite calls this 
  1278   1336   ** routine to find out if it has reached the end of a result set.

Changes to ext/fts5/test/fts5faultB.test.

   142    142     INSERT INTO t1 VALUES('b c d a');  -- 4
   143    143   }
   144    144   do_faultsim_test 5.1 -faults oom* -body {
   145    145     execsql { SELECT rowid FROM t1('^a OR ^b') }
   146    146   } -test {
   147    147     faultsim_test_result {0 {1 4}}
   148    148   }
          149  +
          150  +#-------------------------------------------------------------------------
          151  +# Test OOM injection in a query with two MATCH expressions
          152  +#
          153  +reset_db
          154  +do_execsql_test 6.0 {
          155  +  CREATE VIRTUAL TABLE t1 USING fts5(a);
          156  +  INSERT INTO t1 VALUES('a b c d');  -- 1
          157  +  INSERT INTO t1 VALUES('d a b c');  -- 2
          158  +  INSERT INTO t1 VALUES('c d a b');  -- 3
          159  +  INSERT INTO t1 VALUES('b c d a');  -- 4
          160  +}
          161  +do_faultsim_test 6.1 -faults oom* -body {
          162  +  execsql { SELECT rowid FROM t1 WHERE t1 MATCH 'a' AND t1 MATCH 'b' }
          163  +} -test {
          164  +  faultsim_test_result {0 {1 2 3 4}}
          165  +}
          166  +do_faultsim_test 6.2 -faults oom* -body {
          167  +  execsql { SELECT rowid FROM t1 WHERE t1 MATCH 'a OR b' AND t1 MATCH 'c OR d' }
          168  +} -test {
          169  +  faultsim_test_result {0 {1 2 3 4}}
          170  +}
   149    171   
   150    172   
   151    173   finish_test

Added ext/fts5/test/fts5multi.test.

            1  +# 2014 September 13
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this script is testing the FTS5 module.
           13  +#
           14  +
           15  +source [file join [file dirname [info script]] fts5_common.tcl]
           16  +set testprefix fts5multi
           17  +
           18  +# If SQLITE_ENABLE_FTS5 is not defined, omit this file.
           19  +ifcapable !fts5 {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +fts5_aux_test_functions db
           25  +
           26  +do_execsql_test 1.0 {
           27  +  CREATE VIRTUAL TABLE t1 USING fts5(a, b, c);
           28  +  INSERT INTO t1 VALUES('gg bb bb'   ,'gg ff gg'   ,'ii ii');
           29  +  INSERT INTO t1 VALUES('dd dd hh kk','jj'         ,'aa');
           30  +  INSERT INTO t1 VALUES('kk gg ee'   ,'hh cc'      ,'hh jj aa cc');
           31  +  INSERT INTO t1 VALUES('hh'         ,'bb jj cc'   ,'kk ii');
           32  +  INSERT INTO t1 VALUES('kk dd kk ii','aa ee aa'   ,'ee');
           33  +  INSERT INTO t1 VALUES('ee'         ,'ff gg kk aa','ee ff ee');
           34  +  INSERT INTO t1 VALUES('ff jj'      ,'gg ee'      ,'kk ee gg kk');
           35  +  INSERT INTO t1 VALUES('ff ee dd hh','kk ee'      ,'gg dd');
           36  +  INSERT INTO t1 VALUES('bb'         ,'aa'         ,'bb aa');
           37  +  INSERT INTO t1 VALUES('hh cc bb'   ,'ff bb'      ,'cc');
           38  +  INSERT INTO t1 VALUES('jj'         ,'ff dd bb aa','dd dd ff ff');
           39  +  INSERT INTO t1 VALUES('ff dd gg dd','gg aa bb ff','cc');
           40  +  INSERT INTO t1 VALUES('ff aa cc jj','kk'         ,'ii dd');
           41  +  INSERT INTO t1 VALUES('jj dd'      ,'cc'         ,'ii hh ee aa');
           42  +  INSERT INTO t1 VALUES('ff ii hh'   ,'dd'         ,'gg');
           43  +  INSERT INTO t1 VALUES('ff dd gg hh','hh'         ,'ff dd');
           44  +  INSERT INTO t1 VALUES('cc cc'      ,'ff dd ff'   ,'bb');
           45  +  INSERT INTO t1 VALUES('ii'         ,'bb ii'      ,'jj kk');
           46  +  INSERT INTO t1 VALUES('ff hh'      ,'hh bb'      ,'bb dd ee');
           47  +  INSERT INTO t1 VALUES('jj kk'      ,'jj'         ,'gg ff cc');
           48  +  INSERT INTO t1 VALUES('dd kk'      ,'ii gg'      ,'dd');
           49  +  INSERT INTO t1 VALUES('cc'         ,'aa ff'      ,'ii');
           50  +  INSERT INTO t1 VALUES('bb ff bb ii','bb kk bb aa','hh ff ii dd');
           51  +  INSERT INTO t1 VALUES('aa'         ,'ee bb jj jj','dd');
           52  +  INSERT INTO t1 VALUES('kk dd cc'   ,'aa jj'      ,'ee aa ff');
           53  +  INSERT INTO t1 VALUES('aa gg aa'   ,'jj'         ,'ii kk hh gg');
           54  +  INSERT INTO t1 VALUES('ff hh aa'   ,'jj ii'      ,'hh dd bb jj');
           55  +  INSERT INTO t1 VALUES('hh'         ,'aa gg kk'   ,'bb ee');
           56  +  INSERT INTO t1 VALUES('bb'         ,'ee'         ,'gg');
           57  +  INSERT INTO t1 VALUES('dd kk'      ,'kk bb aa'   ,'ee');
           58  +}
           59  +
           60  +foreach {tn c1 e1 c2 e2} {
           61  +  1     t1 aa     t1 bb
           62  +  2     a  aa     b  bb
           63  +  3     a  "aa OR bb OR cc"    b  "jj OR ii OR hh"
           64  +  4     t1  "aa AND bb"       t1  "cc"
           65  +  5     c   "kk"               b  "aa OR bb OR cc OR dd OR ee"
           66  +} {
           67  +  if {$c1=="t1"} {
           68  +    set lhs "( $e1 )"
           69  +  } else {
           70  +    set lhs "$c1 : ( $e1 )"
           71  +  }
           72  +  if {$c2=="t1"} {
           73  +    set rhs "( $e2 )"
           74  +  } else {
           75  +    set rhs "$c2 : ( $e2 )"
           76  +  }
           77  +
           78  +  set q1 "t1 MATCH '($lhs) AND ($rhs)'"
           79  +  set q2 "$c1 MATCH '$e1' AND $c2 MATCH '$e2'"
           80  +
           81  +  set ret [execsql "SELECT rowid FROM t1 WHERE $q1"]
           82  +  set N [llength $ret]
           83  +  do_execsql_test 1.$tn.1.($N) "SELECT rowid FROM t1 WHERE $q2" $ret
           84  +
           85  +  set ret [execsql "SELECT fts5_test_poslist(t1) FROM t1 WHERE $q1"]
           86  +  do_execsql_test 1.$tn.2.($N) "
           87  +    SELECT fts5_test_poslist(t1) FROM t1 WHERE $q2
           88  +  " $ret
           89  +}
           90  +
           91  +do_catchsql_test 2.1.1 {
           92  +  SELECT rowid FROM t1 WHERE t1 MATCH '(NOT' AND t1 MATCH 'aa bb';
           93  +} {1 {fts5: syntax error near "NOT"}}
           94  +do_catchsql_test 2.1.2 {
           95  +  SELECT rowid FROM t1 WHERE t1 MATCH 'aa bb' AND t1 MATCH '(NOT';
           96  +} {1 {fts5: syntax error near "NOT"}}
           97  +
           98  +finish_test
           99  +

Changes to ext/fts5/test/fts5plan.test.

    27     27   }
    28     28   
    29     29   do_eqp_test 1.1 {
    30     30     SELECT * FROM t1, f1 WHERE f1 MATCH t1.x
    31     31   } {
    32     32     QUERY PLAN
    33     33     |--SCAN TABLE t1
    34         -  `--SCAN TABLE f1 VIRTUAL TABLE INDEX 65537:
           34  +  `--SCAN TABLE f1 VIRTUAL TABLE INDEX 0:m
    35     35   }
    36     36   
    37     37   do_eqp_test 1.2 {
    38     38     SELECT * FROM t1, f1 WHERE f1 > t1.x
    39     39   } {
    40     40     QUERY PLAN
    41     41     |--SCAN TABLE f1 VIRTUAL TABLE INDEX 0:
................................................................................
    42     42     `--SCAN TABLE t1
    43     43   }
    44     44   
    45     45   do_eqp_test 1.3 {
    46     46     SELECT * FROM f1 WHERE f1 MATCH ? ORDER BY ff
    47     47   } {
    48     48     QUERY PLAN
    49         -  |--SCAN TABLE f1 VIRTUAL TABLE INDEX 65537:
           49  +  |--SCAN TABLE f1 VIRTUAL TABLE INDEX 0:m
    50     50     `--USE TEMP B-TREE FOR ORDER BY
    51     51   }
    52     52   
    53     53   do_eqp_test 1.4 {
    54     54     SELECT * FROM f1 ORDER BY rank
    55     55   } {
    56     56     QUERY PLAN
    57     57     |--SCAN TABLE f1 VIRTUAL TABLE INDEX 0:
    58     58     `--USE TEMP B-TREE FOR ORDER BY
    59     59   }
    60     60   
    61     61   do_eqp_test 1.5 {
    62     62     SELECT * FROM f1 WHERE rank MATCH ?
    63         -} {SCAN TABLE f1 VIRTUAL TABLE INDEX 2:}
           63  +} {SCAN TABLE f1 VIRTUAL TABLE INDEX 0:r}
    64     64   
    65     65   finish_test

Changes to ext/fts5/test/fts5simple.test.

   463    463   } {11111 11112}
   464    464   do_execsql_test 21.3 {
   465    465     DELETE FROM x1 WHERE rowid=11111;
   466    466     INSERT INTO x1(x1) VALUES('integrity-check');
   467    467     SELECT rowid FROM x1($doc);
   468    468   } {11112}
   469    469   
          470  +#-------------------------------------------------------------------------
          471  +reset_db
          472  +do_execsql_test 22.0 {
          473  +  CREATE VIRTUAL TABLE x1 USING fts5(x);
          474  +  INSERT INTO x1(x) VALUES('a b c');
          475  +  INSERT INTO x1(x) VALUES('x y z');
          476  +  INSERT INTO x1(x) VALUES('c b a');
          477  +  INSERT INTO x1(x) VALUES('z y x');
          478  +}
          479  +
          480  +do_catchsql_test 22.1 {SELECT * FROM x1('')}   {1 {fts5: syntax error near ""}}
          481  +do_catchsql_test 22.2 {SELECT * FROM x1(NULL)} {1 {fts5: syntax error near ""}}
          482  +
   470    483   finish_test

Changes to src/select.c.

  3474   3474           }
  3475   3475           if( pNew && ExprHasProperty(pExpr,EP_FromJoin) ){
  3476   3476             pNew->iRightJoinTable = pExpr->iRightJoinTable;
  3477   3477             ExprSetProperty(pNew, EP_FromJoin);
  3478   3478           }
  3479   3479           sqlite3ExprDelete(db, pExpr);
  3480   3480           pExpr = pNew;
         3481  +
         3482  +        /* Ensure that the expression now has an implicit collation sequence,
         3483  +        ** just as it did when it was a column of a view or sub-query. */
         3484  +        if( pExpr ){
         3485  +          if( pExpr->op!=TK_COLUMN && pExpr->op!=TK_COLLATE ){
         3486  +            CollSeq *pColl = sqlite3ExprCollSeq(pSubst->pParse, pExpr);
         3487  +            pExpr = sqlite3ExprAddCollateString(pSubst->pParse, pExpr, 
         3488  +                (pColl ? pColl->zName : "BINARY")
         3489  +            );
         3490  +          }
         3491  +          ExprClearProperty(pExpr, EP_Collate);
         3492  +        }
  3481   3493         }
  3482   3494       }
  3483   3495     }else{
  3484   3496       if( pExpr->op==TK_IF_NULL_ROW && pExpr->iTable==pSubst->iTable ){
  3485   3497         pExpr->iTable = pSubst->iNewTable;
  3486   3498       }
  3487   3499       pExpr->pLeft = substExpr(pSubst, pExpr->pLeft);

Changes to src/test1.c.

  7196   7196       { "distinct-opt",        SQLITE_DistinctOpt    },
  7197   7197       { "cover-idx-scan",      SQLITE_CoverIdxScan   },
  7198   7198       { "order-by-idx-join",   SQLITE_OrderByIdxJoin },
  7199   7199       { "transitive",          SQLITE_Transitive     },
  7200   7200       { "omit-noop-join",      SQLITE_OmitNoopJoin   },
  7201   7201       { "stat4",               SQLITE_Stat4          },
  7202   7202       { "skip-scan",           SQLITE_SkipScan       },
         7203  +    { "push-down",           SQLITE_PushDown       },
  7203   7204     };
  7204   7205   
  7205   7206     if( objc!=4 ){
  7206   7207       Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
  7207   7208       return TCL_ERROR;
  7208   7209     }
  7209   7210     if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;

Changes to src/treeview.c.

   532    532       case TK_SPAN: {
   533    533         sqlite3TreeViewLine(pView, "SPAN %Q", pExpr->u.zToken);
   534    534         sqlite3TreeViewExpr(pView, pExpr->pLeft, 0);
   535    535         break;
   536    536       }
   537    537   
   538    538       case TK_COLLATE: {
   539         -      sqlite3TreeViewLine(pView, "COLLATE %Q", pExpr->u.zToken);
          539  +      /* COLLATE operators without the EP_Collate flag are intended to
          540  +      ** emulate collation associated with a table column.  Explicit
          541  +      ** COLLATE operators that appear in the original SQL always have
          542  +      ** the EP_Collate bit set */
          543  +      sqlite3TreeViewLine(pView, "%sCOLLATE %Q%s",
          544  +        !ExprHasProperty(pExpr, EP_Collate) ? "SOFT-" : "",
          545  +        pExpr->u.zToken, zFlgs);
   540    546         sqlite3TreeViewExpr(pView, pExpr->pLeft, 0);
   541    547         break;
   542    548       }
   543    549   
   544    550       case TK_AGG_FUNCTION:
   545    551       case TK_FUNCTION: {
   546    552         ExprList *pFarg;       /* List of function arguments */

Added test/tkt-18458b1a.test.

            1  +# 2019 September 10
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library. In particular,
           12  +# that problems related to ticket [18458b1a] have been fixed.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix tkt-18458b1a
           18  +
           19  +foreach tn {1 2} {
           20  +  reset_db
           21  +  if {$tn==1} {
           22  +    # Disable the flattener and push-down optimizations
           23  +    optimization_control db query-flattener 0
           24  +    optimization_control db push-down 0
           25  +  } else {
           26  +    # Enable them
           27  +    optimization_control db query-flattener 1
           28  +    optimization_control db push-down 1
           29  +  }
           30  +
           31  +  db cache size 0
           32  +
           33  +  do_execsql_test $tn.1.1 {
           34  +    CREATE TABLE t0(c0 COLLATE NOCASE);
           35  +    INSERT INTO t0(c0) VALUES ('B');
           36  +    CREATE VIEW v0(c0, c1) AS SELECT DISTINCT t0.c0, 'a' FROM t0;
           37  +  } 
           38  +
           39  +  do_execsql_test $tn.1.2 {
           40  +    SELECT count(*) FROM v0 WHERE c1 >= c0;
           41  +  } 1
           42  +
           43  +  do_execsql_test $tn.1.3 {
           44  +    SELECT count(*) FROM v0 WHERE NOT NOT (c1 >= c0);
           45  +  } 1
           46  +
           47  +  do_execsql_test $tn.1.4 {
           48  +    SELECT count(*) FROM v0 WHERE ((c1 >= c0) OR 0+0);
           49  +  } 1
           50  +}
           51  +
           52  +finish_test
           53  +

Added test/tkt-a7debbe0.test.

            1  +# 2019 September 10
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library. In particular,
           12  +# that problems related to ticket a7debbe0ad1 have been fixed.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix tkt-a7debbe0
           18  +
           19  +foreach tn {1 2} {
           20  +  reset_db
           21  +  if {$tn==1} {
           22  +    # Disable the flattener
           23  +    optimization_control db query-flattener 0
           24  +  } else {
           25  +    # Enable the flattener
           26  +    optimization_control db query-flattener 1
           27  +  }
           28  +
           29  +  do_execsql_test $tn.1.0 {
           30  +    CREATE TABLE t0(xyz INTEGER);
           31  +    INSERT INTO t0(xyz) VALUES(456);
           32  +    CREATE VIEW v2(a, B) AS 
           33  +        SELECT 'a', 'B' COLLATE NOCASE FROM t0;
           34  +    CREATE TABLE t2(a, B COLLATE NOCASE);
           35  +    INSERT INTO t2 VALUES('a', 'B');
           36  +    CREATE VIEW v3(a, B) AS
           37  +        SELECT 'a' COLLATE BINARY, 'B' COLLATE NOCASE FROM t0;
           38  +
           39  +    CREATE VIEW v4(a, B) AS
           40  +        SELECT 'a', +CAST('B' COLLATE NOCASE AS TEXT) FROM t0;
           41  +
           42  +    CREATE VIEW v5(a, B) AS
           43  +        SELECT 'a', ('B' COLLATE NOCASE) || '' FROM t0;
           44  +  }
           45  +
           46  +  # Table t2 and views v2 through v5 should all be equivalent.
           47  +  do_execsql_test $tn.1.1.1 { SELECT a   >= B FROM t2;         } 1
           48  +  do_execsql_test $tn.1.1.2 { SELECT 'a' >= 'B' COLLATE NOCASE } 0
           49  +  do_execsql_test $tn.1.1.3 { SELECT a   >= B FROM v2          } 1
           50  +  do_execsql_test $tn.1.1.4 { SELECT a   >= B FROM v3          } 1
           51  +  do_execsql_test $tn.1.1.5 { SELECT a   >= B FROM v4          } 1
           52  +  do_execsql_test $tn.1.1.6 { SELECT a   >= B FROM v5          } 1
           53  +
           54  +  do_execsql_test $tn.1.2.1 { SELECT B   < a FROM t2           } 0
           55  +  do_execsql_test $tn.1.2.2 { SELECT 'B' COLLATE NOCASE < 'a'  } 0
           56  +  do_execsql_test $tn.1.2.3 { SELECT B   < a FROM v2           } 0
           57  +  do_execsql_test $tn.1.2.4 { SELECT B   < a FROM v3           } 0
           58  +  do_execsql_test $tn.1.2.5 { SELECT a  < B FROM v4           } 0
           59  +  do_execsql_test $tn.1.2.6 { SELECT a  < B FROM v5           } 0
           60  +
           61  +  #-------------------------------------------------------------------------
           62  +  do_execsql_test $tn.2.0 {
           63  +    CREATE TABLE t5(a, b COLLATE NOCASE);
           64  +    INSERT INTO t5 VALUES(1, 'XYZ');
           65  +  }
           66  +
           67  +  # Result should be 0, as column "xyz" from the sub-query has implicit
           68  +  # collation sequence BINARY.
           69  +  do_execsql_test $tn.2.1 {
           70  +    SELECT xyz==b FROM ( SELECT a, 'xyz' AS xyz FROM t5 ), t5;
           71  +  } {0}
           72  +
           73  +  # Result should be 1, as literal 'xyz' has no collation sequence, so
           74  +  # the comparison uses the implicit collation sequence of the RHS - NOCASE.
           75  +  do_execsql_test $tn.2.2 {
           76  +    SELECT 'xyz'==b FROM ( SELECT a, 'xyz' AS xyz FROM t5 ), t5;
           77  +  } {1}
           78  +
           79  +  #-----------------------------------------------------------------------
           80  +  # The test case submitted with the ticket.
           81  +  #
           82  +  do_execsql_test $tn.3.0 {
           83  +    DROP TABLE t0;
           84  +    DROP VIEW v2;
           85  +
           86  +    CREATE TABLE t0(c0);
           87  +    INSERT INTO t0(c0) VALUES('');
           88  +    CREATE VIEW v2(c0, c1) AS 
           89  +        SELECT 'B' COLLATE NOCASE, 'a' FROM t0 ORDER BY t0.c0;
           90  +    SELECT SUM(count) FROM (
           91  +      SELECT v2.c1 BETWEEN v2.c0 AND v2.c1 as count FROM v2
           92  +    );
           93  +  } 1
           94  +
           95  +  # The result is 1, as the collation used is the implicit collation sequence
           96  +  # of v2.c1 - BINARY.
           97  +  do_execsql_test $tn.3.1 {
           98  +    SELECT v2.c1 BETWEEN v2.c0 AND v2.c1 as count FROM v2;
           99  +  } 1
          100  +}
          101  +
          102  +finish_test
          103  +

Changes to tool/lemon.c.

  4422   4422         fprintf(out, "\n"); lineno++;
  4423   4423         j = 0;
  4424   4424       }else{
  4425   4425         j++;
  4426   4426       }
  4427   4427       i++;
  4428   4428     }
  4429         -  if( j>0 ) fprintf(out, "\n"); lineno++;
         4429  +  if( j>0 ){ fprintf(out, "\n"); lineno++; }
  4430   4430     fprintf(out, "};\n"); lineno++;
  4431   4431   
  4432   4432     /* Output the yy_shift_ofst[] table */
  4433   4433     n = lemp->nxstate;
  4434   4434     while( n>0 && lemp->sorted[n-1]->iTknOfst==NO_OFFSET ) n--;
  4435   4435     fprintf(out, "#define YY_SHIFT_COUNT    (%d)\n", n-1); lineno++;
  4436   4436     fprintf(out, "#define YY_SHIFT_MIN      (%d)\n", mnTknOfst); lineno++;