Merge the latest trunk fixes and enhancements into the right-join branch.

FossilOrigin-Name: afbcf075c1e09ae064c7b16e63501cd1d374711812664aef76bd35d02d64a2b6

5 files changed, 58 insertions, 18 deletions

diff --git a/src/expr.c b/src/expr.c
index 2bc5eae20..77b5e0ad6 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -2660,7 +2660,7 @@ static int sqlite3InRhsIsConstant(Expr *pIn){
 ** all members of the RHS set, skipping duplicates.
 **
 ** A cursor is opened on the b-tree object that is the RHS of the IN operator
-** and pX->iTable is set to the index of that cursor.
+** and the *piTab parameter is set to the index of that cursor.
 **
 ** The returned value of this function indicates the b-tree type, as follows:
 **
@@ -2680,7 +2680,10 @@ static int sqlite3InRhsIsConstant(Expr *pIn){
 ** If the RHS of the IN operator is a list or a more complex subquery, then
 ** an ephemeral table might need to be generated from the RHS and then
 ** pX->iTable made to point to the ephemeral table instead of an
-** existing table.
+** existing table.  In this case, the creation and initialization of the
+** ephmeral table might be put inside of a subroutine, the EP_Subrtn flag
+** will be set on pX and the pX->y.sub fields will be set to show where
+** the subroutine is coded.
 **
 ** The inFlags parameter must contain, at a minimum, one of the bits
 ** IN_INDEX_MEMBERSHIP or IN_INDEX_LOOP but not both.  If inFlags contains
@@ -2741,12 +2744,17 @@ int sqlite3FindInIndex(
 ){
   Select *p;                            /* SELECT to the right of IN operator */
   int eType = 0;                        /* Type of RHS table. IN_INDEX_* */
-  int iTab = pParse->nTab++;            /* Cursor of the RHS table */
+  int iTab;                             /* Cursor of the RHS table */
   int mustBeUnique;                     /* True if RHS must be unique */
   Vdbe *v = sqlite3GetVdbe(pParse);     /* Virtual machine being coded */
 
   assert( pX->op==TK_IN );
   mustBeUnique = (inFlags & IN_INDEX_LOOP)!=0;
+  if( pX->iTable && (inFlags & IN_INDEX_REUSE_CUR)!=0 ){
+    iTab = pX->iTable;
+  }else{
+    iTab = pParse->nTab++;
+  }
 
   /* If the RHS of this IN(...) operator is a SELECT, and if it matters 
   ** whether or not the SELECT result contains NULL values, check whether
@@ -2912,6 +2920,8 @@ int sqlite3FindInIndex(
    && ExprUseXList(pX)
    && (!sqlite3InRhsIsConstant(pX) || pX->x.pList->nExpr<=2)
   ){
+    pParse->nTab--;  /* Back out the allocation of the unused cursor */
+    iTab = -1;       /* Cursor is not allocated */
     eType = IN_INDEX_NOOP;
   }
 
@@ -3078,7 +3088,9 @@ void sqlite3CodeRhsOfIN(
       assert( ExprUseYSub(pExpr) );
       sqlite3VdbeAddOp2(v, OP_Gosub, pExpr->y.sub.regReturn,
                         pExpr->y.sub.iAddr);
-      sqlite3VdbeAddOp2(v, OP_OpenDup, iTab, pExpr->iTable);
+      if( iTab!=pExpr->iTable ){
+        sqlite3VdbeAddOp2(v, OP_OpenDup, iTab, pExpr->iTable);
+      }
       sqlite3VdbeJumpHere(v, addrOnce);
       return;
     }
diff --git a/src/loadext.c b/src/loadext.c
index cefe2eb94..bba431096 100644
--- a/src/loadext.c
+++ b/src/loadext.c
@@ -487,11 +487,19 @@ static const sqlite3_api_routines sqlite3Apis = {
   sqlite3_autovacuum_pages,
   /* Version 3.38.0 and later */
   sqlite3_error_offset,
+#ifndef SQLITE_OMIT_VIRTUALTABLE
   sqlite3_vtab_rhs_value,
   sqlite3_vtab_distinct,
   sqlite3_vtab_in,
   sqlite3_vtab_in_first,
   sqlite3_vtab_in_next,
+#else
+  0,
+  0,
+  0,
+  0,
+  0,
+#endif
   /* Version 3.39.0 and later */
 #ifndef SQLITE_OMIT_DESERIALIZE
   sqlite3_deserialize,
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index f483cc498..0715cae3c 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -5317,6 +5317,7 @@ const char *sqlite3JournalModename(int);
 #define IN_INDEX_NOOP_OK     0x0001  /* OK to return IN_INDEX_NOOP */
 #define IN_INDEX_MEMBERSHIP  0x0002  /* IN operator used for membership test */
 #define IN_INDEX_LOOP        0x0004  /* IN operator used as a loop */
+#define IN_INDEX_REUSE_CUR   0x0008  /* Reuse prior table cursor */
 int sqlite3FindInIndex(Parse *, Expr *, u32, int*, int*, int*);
 
 int sqlite3JournalOpen(sqlite3_vfs *, const char *, sqlite3_file *, int, int);
diff --git a/src/treeview.c b/src/treeview.c
index 04ee80f7a..b498692ba 100644
--- a/src/treeview.c
+++ b/src/treeview.c
@@ -122,7 +122,9 @@ void sqlite3TreeViewColumnList(
     }
     if( flg & COLFLAG_PRIMKEY ) printf(" PRIMARY KEY");
     if( flg & COLFLAG_HIDDEN ) printf(" HIDDEN");
+#ifdef COLFLAG_NOEXPAND
     if( flg & COLFLAG_NOEXPAND ) printf(" NO-EXPAND");
+#endif
     if( flg ) printf(" flags=%04x", flg);
     printf("\n");      
     fflush(stdout);
@@ -216,18 +218,17 @@ void sqlite3TreeViewSrcList(TreeView *pView, const SrcList *pSrc){
     sqlite3StrAccumFinish(&x);
     sqlite3TreeViewItem(pView, zLine, i<pSrc->nSrc-1);
     n = 0;
-    if( pItem->pTab ) n++;
     if( pItem->pSelect ) n++;
     if( pItem->fg.isTabFunc ) n++;
     if( pItem->fg.isUsing ) n++;
     if( pItem->fg.isUsing ){
       sqlite3TreeViewIdList(pView, pItem->u3.pUsing, (--n)>0, "USING");
     }
-    if( pItem->pTab ){
-      Table *pTab = pItem->pTab;
-      sqlite3TreeViewColumnList(pView, pTab->aCol, pTab->nCol, (--n)>0);
-    }
     if( pItem->pSelect ){
+      if( pItem->pTab ){
+        Table *pTab = pItem->pTab;
+        sqlite3TreeViewColumnList(pView, pTab->aCol, pTab->nCol, 1);
+      }
       assert( pItem->fg.isNestedFrom == IsNestedFrom(pItem->pSelect) );
       sqlite3TreeViewSelect(pView, pItem->pSelect, (--n)>0);
     }
@@ -396,6 +397,7 @@ void sqlite3TreeViewBound(
 */
 void sqlite3TreeViewWindow(TreeView *pView, const Window *pWin, u8 more){
   int nElement = 0;
+  if( pWin==0 ) return;
   if( pWin->pFilter ){
     sqlite3TreeViewItem(pView, "FILTER", 1);
     sqlite3TreeViewExpr(pView, pWin->pFilter, 0);
@@ -711,7 +713,17 @@ void sqlite3TreeViewExpr(TreeView *pView, const Expr *pExpr, u8 moreToFollow){
       break;
     }
     case TK_IN: {
-      sqlite3TreeViewLine(pView, "IN flags=0x%x", pExpr->flags);
+      sqlite3_str *pStr = sqlite3_str_new(0);
+      char *z;
+      sqlite3_str_appendf(pStr, "IN flags=0x%x", pExpr->flags);
+      if( pExpr->iTable ) sqlite3_str_appendf(pStr, " iTable=%d",pExpr->iTable);
+      if( ExprHasProperty(pExpr, EP_Subrtn) ){
+        sqlite3_str_appendf(pStr, " subrtn(%d,%d)",
+            pExpr->y.sub.regReturn, pExpr->y.sub.iAddr);
+      }
+      z = sqlite3_str_finish(pStr);
+      sqlite3TreeViewLine(pView, z);
+      sqlite3_free(z);
       sqlite3TreeViewExpr(pView, pExpr->pLeft, 1);
       if( ExprUseXSelect(pExpr) ){
         sqlite3TreeViewSelect(pView, pExpr->x.pSelect, 0);
diff --git a/src/wherecode.c b/src/wherecode.c
index 3ea6c5d8e..d4470ac4d 100644
--- a/src/wherecode.c
+++ b/src/wherecode.c
@@ -611,16 +611,22 @@ static int codeEqualityTerm(
     if( !ExprUseXSelect(pX) || pX->x.pSelect->pEList->nExpr==1 ){
       eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, 0, &iTab);
     }else{
-      sqlite3 *db = pParse->db;
-      pX = removeUnindexableInClauseTerms(pParse, iEq, pLoop, pX);
-
-      if( !db->mallocFailed ){
+      Expr *pExpr = pTerm->pExpr;
+      if( pExpr->iTable==0 || !ExprHasProperty(pExpr, EP_Subrtn) ){
+        sqlite3 *db = pParse->db;
+        pX = removeUnindexableInClauseTerms(pParse, iEq, pLoop, pX);
+        if( !db->mallocFailed ){
+          aiMap = (int*)sqlite3DbMallocZero(pParse->db, sizeof(int)*nEq);
+          eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, aiMap,&iTab);
+          pExpr->iTable = iTab;
+        }
+        sqlite3ExprDelete(db, pX);
+      }else{
         aiMap = (int*)sqlite3DbMallocZero(pParse->db, sizeof(int)*nEq);
-        eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, aiMap, &iTab);
-        pTerm->pExpr->iTable = iTab;
+        eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP|IN_INDEX_REUSE_CUR,                                   0, aiMap,&iTab);
+        iTab = pExpr->iTable;
       }
-      sqlite3ExprDelete(db, pX);
-      pX = pTerm->pExpr;
+      pX = pExpr;
     }
 
     if( eType==IN_INDEX_INDEX_DESC ){
@@ -1247,6 +1253,7 @@ static void preserveExpr(IdxExprTrans *pTrans, Expr *pExpr){
 static int whereIndexExprTransNode(Walker *p, Expr *pExpr){
   IdxExprTrans *pX = p->u.pIdxTrans;
   if( sqlite3ExprCompare(0, pExpr, pX->pIdxExpr, pX->iTabCur)==0 ){
+    pExpr = sqlite3ExprSkipCollate(pExpr);
     preserveExpr(pX, pExpr);
     pExpr->affExpr = sqlite3ExprAffinity(pExpr);
     pExpr->op = TK_COLUMN;