/ Check-in [231c72d9]
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:Fix a problem handling (a, b) IN (SELECT ...) expressions when there is an index on just one of "a" or "b".
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1: 231c72d9f651f3a70d5c8af080f3ff181b89d939
User & Date: dan 2016-09-06 14:58:15
Context
2016-09-06
15:25
Fix the header comment on codeEqualityTerm(). check-in: b7e710e4 user: drh tags: rowvalue
14:58
Fix a problem handling (a, b) IN (SELECT ...) expressions when there is an index on just one of "a" or "b". check-in: 231c72d9 user: dan tags: rowvalue
14:37
Enhance the sqlite3GetTempRange() and sqlite3ReleaseTempRange() internal routines so that they use sqlite3GetTempReg() and sqlite3ReleaseTempReg() when nReg==1. check-in: 4071da2f user: drh tags: rowvalue
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

   459    459             Expr *pNewLhs = sqlite3ExprDup(db, pOrigLhs->a[iField].pExpr, 0);
   460    460   
   461    461             pRhs = sqlite3ExprListAppend(pParse, pRhs, pNewRhs);
   462    462             pLhs = sqlite3ExprListAppend(pParse, pLhs, pNewLhs);
   463    463           }
   464    464         }
   465    465         if( !db->mallocFailed ){
          466  +        Expr *pLeft = pX->pLeft;
          467  +        /* Take care here not to generate a TK_VECTOR containing only a
          468  +        ** single value. Since the parser never creates such a vector, some
          469  +        ** of the subroutines do not handle this case.  */
          470  +        if( pLhs->nExpr==1 ){
          471  +          pX->pLeft = pLhs->a[0].pExpr;
          472  +        }else{
          473  +          pLeft->x.pList = pLhs;
          474  +        }
   466    475           pX->x.pSelect->pEList = pRhs;
   467         -        pX->pLeft->x.pList = pLhs;
   468    476           eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, aiMap);
   469    477           pX->x.pSelect->pEList = pOrigRhs;
   470         -        pX->pLeft->x.pList = pOrigLhs;
          478  +        pLeft->x.pList = pOrigLhs;
          479  +        pX->pLeft = pLeft;
   471    480         }
   472    481         sqlite3ExprListDelete(pParse->db, pLhs);
   473    482         sqlite3ExprListDelete(pParse->db, pRhs);
   474    483       }
   475    484   
   476    485       if( eType==IN_INDEX_INDEX_DESC ){
   477    486         testcase( bRev );

Changes to test/rowvalue9.test.

    13     13   # constructors.
    14     14   #
    15     15   
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   set ::testprefix rowvalue9
           20  +
           21  +# Tests:
           22  +#
           23  +#  1.*: Test that affinities are handled correctly by various row-value
           24  +#       operations without indexes.
           25  +#
           26  +#  2.*: Test an affinity bug that came up during testing.
           27  +#
           28  +#  3.*: Test a row-value version of the bug tested by 2.*.
           29  +#
           30  +#  4.*: Test that affinities are handled correctly by various row-value
           31  +#       operations with assorted indexes.
           32  +#
    20     33   
    21     34   do_execsql_test 1.0.1 {
    22     35     CREATE TABLE a1(c, b INTEGER, a TEXT, PRIMARY KEY(a, b));
    23     36    
    24     37     INSERT INTO a1 (rowid, c, b, a) VALUES(3,  '0x03', 1, 1);
    25     38     INSERT INTO a1 (rowid, c, b, a) VALUES(14, '0x0E', 2, 2);
    26     39     INSERT INTO a1 (rowid, c, b, a) VALUES(15, '0x0F', 3, 3);
................................................................................
   201    214   
   202    215     do_execsql_test 4.$tn.6 {
   203    216       SELECT d1.rowid FROM d1 WHERE a = (
   204    217         SELECT y FROM d2 where d2.rowid=d1.rowid
   205    218       );
   206    219     } {2 4}
   207    220   }
          221  +
          222  +do_execsql_test 5.0 {
          223  +  CREATE TABLE e1(a TEXT, c NUMERIC);
          224  +  CREATE TABLE e2(x BLOB, y BLOB);
          225  +
          226  +  INSERT INTO e1 VALUES(2, 2);
          227  +
          228  +  INSERT INTO e2 VALUES ('2', 2);
          229  +  INSERT INTO e2 VALUES ('2', '2');
          230  +  INSERT INTO e2 VALUES ('2', '2.0');
          231  +
          232  +  CREATE INDEX e1c ON e1(c);
          233  +}
          234  +
          235  +do_execsql_test 5.1 {
          236  +  SELECT rowid FROM e1 WHERE (a, c) IN (SELECT x, y FROM e2);
          237  +} {1}
   208    238   
   209    239   finish_test
   210    240