/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388

389
390
391
392
393
394
395
396
...
401
402
403
404
405
406
407

408
409
410
411
412
413
414
415
....
1435
1436
1437
1438
1439
1440
1441

















































1442
1443
1444
1445
1446
1447
1448
** Compute and return a new Expr object which when passed to
** sqlite3ExprCode() will generate all necessary code to compute
** the iField-th column of the vector expression pVector.
**
** The caller owns the returned Expr object and is responsible for
** ensuring that the returned value eventually gets freed.
**
** Ownership of pVector is controlled by the takeOwnership parameter.  If
** takeOwnership is true, this routine takes responsibility for freeing
** pVector, and may do so before returning, hence the caller must not reference
** pVector again.  If takeOwnership is false, then the caller takes
** responsibility for freeing pVector and must ensure the pVector remains
** valid as long as the returned value remains in use.
*/
Expr *sqlite3ExprForVectorField(
  Parse *pParse,       /* Parsing context */
  Expr *pVector,       /* The vector.  List of expressions or a sub-SELECT */
  int iField,          /* Which column of the vector to return */
  int takeOwnership    /* True to take ownership of pVector before returning */
){
  Expr *pRet;
  assert( sqlite3ExprIsVector(pVector) );
  /* FIXME: Add support for takeOwnership!=0 */ assert( takeOwnership==0 );

  if( pVector->flags & EP_xIsSelect ){
    /* The TK_SELECT_COLUMN Expr node:
    **
    ** pLeft:           pVector containing TK_SELECT
    ** pRight:          pVector if ownership taken
    ** iColumn:         Index of a column in pVector
    ** pLeft->iTable:   First in an array of register holding result, or 0
    **                  if the result is not yet computed.
................................................................................
    ** be multiple TK_SELECT_COLUMN nodes with the same pLeft pointer to 
    ** the pVector, but only one of them will own the pVector.
    */
    pRet = sqlite3PExpr(pParse, TK_SELECT_COLUMN, pVector, 0, 0);
    if( pRet ) pRet->iColumn = iField;
    assert( pRet==0 || pRet->iTable==0 );
  }else{

    pRet = sqlite3ExprDup(pParse->db, pVector->x.pList->a[iField].pExpr, 0);
  }
  return pRet;
}
#endif /* !define(SQLITE_OMIT_SUBQUERY) */

/*
** If expression pExpr is of type TK_SELECT, generate code to evaluate
................................................................................

no_mem:     
  /* Avoid leaking memory if malloc has failed. */
  sqlite3ExprDelete(db, pExpr);
  sqlite3ExprListDelete(db, pList);
  return 0;
}


















































/*
** Set the sort order for the last element on the given ExprList.
*/
void sqlite3ExprListSetSortOrder(ExprList *p, int iSortOrder){
  if( p==0 ) return;
  assert( SQLITE_SO_UNDEFINED<0 && SQLITE_SO_ASC>=0 && SQLITE_SO_DESC>0 );







|
<
<
<
<
|




|
<


<
<
>
|







 







>
|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







366
367
368
369
370
371
372
373




374
375
376
377
378
379

380
381


382
383
384
385
386
387
388
389
390
...
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
....
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
** Compute and return a new Expr object which when passed to
** sqlite3ExprCode() will generate all necessary code to compute
** the iField-th column of the vector expression pVector.
**
** The caller owns the returned Expr object and is responsible for
** ensuring that the returned value eventually gets freed.
**
** The caller retains ownership of pVector and must ensure that pVector




** remains valid as long as the returned value is in use.
*/
Expr *sqlite3ExprForVectorField(
  Parse *pParse,       /* Parsing context */
  Expr *pVector,       /* The vector.  List of expressions or a sub-SELECT */
  int iField           /* Which column of the vector to return */

){
  Expr *pRet;


  if( pVector->op==TK_SELECT ){
    assert( pVector->flags & EP_xIsSelect );
    /* The TK_SELECT_COLUMN Expr node:
    **
    ** pLeft:           pVector containing TK_SELECT
    ** pRight:          pVector if ownership taken
    ** iColumn:         Index of a column in pVector
    ** pLeft->iTable:   First in an array of register holding result, or 0
    **                  if the result is not yet computed.
................................................................................
    ** be multiple TK_SELECT_COLUMN nodes with the same pLeft pointer to 
    ** the pVector, but only one of them will own the pVector.
    */
    pRet = sqlite3PExpr(pParse, TK_SELECT_COLUMN, pVector, 0, 0);
    if( pRet ) pRet->iColumn = iField;
    assert( pRet==0 || pRet->iTable==0 );
  }else{
    if( pVector->op==TK_VECTOR ) pVector = pVector->x.pList->a[iField].pExpr;
    pRet = sqlite3ExprDup(pParse->db, pVector, 0);
  }
  return pRet;
}
#endif /* !define(SQLITE_OMIT_SUBQUERY) */

/*
** If expression pExpr is of type TK_SELECT, generate code to evaluate
................................................................................

no_mem:     
  /* Avoid leaking memory if malloc has failed. */
  sqlite3ExprDelete(db, pExpr);
  sqlite3ExprListDelete(db, pList);
  return 0;
}

/*
** pColumns and pExpr for a vector assignment, like this:
**
**        (a,b,c) = (expr1,expr2,expr3)
** Or:    (a,b,c) = (SELECT x,y,z FROM ....)
**
** For each term of the vector assignment, append new entries to the
** expression list.  In the case of a subquery on the LHS, append
** TK_SELECT_COLUMN expressions.
*/
ExprList *sqlite3ExprListAppendVector(
  Parse *pParse,         /* Parsing context */
  ExprList *pList,       /* List to which to append. Might be NULL */
  IdList *pColumns,      /* List of names of LHS of the assignment */
  Expr *pExpr            /* Vector expression to be appended. Might be NULL */
){
  sqlite3 *db = pParse->db;
  int n;
  int i;
  if( pColumns==0 ) goto vector_append_error;
  if( pExpr==0 ) goto vector_append_error;
  n = sqlite3ExprVectorSize(pExpr);
  if( pColumns->nId!=n ){
    sqlite3ErrorMsg(pParse, "%d columns assigned %d values",
                    pColumns->nId, n);
    goto vector_append_error;
  }
  for(i=0; i<n; i++){
    Expr *pSubExpr = sqlite3ExprForVectorField(pParse, pExpr, i);
    pList = sqlite3ExprListAppend(pParse, pList, pSubExpr);
    if( pList ){
      pList->a[pList->nExpr-1].zName = pColumns->a[i].zName;
      pColumns->a[i].zName = 0;
    }
  }
  if( pExpr->op==TK_SELECT ){
    if( pList && pList->a[0].pExpr ){
      assert( pList->a[0].pExpr->op==TK_SELECT_COLUMN );
      pList->a[0].pExpr->pRight = pExpr;
      pExpr = 0;
    }
  }

vector_append_error:
  sqlite3ExprDelete(db, pExpr);
  sqlite3IdListDelete(db, pColumns);
  return pList;
}

/*
** Set the sort order for the last element on the given ExprList.
*/
void sqlite3ExprListSetSortOrder(ExprList *p, int iSortOrder){
  if( p==0 ) return;
  assert( SQLITE_SO_UNDEFINED<0 && SQLITE_SO_ASC>=0 && SQLITE_SO_DESC>0 );

Changes to src/parse.y.

785
786
787
788
789
790
791



792
793
794
795



796
797
798
799
800
801
802
%type setlist {ExprList*}
%destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}

setlist(A) ::= setlist(A) COMMA nm(X) EQ expr(Y). {
  A = sqlite3ExprListAppend(pParse, A, Y.pExpr);
  sqlite3ExprListSetName(pParse, A, &X, 1);
}



setlist(A) ::= nm(X) EQ expr(Y). {
  A = sqlite3ExprListAppend(pParse, 0, Y.pExpr);
  sqlite3ExprListSetName(pParse, A, &X, 1);
}




////////////////////////// The INSERT command /////////////////////////////////
//
cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S). {
  sqlite3WithPush(pParse, W, 1);
  sqlite3Insert(pParse, X, S, F, R);
}







>
>
>




>
>
>







785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
%type setlist {ExprList*}
%destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}

setlist(A) ::= setlist(A) COMMA nm(X) EQ expr(Y). {
  A = sqlite3ExprListAppend(pParse, A, Y.pExpr);
  sqlite3ExprListSetName(pParse, A, &X, 1);
}
setlist(A) ::= setlist(A) COMMA LP idlist(X) RP EQ expr(Y). {
  A = sqlite3ExprListAppendVector(pParse, A, X, Y.pExpr);
}
setlist(A) ::= nm(X) EQ expr(Y). {
  A = sqlite3ExprListAppend(pParse, 0, Y.pExpr);
  sqlite3ExprListSetName(pParse, A, &X, 1);
}
setlist(A) ::= LP idlist(X) RP EQ expr(Y). {
  A = sqlite3ExprListAppendVector(pParse, 0, X, Y.pExpr);
}

////////////////////////// The INSERT command /////////////////////////////////
//
cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S). {
  sqlite3WithPush(pParse, W, 1);
  sqlite3Insert(pParse, X, S, F, R);
}

Changes to src/sqliteInt.h.

3543
3544
3545
3546
3547
3548
3549

3550
3551
3552
3553
3554
3555
3556
....
4272
4273
4274
4275
4276
4277
4278
4279
4280
4281
Expr *sqlite3PExpr(Parse*, int, Expr*, Expr*, const Token*);
void sqlite3PExprAddSelect(Parse*, Expr*, Select*);
Expr *sqlite3ExprAnd(sqlite3*,Expr*, Expr*);
Expr *sqlite3ExprFunction(Parse*,ExprList*, Token*);
void sqlite3ExprAssignVarNumber(Parse*, Expr*);
void sqlite3ExprDelete(sqlite3*, Expr*);
ExprList *sqlite3ExprListAppend(Parse*,ExprList*,Expr*);

void sqlite3ExprListSetSortOrder(ExprList*,int);
void sqlite3ExprListSetName(Parse*,ExprList*,Token*,int);
void sqlite3ExprListSetSpan(Parse*,ExprList*,ExprSpan*);
void sqlite3ExprListDelete(sqlite3*, ExprList*);
u32 sqlite3ExprListFlags(const ExprList*);
int sqlite3Init(sqlite3*, char**);
int sqlite3InitCallback(void*, int, char**, char**);
................................................................................
#if defined(SQLITE_ENABLE_DBSTAT_VTAB) || defined(SQLITE_TEST)
int sqlite3DbstatRegister(sqlite3*);
#endif

int sqlite3ExprVectorSize(Expr *pExpr);
int sqlite3ExprIsVector(Expr *pExpr);
Expr *sqlite3VectorFieldSubexpr(Expr*, int);
Expr *sqlite3ExprForVectorField(Parse*,Expr*,int,int);

#endif /* SQLITEINT_H */







>







 







|


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

int sqlite3ExprVectorSize(Expr *pExpr);
int sqlite3ExprIsVector(Expr *pExpr);
Expr *sqlite3VectorFieldSubexpr(Expr*, int);
Expr *sqlite3ExprForVectorField(Parse*,Expr*,int);

#endif /* SQLITEINT_H */

Changes to src/whereexpr.c.

1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
  )){
    int nLeft = sqlite3ExprVectorSize(pExpr->pLeft);
    if( nLeft==sqlite3ExprVectorSize(pExpr->pRight) ){
      int i;
      for(i=0; i<sqlite3ExprVectorSize(pExpr->pLeft); i++){
        int idxNew;
        Expr *pNew;
        Expr *pLeft = sqlite3ExprForVectorField(pParse, pExpr->pLeft, i, 0);
        Expr *pRight = sqlite3ExprForVectorField(pParse, pExpr->pRight, i, 0);

        pNew = sqlite3PExpr(pParse, pExpr->op, pLeft, pRight, 0);
        idxNew = whereClauseInsert(pWC, pNew, TERM_DYNAMIC);
        exprAnalyze(pSrc, pWC, idxNew);
        markTermAsChild(pWC, idxNew, idxTerm);
      }
      pTerm = &pWC->a[idxTerm];







|
|







1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
  )){
    int nLeft = sqlite3ExprVectorSize(pExpr->pLeft);
    if( nLeft==sqlite3ExprVectorSize(pExpr->pRight) ){
      int i;
      for(i=0; i<sqlite3ExprVectorSize(pExpr->pLeft); i++){
        int idxNew;
        Expr *pNew;
        Expr *pLeft = sqlite3ExprForVectorField(pParse, pExpr->pLeft, i);
        Expr *pRight = sqlite3ExprForVectorField(pParse, pExpr->pRight, i);

        pNew = sqlite3PExpr(pParse, pExpr->op, pLeft, pRight, 0);
        idxNew = whereClauseInsert(pWC, pNew, TERM_DYNAMIC);
        exprAnalyze(pSrc, pWC, idxNew);
        markTermAsChild(pWC, idxNew, idxTerm);
      }
      pTerm = &pWC->a[idxTerm];

Added test/rowvalue7.test.





















































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
# 2016-08-18
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# The focus of this file is vector assignments in the SET clause of
# an UPDATE statement.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix rowvalue7

do_execsql_test 1.1 {
  CREATE TABLE t1(a,b,c,d);
  CREATE INDEX t1x ON t1(a,b);
  INSERT INTO t1(a,b,c,d) VALUES(1,2,0,0),(3,4,0,0),(5,6,0,0);
  CREATE TABLE t2(w,x,y,z);
  CREATE INDEX t2x ON t2(w,x);
  INSERT INTO t2(w,x,y,z) VALUES(1,2,11,22),(8,9,88,99),(3,5,33,55),(5,6,55,66);

  SELECT *,'|' FROM t1 ORDER BY a;
} {1 2 0 0 | 3 4 0 0 | 5 6 0 0 |}

do_execsql_test 1.2 {
  UPDATE t1 SET (c,d) = (SELECT y,z FROM t2 WHERE (w,x)=(a,b));
  SELECT *,'|' FROM t1 ORDER BY a;
} {1 2 11 22 | 3 4 {} {} | 5 6 55 66 |}

do_execsql_test 1.3 {
  UPDATE t1 SET (c,d) = (SELECT y,z FROM t2 WHERE w=a);
  SELECT *,'|' FROM t1 ORDER BY a;
} {1 2 11 22 | 3 4 33 55 | 5 6 55 66 |}

do_execsql_test 1.4 {
  UPDATE t1 SET (c) = 99 WHERE a=3;
  SELECT *,'|' FROM t1 ORDER BY a;
} {1 2 11 22 | 3 4 99 55 | 5 6 55 66 |}

do_execsql_test 1.5 {
  UPDATE t1 SET b = 8, (c,d) = (123,456) WHERE a=3;
  SELECT *,'|' FROM t1 ORDER BY a;
} {1 2 11 22 | 3 8 123 456 | 5 6 55 66 |}

do_catchsql_test 2.1 {
  UPDATE t1 SET (c,d) = (SELECT x,y,z FROM t2 WHERE w=a);
} {1 {2 columns assigned 3 values}}

do_catchsql_test 2.2 {
  UPDATE t1 SET (b,c,d) = (SELECT x,y FROM t2 WHERE w=a);
} {1 {3 columns assigned 2 values}}

finish_test