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

Changes to src/select.c.

  5539   5539   
  5540   5540       /* Sometimes the code for a subquery will be generated more than
  5541   5541       ** once, if the subquery is part of the WHERE clause in a LEFT JOIN,
  5542   5542       ** for example.  In that case, do not regenerate the code to manifest
  5543   5543       ** a view or the co-routine to implement a view.  The first instance
  5544   5544       ** is sufficient, though the subroutine to manifest the view does need
  5545   5545       ** to be invoked again. */
  5546         -    if( pItem->addrFillSub ){
         5546  +    if( NEVER(pItem->addrFillSub) ){
  5547   5547         if( pItem->fg.viaCoroutine==0 ){
  5548   5548           /* The subroutine that manifests the view might be a one-time routine,
  5549   5549           ** or it might need to be rerun on each iteration because it
  5550   5550           ** encodes a correlated subquery. */
  5551   5551           testcase( sqlite3VdbeGetOp(v, pItem->addrFillSub)->opcode==OP_Once );
  5552   5552           sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub);
  5553   5553         }

Changes to src/where.c.

  2416   2416       }
  2417   2417       if( pTerm->prereqRight & pNew->maskSelf ) continue;
  2418   2418   
  2419   2419       /* Do not allow the upper bound of a LIKE optimization range constraint
  2420   2420       ** to mix with a lower range bound from some other source */
  2421   2421       if( pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT ) continue;
  2422   2422   
  2423         -    /* Do not allow IS constraints from the WHERE clause to be used by the
         2423  +    /* Do not allow constraints from the WHERE clause to be used by the
  2424   2424       ** right table of a LEFT JOIN.  Only constraints in the ON clause are
  2425   2425       ** allowed */
  2426   2426       if( (pSrc->fg.jointype & JT_LEFT)!=0
  2427   2427        && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
  2428         -     && (eOp & (WO_IS|WO_ISNULL))!=0
  2429   2428       ){
  2430         -      testcase( eOp & WO_IS );
  2431         -      testcase( eOp & WO_ISNULL );
  2432   2429         continue;
  2433   2430       }
  2434   2431   
  2435   2432       if( IsUniqueIndex(pProbe) && saved_nEq==pProbe->nKeyCol-1 ){
  2436   2433         pBuilder->bldFlags |= SQLITE_BLDF_UNIQUE;
  2437   2434       }else{
  2438   2435         pBuilder->bldFlags |= SQLITE_BLDF_INDEXED;

Changes to test/where.test.

  1374   1374     CREATE INDEX t191a ON t1(a);
  1375   1375     CREATE INDEX t191b ON t1(b);
  1376   1376     CREATE TABLE t192(x INTEGER PRIMARY KEY,y INT, z INT);
  1377   1377   
  1378   1378     EXPLAIN QUERY PLAN
  1379   1379     SELECT t191.rowid FROM t192, t191 WHERE (a=y OR b=y) AND x=?1;
  1380   1380   } {/.* sqlite_autoindex_t191_1 .* sqlite_autoindex_t191_2 .*/}
         1381  +
         1382  +# 2018-04-24 ticket [https://www.sqlite.org/src/info/4ba5abf65c5b0f9a]
         1383  +# Index on expressions leads to an incorrect answer for a LEFT JOIN
         1384  +#
         1385  +do_execsql_test where-20.0 {
         1386  +  CREATE TABLE t201(x);
         1387  +  CREATE TABLE t202(y, z);
         1388  +  INSERT INTO t201 VALUES('key');
         1389  +  INSERT INTO t202 VALUES('key', -1);
         1390  +  CREATE INDEX t202i ON t202(y, ifnull(z, 0));
         1391  +  SELECT count(*) FROM t201 LEFT JOIN t202 ON (x=y) WHERE ifnull(z, 0) >=0;
         1392  +} {0}
  1381   1393   
  1382   1394   
  1383   1395   finish_test