/ Check-in [0e64ac12]
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:Import from trunk the new fix to ticket [df46dfb631f75694] in which all ephemeral tables used as the RHS of an IN operator be index btrees and never table btrees so that they can always be reused.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.27
Files: files | file ages | folders
SHA3-256: 0e64ac122aac469aff4ca7f55a494c8d2d34bbcc1b854f3d579db5926590a660
User & Date: drh 2019-02-23 00:56:05
Context
2019-02-25
14:25
Fix a typo in the documentation for sqlite3_total_changes(). check-in: 4febdfb3 user: drh tags: branch-3.27
2019-02-23
00:56
Import from trunk the new fix to ticket [df46dfb631f75694] in which all ephemeral tables used as the RHS of an IN operator be index btrees and never table btrees so that they can always be reused. check-in: 0e64ac12 user: drh tags: branch-3.27
00:21
Check-in [fa792714ae62fa98] is incorrect. Add a test case to refute it and also a fix to make it right. Then add an alternative fix to ticket [df46dfb631f75694] in which all ephemeral tables used as the RHS of an IN operator be index btrees and never table btrees so that they can always be reused. check-in: d3915230 user: drh tags: trunk
2019-02-20
13:14
Increment the version number to 3.27.2. check-in: a70d67d8 user: drh tags: branch-3.27
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  2548   2548       ** We will have to generate an ephemeral table to do the job.
  2549   2549       */
  2550   2550       u32 savedNQueryLoop = pParse->nQueryLoop;
  2551   2551       int rMayHaveNull = 0;
  2552   2552       eType = IN_INDEX_EPH;
  2553   2553       if( inFlags & IN_INDEX_LOOP ){
  2554   2554         pParse->nQueryLoop = 0;
  2555         -      if( pX->pLeft->iColumn<0 && !ExprHasProperty(pX, EP_xIsSelect) ){
  2556         -        eType = IN_INDEX_ROWID;
  2557         -      }
  2558   2555       }else if( prRhsHasNull ){
  2559   2556         *prRhsHasNull = rMayHaveNull = ++pParse->nMem;
  2560   2557       }
  2561   2558       assert( pX->op==TK_IN );
  2562         -    sqlite3CodeRhsOfIN(pParse, pX, iTab, eType==IN_INDEX_ROWID);
         2559  +    sqlite3CodeRhsOfIN(pParse, pX, iTab);
  2563   2560       if( rMayHaveNull ){
  2564   2561         sqlite3SetHasNullFlag(v, iTab, rMayHaveNull);
  2565   2562       }
  2566   2563       pParse->nQueryLoop = savedNQueryLoop;
  2567   2564     }
  2568   2565   
  2569   2566     if( aiMap && eType!=IN_INDEX_INDEX_ASC && eType!=IN_INDEX_INDEX_DESC ){
................................................................................
  2656   2653   **
  2657   2654   ** The pExpr parameter is the IN operator.  The cursor number for the
  2658   2655   ** constructed ephermeral table is returned.  The first time the ephemeral
  2659   2656   ** table is computed, the cursor number is also stored in pExpr->iTable,
  2660   2657   ** however the cursor number returned might not be the same, as it might
  2661   2658   ** have been duplicated using OP_OpenDup.
  2662   2659   **
  2663         -** If parameter isRowid is non-zero, then LHS of the IN operator is guaranteed
  2664         -** to be a non-null integer. In this case, the ephemeral table can be an
  2665         -** table B-Tree that keyed by only integers.  The more general cases uses
  2666         -** an index B-Tree which can have arbitrary keys, but is slower to both
  2667         -** read and write.
  2668         -**
  2669   2660   ** If the LHS expression ("x" in the examples) is a column value, or
  2670   2661   ** the SELECT statement returns a column value, then the affinity of that
  2671   2662   ** column is used to build the index keys. If both 'x' and the
  2672   2663   ** SELECT... statement are columns, then numeric affinity is used
  2673   2664   ** if either column has NUMERIC or INTEGER affinity. If neither
  2674   2665   ** 'x' nor the SELECT... statement are columns, then numeric affinity
  2675   2666   ** is used.
  2676   2667   */
  2677   2668   void sqlite3CodeRhsOfIN(
  2678   2669     Parse *pParse,          /* Parsing context */
  2679   2670     Expr *pExpr,            /* The IN operator */
  2680         -  int iTab,               /* Use this cursor number */
  2681         -  int isRowid             /* If true, LHS is a rowid */
         2671  +  int iTab                /* Use this cursor number */
  2682   2672   ){
  2683   2673     int addrOnce = 0;           /* Address of the OP_Once instruction at top */
  2684   2674     int addr;                   /* Address of OP_OpenEphemeral instruction */
  2685   2675     Expr *pLeft;                /* the LHS of the IN operator */
  2686   2676     KeyInfo *pKeyInfo = 0;      /* Key information */
  2687   2677     int nVal;                   /* Size of vector pLeft */
  2688   2678     Vdbe *v;                    /* The prepared statement under construction */
................................................................................
  2727   2717   
  2728   2718       addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
  2729   2719     }
  2730   2720   
  2731   2721     /* Check to see if this is a vector IN operator */
  2732   2722     pLeft = pExpr->pLeft;
  2733   2723     nVal = sqlite3ExprVectorSize(pLeft);
  2734         -  assert( !isRowid || nVal==1 );
  2735   2724   
  2736   2725     /* Construct the ephemeral table that will contain the content of
  2737   2726     ** RHS of the IN operator.
  2738   2727     */
  2739   2728     pExpr->iTable = iTab;
  2740         -  addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, 
  2741         -      pExpr->iTable, (isRowid?0:nVal));
         2729  +  addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pExpr->iTable, nVal);
  2742   2730   #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
  2743   2731     if( ExprHasProperty(pExpr, EP_xIsSelect) ){
  2744   2732       VdbeComment((v, "Result of SELECT %u", pExpr->x.pSelect->selId));
  2745   2733     }else{
  2746   2734       VdbeComment((v, "RHS of IN operator"));
  2747   2735     }
  2748   2736   #endif
  2749         -  pKeyInfo = isRowid ? 0 : sqlite3KeyInfoAlloc(pParse->db, nVal, 1);
         2737  +  pKeyInfo = sqlite3KeyInfoAlloc(pParse->db, nVal, 1);
  2750   2738   
  2751   2739     if( ExprHasProperty(pExpr, EP_xIsSelect) ){
  2752   2740       /* Case 1:     expr IN (SELECT ...)
  2753   2741       **
  2754   2742       ** Generate code to write the results of the select into the temporary
  2755   2743       ** table allocated and opened above.
  2756   2744       */
  2757   2745       Select *pSelect = pExpr->x.pSelect;
  2758   2746       ExprList *pEList = pSelect->pEList;
  2759   2747   
  2760   2748       ExplainQueryPlan((pParse, 1, "%sLIST SUBQUERY %d",
  2761   2749           addrOnce?"":"CORRELATED ", pSelect->selId
  2762   2750       ));
  2763         -    assert( !isRowid );
  2764   2751       /* If the LHS and RHS of the IN operator do not match, that
  2765   2752       ** error will have been caught long before we reach this point. */
  2766   2753       if( ALWAYS(pEList->nExpr==nVal) ){
  2767   2754         SelectDest dest;
  2768   2755         int i;
  2769   2756         sqlite3SelectDestInit(&dest, SRT_Set, iTab);
  2770   2757         dest.zAffSdst = exprINAffinity(pParse, pExpr);
................................................................................
  2809   2796         assert( sqlite3KeyInfoIsWriteable(pKeyInfo) );
  2810   2797         pKeyInfo->aColl[0] = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
  2811   2798       }
  2812   2799   
  2813   2800       /* Loop through each expression in <exprlist>. */
  2814   2801       r1 = sqlite3GetTempReg(pParse);
  2815   2802       r2 = sqlite3GetTempReg(pParse);
  2816         -    if( isRowid ) sqlite3VdbeAddOp4(v, OP_Blob, 0, r2, 0, "", P4_STATIC);
  2817   2803       for(i=pList->nExpr, pItem=pList->a; i>0; i--, pItem++){
  2818   2804         Expr *pE2 = pItem->pExpr;
  2819         -      int iValToIns;
  2820   2805   
  2821   2806         /* If the expression is not constant then we will need to
  2822   2807         ** disable the test that was generated above that makes sure
  2823   2808         ** this code only executes once.  Because for a non-constant
  2824   2809         ** expression we need to rerun this code each time.
  2825   2810         */
  2826   2811         if( addrOnce && !sqlite3ExprIsConstant(pE2) ){
  2827   2812           sqlite3VdbeChangeToNoop(v, addrOnce);
  2828   2813           addrOnce = 0;
  2829   2814         }
  2830   2815   
  2831   2816         /* Evaluate the expression and insert it into the temp table */
  2832         -      if( isRowid && sqlite3ExprIsInteger(pE2, &iValToIns) ){
  2833         -        sqlite3VdbeAddOp3(v, OP_InsertInt, iTab, r2, iValToIns);
  2834         -      }else{
  2835         -        r3 = sqlite3ExprCodeTarget(pParse, pE2, r1);
  2836         -        if( isRowid ){
  2837         -          sqlite3VdbeAddOp2(v, OP_MustBeInt, r3,
  2838         -                            sqlite3VdbeCurrentAddr(v)+2);
  2839         -          VdbeCoverage(v);
  2840         -          sqlite3VdbeAddOp3(v, OP_Insert, iTab, r2, r3);
  2841         -        }else{
  2842         -          sqlite3VdbeAddOp4(v, OP_MakeRecord, r3, 1, r2, &affinity, 1);
  2843         -          sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iTab, r2, r3, 1);
  2844         -        }
  2845         -      }
         2817  +      r3 = sqlite3ExprCodeTarget(pParse, pE2, r1);
         2818  +      sqlite3VdbeAddOp4(v, OP_MakeRecord, r3, 1, r2, &affinity, 1);
         2819  +      sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iTab, r2, r3, 1);
  2846   2820       }
  2847   2821       sqlite3ReleaseTempReg(pParse, r1);
  2848   2822       sqlite3ReleaseTempReg(pParse, r2);
  2849   2823     }
  2850   2824     if( pKeyInfo ){
  2851   2825       sqlite3VdbeChangeP4(v, addr, (void *)pKeyInfo, P4_KEYINFO);
  2852   2826     }

Changes to src/insert.c.

  1915   1915       pik_flags = (useSeekResult ? OPFLAG_USESEEKRESULT : 0);
  1916   1916       if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
  1917   1917         assert( pParse->nested==0 );
  1918   1918         pik_flags |= OPFLAG_NCHANGE;
  1919   1919         pik_flags |= (update_flags & OPFLAG_SAVEPOSITION);
  1920   1920   #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
  1921   1921         if( update_flags==0 ){
  1922         -        sqlite3VdbeAddOp4(v, OP_InsertInt, 
  1923         -            iIdxCur+i, aRegIdx[i], 0, (char*)pTab, P4_TABLE
         1922  +        int r = sqlite3GetTempReg(pParse);
         1923  +        sqlite3VdbeAddOp2(v, OP_Integer, 0, r);
         1924  +        sqlite3VdbeAddOp4(v, OP_Insert, 
         1925  +            iIdxCur+i, aRegIdx[i], r, (char*)pTab, P4_TABLE
  1924   1926           );
  1925   1927           sqlite3VdbeChangeP5(v, OPFLAG_ISNOOP);
         1928  +        sqlite3ReleaseTempReg(pParse, r);
  1926   1929         }
  1927   1930   #endif
  1928   1931       }
  1929   1932       sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i],
  1930   1933                            aRegIdx[i]+1,
  1931   1934                            pIdx->uniqNotNull ? pIdx->nKeyCol: pIdx->nColumn);
  1932   1935       sqlite3VdbeChangeP5(v, pik_flags);

Changes to src/sqliteInt.h.

  4275   4275   void sqlite3Reindex(Parse*, Token*, Token*);
  4276   4276   void sqlite3AlterFunctions(void);
  4277   4277   void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
  4278   4278   void sqlite3AlterRenameColumn(Parse*, SrcList*, Token*, Token*);
  4279   4279   int sqlite3GetToken(const unsigned char *, int *);
  4280   4280   void sqlite3NestedParse(Parse*, const char*, ...);
  4281   4281   void sqlite3ExpirePreparedStatements(sqlite3*, int);
  4282         -void sqlite3CodeRhsOfIN(Parse*, Expr*, int, int);
         4282  +void sqlite3CodeRhsOfIN(Parse*, Expr*, int);
  4283   4283   int sqlite3CodeSubselect(Parse*, Expr*);
  4284   4284   void sqlite3SelectPrep(Parse*, Select*, NameContext*);
  4285   4285   void sqlite3SelectWrongNumTermsError(Parse *pParse, Select *p);
  4286   4286   int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
  4287   4287   int sqlite3ResolveExprNames(NameContext*, Expr*);
  4288   4288   int sqlite3ResolveExprListNames(NameContext*, ExprList*);
  4289   4289   void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);

Changes to src/vdbe.c.

  4576   4576   ** and register P2 becomes ephemeral.  If the cursor is changed, the
  4577   4577   ** value of register P2 will then change.  Make sure this does not
  4578   4578   ** cause any problems.)
  4579   4579   **
  4580   4580   ** This instruction only works on tables.  The equivalent instruction
  4581   4581   ** for indices is OP_IdxInsert.
  4582   4582   */
  4583         -/* Opcode: InsertInt P1 P2 P3 P4 P5
  4584         -** Synopsis: intkey=P3 data=r[P2]
  4585         -**
  4586         -** This works exactly like OP_Insert except that the key is the
  4587         -** integer value P3, not the value of the integer stored in register P3.
  4588         -*/
  4589         -case OP_Insert: 
  4590         -case OP_InsertInt: {
         4583  +case OP_Insert: {
  4591   4584     Mem *pData;       /* MEM cell holding data for the record to be inserted */
  4592   4585     Mem *pKey;        /* MEM cell holding key  for the record */
  4593   4586     VdbeCursor *pC;   /* Cursor to table into which insert is written */
  4594   4587     int seekResult;   /* Result of prior seek or 0 if no USESEEKRESULT flag */
  4595   4588     const char *zDb;  /* database name - used by the update hook */
  4596   4589     Table *pTab;      /* Table structure - used by update and pre-update hooks */
  4597   4590     BtreePayload x;   /* Payload to be inserted */
................................................................................
  4604   4597     assert( pC->eCurType==CURTYPE_BTREE );
  4605   4598     assert( pC->uc.pCursor!=0 );
  4606   4599     assert( (pOp->p5 & OPFLAG_ISNOOP) || pC->isTable );
  4607   4600     assert( pOp->p4type==P4_TABLE || pOp->p4type>=P4_STATIC );
  4608   4601     REGISTER_TRACE(pOp->p2, pData);
  4609   4602     sqlite3VdbeIncrWriteCounter(p, pC);
  4610   4603   
  4611         -  if( pOp->opcode==OP_Insert ){
  4612         -    pKey = &aMem[pOp->p3];
  4613         -    assert( pKey->flags & MEM_Int );
  4614         -    assert( memIsValid(pKey) );
  4615         -    REGISTER_TRACE(pOp->p3, pKey);
  4616         -    x.nKey = pKey->u.i;
  4617         -  }else{
  4618         -    assert( pOp->opcode==OP_InsertInt );
  4619         -    x.nKey = pOp->p3;
  4620         -  }
         4604  +  pKey = &aMem[pOp->p3];
         4605  +  assert( pKey->flags & MEM_Int );
         4606  +  assert( memIsValid(pKey) );
         4607  +  REGISTER_TRACE(pOp->p3, pKey);
         4608  +  x.nKey = pKey->u.i;
  4621   4609   
  4622   4610     if( pOp->p4type==P4_TABLE && HAS_UPDATE_HOOK(db) ){
  4623   4611       assert( pC->iDb>=0 );
  4624   4612       zDb = db->aDb[pC->iDb].zDbSName;
  4625   4613       pTab = pOp->p4.pTab;
  4626   4614       assert( (pOp->p5 & OPFLAG_ISNOOP) || HasRowid(pTab) );
  4627   4615     }else{

Changes to src/wherecode.c.

   578    578       if( pIn ){
   579    579         int iMap = 0;               /* Index in aiMap[] */
   580    580         pIn += i;
   581    581         for(i=iEq;i<pLoop->nLTerm; i++){
   582    582           if( pLoop->aLTerm[i]->pExpr==pX ){
   583    583             int iOut = iReg + i - iEq;
   584    584             if( eType==IN_INDEX_ROWID ){
   585         -            testcase( nEq>1 );  /* Happens with a UNIQUE index on ROWID */
   586    585               pIn->addrInTop = sqlite3VdbeAddOp2(v, OP_Rowid, iTab, iOut);
   587    586             }else{
   588    587               int iCol = aiMap ? aiMap[iMap++] : 0;
   589    588               pIn->addrInTop = sqlite3VdbeAddOp3(v,OP_Column,iTab, iCol, iOut);
   590    589             }
   591    590             sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v);
   592    591             if( i==iEq ){
................................................................................
  1340   1339       iReleaseReg = ++pParse->nMem;
  1341   1340       iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, bRev, iReleaseReg);
  1342   1341       if( iRowidReg!=iReleaseReg ) sqlite3ReleaseTempReg(pParse, iReleaseReg);
  1343   1342       addrNxt = pLevel->addrNxt;
  1344   1343       sqlite3VdbeAddOp3(v, OP_SeekRowid, iCur, addrNxt, iRowidReg);
  1345   1344       VdbeCoverage(v);
  1346   1345       pLevel->op = OP_Noop;
  1347         -    pTerm->wtFlags |= TERM_CODED;
         1346  +    if( (pTerm->prereqAll & pLevel->notReady)==0 ){
         1347  +      pTerm->wtFlags |= TERM_CODED;
         1348  +    }
  1348   1349     }else if( (pLoop->wsFlags & WHERE_IPK)!=0
  1349   1350            && (pLoop->wsFlags & WHERE_COLUMN_RANGE)!=0
  1350   1351     ){
  1351   1352       /* Case 3:  We have an inequality comparison against the ROWID field.
  1352   1353       */
  1353   1354       int testOp = OP_Noop;
  1354   1355       int start;

Changes to test/where.test.

  1421   1421     CREATE TABLE t1(a INT);
  1422   1422     CREATE INDEX t1a ON t1(a);
  1423   1423     INSERT INTO t1(a) VALUES(NULL),(NULL),(42),(NULL),(NULL);
  1424   1424     CREATE TABLE t2(dummy INT);
  1425   1425     SELECT count(*) FROM t1 LEFT JOIN t2 ON a IS NOT NULL;
  1426   1426   } {5}
  1427   1427   
         1428  +# 20190-02-22:  A bug introduced by checkin
         1429  +# https://www.sqlite.org/src/info/fa792714ae62fa98.
         1430  +#
         1431  +do_execsql_test where-23.0 {
         1432  +  DROP TABLE IF EXISTS t1;
         1433  +  DROP TABLE IF EXISTS t2;
         1434  +  CREATE TABLE t1(a INTEGER PRIMARY KEY);
         1435  +  INSERT INTO t1(a) VALUES(1),(2),(3);
         1436  +  CREATE TABLE t2(x INTEGER PRIMARY KEY, y INT);
         1437  +  INSERT INTO t2(y) VALUES(2),(3);
         1438  +  SELECT * FROM t1, t2 WHERE a=y AND y=3;
         1439  +} {3 2 3}
  1428   1440   
  1429   1441   finish_test