1# 2017 January 9
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
13set testdir [file dirname $argv0]
14source $testdir/tester.tcl
15set testprefix update2
16
17db func repeat [list string repeat]
18
19#-------------------------------------------------------------------------
20# 1.1.* A one-pass UPDATE that does balance() operations on the IPK index
21#       that it is scanning.
22#
23# 1.2.* Same again, but with a WITHOUT ROWID table.
24#
25set nrow [expr 10]
26do_execsql_test 1.1.0 {
27  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
28  CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
29  WITH s(i) AS ( SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<$nrow )
30  INSERT INTO t1(b) SELECT char((i % 26) + 65) FROM s;
31  INSERT INTO t2 SELECT * FROM t1;
32}
33
34do_execsql_test 1.1.1 {
35  UPDATE t1 SET b = repeat(b, 100)
36}
37
38do_execsql_test 1.1.2 {
39  SELECT * FROM t1;
40} [db eval { SELECT a, repeat(b, 100) FROM t2 }]
41
42do_execsql_test 1.2.0 {
43  DROP TABLE t1;
44  CREATE TABLE t1(a INT PRIMARY KEY, b) WITHOUT ROWID;
45  WITH s(i) AS ( SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<$nrow )
46  INSERT INTO t1(a, b) SELECT i+1, char((i % 26) + 65) FROM s;
47}
48
49#explain_i { UPDATE t1 SET b = repeat(b, 100) }
50do_execsql_test 1.2.1 {
51  UPDATE t1 SET b = repeat(b, 100)
52}
53
54do_execsql_test 1.2.2 {
55  SELECT * FROM t1;
56} [db eval { SELECT a, repeat(b, 100) FROM t2 }]
57
58
59#-------------------------------------------------------------------------
60# A one-pass UPDATE that does balance() operations on the IPK index
61# that it is scanning.
62#
63do_execsql_test 2.1 {
64  CREATE TABLE t3(a PRIMARY KEY, b, c);
65  CREATE INDEX t3i ON t3(b);
66} {}
67do_execsql_test 2.2 { UPDATE t3 SET c=1 WHERE b=?      } {}
68do_execsql_test 2.3 { UPDATE t3 SET c=1 WHERE rowid=?  } {}
69
70#-------------------------------------------------------------------------
71#
72do_execsql_test 3.0 {
73  CREATE TABLE t4(a PRIMARY KEY, b, c) WITHOUT ROWID;
74  CREATE INDEX t4c ON t4(c);
75  INSERT INTO t4 VALUES(1, 2, 3);
76  INSERT INTO t4 VALUES(2, 3, 4);
77}
78
79do_execsql_test 3.1 {
80  UPDATE t4 SET c=c+2 WHERE c>2;
81  SELECT a, c FROM t4 ORDER BY a;
82} {1 5 2 6}
83
84#-------------------------------------------------------------------------
85#
86foreach {tn sql} {
87  1 {
88    CREATE TABLE b1(a INTEGER PRIMARY KEY, b, c);
89    CREATE TABLE c1(a INTEGER PRIMARY KEY, b, c, d)
90  }
91  2 {
92    CREATE TABLE b1(a INT PRIMARY KEY, b, c) WITHOUT ROWID;
93    CREATE TABLE c1(a INT PRIMARY KEY, b, c, d) WITHOUT ROWID;
94  }
95} {
96  execsql { DROP TABLE IF EXISTS b1; DROP TABLE IF EXISTS c1; }
97  execsql $sql
98
99  do_execsql_test 4.$tn.0 {
100    CREATE UNIQUE INDEX b1c ON b1(c);
101    INSERT INTO b1 VALUES(1, 'a', 1);
102    INSERT INTO b1 VALUES(2, 'b', 15);
103    INSERT INTO b1 VALUES(3, 'c', 3);
104    INSERT INTO b1 VALUES(4, 'd', 4);
105    INSERT INTO b1 VALUES(5, 'e', 5);
106    INSERT INTO b1 VALUES(6, 'f', 6);
107    INSERT INTO b1 VALUES(7, 'g', 7);
108  }
109
110  do_execsql_test 4.$tn.1 {
111    UPDATE OR REPLACE b1 SET c=c+10 WHERE a BETWEEN 4 AND 7;
112    SELECT * FROM b1 ORDER BY a;
113  } {
114    1 a 1
115    3 c 3
116    4 d 14
117    5 e 15
118    6 f 16
119    7 g 17
120  }
121
122  do_execsql_test 4.$tn.2 {
123    CREATE INDEX c1d ON c1(d, b);
124    CREATE UNIQUE INDEX c1c ON c1(c, b);
125
126    INSERT INTO c1 VALUES(1, 'a', 1,  1);
127    INSERT INTO c1 VALUES(2, 'a', 15, 2);
128    INSERT INTO c1 VALUES(3, 'a', 3,  3);
129    INSERT INTO c1 VALUES(4, 'a', 4,  4);
130    INSERT INTO c1 VALUES(5, 'a', 5,  5);
131    INSERT INTO c1 VALUES(6, 'a', 6,  6);
132    INSERT INTO c1 VALUES(7, 'a', 7,  7);
133  }
134
135  do_execsql_test 4.$tn.3 {
136    UPDATE OR REPLACE c1 SET c=c+10 WHERE d BETWEEN 4 AND 7;
137    SELECT * FROM c1 ORDER BY a;
138  } {
139    1 a 1 1
140    3 a 3 3
141    4 a 14 4
142    5 a 15 5
143    6 a 16 6
144    7 a 17 7
145  }
146
147  do_execsql_test 4.$tn.4 { PRAGMA integrity_check } ok
148
149  do_execsql_test 4.$tn.5 {
150    DROP INDEX c1d;
151    DROP INDEX c1c;
152    DELETE FROM c1;
153
154    INSERT INTO c1 VALUES(1, 'a', 1,  1);
155    INSERT INTO c1 VALUES(2, 'a', 15, 2);
156    INSERT INTO c1 VALUES(3, 'a', 3,  3);
157    INSERT INTO c1 VALUES(4, 'a', 4,  4);
158    INSERT INTO c1 VALUES(5, 'a', 5,  5);
159    INSERT INTO c1 VALUES(6, 'a', 6,  6);
160    INSERT INTO c1 VALUES(7, 'a', 7,  7);
161
162    CREATE INDEX c1d ON c1(d);
163    CREATE UNIQUE INDEX c1c ON c1(c);
164  }
165
166  do_execsql_test 4.$tn.6 {
167    UPDATE OR REPLACE c1 SET c=c+10 WHERE d BETWEEN 4 AND 7;
168    SELECT * FROM c1 ORDER BY a;
169  } {
170    1 a 1 1
171    3 a 3 3
172    4 a 14 4
173    5 a 15 5
174    6 a 16 6
175    7 a 17 7
176  }
177}
178
179#-------------------------------------------------------------------------
180#
181do_execsql_test 5.0 {
182  CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c);
183  CREATE INDEX x1c ON x1(b, c);
184  INSERT INTO x1 VALUES(1, 'a', 1);
185  INSERT INTO x1 VALUES(2, 'a', 2);
186  INSERT INTO x1 VALUES(3, 'a', 3);
187}
188
189do_execsql_test 5.1.1 {
190  UPDATE x1 SET c=c+1 WHERE b='a';
191}
192
193do_execsql_test 5.1.2 {
194  SELECT * FROM x1;
195} {1 a 2 2 a 3 3 a 4}
196
197do_test 5.2 {
198  catch { array unset A }
199  db eval { EXPLAIN UPDATE x1 SET c=c+1 WHERE b='a' } { incr A($opcode) }
200  set A(NotExists)
201} {1}
202
203#-------------------------------------------------------------------------
204do_execsql_test 6.0 {
205  CREATE TABLE d1(a,b);
206  CREATE INDEX d1b ON d1(a);
207  CREATE INDEX d1c ON d1(b);
208  INSERT INTO d1 VALUES(1,2);
209}
210
211do_execsql_test 6.1 {
212  UPDATE d1 SET a = a+2 WHERE a>0 OR b>0;
213}
214
215do_execsql_test 6.2 {
216  SELECT * FROM d1;
217} {3 2}
218
219# 2019-01-22 Bug in UPDATE OR REPLACE discovered by the
220# Matt Denton's LPM fuzzer
221#
222do_execsql_test 7.100 {
223  DROP TABLE IF EXISTS t1;
224  CREATE TABLE t1(x,y);
225  CREATE UNIQUE INDEX t1x1 ON t1(x) WHERE x IS NOT NULL;
226  INSERT INTO t1(x) VALUES(NULL),(NULL);
227  CREATE INDEX t1x2 ON t1(y);
228  SELECT quote(x), quote(y), '|' FROM t1;
229} {NULL NULL | NULL NULL |}
230do_execsql_test 7.110 {
231  UPDATE OR REPLACE t1 SET x=1;
232  SELECT quote(x), quote(y), '|' FROM t1;
233} {1 NULL |}
234
235finish_test
236