summaryrefslogtreecommitdiffstats
path: root/test/update2.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/update2.test')
-rw-r--r--test/update2.test235
1 files changed, 235 insertions, 0 deletions
diff --git a/test/update2.test b/test/update2.test
new file mode 100644
index 0000000..41a7224
--- /dev/null
+++ b/test/update2.test
@@ -0,0 +1,235 @@
+# 2017 January 9
+#
+# 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.
+#
+#***********************************************************************
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix update2
+
+db func repeat [list string repeat]
+
+#-------------------------------------------------------------------------
+# 1.1.* A one-pass UPDATE that does balance() operations on the IPK index
+# that it is scanning.
+#
+# 1.2.* Same again, but with a WITHOUT ROWID table.
+#
+set nrow [expr 10]
+do_execsql_test 1.1.0 {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
+ WITH s(i) AS ( SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<$nrow )
+ INSERT INTO t1(b) SELECT char((i % 26) + 65) FROM s;
+ INSERT INTO t2 SELECT * FROM t1;
+}
+
+do_execsql_test 1.1.1 {
+ UPDATE t1 SET b = repeat(b, 100)
+}
+
+do_execsql_test 1.1.2 {
+ SELECT * FROM t1;
+} [db eval { SELECT a, repeat(b, 100) FROM t2 }]
+
+do_execsql_test 1.2.0 {
+ DROP TABLE t1;
+ CREATE TABLE t1(a INT PRIMARY KEY, b) WITHOUT ROWID;
+ WITH s(i) AS ( SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<$nrow )
+ INSERT INTO t1(a, b) SELECT i+1, char((i % 26) + 65) FROM s;
+}
+
+#explain_i { UPDATE t1 SET b = repeat(b, 100) }
+do_execsql_test 1.2.1 {
+ UPDATE t1 SET b = repeat(b, 100)
+}
+
+do_execsql_test 1.2.2 {
+ SELECT * FROM t1;
+} [db eval { SELECT a, repeat(b, 100) FROM t2 }]
+
+
+#-------------------------------------------------------------------------
+# A one-pass UPDATE that does balance() operations on the IPK index
+# that it is scanning.
+#
+do_execsql_test 2.1 {
+ CREATE TABLE t3(a PRIMARY KEY, b, c);
+ CREATE INDEX t3i ON t3(b);
+} {}
+do_execsql_test 2.2 { UPDATE t3 SET c=1 WHERE b=? } {}
+do_execsql_test 2.3 { UPDATE t3 SET c=1 WHERE rowid=? } {}
+
+#-------------------------------------------------------------------------
+#
+do_execsql_test 3.0 {
+ CREATE TABLE t4(a PRIMARY KEY, b, c) WITHOUT ROWID;
+ CREATE INDEX t4c ON t4(c);
+ INSERT INTO t4 VALUES(1, 2, 3);
+ INSERT INTO t4 VALUES(2, 3, 4);
+}
+
+do_execsql_test 3.1 {
+ UPDATE t4 SET c=c+2 WHERE c>2;
+ SELECT a, c FROM t4 ORDER BY a;
+} {1 5 2 6}
+
+#-------------------------------------------------------------------------
+#
+foreach {tn sql} {
+ 1 {
+ CREATE TABLE b1(a INTEGER PRIMARY KEY, b, c);
+ CREATE TABLE c1(a INTEGER PRIMARY KEY, b, c, d)
+ }
+ 2 {
+ CREATE TABLE b1(a INT PRIMARY KEY, b, c) WITHOUT ROWID;
+ CREATE TABLE c1(a INT PRIMARY KEY, b, c, d) WITHOUT ROWID;
+ }
+} {
+ execsql { DROP TABLE IF EXISTS b1; DROP TABLE IF EXISTS c1; }
+ execsql $sql
+
+ do_execsql_test 4.$tn.0 {
+ CREATE UNIQUE INDEX b1c ON b1(c);
+ INSERT INTO b1 VALUES(1, 'a', 1);
+ INSERT INTO b1 VALUES(2, 'b', 15);
+ INSERT INTO b1 VALUES(3, 'c', 3);
+ INSERT INTO b1 VALUES(4, 'd', 4);
+ INSERT INTO b1 VALUES(5, 'e', 5);
+ INSERT INTO b1 VALUES(6, 'f', 6);
+ INSERT INTO b1 VALUES(7, 'g', 7);
+ }
+
+ do_execsql_test 4.$tn.1 {
+ UPDATE OR REPLACE b1 SET c=c+10 WHERE a BETWEEN 4 AND 7;
+ SELECT * FROM b1 ORDER BY a;
+ } {
+ 1 a 1
+ 3 c 3
+ 4 d 14
+ 5 e 15
+ 6 f 16
+ 7 g 17
+ }
+
+ do_execsql_test 4.$tn.2 {
+ CREATE INDEX c1d ON c1(d, b);
+ CREATE UNIQUE INDEX c1c ON c1(c, b);
+
+ INSERT INTO c1 VALUES(1, 'a', 1, 1);
+ INSERT INTO c1 VALUES(2, 'a', 15, 2);
+ INSERT INTO c1 VALUES(3, 'a', 3, 3);
+ INSERT INTO c1 VALUES(4, 'a', 4, 4);
+ INSERT INTO c1 VALUES(5, 'a', 5, 5);
+ INSERT INTO c1 VALUES(6, 'a', 6, 6);
+ INSERT INTO c1 VALUES(7, 'a', 7, 7);
+ }
+
+ do_execsql_test 4.$tn.3 {
+ UPDATE OR REPLACE c1 SET c=c+10 WHERE d BETWEEN 4 AND 7;
+ SELECT * FROM c1 ORDER BY a;
+ } {
+ 1 a 1 1
+ 3 a 3 3
+ 4 a 14 4
+ 5 a 15 5
+ 6 a 16 6
+ 7 a 17 7
+ }
+
+ do_execsql_test 4.$tn.4 { PRAGMA integrity_check } ok
+
+ do_execsql_test 4.$tn.5 {
+ DROP INDEX c1d;
+ DROP INDEX c1c;
+ DELETE FROM c1;
+
+ INSERT INTO c1 VALUES(1, 'a', 1, 1);
+ INSERT INTO c1 VALUES(2, 'a', 15, 2);
+ INSERT INTO c1 VALUES(3, 'a', 3, 3);
+ INSERT INTO c1 VALUES(4, 'a', 4, 4);
+ INSERT INTO c1 VALUES(5, 'a', 5, 5);
+ INSERT INTO c1 VALUES(6, 'a', 6, 6);
+ INSERT INTO c1 VALUES(7, 'a', 7, 7);
+
+ CREATE INDEX c1d ON c1(d);
+ CREATE UNIQUE INDEX c1c ON c1(c);
+ }
+
+ do_execsql_test 4.$tn.6 {
+ UPDATE OR REPLACE c1 SET c=c+10 WHERE d BETWEEN 4 AND 7;
+ SELECT * FROM c1 ORDER BY a;
+ } {
+ 1 a 1 1
+ 3 a 3 3
+ 4 a 14 4
+ 5 a 15 5
+ 6 a 16 6
+ 7 a 17 7
+ }
+}
+
+#-------------------------------------------------------------------------
+#
+do_execsql_test 5.0 {
+ CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c);
+ CREATE INDEX x1c ON x1(b, c);
+ INSERT INTO x1 VALUES(1, 'a', 1);
+ INSERT INTO x1 VALUES(2, 'a', 2);
+ INSERT INTO x1 VALUES(3, 'a', 3);
+}
+
+do_execsql_test 5.1.1 {
+ UPDATE x1 SET c=c+1 WHERE b='a';
+}
+
+do_execsql_test 5.1.2 {
+ SELECT * FROM x1;
+} {1 a 2 2 a 3 3 a 4}
+
+do_test 5.2 {
+ catch { array unset A }
+ db eval { EXPLAIN UPDATE x1 SET c=c+1 WHERE b='a' } { incr A($opcode) }
+ set A(NotExists)
+} {1}
+
+#-------------------------------------------------------------------------
+do_execsql_test 6.0 {
+ CREATE TABLE d1(a,b);
+ CREATE INDEX d1b ON d1(a);
+ CREATE INDEX d1c ON d1(b);
+ INSERT INTO d1 VALUES(1,2);
+}
+
+do_execsql_test 6.1 {
+ UPDATE d1 SET a = a+2 WHERE a>0 OR b>0;
+}
+
+do_execsql_test 6.2 {
+ SELECT * FROM d1;
+} {3 2}
+
+# 2019-01-22 Bug in UPDATE OR REPLACE discovered by the
+# Matt Denton's LPM fuzzer
+#
+do_execsql_test 7.100 {
+ DROP TABLE IF EXISTS t1;
+ CREATE TABLE t1(x,y);
+ CREATE UNIQUE INDEX t1x1 ON t1(x) WHERE x IS NOT NULL;
+ INSERT INTO t1(x) VALUES(NULL),(NULL);
+ CREATE INDEX t1x2 ON t1(y);
+ SELECT quote(x), quote(y), '|' FROM t1;
+} {NULL NULL | NULL NULL |}
+do_execsql_test 7.110 {
+ UPDATE OR REPLACE t1 SET x=1;
+ SELECT quote(x), quote(y), '|' FROM t1;
+} {1 NULL |}
+
+finish_test