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 { CREATE TABLE b1(a INTEGER PRIMARY KEY, b, c) } 88 2 { CREATE TABLE b1(a INT PRIMARY KEY, b, c) WITHOUT ROWID } 89} { 90 execsql { DROP TABLE IF EXISTS b1 } 91 execsql $sql 92 do_execsql_test 4.$tn.0 { 93 CREATE UNIQUE INDEX b1c ON b1(c); 94 95 INSERT INTO b1 VALUES(1, 'a', 1); 96 INSERT INTO b1 VALUES(2, 'b', 15); 97 INSERT INTO b1 VALUES(3, 'c', 3); 98 INSERT INTO b1 VALUES(4, 'd', 4); 99 INSERT INTO b1 VALUES(5, 'e', 5); 100 INSERT INTO b1 VALUES(6, 'f', 6); 101 INSERT INTO b1 VALUES(7, 'g', 7); 102 } 103 104 do_execsql_test 4.$tn.1 { 105 UPDATE OR REPLACE b1 SET c=c+10 WHERE a BETWEEN 4 AND 7; 106 SELECT * FROM b1 ORDER BY a; 107 } { 108 1 a 1 109 3 c 3 110 4 d 14 111 5 e 15 112 6 f 16 113 7 g 17 114 } 115} 116 117 118finish_test 119