/ Check-in [cb667449]
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.

Overview
Comment:In the query optimizer, when converting BETWEEN and LIKE/GLOB expressions into simpler forms for processing, be sure to transfer the LEFT JOIN markings. Fix for ticket [bc878246eafe0f52c]. Cherrypick of [caab361ebe].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.8.0
Files: files | file ages | folders
SHA1: cb667449d0ff64cba4c951325d38186366779610
User & Date: drh 2013-08-29 13:21:52
Context
2013-08-29
13:23
Fix an off-by-one error that causes a quoted empty string at the end of a CRNL-terminated line of CSV input to be misread by the shell. Cherrypick of [b5617e4fdadc4c]. check-in: 43aa7d23 user: drh tags: branch-3.8.0
13:21
In the query optimizer, when converting BETWEEN and LIKE/GLOB expressions into simpler forms for processing, be sure to transfer the LEFT JOIN markings. Fix for ticket [bc878246eafe0f52c]. Cherrypick of [caab361ebe]. check-in: cb667449 user: drh tags: branch-3.8.0
13:15
Cherrypick of [c1152bdcbb] and fix for [9f2eb3abac]: Have the whereShortCut() planner ignore indexes with more than four columns. check-in: c3f75941 user: drh tags: branch-3.8.0
2013-08-28
16:27
In the query optimizer, when converting BETWEEN and LIKE/GLOB expressions into simpler forms for processing, be sure to transfer the LEFT JOIN markings. Fix for ticket [bc878246eafe0f52c]. check-in: caab361e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  1201   1201   #endif /* SQLITE_OMIT_VIRTUALTABLE */
  1202   1202   
  1203   1203   /*
  1204   1204   ** If the pBase expression originated in the ON or USING clause of
  1205   1205   ** a join, then transfer the appropriate markings over to derived.
  1206   1206   */
  1207   1207   static void transferJoinMarkings(Expr *pDerived, Expr *pBase){
  1208         -  pDerived->flags |= pBase->flags & EP_FromJoin;
  1209         -  pDerived->iRightJoinTable = pBase->iRightJoinTable;
         1208  +  if( pDerived ){
         1209  +    pDerived->flags |= pBase->flags & EP_FromJoin;
         1210  +    pDerived->iRightJoinTable = pBase->iRightJoinTable;
         1211  +  }
  1210   1212   }
  1211   1213   
  1212   1214   #if !defined(SQLITE_OMIT_OR_OPTIMIZATION) && !defined(SQLITE_OMIT_SUBQUERY)
  1213   1215   /*
  1214   1216   ** Analyze a term that consists of two or more OR-connected
  1215   1217   ** subterms.  So in:
  1216   1218   **
................................................................................
  1659   1661       assert( pList->nExpr==2 );
  1660   1662       for(i=0; i<2; i++){
  1661   1663         Expr *pNewExpr;
  1662   1664         int idxNew;
  1663   1665         pNewExpr = sqlite3PExpr(pParse, ops[i], 
  1664   1666                                sqlite3ExprDup(db, pExpr->pLeft, 0),
  1665   1667                                sqlite3ExprDup(db, pList->a[i].pExpr, 0), 0);
         1668  +      transferJoinMarkings(pNewExpr, pExpr);
  1666   1669         idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
  1667   1670         testcase( idxNew==0 );
  1668   1671         exprAnalyze(pSrc, pWC, idxNew);
  1669   1672         pTerm = &pWC->a[idxTerm];
  1670   1673         pWC->a[idxNew].iParent = idxTerm;
  1671   1674       }
  1672   1675       pTerm->nChild = 2;
................................................................................
  1726   1729       }
  1727   1730       sCollSeqName.z = noCase ? "NOCASE" : "BINARY";
  1728   1731       sCollSeqName.n = 6;
  1729   1732       pNewExpr1 = sqlite3ExprDup(db, pLeft, 0);
  1730   1733       pNewExpr1 = sqlite3PExpr(pParse, TK_GE, 
  1731   1734              sqlite3ExprAddCollateToken(pParse,pNewExpr1,&sCollSeqName),
  1732   1735              pStr1, 0);
         1736  +    transferJoinMarkings(pNewExpr1, pExpr);
  1733   1737       idxNew1 = whereClauseInsert(pWC, pNewExpr1, TERM_VIRTUAL|TERM_DYNAMIC);
  1734   1738       testcase( idxNew1==0 );
  1735   1739       exprAnalyze(pSrc, pWC, idxNew1);
  1736   1740       pNewExpr2 = sqlite3ExprDup(db, pLeft, 0);
  1737   1741       pNewExpr2 = sqlite3PExpr(pParse, TK_LT,
  1738   1742              sqlite3ExprAddCollateToken(pParse,pNewExpr2,&sCollSeqName),
  1739   1743              pStr2, 0);
         1744  +    transferJoinMarkings(pNewExpr2, pExpr);
  1740   1745       idxNew2 = whereClauseInsert(pWC, pNewExpr2, TERM_VIRTUAL|TERM_DYNAMIC);
  1741   1746       testcase( idxNew2==0 );
  1742   1747       exprAnalyze(pSrc, pWC, idxNew2);
  1743   1748       pTerm = &pWC->a[idxTerm];
  1744   1749       if( isComplete ){
  1745   1750         pWC->a[idxNew1].iParent = idxTerm;
  1746   1751         pWC->a[idxNew2].iParent = idxTerm;

Changes to test/where9.test.

   946    946       SELECT 5 FROM x9 WHERE y IS NULL;
   947    947       SELECT 6 FROM t91 LEFT JOIN t92 ON a=2 OR b=3 WHERE y IS NULL;
   948    948       SELECT 7 FROM t91 LEFT JOIN t92 ON a=2 AND b=3 WHERE y IS NULL;
   949    949       SELECT 8 FROM t91 LEFT JOIN t92 ON a=22 OR b=33 WHERE y IS NULL;
   950    950       SELECT 9 FROM t91 LEFT JOIN t92 ON a=22 AND b=33 WHERE y IS NULL;
   951    951     }
   952    952   } {1 2 3 4 8 9}
          953  +
          954  +# Fix for ticket [bc878246eafe0f52c519e29049b2fe4a99491b27]
          955  +# Incorrect result when OR is used in a join to the right of a LEFT JOIN
          956  +#
          957  +do_test where9-10.1 {
          958  +  db eval {
          959  +    CREATE TABLE t101 (id INTEGER PRIMARY KEY);
          960  +    INSERT INTO t101 VALUES (1);
          961  +    SELECT * FROM t101 AS t0
          962  +         LEFT JOIN t101 AS t1 ON t1.id BETWEEN 10 AND 20
          963  +         JOIN t101 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
          964  +  }
          965  +} {1 {} 1}
          966  +do_test where9-10.2 {
          967  +  db eval {
          968  +    CREATE TABLE t102 (id TEXT UNIQUE NOT NULL);
          969  +    INSERT INTO t102 VALUES ('1');
          970  +    SELECT * FROM t102 AS t0
          971  +         LEFT JOIN t102 AS t1 ON t1.id GLOB 'abc%'
          972  +         JOIN t102 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
          973  +  }
          974  +} {1 {} 1}
          975  +
          976  +
   953    977   
   954    978   finish_test