/ Check-in [0203f34f]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add support for the "excluded.*" names in the UPDATE clause of an upsert.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256: 0203f34faae07fbea0bff2d23b81fb37df8854cded4cdadac5a034132a096b6d
User & Date: drh 2018-04-16 13:00:50
Context
2018-04-16
13:26
Remove the MySQL upsert syntax. As an optional alias to the insert table name in order to finish out PostgreSQL upsert syntax emulation. check-in: 810d9f63 user: drh tags: upsert
13:00
Add support for the "excluded.*" names in the UPDATE clause of an upsert. check-in: 0203f34f user: drh tags: upsert
10:47
Merge changes from trunk. check-in: 54d96772 user: drh tags: upsert
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

   804    804         pParse->nMem += pIdx->nColumn;
   805    805       }
   806    806     }
   807    807   #ifndef SQLITE_OMIT_UPSERT
   808    808     if( pUpsert ){
   809    809       pTabList->a[0].iCursor = iDataCur;
   810    810       pUpsert->pUpsertSrc = pTabList;
          811  +    pUpsert->regData = regData;
   811    812       if( pUpsert->pUpsertTarget ){
   812    813         sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert);
   813    814       }
   814    815     }
   815    816   #endif
   816    817   
   817    818   

Changes to src/parse.y.

   822    822   ////////////////////////// The UPDATE command ////////////////////////////////
   823    823   //
   824    824   %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   825    825   cmd ::= with UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
   826    826           where_opt(W) orderby_opt(O) limit_opt(L).  {
   827    827     sqlite3SrcListIndexedBy(pParse, X, &I);
   828    828     sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   829         -  sqlite3Update(pParse,X,Y,W,R,O,L);
          829  +  sqlite3Update(pParse,X,Y,W,R,O,L,0);
   830    830   }
   831    831   %endif
   832    832   %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   833    833   cmd ::= with UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
   834    834           where_opt(W).  {
   835    835     sqlite3SrcListIndexedBy(pParse, X, &I);
   836    836     sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   837         -  sqlite3Update(pParse,X,Y,W,R,0,0);
          837  +  sqlite3Update(pParse,X,Y,W,R,0,0,0);
   838    838   }
   839    839   %endif
   840    840   
   841    841   %type setlist {ExprList*}
   842    842   %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}
   843    843   
   844    844   setlist(A) ::= setlist(A) COMMA nm(X) EQ expr(Y). {

Changes to src/resolve.c.

   187    187     int cntTab = 0;                   /* Number of matching table names */
   188    188     int nSubquery = 0;                /* How many levels of subquery */
   189    189     sqlite3 *db = pParse->db;         /* The database connection */
   190    190     struct SrcList_item *pItem;       /* Use for looping over pSrcList items */
   191    191     struct SrcList_item *pMatch = 0;  /* The matching pSrcList item */
   192    192     NameContext *pTopNC = pNC;        /* First namecontext in the list */
   193    193     Schema *pSchema = 0;              /* Schema of the expression */
   194         -  int isTrigger = 0;                /* True if resolved to a trigger column */
          194  +  int eNewExprOp = TK_COLUMN;       /* New value for pExpr->op on success */
   195    195     Table *pTab = 0;                  /* Table hold the row */
   196    196     Column *pCol;                     /* A column of pTab */
   197    197   
   198    198     assert( pNC );     /* the name context cannot be NULL. */
   199    199     assert( zCol );    /* The Z in X.Y.Z cannot be NULL */
   200    200     assert( !ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced) );
   201    201   
................................................................................
   292    292           if( (pMatch->fg.jointype & JT_LEFT)!=0 ){
   293    293             ExprSetProperty(pExpr, EP_CanBeNull);
   294    294           }
   295    295           pSchema = pExpr->pTab->pSchema;
   296    296         }
   297    297       } /* if( pSrcList ) */
   298    298   
   299         -#ifndef SQLITE_OMIT_TRIGGER
          299  +#if !defined(SQLITE_OMIT_TRIGGER) || !defined(SQLITE_OMIT_UPSERT)
   300    300       /* If we have not already resolved the name, then maybe 
   301         -    ** it is a new.* or old.* trigger argument reference
          301  +    ** it is a new.* or old.* trigger argument reference.  Or
          302  +    ** maybe it is an excluded.* from an upsert.
   302    303       */
   303         -    if( zDb==0 && zTab!=0 && cntTab==0 && pParse->pTriggerTab!=0 ){
   304         -      int op = pParse->eTriggerOp;
   305         -      assert( op==TK_DELETE || op==TK_UPDATE || op==TK_INSERT );
   306         -      if( op!=TK_DELETE && sqlite3StrICmp("new",zTab) == 0 ){
   307         -        pExpr->iTable = 1;
   308         -        pTab = pParse->pTriggerTab;
   309         -      }else if( op!=TK_INSERT && sqlite3StrICmp("old",zTab)==0 ){
   310         -        pExpr->iTable = 0;
   311         -        pTab = pParse->pTriggerTab;
   312         -      }else{
   313         -        pTab = 0;
          304  +    if( zDb==0 && zTab!=0 && cntTab==0 ){
          305  +      pTab = 0;
          306  +#ifndef SQLITE_OMIT_TRIGGER
          307  +      if( pParse->pTriggerTab!=0 ){
          308  +        int op = pParse->eTriggerOp;
          309  +        assert( op==TK_DELETE || op==TK_UPDATE || op==TK_INSERT );
          310  +        if( op!=TK_DELETE && sqlite3StrICmp("new",zTab) == 0 ){
          311  +          pExpr->iTable = 1;
          312  +          pTab = pParse->pTriggerTab;
          313  +        }else if( op!=TK_INSERT && sqlite3StrICmp("old",zTab)==0 ){
          314  +          pExpr->iTable = 0;
          315  +          pTab = pParse->pTriggerTab;
          316  +        }
   314    317         }
          318  +#endif /* SQLITE_OMIT_TRIGGER */
          319  +#ifndef SQLITE_OMIT_UPSERT
          320  +      if( (pNC->ncFlags & NC_UUpsert)!=0 ){
          321  +        Upsert *pUpsert = pNC->uNC.pUpsert;
          322  +        if( pUpsert && sqlite3StrICmp("excluded",zTab)==0 ){
          323  +          pTab = pUpsert->pUpsertSrc->a[0].pTab;
          324  +          pExpr->iTable = 2;
          325  +        }
          326  +      }
          327  +#endif /* SQLITE_OMIT_UPSERT */
   315    328   
   316    329         if( pTab ){ 
   317    330           int iCol;
   318    331           pSchema = pTab->pSchema;
   319    332           cntTab++;
   320    333           for(iCol=0, pCol=pTab->aCol; iCol<pTab->nCol; iCol++, pCol++){
   321    334             if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
................................................................................
   327    340           }
   328    341           if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) && VisibleRowid(pTab) ){
   329    342             /* IMP: R-51414-32910 */
   330    343             iCol = -1;
   331    344           }
   332    345           if( iCol<pTab->nCol ){
   333    346             cnt++;
   334         -          if( iCol<0 ){
   335         -            pExpr->affinity = SQLITE_AFF_INTEGER;
   336         -          }else if( pExpr->iTable==0 ){
   337         -            testcase( iCol==31 );
   338         -            testcase( iCol==32 );
   339         -            pParse->oldmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol));
   340         -          }else{
   341         -            testcase( iCol==31 );
   342         -            testcase( iCol==32 );
   343         -            pParse->newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol));
          347  +#ifndef SQLITE_OMIT_UPSERT
          348  +          if( pExpr->iTable==2 ){
          349  +            pExpr->iTable = pNC->uNC.pUpsert->regData + iCol;
          350  +            eNewExprOp = TK_REGISTER;
          351  +          }else
          352  +#endif
          353  +          {
          354  +#ifndef SQLITE_OMIT_TRIGGER
          355  +            if( iCol<0 ){
          356  +              pExpr->affinity = SQLITE_AFF_INTEGER;
          357  +            }else if( pExpr->iTable==0 ){
          358  +              testcase( iCol==31 );
          359  +              testcase( iCol==32 );
          360  +              pParse->oldmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol));
          361  +            }else{
          362  +              testcase( iCol==31 );
          363  +              testcase( iCol==32 );
          364  +              pParse->newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol));
          365  +            }
          366  +            pExpr->pTab = pTab;
          367  +            pExpr->iColumn = (i16)iCol;
          368  +            eNewExprOp = TK_TRIGGER;
          369  +#endif /* SQLITE_OMIT_TRIGGER
   344    370             }
   345         -          pExpr->iColumn = (i16)iCol;
   346         -          pExpr->pTab = pTab;
   347         -          isTrigger = 1;
   348    371           }
   349    372         }
   350    373       }
   351         -#endif /* !defined(SQLITE_OMIT_TRIGGER) */
          374  +#endif /* !defined(SQLITE_OMIT_TRIGGER) || !defined(SQLITE_OMIT_UPSERT) */
   352    375   
   353    376       /*
   354    377       ** Perhaps the name is a reference to the ROWID
   355    378       */
   356    379       if( cnt==0
   357    380        && cntTab==1
   358    381        && pMatch
................................................................................
   481    504   
   482    505     /* Clean up and return
   483    506     */
   484    507     sqlite3ExprDelete(db, pExpr->pLeft);
   485    508     pExpr->pLeft = 0;
   486    509     sqlite3ExprDelete(db, pExpr->pRight);
   487    510     pExpr->pRight = 0;
   488         -  pExpr->op = (isTrigger ? TK_TRIGGER : TK_COLUMN);
          511  +  pExpr->op = eNewExprOp;
   489    512     ExprSetProperty(pExpr, EP_Leaf);
   490    513   lookupname_end:
   491    514     if( cnt==1 ){
   492    515       assert( pNC!=0 );
   493    516       if( !ExprHasProperty(pExpr, EP_Alias) ){
   494    517         sqlite3AuthRead(pParse, pExpr, pSchema, pNC->pSrcList);
   495    518       }
................................................................................
  1297   1320       ** other expressions in the SELECT statement. This is so that
  1298   1321       ** expressions in the WHERE clause (etc.) can refer to expressions by
  1299   1322       ** aliases in the result set.
  1300   1323       **
  1301   1324       ** Minor point: If this is the case, then the expression will be
  1302   1325       ** re-evaluated for each reference to it.
  1303   1326       */
  1304         -    assert( (sNC.ncFlags & (NC_UAggInfo))==0 );
         1327  +    assert( (sNC.ncFlags & (NC_UAggInfo|NC_UUpsert))==0 );
  1305   1328       sNC.uNC.pEList = p->pEList;
  1306   1329       sNC.ncFlags |= NC_UEList;
  1307   1330       if( sqlite3ResolveExprNames(&sNC, p->pHaving) ) return WRC_Abort;
  1308   1331       if( sqlite3ResolveExprNames(&sNC, p->pWhere) ) return WRC_Abort;
  1309   1332   
  1310   1333       /* Resolve names in table-valued-function arguments */
  1311   1334       for(i=0; i<p->pSrc->nSrc; i++){

Changes to src/sqliteInt.h.

  2681   2681   */
  2682   2682   struct NameContext {
  2683   2683     Parse *pParse;       /* The parser */
  2684   2684     SrcList *pSrcList;   /* One or more tables used to resolve names */
  2685   2685     union {
  2686   2686       ExprList *pEList;    /* Optional list of result-set columns */
  2687   2687       AggInfo *pAggInfo;   /* Information about aggregates at this level */
         2688  +    Upsert *pUpsert;     /* ON CONFLICT clause information from an upsert */
  2688   2689     } uNC;
  2689   2690     NameContext *pNext;  /* Next outer name context.  NULL for outermost */
  2690   2691     int nRef;            /* Number of names resolved by this context */
  2691   2692     int nErr;            /* Number of errors encountered while resolving names */
  2692   2693     u16 ncFlags;         /* Zero or more NC_* flags defined below */
  2693   2694   };
  2694   2695   
................................................................................
  2705   2706   #define NC_IsCheck   0x0004  /* True if resolving names in a CHECK constraint */
  2706   2707   #define NC_InAggFunc 0x0008  /* True if analyzing arguments to an agg func */
  2707   2708   #define NC_HasAgg    0x0010  /* One or more aggregate functions seen */
  2708   2709   #define NC_IdxExpr   0x0020  /* True if resolving columns of CREATE INDEX */
  2709   2710   #define NC_VarSelect 0x0040  /* A correlated subquery has been seen */
  2710   2711   #define NC_UEList    0x0080  /* True if uNC.pEList is used */
  2711   2712   #define NC_UAggInfo  0x0100  /* True if uNC.pAggInfo is used */
         2713  +#define NC_UUpsert   0x0200  /* True if uNC.pUpsert is used */
  2712   2714   #define NC_MinMaxAgg 0x1000  /* min/max aggregates seen.  See note above */
  2713   2715   #define NC_Complex   0x2000  /* True if a function or subquery seen */
  2714   2716   
  2715   2717   /*
  2716   2718   ** An instance of the following object describes a single ON CONFLICT
  2717   2719   ** clause in an upsert.
  2718   2720   **
................................................................................
  2729   2731   struct Upsert {
  2730   2732     ExprList *pUpsertTarget;  /* Optional description of conflicting index */
  2731   2733     Expr *pUpsertTargetWhere; /* WHERE clause for partial index targets */
  2732   2734     Index *pUpsertIdx;        /* Constraint that pUpsertTarget identifies */
  2733   2735     ExprList *pUpsertSet;     /* The SET clause from an ON CONFLICT UPDATE */
  2734   2736     Expr *pUpsertWhere;       /* WHERE clause for the ON CONFLICT UPDATE */
  2735   2737     SrcList *pUpsertSrc;      /* Table to be updated */
         2738  +  int regData;              /* First register holding array of VALUES */
  2736   2739   };
  2737   2740   
  2738   2741   /*
  2739   2742   ** An instance of the following structure contains all information
  2740   2743   ** needed to generate code for a single SELECT statement.
  2741   2744   **
  2742   2745   ** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
................................................................................
  3794   3797   Table *sqlite3SrcListLookup(Parse*, SrcList*);
  3795   3798   int sqlite3IsReadOnly(Parse*, Table*, int);
  3796   3799   void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
  3797   3800   #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
  3798   3801   Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,char*);
  3799   3802   #endif
  3800   3803   void sqlite3DeleteFrom(Parse*, SrcList*, Expr*, ExprList*, Expr*);
  3801         -void sqlite3Update(Parse*, SrcList*, ExprList*,Expr*,int,ExprList*,Expr*);
         3804  +void sqlite3Update(Parse*, SrcList*, ExprList*,Expr*,int,ExprList*,Expr*,
         3805  +                   Upsert*);
  3802   3806   WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int);
  3803   3807   void sqlite3WhereEnd(WhereInfo*);
  3804   3808   LogEst sqlite3WhereOutputRowCount(WhereInfo*);
  3805   3809   int sqlite3WhereIsDistinct(WhereInfo*);
  3806   3810   int sqlite3WhereIsOrdered(WhereInfo*);
  3807   3811   int sqlite3WhereOrderedInnerLoop(WhereInfo*);
  3808   3812   int sqlite3WhereIsSorted(WhereInfo*);

Changes to src/trigger.c.

   746    746   
   747    747       switch( pStep->op ){
   748    748         case TK_UPDATE: {
   749    749           sqlite3Update(pParse, 
   750    750             targetSrcList(pParse, pStep),
   751    751             sqlite3ExprListDup(db, pStep->pExprList, 0), 
   752    752             sqlite3ExprDup(db, pStep->pWhere, 0), 
   753         -          pParse->eOrconf, 0, 0
          753  +          pParse->eOrconf, 0, 0, 0
   754    754           );
   755    755           break;
   756    756         }
   757    757         case TK_INSERT: {
   758    758           sqlite3Insert(pParse, 
   759    759             targetSrcList(pParse, pStep),
   760    760             sqlite3SelectDup(db, pStep->pSelect, 0), 

Changes to src/update.c.

    89     89   void sqlite3Update(
    90     90     Parse *pParse,         /* The parser context */
    91     91     SrcList *pTabList,     /* The table in which we should change things */
    92     92     ExprList *pChanges,    /* Things to be changed */
    93     93     Expr *pWhere,          /* The WHERE clause.  May be null */
    94     94     int onError,           /* How to handle constraint errors */
    95     95     ExprList *pOrderBy,    /* ORDER BY clause. May be null */
    96         -  Expr *pLimit           /* LIMIT clause. May be null */
           96  +  Expr *pLimit,          /* LIMIT clause. May be null */
           97  +  Upsert *pUpsert        /* ON CONFLICT clause, or null */
    97     98   ){
    98     99     int i, j;              /* Loop counters */
    99    100     Table *pTab;           /* The table to be updated */
   100    101     int addrTop = 0;       /* VDBE instruction address of the start of the loop */
   101    102     WhereInfo *pWInfo;     /* Information about the WHERE clause */
   102    103     Vdbe *v;               /* The virtual database engine */
   103    104     Index *pIdx;           /* For looping over indices */
................................................................................
   222    223     aToOpen[nIdx+1] = 0;
   223    224     for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;
   224    225   
   225    226     /* Initialize the name-context */
   226    227     memset(&sNC, 0, sizeof(sNC));
   227    228     sNC.pParse = pParse;
   228    229     sNC.pSrcList = pTabList;
          230  +  sNC.uNC.pUpsert = pUpsert;
          231  +  sNC.ncFlags = NC_UUpsert;
   229    232   
   230    233     /* Resolve the column names in all the expressions of the
   231    234     ** of the UPDATE statement.  Also find the column index
   232    235     ** for each column to be updated in the pChanges array.  For each
   233    236     ** column to be updated, make sure we have authorization to change
   234    237     ** that column.
   235    238     */

Changes to src/upsert.c.

   247    247         }
   248    248         pWhere = sqlite3ExprAnd(db,pWhere,sqlite3PExpr(pParse, TK_EQ, pE1, pE2));
   249    249       }
   250    250     }
   251    251     pSrc = sqlite3SrcListDup(db, pUpsert->pUpsertSrc, 0);
   252    252     sqlite3Update(pParse, pSrc, 
   253    253         sqlite3ExprListDup(db, pUpsert->pUpsertSet, 0),
   254         -      pWhere, OE_Abort, 0, 0);
          254  +      pWhere, OE_Abort, 0, 0, pUpsert);
   255    255     VdbeNoopComment((v, "End DO UPDATE of UPSERT"));
   256    256   }
   257    257   
   258    258   #endif /* SQLITE_OMIT_UPSERT */