/ Check-in [907b5a37]
Login
Overview
Comment:New test cases for upsert.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256:907b5a37c539ea67c285c87399b494c7fe15014389a2ea3d49e90640b58946ae
User & Date: drh 2018-04-17 16:16:40
Context
2018-04-17
18:16
Add some more simple test cases for UPSERT. And a minor fix. check-in: 27cd3b2f user: dan tags: upsert
16:16
New test cases for upsert. check-in: 907b5a37 user: drh tags: upsert
2018-04-16
14:36
Merge the Lemon enhancement from trunk, resulting in smaller parser tables. check-in: 641f4172 user: drh tags: upsert
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/distinct.test.

164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
  2   "b, a FROM t1"                                       {}      {B A b a}
  3   "a, b, c FROM t1"                                    {hash}  {A B C a b c}
  4   "a, b, c FROM t1 ORDER BY a, b, c"                   {btree} {A B C a b c}
  5   "b FROM t1 WHERE a = 'a'"                            {}      {b}
  6   "b FROM t1 ORDER BY +b COLLATE binary"          {btree hash} {B b}
  7   "a FROM t1"                                          {}      {A a}
  8   "b COLLATE nocase FROM t1"                           {}      {b}
  9   "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {}      {b}
} {
  do_execsql_test    2.$tn.1 "SELECT DISTINCT $sql" $res
  do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
}

do_execsql_test 2.A {
  SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o ORDER BY rowid;







|







164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
  2   "b, a FROM t1"                                       {}      {B A b a}
  3   "a, b, c FROM t1"                                    {hash}  {A B C a b c}
  4   "a, b, c FROM t1 ORDER BY a, b, c"                   {btree} {A B C a b c}
  5   "b FROM t1 WHERE a = 'a'"                            {}      {b}
  6   "b FROM t1 ORDER BY +b COLLATE binary"          {btree hash} {B b}
  7   "a FROM t1"                                          {}      {A a}
  8   "b COLLATE nocase FROM t1"                           {}      {b}
  9   "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {}      {B}
} {
  do_execsql_test    2.$tn.1 "SELECT DISTINCT $sql" $res
  do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
}

do_execsql_test 2.A {
  SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o ORDER BY rowid;

Added test/upsert2.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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
# 2018-04-17
#
# 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.
#
#***********************************************************************
#
# Test cases for UPSERT

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

do_execsql_test upsert2-100 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
  INSERT INTO t1(a,b) VALUES(1,8),(2,11),(3,1)
    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
  SELECT *, 'x' FROM t1 ORDER BY a;
} {1 8 1 x 2 11 0 x 3 4 0 x}
do_execsql_test upsert2-110 {
  DROP TABLE t1;
  CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
  INSERT INTO t1(a,b) VALUES(1,8),(2,11),(3,1)
    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
  SELECT *, 'x' FROM t1 ORDER BY a;
} {1 8 1 x 2 11 0 x 3 4 0 x}

do_execsql_test upsert2-200 {
  DROP TABLE t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
  WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
  INSERT INTO t1(a,b) SELECT a, b FROM nx WHERE true
    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
  SELECT *, 'x' FROM t1 ORDER BY a;
} {1 99 2 x 2 15 1 x 3 4 0 x}
do_execsql_test upsert2-201 {
  DELETE FROM t1;
  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
  WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
  INSERT INTO t1(a,b) AS t2 SELECT a, b FROM nx WHERE true
    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=t2.c+1 WHERE t2.b<excluded.b;
  SELECT *, 'x' FROM t1 ORDER BY a;
} {1 99 2 x 2 15 1 x 3 4 0 x}
do_catchsql_test upsert2-202 {
  WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
  INSERT INTO t1(a,b) AS t2 SELECT a, b FROM nx WHERE true
    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=t1.c+1 WHERE t1.b<excluded.b;
} {1 {no such column: t1.c}}
do_execsql_test upsert2-210 {
  DROP TABLE t1;
  CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
  WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
  INSERT INTO t1(a,b) SELECT a, b FROM nx WHERE true
    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
  SELECT *, 'x' FROM t1 ORDER BY a;
} {1 99 2 x 2 15 1 x 3 4 0 x}

# On an ON CONFLICT DO UPDATE, the before-insert, before-update, and
# after-update triggers fire.
#
do_execsql_test upsert2-300 {
  DROP TABLE t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
  CREATE TABLE record(x TEXT, y TEXT);
  CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
    INSERT INTO record(x,y)
        VALUES('before-insert',printf('%d,%d,%d',new.a,new.b,new.c));
  END;
  CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
    INSERT INTO record(x,y)
        VALUES('after-insert',printf('%d,%d,%d',new.a,new.b,new.c));
  END;
  CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
    INSERT INTO record(x,y)
        VALUES('before-update',printf('%d,%d,%d/%d,%d,%d',
                                      old.a,old.b,old.c,new.a,new.b,new.c));
  END;
  CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
    INSERT INTO record(x,y)
        VALUES('after-update',printf('%d,%d,%d/%d,%d,%d',
                                      old.a,old.b,old.c,new.a,new.b,new.c));
  END;
  INSERT INTO t1(a,b) VALUES(1,2);
  DELETE FROM record;
  INSERT INTO t1(a,b) VALUES(1,2)
    ON CONFLICT(a) DO UPDATE SET c=t1.c+1;
  SELECT * FROM record
} {before-insert 1,2,0 before-update 1,2,0/1,2,1 after-update 1,2,0/1,2,1}

# On an ON CONFLICT DO NOTHING, only the before-insert trigger fires.
#
do_execsql_test upsert2-310 {
  DELETE FROM record;
  INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
  SELECT * FROM record;
} {before-insert 1,2,0}

# With ON CONFLICT DO UPDATE and a failed WHERE, only the before-insert
# trigger fires.
#
do_execsql_test upsert2-320 {
  DELETE FROM record;
  INSERT INTO t1(a,b) VALUES(1,2)
    ON CONFLICT(a) DO UPDATE SET c=c+1 WHERE c<0;
  SELECT * FROM record;
} {before-insert 1,2,0}
do_execsql_test upsert2-321 {
  SELECT * FROM t1;
} {1 2 1}

# Trigger tests repeated for a WITHOUT ROWID table.
#
do_execsql_test upsert2-400 {
  DROP TABLE t1;
  CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
  CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
    INSERT INTO record(x,y)
        VALUES('before-insert',printf('%d,%d,%d',new.a,new.b,new.c));
  END;
  CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
    INSERT INTO record(x,y)
        VALUES('after-insert',printf('%d,%d,%d',new.a,new.b,new.c));
  END;
  CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
    INSERT INTO record(x,y)
        VALUES('before-update',printf('%d,%d,%d/%d,%d,%d',
                                      old.a,old.b,old.c,new.a,new.b,new.c));
  END;
  CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
    INSERT INTO record(x,y)
        VALUES('after-update',printf('%d,%d,%d/%d,%d,%d',
                                      old.a,old.b,old.c,new.a,new.b,new.c));
  END;
  INSERT INTO t1(a,b) VALUES(1,2);
  DELETE FROM record;
  INSERT INTO t1(a,b) VALUES(1,2)
    ON CONFLICT(a) DO UPDATE SET c=t1.c+1;
  SELECT * FROM record
} {before-insert 1,2,0 before-update 1,2,0/1,2,1 after-update 1,2,0/1,2,1}

# On an ON CONFLICT DO NOTHING, only the before-insert trigger fires.
#
do_execsql_test upsert2-410 {
  DELETE FROM record;
  INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
  SELECT * FROM record;
} {before-insert 1,2,0}

# With ON CONFLICT DO UPDATE and a failed WHERE, only the before-insert
# trigger fires.
#
do_execsql_test upsert2-420 {
  DELETE FROM record;
  INSERT INTO t1(a,b) VALUES(1,2)
    ON CONFLICT(a) DO UPDATE SET c=c+1 WHERE c<0;
  SELECT * FROM record;
} {before-insert 1,2,0}
do_execsql_test upsert2-421 {
  SELECT * FROM t1;
} {1 2 1}

finish_test

Added test/upsert3.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
# 2018-04-17
#
# 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.
#
#***********************************************************************
#
# Test cases for UPSERT
#

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

do_execsql_test upsert3-100 {
  CREATE TABLE t1(k int, v text);
  CREATE UNIQUE INDEX x1 ON t1(k, v);
} {}
do_catchsql_test upsert3-110 {
  INSERT INTO t1 VALUES(0,'abcdefghij')
     ON CONFLICT(k) DO NOTHING;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
do_catchsql_test upsert3-120 {
  INSERT INTO t1 VALUES(0,'abcdefghij')
     ON CONFLICT(v) DO NOTHING;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}

do_execsql_test upsert3-130 {
  INSERT INTO t1 VALUES(0, 'abcdefghij')
      ON CONFLICT(k,v) DO NOTHING;
  SELECT * FROM t1;
} {0 abcdefghij}
do_execsql_test upsert3-140 {
  INSERT INTO t1 VALUES(0, 'abcdefghij')
      ON CONFLICT(v,k) DO NOTHING;
  SELECT * FROM t1;
} {0 abcdefghij}

do_execsql_test upsert3-200 {
  CREATE TABLE excluded(a INT, b INT, c INT DEFAULT 0);
  CREATE UNIQUE INDEX excludedab ON excluded(a,b);
  INSERT INTO excluded(a,b) VALUES(1,2),(1,2),(3,4),(1,2),(5,6),(3,4)
    ON CONFLICT(b,a) DO UPDATE SET c=excluded.c+1;
  SELECT *, 'x' FROM excluded ORDER BY a;
} {1 2 2 x 3 4 1 x 5 6 0 x}
do_execsql_test upsert3-210 {
  INSERT INTO excluded(a,b,c) AS base VALUES(1,2,8),(1,2,3)
    ON CONFLICT(b,a) DO UPDATE SET c=excluded.c+1 WHERE base.c<excluded.c;
  SELECT *, 'x' FROM excluded ORDER BY a;
} {1 2 9 x 3 4 1 x 5 6 0 x}



finish_test