/ Check-in [c0f4e308]
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:Make sure partial automatic indexes are not based on terms in the ON clause of a LEFT JOIN. Fix for ticket [2326c258d02ead3]. UPDATE: This fix does not work where the partial index is a named index.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | tkt-2326c258
Files: files | file ages | folders
SHA1: c0f4e308a508183b72ceda447dc3ac778cb85b9f
User & Date: drh 2015-02-24 16:05:54
Original Comment: Make sure partial automatic indexes are not based on terms in the ON clause of a LEFT JOIN. Fix for ticket [2326c258d02ead3].
Context
2015-02-24
16:48
This additional fix prevents a partial index from being qualified for use if the constraint that qualifies the partial index is part of the ON clause of a LEFT JOIN. check-in: 1a1516e4 user: drh tags: tkt-2326c258
16:05
Make sure partial automatic indexes are not based on terms in the ON clause of a LEFT JOIN. Fix for ticket [2326c258d02ead3]. UPDATE: This fix does not work where the partial index is a named index. check-in: c0f4e308 user: drh tags: tkt-2326c258
2015-02-21
15:42
Update document on sqlite3_mprintf() and related functions. Discuss the %w format and point out that obscure ANSI-C formats are not supported. No changes to code. check-in: f8917ba4 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  1610   1610     pTable = pSrc->pTab;
  1611   1611     pWCEnd = &pWC->a[pWC->nTerm];
  1612   1612     pLoop = pLevel->pWLoop;
  1613   1613     idxCols = 0;
  1614   1614     for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
  1615   1615       if( pLoop->prereq==0
  1616   1616        && (pTerm->wtFlags & TERM_VIRTUAL)==0
         1617  +     && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
  1617   1618        && sqlite3ExprIsTableConstant(pTerm->pExpr, pSrc->iCursor) ){
  1618   1619         pPartial = sqlite3ExprAnd(pParse->db, pPartial,
  1619   1620                                   sqlite3ExprDup(pParse->db, pTerm->pExpr, 0));
  1620   1621       }
  1621   1622       if( termCanDriveIndex(pTerm, pSrc, notReady) ){
  1622   1623         int iCol = pTerm->u.leftColumn;
  1623   1624         Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);

Changes to test/autoindex4.test.

    44     44     CREATE TABLE t3(e,f);
    45     45     INSERT INTO t3 VALUES(123,654),(555,444),(234,987);
    46     46   
    47     47     SELECT (SELECT count(*) FROM t1, t2 WHERE a=e AND x=f), e, f, '|'
    48     48       FROM t3
    49     49      ORDER BY rowid;
    50     50   } {1 123 654 | 0 555 444 | 4 234 987 |}
           51  +
           52  +# Ticket [2326c258d02ead33d]
           53  +# Two joins, one with and the other without an ORDER BY clause.
           54  +# The one without ORDER BY correctly returns two rows of result.
           55  +# The one with ORDER BY returns no rows. 
           56  +#
           57  +do_execsql_test autoindex4-3.0 {
           58  +  CREATE TABLE A(Name text);
           59  +  CREATE TABLE Items(ItemName text , Name text);
           60  +  INSERT INTO Items VALUES('Item1','Parent');
           61  +  INSERT INTO Items VALUES('Item2','Parent');
           62  +  CREATE TABLE B(Name text);
           63  +  
           64  +  SELECT Items.ItemName
           65  +    FROM Items
           66  +      LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
           67  +      LEFT JOIN B ON (B.Name = Items.ItemName)
           68  +    WHERE Items.Name = 'Parent'
           69  +    ORDER BY Items.ItemName;
           70  +} {Item1 Item2}
           71  +
    51     72   
    52     73   finish_test