github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/rowvalue7.test (about)

     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) = (SELECT 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  # 2019-08-26
    59  # ticket https://www.sqlite.org/src/info/78acc9d40f0786e8
    60  #
    61  do_catchsql_test 3.0 {
    62    DROP TABLE IF EXISTS t1;
    63    CREATE TABLE t1(a,b);
    64    INSERT INTO t1 VALUES(1,2);
    65    UPDATE t1 SET (a,a,a,b)=(SELECT 99,100);
    66  } {1 {4 columns assigned 2 values}}
    67  
    68  finish_test