gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/upfrom4.test (about)

     1  # 2022-05-24
     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  #
    12  
    13  set testdir [file dirname $argv0]
    14  source $testdir/tester.tcl
    15  set testprefix upfrom4
    16  
    17  do_execsql_test 100 {
    18    DROP TABLE IF EXISTS t5;
    19    DROP TABLE IF EXISTS m1;
    20    DROP TABLE IF EXISTS m2;
    21    CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT);
    22    CREATE TABLE m1(x INTEGER PRIMARY KEY, y TEXT);
    23    CREATE TABLE m2(u INTEGER PRIMARY KEY, v TEXT);
    24  
    25    INSERT INTO t5 VALUES(1, 'one', 'ONE');
    26    INSERT INTO t5 VALUES(2, 'two', 'TWO');
    27    INSERT INTO t5 VALUES(3, 'three', 'THREE');
    28    INSERT INTO t5 VALUES(4, 'four', 'FOUR');
    29  
    30    INSERT INTO m1 VALUES(1, 'i');
    31    INSERT INTO m1 VALUES(2, 'ii');
    32    INSERT INTO m1 VALUES(3, 'iii');
    33  
    34    INSERT INTO m2 VALUES(1, 'I');
    35    INSERT INTO m2 VALUES(3, 'II');
    36    INSERT INTO m2 VALUES(4, 'III');
    37    SELECT * FROM t5;
    38  } {1 one ONE 2 two TWO 3 three THREE 4 four FOUR}
    39  
    40  do_execsql_test 110 {
    41    BEGIN;
    42    UPDATE t5 SET b=y, c=v FROM m1 LEFT JOIN m2 ON (x=u) WHERE x=a;
    43    SELECT * FROM t5 ORDER BY a;
    44    ROLLBACK;
    45  } {1 i I 2 ii {} 3 iii II 4 four FOUR}
    46  
    47  do_execsql_test 120 {
    48    BEGIN;
    49    UPDATE t5 SET b=y, c=v FROM m2 RIGHT JOIN m1 ON (x=u) WHERE x=a;
    50    SELECT * FROM t5 ORDER BY a;
    51    ROLLBACK;
    52  } {1 i I 2 ii {} 3 iii II 4 four FOUR}
    53  
    54  
    55  reset_db
    56  db null -
    57  do_execsql_test 200 {
    58    CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT);
    59    INSERT INTO t1(a) VALUES(1),(2),(8),(19);
    60    CREATE TABLE c1(x INTEGER PRIMARY KEY, b INT);
    61    INSERT INTO c1(x,b) VALUES(1,1),(8,8),(17,17),(NULL,NULL);
    62    CREATE TABLE c2(x INT,c INT);
    63    INSERT INTO c2(x,c) VALUES(2,2),(8,8),(NULL,NULL);
    64    CREATE TABLE dual(dummy TEXT);
    65    INSERT INTO dual VALUES('X');
    66  } {}
    67  do_execsql_test 210 {
    68    BEGIN;
    69    SELECT * FROM t1 ORDER BY a;
    70    UPDATE t1 SET b=c1.b, c=c2.c
    71      FROM dual, c1 NATURAL RIGHT JOIN c2
    72     WHERE x=a;
    73    SELECT * FROM t1 ORDER BY a;
    74    ROLLBACK;
    75  } {
    76    1  -  -
    77    2  -  -
    78    8  -  -
    79    19 -  -
    80    1  -  -
    81    2  -  2
    82    8  8  8
    83    19 -  -
    84  }
    85  do_execsql_test 300 {
    86    CREATE TABLE t2(x);
    87    CREATE TRIGGER AFTER INSERT ON t2 BEGIN
    88      UPDATE t1 SET b=c1.b, c=c2.c
    89        FROM dual, c1 NATURAL RIGHT JOIN c2
    90       WHERE x=a;
    91    END;
    92  } {}
    93  do_execsql_test 310 {
    94    BEGIN;
    95    SELECT * FROM t1 ORDER BY a;
    96    INSERT INTO t2(x) VALUES(1);
    97    SELECT * FROM t1 ORDER BY a;
    98    ROLLBACK;
    99  } {
   100    1  -  -
   101    2  -  -
   102    8  -  -
   103    19 -  -
   104    1  -  -
   105    2  -  2
   106    8  8  8
   107    19 -  -
   108  }
   109  
   110  # 2022-05-26 dbsqlfuzz crash-9401d6ba699f1257d352a657de236286bf2b14da
   111  #
   112  reset_db
   113  db null -
   114  do_execsql_test 400 {
   115    CREATE TABLE t2(x,y,z PRIMARY KEY) WITHOUT ROWID;
   116    INSERT INTO t2 VALUES(89,-89,6);
   117    CREATE TABLE t1(a INT,b TEXT,c TEXT,d REAL) STRICT;
   118    INSERT INTO t1 VALUES(1,'xyz','def',4.5);
   119    CREATE TRIGGER t1tr BEFORE UPDATE ON t1 BEGIN
   120      INSERT INTO t1(a,b) VALUES(1000,'uvw');
   121      UPDATE t1 SET b=NULL FROM (SELECT CAST(a AS varchar) FROM t1 ORDER BY b) NATURAL LEFT FULL JOIN t1 AS text;
   122    END;
   123    UPDATE t1 SET b=b|100;
   124    SELECT * FROM t1 ORDER BY a;
   125  } {
   126    1    100  def 4.5
   127    1000 -    -   -
   128  }
   129  
   130  finish_test