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 84finish_test 85