/ Check-in [f320d47d]
Login

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

Overview
Comment:Add support for vector assignments in the SET clause of an UPDATE statement.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1: f320d47d6b7b08d9552c8444004bc256348bda90
User & Date: drh 2016-08-20 00:51:37
Context
2016-08-20
01:06
Improvements to comments. No code changes. check-in: 4165d20f user: drh tags: rowvalue
00:51
Add support for vector assignments in the SET clause of an UPDATE statement. check-in: f320d47d user: drh tags: rowvalue
00:07
Change the way TK_SELECT_COLUMN is handled so that the subquery is only generated once even if part of the vector comparison is used for indexing and the other part is now. This change also is a pathway to vector assignment in UPDATE statements. check-in: d8feea7d user: drh tags: rowvalue
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

   366    366   ** Compute and return a new Expr object which when passed to
   367    367   ** sqlite3ExprCode() will generate all necessary code to compute
   368    368   ** the iField-th column of the vector expression pVector.
   369    369   **
   370    370   ** The caller owns the returned Expr object and is responsible for
   371    371   ** ensuring that the returned value eventually gets freed.
   372    372   **
   373         -** Ownership of pVector is controlled by the takeOwnership parameter.  If
   374         -** takeOwnership is true, this routine takes responsibility for freeing
   375         -** pVector, and may do so before returning, hence the caller must not reference
   376         -** pVector again.  If takeOwnership is false, then the caller takes
   377         -** responsibility for freeing pVector and must ensure the pVector remains
   378         -** valid as long as the returned value remains in use.
          373  +** The caller retains ownership of pVector and must ensure that pVector
          374  +** remains valid as long as the returned value is in use.
   379    375   */
   380    376   Expr *sqlite3ExprForVectorField(
   381    377     Parse *pParse,       /* Parsing context */
   382    378     Expr *pVector,       /* The vector.  List of expressions or a sub-SELECT */
   383         -  int iField,          /* Which column of the vector to return */
   384         -  int takeOwnership    /* True to take ownership of pVector before returning */
          379  +  int iField           /* Which column of the vector to return */
   385    380   ){
   386    381     Expr *pRet;
   387         -  assert( sqlite3ExprIsVector(pVector) );
   388         -  /* FIXME: Add support for takeOwnership!=0 */ assert( takeOwnership==0 );
   389         -  if( pVector->flags & EP_xIsSelect ){
          382  +  if( pVector->op==TK_SELECT ){
          383  +    assert( pVector->flags & EP_xIsSelect );
   390    384       /* The TK_SELECT_COLUMN Expr node:
   391    385       **
   392    386       ** pLeft:           pVector containing TK_SELECT
   393    387       ** pRight:          pVector if ownership taken
   394    388       ** iColumn:         Index of a column in pVector
   395    389       ** pLeft->iTable:   First in an array of register holding result, or 0
   396    390       **                  if the result is not yet computed.
................................................................................
   401    395       ** be multiple TK_SELECT_COLUMN nodes with the same pLeft pointer to 
   402    396       ** the pVector, but only one of them will own the pVector.
   403    397       */
   404    398       pRet = sqlite3PExpr(pParse, TK_SELECT_COLUMN, pVector, 0, 0);
   405    399       if( pRet ) pRet->iColumn = iField;
   406    400       assert( pRet==0 || pRet->iTable==0 );
   407    401     }else{
   408         -    pRet = sqlite3ExprDup(pParse->db, pVector->x.pList->a[iField].pExpr, 0);
          402  +    if( pVector->op==TK_VECTOR ) pVector = pVector->x.pList->a[iField].pExpr;
          403  +    pRet = sqlite3ExprDup(pParse->db, pVector, 0);
   409    404     }
   410    405     return pRet;
   411    406   }
   412    407   #endif /* !define(SQLITE_OMIT_SUBQUERY) */
   413    408   
   414    409   /*
   415    410   ** If expression pExpr is of type TK_SELECT, generate code to evaluate
................................................................................
  1435   1430   
  1436   1431   no_mem:     
  1437   1432     /* Avoid leaking memory if malloc has failed. */
  1438   1433     sqlite3ExprDelete(db, pExpr);
  1439   1434     sqlite3ExprListDelete(db, pList);
  1440   1435     return 0;
  1441   1436   }
         1437  +
         1438  +/*
         1439  +** pColumns and pExpr for a vector assignment, like this:
         1440  +**
         1441  +**        (a,b,c) = (expr1,expr2,expr3)
         1442  +** Or:    (a,b,c) = (SELECT x,y,z FROM ....)
         1443  +**
         1444  +** For each term of the vector assignment, append new entries to the
         1445  +** expression list.  In the case of a subquery on the LHS, append
         1446  +** TK_SELECT_COLUMN expressions.
         1447  +*/
         1448  +ExprList *sqlite3ExprListAppendVector(
         1449  +  Parse *pParse,         /* Parsing context */
         1450  +  ExprList *pList,       /* List to which to append. Might be NULL */
         1451  +  IdList *pColumns,      /* List of names of LHS of the assignment */
         1452  +  Expr *pExpr            /* Vector expression to be appended. Might be NULL */
         1453  +){
         1454  +  sqlite3 *db = pParse->db;
         1455  +  int n;
         1456  +  int i;
         1457  +  if( pColumns==0 ) goto vector_append_error;
         1458  +  if( pExpr==0 ) goto vector_append_error;
         1459  +  n = sqlite3ExprVectorSize(pExpr);
         1460  +  if( pColumns->nId!=n ){
         1461  +    sqlite3ErrorMsg(pParse, "%d columns assigned %d values",
         1462  +                    pColumns->nId, n);
         1463  +    goto vector_append_error;
         1464  +  }
         1465  +  for(i=0; i<n; i++){
         1466  +    Expr *pSubExpr = sqlite3ExprForVectorField(pParse, pExpr, i);
         1467  +    pList = sqlite3ExprListAppend(pParse, pList, pSubExpr);
         1468  +    if( pList ){
         1469  +      pList->a[pList->nExpr-1].zName = pColumns->a[i].zName;
         1470  +      pColumns->a[i].zName = 0;
         1471  +    }
         1472  +  }
         1473  +  if( pExpr->op==TK_SELECT ){
         1474  +    if( pList && pList->a[0].pExpr ){
         1475  +      assert( pList->a[0].pExpr->op==TK_SELECT_COLUMN );
         1476  +      pList->a[0].pExpr->pRight = pExpr;
         1477  +      pExpr = 0;
         1478  +    }
         1479  +  }
         1480  +
         1481  +vector_append_error:
         1482  +  sqlite3ExprDelete(db, pExpr);
         1483  +  sqlite3IdListDelete(db, pColumns);
         1484  +  return pList;
         1485  +}
  1442   1486   
  1443   1487   /*
  1444   1488   ** Set the sort order for the last element on the given ExprList.
  1445   1489   */
  1446   1490   void sqlite3ExprListSetSortOrder(ExprList *p, int iSortOrder){
  1447   1491     if( p==0 ) return;
  1448   1492     assert( SQLITE_SO_UNDEFINED<0 && SQLITE_SO_ASC>=0 && SQLITE_SO_DESC>0 );

Changes to src/parse.y.

   785    785   %type setlist {ExprList*}
   786    786   %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}
   787    787   
   788    788   setlist(A) ::= setlist(A) COMMA nm(X) EQ expr(Y). {
   789    789     A = sqlite3ExprListAppend(pParse, A, Y.pExpr);
   790    790     sqlite3ExprListSetName(pParse, A, &X, 1);
   791    791   }
          792  +setlist(A) ::= setlist(A) COMMA LP idlist(X) RP EQ expr(Y). {
          793  +  A = sqlite3ExprListAppendVector(pParse, A, X, Y.pExpr);
          794  +}
   792    795   setlist(A) ::= nm(X) EQ expr(Y). {
   793    796     A = sqlite3ExprListAppend(pParse, 0, Y.pExpr);
   794    797     sqlite3ExprListSetName(pParse, A, &X, 1);
   795    798   }
          799  +setlist(A) ::= LP idlist(X) RP EQ expr(Y). {
          800  +  A = sqlite3ExprListAppendVector(pParse, 0, X, Y.pExpr);
          801  +}
   796    802   
   797    803   ////////////////////////// The INSERT command /////////////////////////////////
   798    804   //
   799    805   cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S). {
   800    806     sqlite3WithPush(pParse, W, 1);
   801    807     sqlite3Insert(pParse, X, S, F, R);
   802    808   }

Changes to src/sqliteInt.h.

  3543   3543   Expr *sqlite3PExpr(Parse*, int, Expr*, Expr*, const Token*);
  3544   3544   void sqlite3PExprAddSelect(Parse*, Expr*, Select*);
  3545   3545   Expr *sqlite3ExprAnd(sqlite3*,Expr*, Expr*);
  3546   3546   Expr *sqlite3ExprFunction(Parse*,ExprList*, Token*);
  3547   3547   void sqlite3ExprAssignVarNumber(Parse*, Expr*);
  3548   3548   void sqlite3ExprDelete(sqlite3*, Expr*);
  3549   3549   ExprList *sqlite3ExprListAppend(Parse*,ExprList*,Expr*);
         3550  +ExprList *sqlite3ExprListAppendVector(Parse*,ExprList*,IdList*,Expr*);
  3550   3551   void sqlite3ExprListSetSortOrder(ExprList*,int);
  3551   3552   void sqlite3ExprListSetName(Parse*,ExprList*,Token*,int);
  3552   3553   void sqlite3ExprListSetSpan(Parse*,ExprList*,ExprSpan*);
  3553   3554   void sqlite3ExprListDelete(sqlite3*, ExprList*);
  3554   3555   u32 sqlite3ExprListFlags(const ExprList*);
  3555   3556   int sqlite3Init(sqlite3*, char**);
  3556   3557   int sqlite3InitCallback(void*, int, char**, char**);
................................................................................
  4272   4273   #if defined(SQLITE_ENABLE_DBSTAT_VTAB) || defined(SQLITE_TEST)
  4273   4274   int sqlite3DbstatRegister(sqlite3*);
  4274   4275   #endif
  4275   4276   
  4276   4277   int sqlite3ExprVectorSize(Expr *pExpr);
  4277   4278   int sqlite3ExprIsVector(Expr *pExpr);
  4278   4279   Expr *sqlite3VectorFieldSubexpr(Expr*, int);
  4279         -Expr *sqlite3ExprForVectorField(Parse*,Expr*,int,int);
         4280  +Expr *sqlite3ExprForVectorField(Parse*,Expr*,int);
  4280   4281   
  4281   4282   #endif /* SQLITEINT_H */

Changes to src/whereexpr.c.

  1184   1184     )){
  1185   1185       int nLeft = sqlite3ExprVectorSize(pExpr->pLeft);
  1186   1186       if( nLeft==sqlite3ExprVectorSize(pExpr->pRight) ){
  1187   1187         int i;
  1188   1188         for(i=0; i<sqlite3ExprVectorSize(pExpr->pLeft); i++){
  1189   1189           int idxNew;
  1190   1190           Expr *pNew;
  1191         -        Expr *pLeft = sqlite3ExprForVectorField(pParse, pExpr->pLeft, i, 0);
  1192         -        Expr *pRight = sqlite3ExprForVectorField(pParse, pExpr->pRight, i, 0);
         1191  +        Expr *pLeft = sqlite3ExprForVectorField(pParse, pExpr->pLeft, i);
         1192  +        Expr *pRight = sqlite3ExprForVectorField(pParse, pExpr->pRight, i);
  1193   1193   
  1194   1194           pNew = sqlite3PExpr(pParse, pExpr->op, pLeft, pRight, 0);
  1195   1195           idxNew = whereClauseInsert(pWC, pNew, TERM_DYNAMIC);
  1196   1196           exprAnalyze(pSrc, pWC, idxNew);
  1197   1197           markTermAsChild(pWC, idxNew, idxTerm);
  1198   1198         }
  1199   1199         pTerm = &pWC->a[idxTerm];

Added test/rowvalue7.test.

            1  +# 2016-08-18
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# The focus of this file is vector assignments in the SET clause of
           12  +# an UPDATE statement.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set ::testprefix rowvalue7
           18  +
           19  +do_execsql_test 1.1 {
           20  +  CREATE TABLE t1(a,b,c,d);
           21  +  CREATE INDEX t1x ON t1(a,b);
           22  +  INSERT INTO t1(a,b,c,d) VALUES(1,2,0,0),(3,4,0,0),(5,6,0,0);
           23  +  CREATE TABLE t2(w,x,y,z);
           24  +  CREATE INDEX t2x ON t2(w,x);
           25  +  INSERT INTO t2(w,x,y,z) VALUES(1,2,11,22),(8,9,88,99),(3,5,33,55),(5,6,55,66);
           26  +
           27  +  SELECT *,'|' FROM t1 ORDER BY a;
           28  +} {1 2 0 0 | 3 4 0 0 | 5 6 0 0 |}
           29  +
           30  +do_execsql_test 1.2 {
           31  +  UPDATE t1 SET (c,d) = (SELECT y,z FROM t2 WHERE (w,x)=(a,b));
           32  +  SELECT *,'|' FROM t1 ORDER BY a;
           33  +} {1 2 11 22 | 3 4 {} {} | 5 6 55 66 |}
           34  +
           35  +do_execsql_test 1.3 {
           36  +  UPDATE t1 SET (c,d) = (SELECT y,z FROM t2 WHERE w=a);
           37  +  SELECT *,'|' FROM t1 ORDER BY a;
           38  +} {1 2 11 22 | 3 4 33 55 | 5 6 55 66 |}
           39  +
           40  +do_execsql_test 1.4 {
           41  +  UPDATE t1 SET (c) = 99 WHERE a=3;
           42  +  SELECT *,'|' FROM t1 ORDER BY a;
           43  +} {1 2 11 22 | 3 4 99 55 | 5 6 55 66 |}
           44  +
           45  +do_execsql_test 1.5 {
           46  +  UPDATE t1 SET b = 8, (c,d) = (123,456) WHERE a=3;
           47  +  SELECT *,'|' FROM t1 ORDER BY a;
           48  +} {1 2 11 22 | 3 8 123 456 | 5 6 55 66 |}
           49  +
           50  +do_catchsql_test 2.1 {
           51  +  UPDATE t1 SET (c,d) = (SELECT x,y,z FROM t2 WHERE w=a);
           52  +} {1 {2 columns assigned 3 values}}
           53  +
           54  +do_catchsql_test 2.2 {
           55  +  UPDATE t1 SET (b,c,d) = (SELECT x,y FROM t2 WHERE w=a);
           56  +} {1 {3 columns assigned 2 values}}
           57  +
           58  +finish_test