/ Check-in [528f71e2]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add tests to e_update.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 528f71e29c5422af778dbae2c1dce3b0ee289750
User & Date: dan 2010-09-21 16:59:16
Context
2010-09-21
19:00
Add new file e_delete.test. check-in: 14e8659e user: dan tags: trunk
16:59
Add tests to e_update.test. check-in: 528f71e2 user: dan tags: trunk
2010-09-20
19:17
Add test file e_update.test. check-in: 03985ed4 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/e_insert.test.

34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
do_execsql_test e_insert-0.0 {
  CREATE TABLE a1(a, b);
  CREATE TABLE a2(a, b, c DEFAULT 'xyz');
  CREATE TABLE a3(x DEFAULT 1.0, y DEFAULT 'string', z);
  CREATE TABLE a4(c UNIQUE, d);
} {}

proc delete_all_data {} {
  db eval {SELECT tbl_name AS t FROM sqlite_master WHERE type = 'table'} {
    db eval "DELETE FROM '[string map {' ''} $t]'"
  }
}

proc do_insert_tests {args} {
  uplevel do_select_tests $args
}

# EVIDENCE-OF: R-41448-54465 -- syntax diagram insert-stmt
#
do_insert_tests e_insert-0 {







<
<
<
<
<
<







34
35
36
37
38
39
40






41
42
43
44
45
46
47
do_execsql_test e_insert-0.0 {
  CREATE TABLE a1(a, b);
  CREATE TABLE a2(a, b, c DEFAULT 'xyz');
  CREATE TABLE a3(x DEFAULT 1.0, y DEFAULT 'string', z);
  CREATE TABLE a4(c UNIQUE, d);
} {}







proc do_insert_tests {args} {
  uplevel do_select_tests $args
}

# EVIDENCE-OF: R-41448-54465 -- syntax diagram insert-stmt
#
do_insert_tests e_insert-0 {

Changes to test/e_update.test.

10
11
12
13
14
15
16













17
18
19
20

21
22
23

24

25
26


27
28

29
30
31
32
33
34
35
...
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
...
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
...
292
293
294
295
296
297
298
299
300
301
302
303































































































































































































































































































304

#***********************************************************************
#
# This file implements tests to verify that the "testable statements" in 
# the lang_update.html document are correct.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl














forcedelete test.db2

do_execsql_test e_update-0.0 {

  CREATE TABLE t1(a, b);

  ATTACH 'test.db2' AS aux;

  CREATE TABLE aux.t1(a, b);


  CREATE TABLE t2(a, b, c);



  CREATE TABLE t3(a, b UNIQUE);

} {}

proc do_update_tests {args} {
  uplevel do_select_tests $args
}

# EVIDENCE-OF: R-05685-44205 -- syntax diagram update-stmt
................................................................................
# EVIDENCE-OF: R-40598-36595 For each affected row, the named columns
# are set to the values found by evaluating the corresponding scalar
# expressions.
#
# EVIDENCE-OF: R-40472-60438 Columns that do not appear in the list of
# assignments are left unmodified.
#
do_execsql_test e_update-2.1.0 {
  INSERT INTO t2(rowid, a, b, c) VALUES(1,  3, 1, 4);
  INSERT INTO t2(rowid, a, b, c) VALUES(2,  1, 5, 9);
  INSERT INTO t2(rowid, a, b, c) VALUES(3,  2, 6, 5);
} {}
do_update_tests e_update-2.1 -query {
  SELECT * FROM t2
} {
  1   "UPDATE t2 SET c = 1+1 WHERE a=2" 
      {3 1 4   1 5 9   2 6 2}

  2   "UPDATE t2 SET b = 4/2, c=CAST((0.4*5) AS INTEGER) WHERE a<3"
      {3 1 4   1 2 2   2 2 2}
................................................................................
      {3 1 4   1 5 9   2 6 5}
}

# EVIDENCE-OF: R-09060-20018 If a single column-name appears more than
# once in the list of assignment expressions, all but the rightmost
# occurence is ignored.
#
do_update_tests e_update-2.1 -query {
  SELECT * FROM t2
} {
  1   "UPDATE t2 SET c=5, c=6, c=7 WHERE rowid=1" {3 1 7   1 5 9   2 6 5}
  2   "UPDATE t2 SET c=7, c=6, c=5 WHERE rowid=1" {3 1 5   1 5 9   2 6 5}
  3   "UPDATE t2 SET c=5, b=6, c=7 WHERE rowid=1" {3 6 7   1 5 9   2 6 5}
}

# EVIDENCE-OF: R-36239-04077 The scalar expressions may refer to columns
# of the row being updated.
#
# EVIDENCE-OF: R-04558-24451 In this case all scalar expressions are
# evaluated before any assignments are made.
#
do_execsql_test e_update-2.2.0 {
  DELETE FROM t2;
  INSERT INTO t2(rowid, a, b, c) VALUES(1,  3, 1, 4);
  INSERT INTO t2(rowid, a, b, c) VALUES(2,  1, 5, 9);
  INSERT INTO t2(rowid, a, b, c) VALUES(3,  2, 6, 5);
} {}
do_update_tests e_update-2.2 -query {
  SELECT * FROM t2
} {
  1   "UPDATE t2 SET a=b+c"          {5 1 4     14 5 9   11  6 5}
  2   "UPDATE t2 SET a=b, b=a"       {1 5 4     5 14 9    6 11 5}
  3   "UPDATE t2 SET a=c||c, c=NULL" {44 5 {}  99 14 {}  55 11 {}}
}

# EVIDENCE-OF: R-12619-24112 The optional conflict-clause allows the
# user to nominate a specific constraint conflict resolution algorithm
# to use during this one UPDATE command.
#
do_execsql_test e_update-2.2.0 {
  DELETE FROM t3;
  INSERT INTO t3 VALUES(1, 'one');
  INSERT INTO t3 VALUES(2, 'two');
  INSERT INTO t3 VALUES(3, 'three');
  INSERT INTO t3 VALUES(4, 'four');
} {}
foreach {tn sql error ac data } {
................................................................................

  11 "UPDATE OR REPLACE t3 SET b='four' WHERE a=3"
     {} 0 {2 two 3 four}

  12 "UPDATE OR ROLLBACK t3 SET b='four'"
     {column b is not unique} 1 {2 three 3 one 4 four}
} {
  do_catchsql_test e_update-2.3.$tn.1 $sql [list [expr {$error!=""}] $error]
  do_execsql_test  e_update-2.3.$tn.2 {SELECT * FROM t3} [list {*}$data]
  do_test          e_update-2.3.$tn.3 {sqlite3_get_autocommit db} $ac
}
































































































































































































































































































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>




>

<
|
>
|
>

<
>
>

|
>







 







|




|







 







|













|





|











|







 







|
|
|


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

36
37
38
39
40

41
42
43
44
45
46
47
48
49
50
51
52
...
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
...
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
...
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
#***********************************************************************
#
# This file implements tests to verify that the "testable statements" in 
# the lang_update.html document are correct.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl

#--------------------
# Test organization:
#
#   e_update-1.*: Test statements describing the workings of UPDATE statements.
#
#   e_update-2.*: Test the restrictions on the UPDATE statement syntax that
#                 can be used within triggers.
#
#   e_update-3.*: Test the special LIMIT/OFFSET and ORDER BY clauses that can
#                 be used with UPDATE when SQLite is compiled with
#                 SQLITE_ENABLE_UPDATE_DELETE_LIMIT.
#

forcedelete test.db2

do_execsql_test e_update-0.0 {
  ATTACH 'test.db2' AS aux;
  CREATE TABLE t1(a, b);

  CREATE TABLE t2(a, b, c);
  CREATE TABLE t3(a, b UNIQUE);
  CREATE TABLE t6(x, y);
  CREATE INDEX i1 ON t1(a);


  CREATE TEMP TABLE t4(x, y);
  CREATE TEMP TABLE t6(x, y);

  CREATE TABLE aux.t1(a, b);
  CREATE TABLE aux.t5(a, b);
} {}

proc do_update_tests {args} {
  uplevel do_select_tests $args
}

# EVIDENCE-OF: R-05685-44205 -- syntax diagram update-stmt
................................................................................
# EVIDENCE-OF: R-40598-36595 For each affected row, the named columns
# are set to the values found by evaluating the corresponding scalar
# expressions.
#
# EVIDENCE-OF: R-40472-60438 Columns that do not appear in the list of
# assignments are left unmodified.
#
do_execsql_test e_update-1.5.0 {
  INSERT INTO t2(rowid, a, b, c) VALUES(1,  3, 1, 4);
  INSERT INTO t2(rowid, a, b, c) VALUES(2,  1, 5, 9);
  INSERT INTO t2(rowid, a, b, c) VALUES(3,  2, 6, 5);
} {}
do_update_tests e_update-1.5 -query {
  SELECT * FROM t2
} {
  1   "UPDATE t2 SET c = 1+1 WHERE a=2" 
      {3 1 4   1 5 9   2 6 2}

  2   "UPDATE t2 SET b = 4/2, c=CAST((0.4*5) AS INTEGER) WHERE a<3"
      {3 1 4   1 2 2   2 2 2}
................................................................................
      {3 1 4   1 5 9   2 6 5}
}

# EVIDENCE-OF: R-09060-20018 If a single column-name appears more than
# once in the list of assignment expressions, all but the rightmost
# occurence is ignored.
#
do_update_tests e_update-1.6 -query {
  SELECT * FROM t2
} {
  1   "UPDATE t2 SET c=5, c=6, c=7 WHERE rowid=1" {3 1 7   1 5 9   2 6 5}
  2   "UPDATE t2 SET c=7, c=6, c=5 WHERE rowid=1" {3 1 5   1 5 9   2 6 5}
  3   "UPDATE t2 SET c=5, b=6, c=7 WHERE rowid=1" {3 6 7   1 5 9   2 6 5}
}

# EVIDENCE-OF: R-36239-04077 The scalar expressions may refer to columns
# of the row being updated.
#
# EVIDENCE-OF: R-04558-24451 In this case all scalar expressions are
# evaluated before any assignments are made.
#
do_execsql_test e_update-1.7.0 {
  DELETE FROM t2;
  INSERT INTO t2(rowid, a, b, c) VALUES(1,  3, 1, 4);
  INSERT INTO t2(rowid, a, b, c) VALUES(2,  1, 5, 9);
  INSERT INTO t2(rowid, a, b, c) VALUES(3,  2, 6, 5);
} {}
do_update_tests e_update-1.7 -query {
  SELECT * FROM t2
} {
  1   "UPDATE t2 SET a=b+c"          {5 1 4     14 5 9   11  6 5}
  2   "UPDATE t2 SET a=b, b=a"       {1 5 4     5 14 9    6 11 5}
  3   "UPDATE t2 SET a=c||c, c=NULL" {44 5 {}  99 14 {}  55 11 {}}
}

# EVIDENCE-OF: R-12619-24112 The optional conflict-clause allows the
# user to nominate a specific constraint conflict resolution algorithm
# to use during this one UPDATE command.
#
do_execsql_test e_update-1.8.0 {
  DELETE FROM t3;
  INSERT INTO t3 VALUES(1, 'one');
  INSERT INTO t3 VALUES(2, 'two');
  INSERT INTO t3 VALUES(3, 'three');
  INSERT INTO t3 VALUES(4, 'four');
} {}
foreach {tn sql error ac data } {
................................................................................

  11 "UPDATE OR REPLACE t3 SET b='four' WHERE a=3"
     {} 0 {2 two 3 four}

  12 "UPDATE OR ROLLBACK t3 SET b='four'"
     {column b is not unique} 1 {2 three 3 one 4 four}
} {
  do_catchsql_test e_update-1.8.$tn.1 $sql [list [expr {$error!=""}] $error]
  do_execsql_test  e_update-1.8.$tn.2 {SELECT * FROM t3} [list {*}$data]
  do_test          e_update-1.8.$tn.3 {sqlite3_get_autocommit db} $ac
}



# EVIDENCE-OF: R-12123-54095 The table-name specified as part of an
# UPDATE statement within a trigger body must be unqualified.
#
# EVIDENCE-OF: R-09690-36749 In other words, the database-name. prefix
# on the table name of the UPDATE is not allowed within triggers.
#
do_update_tests e_update-2.1 -error {
  qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers
} {
  1 {
      CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
        UPDATE main.t2 SET a=1, b=2, c=3;
      END;
  } {}

  2 {
      CREATE TRIGGER tr1 BEFORE UPDATE ON t2 BEGIN
        UPDATE aux.t1 SET a=1, b=2;
      END;
  } {}

  3 {
      CREATE TRIGGER tr1 AFTER DELETE ON t4 BEGIN
        UPDATE main.t1 SET a=1, b=2;
      END;
  } {}
}

# EVIDENCE-OF: R-06085-13761 Unless the table to which the trigger is
# attached is in the TEMP database, the table being updated by the
# trigger program must reside in the same database as it.
#
do_update_tests e_update-2.2 -error {
  no such table: %s
} {
  1 {
      CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
        UPDATE t4 SET x=x+1;
      END;
      INSERT INTO t1 VALUES(1, 2);
  } "main.t4"

  2 {
      CREATE TRIGGER aux.tr1 AFTER INSERT ON t5 BEGIN
        UPDATE t4 SET x=x+1;
      END;
      INSERT INTO t5 VALUES(1, 2);
  } "aux.t4"
}
do_execsql_test e_update-2.2.X {
  DROP TRIGGER tr1;
  DROP TRIGGER aux.tr1;
} {}

# EVIDENCE-OF: R-29512-54644 If the table to which the trigger is
# attached is in the TEMP database, then the unqualified name of the
# table being updated is resolved in the same way as it is for a
# top-level statement (by searching first the TEMP database, then the
# main database, then any other databases in the order they were
# attached).
#
do_execsql_test e_update-2.3.0 {
  SELECT 'main', tbl_name FROM main.sqlite_master WHERE type = 'table'
    UNION ALL
  SELECT 'temp', tbl_name FROM sqlite_temp_master WHERE type = 'table'
    UNION ALL
  SELECT 'aux', tbl_name FROM aux.sqlite_master WHERE type = 'table'
} [list {*}{
    main t1
    main t2
    main t3
    main t6
    temp t4
    temp t6
    aux  t1
    aux  t5
}]
do_execsql_test e_update-2.3.1 {
  DELETE FROM main.t6;
  DELETE FROM temp.t6;
  INSERT INTO main.t6 VALUES(1, 2);
  INSERT INTO temp.t6 VALUES(1, 2);

  CREATE TRIGGER temp.tr1 AFTER INSERT ON t4 BEGIN
    UPDATE t6 SET x=x+1;
  END;

  INSERT INTO t4 VALUES(1, 2);
  SELECT * FROM main.t6;
  SELECT * FROM temp.t6;
} {1 2 2 2}
do_execsql_test e_update-2.3.2 {
  DELETE FROM main.t1;
  DELETE FROM aux.t1;
  INSERT INTO main.t1 VALUES(1, 2);
  INSERT INTO aux.t1 VALUES(1, 2);

  CREATE TRIGGER temp.tr2 AFTER DELETE ON t4 BEGIN
    UPDATE t1 SET a=a+1;
  END;

  DELETE FROM t4;
  SELECT * FROM main.t1;
  SELECT * FROM aux.t1;
} {2 2 1 2}
do_execsql_test e_update-2.3.3 {
  DELETE FROM aux.t5;
  INSERT INTO aux.t5 VALUES(1, 2);

  INSERT INTO t4 VALUES('x', 'y');
  CREATE TRIGGER temp.tr3 AFTER UPDATE ON t4 BEGIN
    UPDATE t5 SET a=a+1;
  END;

  UPDATE t4 SET x=10;
  SELECT * FROM aux.t5;
} {2 2}

# EVIDENCE-OF: R-19619-42762 The INDEXED BY and NOT INDEXED clauses are
# not allowed on UPDATE statements within triggers.
#
do_update_tests e_update-2.4 -error {
  the %s %s clause is not allowed on UPDATE or DELETE statements within triggers
} {
  1 {
      CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN
        UPDATE t1 INDEXED BY i1 SET a=a+1;
      END;
  } {INDEXED BY}

  2 {
      CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN
        UPDATE t1 NOT INDEXED SET a=a+1;
      END;
  } {NOT INDEXED}
}

ifcapable update_delete_limit {

# EVIDENCE-OF: R-57359-59558 The LIMIT and ORDER BY clauses for UPDATE
# are unsupported within triggers, regardless of the compilation options
# used to build SQLite.
#
do_update_tests e_update-2.5 -error {
  near "%s": syntax error
} {
  1 {
      CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN
        UPDATE t1 SET a=a+1 LIMIT 10;
      END;
  } {LIMIT}

  2 {
      CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN
        UPDATE t1 SET a=a+1 ORDER BY a LIMIT 10;
      END;
  } {ORDER}

  3 {
      CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN
        UPDATE t1 SET a=a+1 ORDER BY a LIMIT 10 OFFSET 2;
      END;
  } {ORDER}

  4 {
      CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN
        UPDATE t1 SET a=a+1 LIMIT 10 OFFSET 2;
      END;
  } {LIMIT}
}

# EVIDENCE-OF: R-59581-44104 If SQLite is built with the
# SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax
# of the UPDATE statement is extended with optional ORDER BY and LIMIT
# clauses
#
# EVIDENCE-OF: R-07033-64910 -- syntax diagram update-stmt-limited
#
do_update_tests e_update-3.0 {
  1   "UPDATE t1 SET a=b LIMIT 5"                                    {}
  2   "UPDATE t1 SET a=b LIMIT 5-1 OFFSET 2+2"                       {}
  3   "UPDATE t1 SET a=b LIMIT 2+2, 16/4"                            {}
  4   "UPDATE t1 SET a=b ORDER BY a LIMIT 5"                         {}
  5   "UPDATE t1 SET a=b ORDER BY a LIMIT 5-1 OFFSET 2+2"            {}
  6   "UPDATE t1 SET a=b ORDER BY a LIMIT 2+2, 16/4"                 {}
  7   "UPDATE t1 SET a=b WHERE a>2 LIMIT 5"                          {}
  8   "UPDATE t1 SET a=b WHERE a>2 LIMIT 5-1 OFFSET 2+2"             {}
  9   "UPDATE t1 SET a=b WHERE a>2 LIMIT 2+2, 16/4"                  {}
  10  "UPDATE t1 SET a=b WHERE a>2 ORDER BY a LIMIT 5"               {}
  11  "UPDATE t1 SET a=b WHERE a>2 ORDER BY a LIMIT 5-1 OFFSET 2+2"  {}
  12  "UPDATE t1 SET a=b WHERE a>2 ORDER BY a LIMIT 2+2, 16/4"       {}
}

do_execsql_test e_update-3.1.0 {
  CREATE TABLE t7(q, r, s);
  INSERT INTO t7 VALUES(1, 'one',   'X');
  INSERT INTO t7 VALUES(2, 'two',   'X');
  INSERT INTO t7 VALUES(3, 'three', 'X');
  INSERT INTO t7 VALUES(4, 'four',  'X');
  INSERT INTO t7 VALUES(5, 'five',  'X');
  INSERT INTO t7 VALUES(6, 'six',   'X');
  INSERT INTO t7 VALUES(7, 'seven', 'X');
  INSERT INTO t7 VALUES(8, 'eight', 'X');
  INSERT INTO t7 VALUES(9, 'nine',  'X');
  INSERT INTO t7 VALUES(10, 'ten',  'X');
} {}

# EVIDENCE-OF: R-58862-44169 If an UPDATE statement has a LIMIT clause,
# the maximum number of rows that will be updated is found by evaluating
# the accompanying expression and casting it to an integer value.
#
do_update_tests e_update-3.1 -query { SELECT s FROM t7 } {
  1   "UPDATE t7 SET s = q LIMIT 5"            {1 2 3 4 5 X X X X X}
  2   "UPDATE t7 SET s = r WHERE q>2 LIMIT 4"  {1 2 three four five six X X X X}
  3   "UPDATE t7 SET s = q LIMIT 0"            {1 2 three four five six X X X X}
}

# EVIDENCE-OF: R-63582-45120 A negative value is interpreted as "no limit".
#
do_update_tests e_update-3.2 -query { SELECT s FROM t7 } {
  1   "UPDATE t7 SET s = q LIMIT -1"              {1 2 3 4 5 6 7 8 9 10}
  2   "UPDATE t7 SET s = r WHERE q>4 LIMIT -1"  
      {1 2 3 4 five six seven eight nine ten}
  3   "UPDATE t7 SET s = 'X' LIMIT -1"            {X X X X X X X X X X}
}

# EVIDENCE-OF: R-18628-11938 If the LIMIT expression evaluates to
# non-negative value N and the UPDATE statement has an ORDER BY clause,
# then all rows that would be updated in the absence of the LIMIT clause
# are sorted according to the ORDER BY and the first N updated.
#
do_update_tests e_update-3.3 -query { SELECT s FROM t7 } {
  1   "UPDATE t7 SET s = q ORDER BY r LIMIT 3"      {X X X 4 5 X X 8 X X}
  2   "UPDATE t7 SET s = r ORDER BY r DESC LIMIT 2" {X two three 4 5 X X 8 X X}
  3   "UPDATE t7 SET s = q ORDER BY q DESC LIMIT 5" {X two three 4 5 6 7 8 9 10}

  X   "UPDATE t7 SET s = 'X'"                       {X X X X X X X X X X}
}

# EVIDENCE-OF: R-30955-38324 If the UPDATE statement also has an OFFSET
# clause, then it is similarly evaluated and cast to an integer value.
# If the OFFSET expression evaluates to a non-negative value M, then the
# first M rows are skipped and the following N rows updated instead.
#
do_update_tests e_update-3.3 -query { SELECT s FROM t7 } {
  1   "UPDATE t7 SET s = q ORDER BY q LIMIT 3 OFFSET 2"  {X X 3 4 5 X X X X X}
  2   "UPDATE t7 SET s = q ORDER BY q DESC LIMIT 2, 3 "  {X X 3 4 5 6 7 8 X X}

  X   "UPDATE t7 SET s = 'X'"                       {X X X X X X X X X X}
}

# EVIDENCE-OF: R-19486-35828 If the UPDATE statement has no ORDER BY
# clause, then all rows that would be updated in the absence of the
# LIMIT clause are assembled in an arbitrary order before applying the
# LIMIT and OFFSET clauses to determine which are actually updated.
#
#     In practice, "arbitrary order" is rowid order. This is also tested
#     by e_update-3.2.* above.
#
do_update_tests e_update-3.4 -query { SELECT s FROM t7 } {
  1   "UPDATE t7 SET s = q LIMIT 4, 2"        {X X X X 5 6 X X X X}
  2   "UPDATE t7 SET s = q LIMIT 2 OFFSET 7"  {X X X X 5 6 X 8 9 X}
}

# EVIDENCE-OF: R-10927-26133 The ORDER BY clause on an UPDATE statement
# is used only to determine which rows fall within the LIMIT. The order
# in which rows are modified is arbitrary and is not influenced by the
# ORDER BY clause.
#
do_execsql_test e_update-3.5.0 {
  CREATE TABLE t8(x);
  CREATE TRIGGER tr7 BEFORE UPDATE ON t7 BEGIN
    INSERT INTO t8 VALUES(old.q);
  END;
} {}
do_update_tests e_update-3.5 -query { SELECT x FROM t8 ; DELETE FROM t8 } {
  1   "UPDATE t7 SET s = q ORDER BY r LIMIT -1"        {1 2 3 4 5 6 7 8 9 10}
  2   "UPDATE t7 SET s = q ORDER BY r ASC LIMIT -1"    {1 2 3 4 5 6 7 8 9 10}
  3   "UPDATE t7 SET s = q ORDER BY r DESC LIMIT -1"   {1 2 3 4 5 6 7 8 9 10}
  4   "UPDATE t7 SET s = q ORDER BY q DESC LIMIT 5"    {6 7 8 9 10}
}


} ;# ifcapable update_delete_limit
 
finish_test

Changes to test/tester.tcl.

398
399
400
401
402
403
404





405
406
407
408
409
410
411
    } else {
      set res [list 1 [string trim [format $errfmt {*}$res]]]
      uplevel do_catchsql_test ${prefix}.${tn} [list $sql] [list $res]
    }
  }
}







# Run an SQL script.  
# Return the number of microseconds per statement.
#
proc speed_trial {name numstmt units sql} {
  puts -nonewline [format {%-21.21s } $name...]
  flush stdout







>
>
>
>
>







398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
    } else {
      set res [list 1 [string trim [format $errfmt {*}$res]]]
      uplevel do_catchsql_test ${prefix}.${tn} [list $sql] [list $res]
    }
  }
}

proc delete_all_data {} {
  db eval {SELECT tbl_name AS t FROM sqlite_master WHERE type = 'table'} {
    db eval "DELETE FROM '[string map {' ''} $t]'"
  }
}

# Run an SQL script.  
# Return the number of microseconds per statement.
#
proc speed_trial {name numstmt units sql} {
  puts -nonewline [format {%-21.21s } $name...]
  flush stdout