1*1da57d55SToomas Soome# 2c5c4113dSnw141292# 2001 September 15 3c5c4113dSnw141292# 4c5c4113dSnw141292# The author disclaims copyright to this source code. In place of 5c5c4113dSnw141292# a legal notice, here is a blessing: 6c5c4113dSnw141292# 7c5c4113dSnw141292# May you do good and not evil. 8c5c4113dSnw141292# May you find forgiveness for yourself and forgive others. 9c5c4113dSnw141292# May you share freely, never taking more than you give. 10c5c4113dSnw141292# 11c5c4113dSnw141292#*********************************************************************** 12c5c4113dSnw141292# This file implements regression tests for SQLite library. The 13c5c4113dSnw141292# focus of this file is testing the UPDATE statement. 14c5c4113dSnw141292# 15c5c4113dSnw141292# $Id: update.test,v 1.15 2004/02/10 13:41:53 drh Exp $ 16c5c4113dSnw141292 17c5c4113dSnw141292set testdir [file dirname $argv0] 18c5c4113dSnw141292source $testdir/tester.tcl 19c5c4113dSnw141292 20c5c4113dSnw141292# Try to update an non-existent table 21c5c4113dSnw141292# 22c5c4113dSnw141292do_test update-1.1 { 23c5c4113dSnw141292 set v [catch {execsql {UPDATE test1 SET f2=5 WHERE f1<1}} msg] 24c5c4113dSnw141292 lappend v $msg 25c5c4113dSnw141292} {1 {no such table: test1}} 26c5c4113dSnw141292 27c5c4113dSnw141292# Try to update a read-only table 28c5c4113dSnw141292# 29c5c4113dSnw141292do_test update-2.1 { 30c5c4113dSnw141292 set v [catch \ 31c5c4113dSnw141292 {execsql {UPDATE sqlite_master SET name='xyz' WHERE name='123'}} msg] 32c5c4113dSnw141292 lappend v $msg 33c5c4113dSnw141292} {1 {table sqlite_master may not be modified}} 34c5c4113dSnw141292 35c5c4113dSnw141292# Create a table to work with 36c5c4113dSnw141292# 37c5c4113dSnw141292do_test update-3.1 { 38c5c4113dSnw141292 execsql {CREATE TABLE test1(f1 int,f2 int)} 39c5c4113dSnw141292 for {set i 1} {$i<=10} {incr i} { 40c5c4113dSnw141292 set sql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])" 41c5c4113dSnw141292 execsql $sql 42c5c4113dSnw141292 } 43c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1} 44c5c4113dSnw141292} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024} 45c5c4113dSnw141292 46c5c4113dSnw141292# Unknown column name in an expression 47c5c4113dSnw141292# 48c5c4113dSnw141292do_test update-3.2 { 49c5c4113dSnw141292 set v [catch {execsql {UPDATE test1 SET f1=f3*2 WHERE f2==32}} msg] 50c5c4113dSnw141292 lappend v $msg 51c5c4113dSnw141292} {1 {no such column: f3}} 52c5c4113dSnw141292do_test update-3.3 { 53c5c4113dSnw141292 set v [catch {execsql {UPDATE test1 SET f1=test2.f1*2 WHERE f2==32}} msg] 54c5c4113dSnw141292 lappend v $msg 55c5c4113dSnw141292} {1 {no such column: test2.f1}} 56c5c4113dSnw141292do_test update-3.4 { 57c5c4113dSnw141292 set v [catch {execsql {UPDATE test1 SET f3=f1*2 WHERE f2==32}} msg] 58c5c4113dSnw141292 lappend v $msg 59c5c4113dSnw141292} {1 {no such column: f3}} 60c5c4113dSnw141292 61c5c4113dSnw141292# Actually do some updates 62c5c4113dSnw141292# 63c5c4113dSnw141292do_test update-3.5 { 64c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2*3} 65c5c4113dSnw141292} {} 66c5c4113dSnw141292do_test update-3.6 { 67c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1} 68c5c4113dSnw141292} {1 6 2 12 3 24 4 48 5 96 6 192 7 384 8 768 9 1536 10 3072} 69c5c4113dSnw141292do_test update-3.7 { 70c5c4113dSnw141292 execsql {PRAGMA count_changes=on} 71c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2/3 WHERE f1<=5} 72c5c4113dSnw141292} {5} 73c5c4113dSnw141292do_test update-3.8 { 74c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1} 75c5c4113dSnw141292} {1 2 2 4 3 8 4 16 5 32 6 192 7 384 8 768 9 1536 10 3072} 76c5c4113dSnw141292do_test update-3.9 { 77c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2/3 WHERE f1>5} 78c5c4113dSnw141292} {5} 79c5c4113dSnw141292do_test update-3.10 { 80c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1} 81c5c4113dSnw141292} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024} 82c5c4113dSnw141292 83c5c4113dSnw141292# Swap the values of f1 and f2 for all elements 84c5c4113dSnw141292# 85c5c4113dSnw141292do_test update-3.11 { 86c5c4113dSnw141292 execsql {UPDATE test1 SET F2=f1, F1=f2} 87c5c4113dSnw141292} {10} 88c5c4113dSnw141292do_test update-3.12 { 89c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY F1} 90c5c4113dSnw141292} {2 1 4 2 8 3 16 4 32 5 64 6 128 7 256 8 512 9 1024 10} 91c5c4113dSnw141292do_test update-3.13 { 92c5c4113dSnw141292 execsql {PRAGMA count_changes=off} 93c5c4113dSnw141292 execsql {UPDATE test1 SET F2=f1, F1=f2} 94c5c4113dSnw141292} {} 95c5c4113dSnw141292do_test update-3.14 { 96c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY F1} 97c5c4113dSnw141292} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024} 98c5c4113dSnw141292 99c5c4113dSnw141292# Create duplicate entries and make sure updating still 100c5c4113dSnw141292# works. 101c5c4113dSnw141292# 102c5c4113dSnw141292do_test update-4.0 { 103c5c4113dSnw141292 execsql { 104c5c4113dSnw141292 DELETE FROM test1 WHERE f1<=5; 105c5c4113dSnw141292 INSERT INTO test1(f1,f2) VALUES(8,88); 106c5c4113dSnw141292 INSERT INTO test1(f1,f2) VALUES(8,888); 107c5c4113dSnw141292 INSERT INTO test1(f1,f2) VALUES(77,128); 108c5c4113dSnw141292 INSERT INTO test1(f1,f2) VALUES(777,128); 109c5c4113dSnw141292 } 110c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 111c5c4113dSnw141292} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 112c5c4113dSnw141292do_test update-4.1 { 113c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8} 114c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 115c5c4113dSnw141292} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128} 116c5c4113dSnw141292do_test update-4.2 { 117c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800} 118c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 119c5c4113dSnw141292} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128} 120c5c4113dSnw141292do_test update-4.3 { 121c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800} 122c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 123c5c4113dSnw141292} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 124c5c4113dSnw141292do_test update-4.4 { 125c5c4113dSnw141292 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128} 126c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 127c5c4113dSnw141292} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128} 128c5c4113dSnw141292do_test update-4.5 { 129c5c4113dSnw141292 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128} 130c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 131c5c4113dSnw141292} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128} 132c5c4113dSnw141292do_test update-4.6 { 133c5c4113dSnw141292 execsql { 134c5c4113dSnw141292 PRAGMA count_changes=on; 135c5c4113dSnw141292 UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128; 136c5c4113dSnw141292 } 137c5c4113dSnw141292} {2} 138c5c4113dSnw141292do_test update-4.7 { 139c5c4113dSnw141292 execsql { 140c5c4113dSnw141292 PRAGMA count_changes=off; 141c5c4113dSnw141292 SELECT * FROM test1 ORDER BY f1,f2 142c5c4113dSnw141292 } 143c5c4113dSnw141292} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 144c5c4113dSnw141292 145c5c4113dSnw141292# Repeat the previous sequence of tests with an index. 146c5c4113dSnw141292# 147c5c4113dSnw141292do_test update-5.0 { 148c5c4113dSnw141292 execsql {CREATE INDEX idx1 ON test1(f1)} 149c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 150c5c4113dSnw141292} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 151c5c4113dSnw141292do_test update-5.1 { 152c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8} 153c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 154c5c4113dSnw141292} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128} 155c5c4113dSnw141292do_test update-5.2 { 156c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800} 157c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 158c5c4113dSnw141292} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128} 159c5c4113dSnw141292do_test update-5.3 { 160c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800} 161c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 162c5c4113dSnw141292} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 163c5c4113dSnw141292do_test update-5.4 { 164c5c4113dSnw141292 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128} 165c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 166c5c4113dSnw141292} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128} 167c5c4113dSnw141292do_test update-5.4.1 { 168c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 169c5c4113dSnw141292} {78 128} 170c5c4113dSnw141292do_test update-5.4.2 { 171c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 172c5c4113dSnw141292} {778 128} 173c5c4113dSnw141292do_test update-5.4.3 { 174c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 175c5c4113dSnw141292} {8 88 8 128 8 256 8 888} 176c5c4113dSnw141292do_test update-5.5 { 177c5c4113dSnw141292 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128} 178c5c4113dSnw141292} {} 179c5c4113dSnw141292do_test update-5.5.1 { 180c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 181c5c4113dSnw141292} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128} 182c5c4113dSnw141292do_test update-5.5.2 { 183c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 184c5c4113dSnw141292} {78 128} 185c5c4113dSnw141292do_test update-5.5.3 { 186c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 187c5c4113dSnw141292} {} 188c5c4113dSnw141292do_test update-5.5.4 { 189c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 190c5c4113dSnw141292} {777 128} 191c5c4113dSnw141292do_test update-5.5.5 { 192c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 193c5c4113dSnw141292} {8 88 8 128 8 256 8 888} 194c5c4113dSnw141292do_test update-5.6 { 195c5c4113dSnw141292 execsql { 196c5c4113dSnw141292 PRAGMA count_changes=on; 197c5c4113dSnw141292 UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128; 198c5c4113dSnw141292 } 199c5c4113dSnw141292} {2} 200c5c4113dSnw141292do_test update-5.6.1 { 201c5c4113dSnw141292 execsql { 202c5c4113dSnw141292 PRAGMA count_changes=off; 203c5c4113dSnw141292 SELECT * FROM test1 ORDER BY f1,f2 204c5c4113dSnw141292 } 205c5c4113dSnw141292} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 206c5c4113dSnw141292do_test update-5.6.2 { 207c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2} 208c5c4113dSnw141292} {77 128} 209c5c4113dSnw141292do_test update-5.6.3 { 210c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 211c5c4113dSnw141292} {} 212c5c4113dSnw141292do_test update-5.6.4 { 213c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 214c5c4113dSnw141292} {777 128} 215c5c4113dSnw141292do_test update-5.6.5 { 216c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 217c5c4113dSnw141292} {8 88 8 256 8 888} 218c5c4113dSnw141292 219c5c4113dSnw141292# Repeat the previous sequence of tests with a different index. 220c5c4113dSnw141292# 221c5c4113dSnw141292execsql {PRAGMA synchronous=FULL} 222c5c4113dSnw141292do_test update-6.0 { 223c5c4113dSnw141292 execsql {DROP INDEX idx1} 224c5c4113dSnw141292 execsql {CREATE INDEX idx1 ON test1(f2)} 225c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 226c5c4113dSnw141292} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 227c5c4113dSnw141292do_test update-6.1 { 228c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8} 229c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 230c5c4113dSnw141292} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128} 231c5c4113dSnw141292do_test update-6.1.1 { 232c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 233c5c4113dSnw141292} {8 89 8 257 8 889} 234c5c4113dSnw141292do_test update-6.1.2 { 235c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} 236c5c4113dSnw141292} {8 89} 237c5c4113dSnw141292do_test update-6.1.3 { 238c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2} 239c5c4113dSnw141292} {} 240c5c4113dSnw141292do_test update-6.2 { 241c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800} 242c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 243c5c4113dSnw141292} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128} 244c5c4113dSnw141292do_test update-6.3 { 245c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800} 246c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 247c5c4113dSnw141292} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 248c5c4113dSnw141292do_test update-6.3.1 { 249c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 250c5c4113dSnw141292} {8 88 8 256 8 888} 251c5c4113dSnw141292do_test update-6.3.2 { 252c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} 253c5c4113dSnw141292} {} 254c5c4113dSnw141292do_test update-6.3.3 { 255c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2} 256c5c4113dSnw141292} {8 88} 257c5c4113dSnw141292do_test update-6.4 { 258c5c4113dSnw141292 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128} 259c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 260c5c4113dSnw141292} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128} 261c5c4113dSnw141292do_test update-6.4.1 { 262c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 263c5c4113dSnw141292} {78 128} 264c5c4113dSnw141292do_test update-6.4.2 { 265c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 266c5c4113dSnw141292} {778 128} 267c5c4113dSnw141292do_test update-6.4.3 { 268c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 269c5c4113dSnw141292} {8 88 8 128 8 256 8 888} 270c5c4113dSnw141292do_test update-6.5 { 271c5c4113dSnw141292 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128} 272c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 273c5c4113dSnw141292} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128} 274c5c4113dSnw141292do_test update-6.5.1 { 275c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 276c5c4113dSnw141292} {78 128} 277c5c4113dSnw141292do_test update-6.5.2 { 278c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 279c5c4113dSnw141292} {} 280c5c4113dSnw141292do_test update-6.5.3 { 281c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 282c5c4113dSnw141292} {777 128} 283c5c4113dSnw141292do_test update-6.5.4 { 284c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 285c5c4113dSnw141292} {8 88 8 128 8 256 8 888} 286c5c4113dSnw141292do_test update-6.6 { 287c5c4113dSnw141292 execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128} 288c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 289c5c4113dSnw141292} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 290c5c4113dSnw141292do_test update-6.6.1 { 291c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2} 292c5c4113dSnw141292} {77 128} 293c5c4113dSnw141292do_test update-6.6.2 { 294c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 295c5c4113dSnw141292} {} 296c5c4113dSnw141292do_test update-6.6.3 { 297c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 298c5c4113dSnw141292} {777 128} 299c5c4113dSnw141292do_test update-6.6.4 { 300c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 301c5c4113dSnw141292} {8 88 8 256 8 888} 302c5c4113dSnw141292 303c5c4113dSnw141292# Repeat the previous sequence of tests with multiple 304c5c4113dSnw141292# indices 305c5c4113dSnw141292# 306c5c4113dSnw141292do_test update-7.0 { 307c5c4113dSnw141292 execsql {CREATE INDEX idx2 ON test1(f2)} 308c5c4113dSnw141292 execsql {CREATE INDEX idx3 ON test1(f1,f2)} 309c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 310c5c4113dSnw141292} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 311c5c4113dSnw141292do_test update-7.1 { 312c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8} 313c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 314c5c4113dSnw141292} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128} 315c5c4113dSnw141292do_test update-7.1.1 { 316c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 317c5c4113dSnw141292} {8 89 8 257 8 889} 318c5c4113dSnw141292do_test update-7.1.2 { 319c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} 320c5c4113dSnw141292} {8 89} 321c5c4113dSnw141292do_test update-7.1.3 { 322c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2} 323c5c4113dSnw141292} {} 324c5c4113dSnw141292do_test update-7.2 { 325c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800} 326c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 327c5c4113dSnw141292} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128} 328c5c4113dSnw141292do_test update-7.3 { 329c5c4113dSnw141292 # explain {UPDATE test1 SET f2=f2-1 WHERE f1==8 and F2<300} 330c5c4113dSnw141292 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800} 331c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 332c5c4113dSnw141292} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 333c5c4113dSnw141292do_test update-7.3.1 { 334c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 335c5c4113dSnw141292} {8 88 8 256 8 888} 336c5c4113dSnw141292do_test update-7.3.2 { 337c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} 338c5c4113dSnw141292} {} 339c5c4113dSnw141292do_test update-7.3.3 { 340c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2} 341c5c4113dSnw141292} {8 88} 342c5c4113dSnw141292do_test update-7.4 { 343c5c4113dSnw141292 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128} 344c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 345c5c4113dSnw141292} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128} 346c5c4113dSnw141292do_test update-7.4.1 { 347c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 348c5c4113dSnw141292} {78 128} 349c5c4113dSnw141292do_test update-7.4.2 { 350c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 351c5c4113dSnw141292} {778 128} 352c5c4113dSnw141292do_test update-7.4.3 { 353c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 354c5c4113dSnw141292} {8 88 8 128 8 256 8 888} 355c5c4113dSnw141292do_test update-7.5 { 356c5c4113dSnw141292 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128} 357c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 358c5c4113dSnw141292} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128} 359c5c4113dSnw141292do_test update-7.5.1 { 360c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 361c5c4113dSnw141292} {78 128} 362c5c4113dSnw141292do_test update-7.5.2 { 363c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 364c5c4113dSnw141292} {} 365c5c4113dSnw141292do_test update-7.5.3 { 366c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 367c5c4113dSnw141292} {777 128} 368c5c4113dSnw141292do_test update-7.5.4 { 369c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 370c5c4113dSnw141292} {8 88 8 128 8 256 8 888} 371c5c4113dSnw141292do_test update-7.6 { 372c5c4113dSnw141292 execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128} 373c5c4113dSnw141292 execsql {SELECT * FROM test1 ORDER BY f1,f2} 374c5c4113dSnw141292} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 375c5c4113dSnw141292do_test update-7.6.1 { 376c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2} 377c5c4113dSnw141292} {77 128} 378c5c4113dSnw141292do_test update-7.6.2 { 379c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 380c5c4113dSnw141292} {} 381c5c4113dSnw141292do_test update-7.6.3 { 382c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 383c5c4113dSnw141292} {777 128} 384c5c4113dSnw141292do_test update-7.6.4 { 385c5c4113dSnw141292 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 386c5c4113dSnw141292} {8 88 8 256 8 888} 387c5c4113dSnw141292 388c5c4113dSnw141292# Error messages 389c5c4113dSnw141292# 390c5c4113dSnw141292do_test update-9.1 { 391c5c4113dSnw141292 set v [catch {execsql { 392c5c4113dSnw141292 UPDATE test1 SET x=11 WHERE f1=1025 393c5c4113dSnw141292 }} msg] 394c5c4113dSnw141292 lappend v $msg 395c5c4113dSnw141292} {1 {no such column: x}} 396c5c4113dSnw141292do_test update-9.2 { 397c5c4113dSnw141292 set v [catch {execsql { 398c5c4113dSnw141292 UPDATE test1 SET f1=x(11) WHERE f1=1025 399c5c4113dSnw141292 }} msg] 400c5c4113dSnw141292 lappend v $msg 401c5c4113dSnw141292} {1 {no such function: x}} 402c5c4113dSnw141292do_test update-9.3 { 403c5c4113dSnw141292 set v [catch {execsql { 404c5c4113dSnw141292 UPDATE test1 SET f1=11 WHERE x=1025 405c5c4113dSnw141292 }} msg] 406c5c4113dSnw141292 lappend v $msg 407c5c4113dSnw141292} {1 {no such column: x}} 408c5c4113dSnw141292do_test update-9.4 { 409c5c4113dSnw141292 set v [catch {execsql { 410c5c4113dSnw141292 UPDATE test1 SET f1=11 WHERE x(f1)=1025 411c5c4113dSnw141292 }} msg] 412c5c4113dSnw141292 lappend v $msg 413c5c4113dSnw141292} {1 {no such function: x}} 414c5c4113dSnw141292 415c5c4113dSnw141292# Try doing updates on a unique column where the value does not 416c5c4113dSnw141292# really change. 417c5c4113dSnw141292# 418c5c4113dSnw141292do_test update-10.1 { 419c5c4113dSnw141292 execsql { 420c5c4113dSnw141292 DROP TABLE test1; 421c5c4113dSnw141292 CREATE TABLE t1( 422c5c4113dSnw141292 a integer primary key, 423c5c4113dSnw141292 b UNIQUE, 424c5c4113dSnw141292 c, d, 425c5c4113dSnw141292 e, f, 426c5c4113dSnw141292 UNIQUE(c,d) 427c5c4113dSnw141292 ); 428c5c4113dSnw141292 INSERT INTO t1 VALUES(1,2,3,4,5,6); 429c5c4113dSnw141292 INSERT INTO t1 VALUES(2,3,4,4,6,7); 430c5c4113dSnw141292 SELECT * FROM t1 431c5c4113dSnw141292 } 432c5c4113dSnw141292} {1 2 3 4 5 6 2 3 4 4 6 7} 433c5c4113dSnw141292do_test update-10.2 { 434c5c4113dSnw141292 catchsql { 435c5c4113dSnw141292 UPDATE t1 SET a=1, e=9 WHERE f=6; 436c5c4113dSnw141292 SELECT * FROM t1; 437c5c4113dSnw141292 } 438c5c4113dSnw141292} {0 {1 2 3 4 9 6 2 3 4 4 6 7}} 439c5c4113dSnw141292do_test update-10.3 { 440c5c4113dSnw141292 catchsql { 441c5c4113dSnw141292 UPDATE t1 SET a=1, e=10 WHERE f=7; 442c5c4113dSnw141292 SELECT * FROM t1; 443c5c4113dSnw141292 } 444c5c4113dSnw141292} {1 {PRIMARY KEY must be unique}} 445c5c4113dSnw141292do_test update-10.4 { 446c5c4113dSnw141292 catchsql { 447c5c4113dSnw141292 SELECT * FROM t1; 448c5c4113dSnw141292 } 449c5c4113dSnw141292} {0 {1 2 3 4 9 6 2 3 4 4 6 7}} 450c5c4113dSnw141292do_test update-10.5 { 451c5c4113dSnw141292 catchsql { 452c5c4113dSnw141292 UPDATE t1 SET b=2, e=11 WHERE f=6; 453c5c4113dSnw141292 SELECT * FROM t1; 454c5c4113dSnw141292 } 455c5c4113dSnw141292} {0 {1 2 3 4 11 6 2 3 4 4 6 7}} 456c5c4113dSnw141292do_test update-10.6 { 457c5c4113dSnw141292 catchsql { 458c5c4113dSnw141292 UPDATE t1 SET b=2, e=12 WHERE f=7; 459c5c4113dSnw141292 SELECT * FROM t1; 460c5c4113dSnw141292 } 461c5c4113dSnw141292} {1 {column b is not unique}} 462c5c4113dSnw141292do_test update-10.7 { 463c5c4113dSnw141292 catchsql { 464c5c4113dSnw141292 SELECT * FROM t1; 465c5c4113dSnw141292 } 466c5c4113dSnw141292} {0 {1 2 3 4 11 6 2 3 4 4 6 7}} 467c5c4113dSnw141292do_test update-10.8 { 468c5c4113dSnw141292 catchsql { 469c5c4113dSnw141292 UPDATE t1 SET c=3, d=4, e=13 WHERE f=6; 470c5c4113dSnw141292 SELECT * FROM t1; 471c5c4113dSnw141292 } 472c5c4113dSnw141292} {0 {1 2 3 4 13 6 2 3 4 4 6 7}} 473c5c4113dSnw141292do_test update-10.9 { 474c5c4113dSnw141292 catchsql { 475c5c4113dSnw141292 UPDATE t1 SET c=3, d=4, e=14 WHERE f=7; 476c5c4113dSnw141292 SELECT * FROM t1; 477c5c4113dSnw141292 } 478c5c4113dSnw141292} {1 {columns c, d are not unique}} 479c5c4113dSnw141292do_test update-10.10 { 480c5c4113dSnw141292 catchsql { 481c5c4113dSnw141292 SELECT * FROM t1; 482c5c4113dSnw141292 } 483c5c4113dSnw141292} {0 {1 2 3 4 13 6 2 3 4 4 6 7}} 484c5c4113dSnw141292 485c5c4113dSnw141292# Make sure we can handle a subquery in the where clause. 486c5c4113dSnw141292# 487c5c4113dSnw141292do_test update-11.1 { 488c5c4113dSnw141292 execsql { 489c5c4113dSnw141292 UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1); 490c5c4113dSnw141292 SELECT b,e FROM t1; 491c5c4113dSnw141292 } 492c5c4113dSnw141292} {2 14 3 7} 493c5c4113dSnw141292do_test update-11.2 { 494c5c4113dSnw141292 execsql { 495c5c4113dSnw141292 UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1); 496c5c4113dSnw141292 SELECT a,e FROM t1; 497c5c4113dSnw141292 } 498c5c4113dSnw141292} {1 15 2 8} 499c5c4113dSnw141292 500c5c4113dSnw141292integrity_check update-12.1 501c5c4113dSnw141292 502c5c4113dSnw141292# Ticket 602. Updates should occur in the same order as the records 503c5c4113dSnw141292# were discovered in the WHERE clause. 504c5c4113dSnw141292# 505c5c4113dSnw141292do_test update-13.1 { 506c5c4113dSnw141292 execsql { 507c5c4113dSnw141292 BEGIN; 508c5c4113dSnw141292 CREATE TABLE t2(a); 509c5c4113dSnw141292 INSERT INTO t2 VALUES(1); 510c5c4113dSnw141292 INSERT INTO t2 VALUES(2); 511c5c4113dSnw141292 INSERT INTO t2 SELECT a+2 FROM t2; 512c5c4113dSnw141292 INSERT INTO t2 SELECT a+4 FROM t2; 513c5c4113dSnw141292 INSERT INTO t2 SELECT a+8 FROM t2; 514c5c4113dSnw141292 INSERT INTO t2 SELECT a+16 FROM t2; 515c5c4113dSnw141292 INSERT INTO t2 SELECT a+32 FROM t2; 516c5c4113dSnw141292 INSERT INTO t2 SELECT a+64 FROM t2; 517c5c4113dSnw141292 INSERT INTO t2 SELECT a+128 FROM t2; 518c5c4113dSnw141292 INSERT INTO t2 SELECT a+256 FROM t2; 519c5c4113dSnw141292 INSERT INTO t2 SELECT a+512 FROM t2; 520c5c4113dSnw141292 INSERT INTO t2 SELECT a+1024 FROM t2; 521c5c4113dSnw141292 COMMIT; 522c5c4113dSnw141292 SELECT count(*) FROM t2; 523c5c4113dSnw141292 } 524c5c4113dSnw141292} {2048} 525c5c4113dSnw141292do_test update-13.2 { 526c5c4113dSnw141292 execsql { 527c5c4113dSnw141292 SELECT count(*) FROM t2 WHERE a=rowid; 528c5c4113dSnw141292 } 529c5c4113dSnw141292} {2048} 530c5c4113dSnw141292do_test update-13.3 { 531c5c4113dSnw141292 execsql { 532c5c4113dSnw141292 UPDATE t2 SET rowid=rowid-1; 533c5c4113dSnw141292 SELECT count(*) FROM t2 WHERE a=rowid+1; 534c5c4113dSnw141292 } 535c5c4113dSnw141292} {2048} 536c5c4113dSnw141292do_test update-13.3 { 537c5c4113dSnw141292 execsql { 538c5c4113dSnw141292 UPDATE t2 SET rowid=rowid+10000; 539c5c4113dSnw141292 UPDATE t2 SET rowid=rowid-9999; 540c5c4113dSnw141292 SELECT count(*) FROM t2 WHERE a=rowid; 541c5c4113dSnw141292 } 542c5c4113dSnw141292} {2048} 543c5c4113dSnw141292do_test update-13.4 { 544c5c4113dSnw141292 execsql { 545c5c4113dSnw141292 BEGIN; 546c5c4113dSnw141292 INSERT INTO t2 SELECT a+2048 FROM t2; 547c5c4113dSnw141292 INSERT INTO t2 SELECT a+4096 FROM t2; 548c5c4113dSnw141292 INSERT INTO t2 SELECT a+8192 FROM t2; 549c5c4113dSnw141292 SELECT count(*) FROM t2 WHERE a=rowid; 550c5c4113dSnw141292 COMMIT; 551c5c4113dSnw141292 } 552c5c4113dSnw141292} 16384 553c5c4113dSnw141292do_test update-13.5 { 554c5c4113dSnw141292 execsql { 555c5c4113dSnw141292 UPDATE t2 SET rowid=rowid-1; 556c5c4113dSnw141292 SELECT count(*) FROM t2 WHERE a=rowid+1; 557c5c4113dSnw141292 } 558c5c4113dSnw141292} 16384 559c5c4113dSnw141292 560c5c4113dSnw141292integrity_check update-13.6 561c5c4113dSnw141292 562c5c4113dSnw141292 563c5c4113dSnw141292finish_test 564