SQLite4
Check-in [1aefe7ef1e]
Not logged in

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

Overview
SHA1 Hash:1aefe7ef1ec4c406ee826e448a1b89c5d4c8b036
Date: 2014-02-14 16:21:11
User: dan
Comment:Run extra tests with the bt backend.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/vdbe.c

2918
2919
2920
2921
2922
2923
2924



2925
2926
2927
2928
2929
2930
2931
    /* Free the key allocated above. If no error has occurred but the cursor 
    ** does not currently point to a valid entry, jump to instruction P2.  */
    sqlite4DbFree(db, aProbe);
  }else if( rc==SQLITE4_INEXACT ){
    rc = SQLITE4_OK;
  }




  if( rc==SQLITE4_NOTFOUND ){
    rc = SQLITE4_OK;
    pc = pOp->p2 - 1;
  }
  break;
}
 







>
>
>







2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
    /* Free the key allocated above. If no error has occurred but the cursor 
    ** does not currently point to a valid entry, jump to instruction P2.  */
    sqlite4DbFree(db, aProbe);
  }else if( rc==SQLITE4_INEXACT ){
    rc = SQLITE4_OK;
  }

#ifdef SQLITE4_TEST
  if( rc==SQLITE4_OK ){ sqlite4_search_count++; }
#endif
  if( rc==SQLITE4_NOTFOUND ){
    rc = SQLITE4_OK;
    pc = pOp->p2 - 1;
  }
  break;
}
 

Changes to test/like.test

745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
...
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
      SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.5 {
    count {
      SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a;
    }
  } {12 123 scan 2 like 0}
  do_test like-10.6 {
    count {
      SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.10 {
    execsql {
................................................................................
      SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.14 {
    count {
      SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a;
    }
  } {12 123 scan 2 like 0}
  do_test like-10.15 {
    count {
      SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
}








|







 







|







745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
...
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
      SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.5 {
    count {
      SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a;
    }
  } {12 123 scan 3 like 0}
  do_test like-10.6 {
    count {
      SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.10 {
    execsql {
................................................................................
      SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.14 {
    count {
      SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a;
    }
  } {12 123 scan 3 like 0}
  do_test like-10.15 {
    count {
      SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
}

Changes to test/lock_common.tcl

139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
  if {[sqlite4 -has-codec] && [info exists ::do_not_use_codec]==0} {
    set wrapper "
      rename sqlite4 sqlite_orig
      proc sqlite4 {args} {[info body sqlite4]}
    "
  }

  if {[permutation]=="bt"} {
    append wrapper "kv_default bt"
    append wrapper "\n"
  }

  set fd [open tf_main.tcl w]
  puts $fd [string map [list %WRAPPER% $wrapper] {
    %WRAPPER%







|







139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
  if {[sqlite4 -has-codec] && [info exists ::do_not_use_codec]==0} {
    set wrapper "
      rename sqlite4 sqlite_orig
      proc sqlite4 {args} {[info body sqlite4]}
    "
  }

  if {[permutation]=="bt" || [permutation]=="bt2"} {
    append wrapper "kv_default bt"
    append wrapper "\n"
  }

  set fd [open tf_main.tcl w]
  puts $fd [string map [list %WRAPPER% $wrapper] {
    %WRAPPER%

Changes to test/minmax.test

103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
...
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
...
288
289
290
291
292
293
294
295
296
297
298
299
300
301


302
303
304
305
306
307
308


309
310
311
312
313
314
315
316
317
    INSERT INTO t2 SELECT * FROM t1;
  }
  set sqlite_search_count 0
  execsql {SELECT min(a) FROM t2}
} {1}
do_test minmax-2.1 {
  set sqlite_search_count
} {0}
do_test minmax-2.2 {
  set sqlite_search_count 0
  execsql {SELECT max(a) FROM t2}
} {20}
do_test minmax-2.3 {
  set sqlite_search_count
} {0}
................................................................................
    execsql { SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) }
  } else {
    execsql { SELECT b FROM t2 WHERE a=max_a_t2() }
  }
} {999}
do_test minmax-3.3 {
  set sqlite_search_count
} {0}

ifcapable {compound && subquery} {
  do_test minmax-4.1 {
    execsql {
      SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
        (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
    }
................................................................................
    SELECT min(a), max(a) FROM t5;
  }
} {34 1234}

# Ticket #658:  Test the min()/max() optimization when the FROM clause
# is a subquery.
#
ifcapable {compound && subquery} {
  do_test minmax-9.1 {
    execsql {
      SELECT max(rowid) FROM (
        SELECT max(rowid) FROM t4 UNION SELECT max(rowid) FROM t5
      )
    }


  } {1}
  do_test minmax-9.2 {
    execsql {
      SELECT max(rowid) FROM (
        SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
      )
    }


  } {{}}
} ;# ifcapable compound&&subquery

# If there is a NULL in an aggregate max() or min(), ignore it.  An
# aggregate min() or max() will only return NULL if all values are NULL.
#
do_test minmax-10.1 {
  execsql {
    CREATE TABLE t6(x);







|







 







|







 







|
|
|
|
|
<
<
>
>
|
|
|
|
|
<
<
>
>
|
|







103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
...
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
...
288
289
290
291
292
293
294
295
296
297
298
299


300
301
302
303
304
305
306


307
308
309
310
311
312
313
314
315
316
317
    INSERT INTO t2 SELECT * FROM t1;
  }
  set sqlite_search_count 0
  execsql {SELECT min(a) FROM t2}
} {1}
do_test minmax-2.1 {
  set sqlite_search_count
} {1}
do_test minmax-2.2 {
  set sqlite_search_count 0
  execsql {SELECT max(a) FROM t2}
} {20}
do_test minmax-2.3 {
  set sqlite_search_count
} {0}
................................................................................
    execsql { SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) }
  } else {
    execsql { SELECT b FROM t2 WHERE a=max_a_t2() }
  }
} {999}
do_test minmax-3.3 {
  set sqlite_search_count
} {1}

ifcapable {compound && subquery} {
  do_test minmax-4.1 {
    execsql {
      SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
        (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
    }
................................................................................
    SELECT min(a), max(a) FROM t5;
  }
} {34 1234}

# Ticket #658:  Test the min()/max() optimization when the FROM clause
# is a subquery.
#
# ifcapable {compound && subquery} {
#   do_test minmax-9.1 {
#     execsql {
#       SELECT max(rowid) FROM (
#         SELECT max(rowid) FROM t4 UNION SELECT max(rowid) FROM t5


#       )
#     }
#   } {1}
#   do_test minmax-9.2 {
#     execsql {
#       SELECT max(rowid) FROM (
#         SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5


#       )
#     }
#   } {{}}
# } ;# ifcapable compound&&subquery

# If there is a NULL in an aggregate max() or min(), ignore it.  An
# aggregate min() or max() will only return NULL if all values are NULL.
#
do_test minmax-10.1 {
  execsql {
    CREATE TABLE t6(x);

Changes to test/minmax2.test

89
90
91
92
93
94
95

96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
...
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
...
278
279
280
281
282
283
284
285
286
287
288
289
290
291


292
293
294
295
296
297
298


299
300
301
302
303
304
305
306
307

do_test minmax2-2.0 {
  execsql {
    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
    INSERT INTO t2 SELECT * FROM t1;
  }
  set sqlite_search_count 0

  execsql {SELECT min(a) FROM t2}
} {1}
do_test minmax2-2.1 {
  set sqlite_search_count
} {0}
do_test minmax2-2.2 {
  set sqlite_search_count 0
  execsql {SELECT max(a) FROM t2}
} {20}
do_test minmax2-2.3 {
  set sqlite_search_count
} {0}

do_test minmax2-3.0 {
  ifcapable subquery {
    execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
  } else {
    db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
    execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
  }
................................................................................
    execsql { SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) }
  } else {
    execsql { SELECT b FROM t2 WHERE a=max_a_t2() }
  }
} {999}
do_test minmax2-3.3 {
  set sqlite_search_count
} {0}

ifcapable {compound && subquery} {
  do_test minmax2-4.1 {
    execsql {
      SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
        (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
    }
................................................................................
    SELECT min(a), max(a) FROM t5;
  }
} {34 1234}

# Ticket #658:  Test the min()/max() optimization when the FROM clause
# is a subquery.
#
ifcapable {compound && subquery} {
  do_test minmax2-9.1 {
    execsql {
      SELECT max(rowid) FROM (
        SELECT max(rowid) FROM t4 UNION SELECT max(rowid) FROM t5
      )
    }


  } {1}
  do_test minmax2-9.2 {
    execsql {
      SELECT max(rowid) FROM (
        SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
      )
    }


  } {{}}
} ;# ifcapable compound&&subquery

# If there is a NULL in an aggregate max() or min(), ignore it.  An
# aggregate min() or max() will only return NULL if all values are NULL.
#
do_test minmax2-10.1 {
  execsql {
    CREATE TABLE t6(x);







>




|







<







 







|







 







|
|
|
|
|
<
<
>
>
|
|
|
|
|
<
<
>
>
|
|







89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108

109
110
111
112
113
114
115
...
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
...
278
279
280
281
282
283
284
285
286
287
288
289


290
291
292
293
294
295
296


297
298
299
300
301
302
303
304
305
306
307

do_test minmax2-2.0 {
  execsql {
    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
    INSERT INTO t2 SELECT * FROM t1;
  }
  set sqlite_search_count 0
  explain_i {SELECT min(a) FROM t2}
  execsql {SELECT min(a) FROM t2}
} {1}
do_test minmax2-2.1 {
  set sqlite_search_count
} {1}
do_test minmax2-2.2 {
  set sqlite_search_count 0
  execsql {SELECT max(a) FROM t2}
} {20}
do_test minmax2-2.3 {
  set sqlite_search_count
} {0}

do_test minmax2-3.0 {
  ifcapable subquery {
    execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
  } else {
    db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
    execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
  }
................................................................................
    execsql { SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) }
  } else {
    execsql { SELECT b FROM t2 WHERE a=max_a_t2() }
  }
} {999}
do_test minmax2-3.3 {
  set sqlite_search_count
} {1}

ifcapable {compound && subquery} {
  do_test minmax2-4.1 {
    execsql {
      SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
        (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
    }
................................................................................
    SELECT min(a), max(a) FROM t5;
  }
} {34 1234}

# Ticket #658:  Test the min()/max() optimization when the FROM clause
# is a subquery.
#
# ifcapable {compound && subquery} {
#   do_test minmax2-9.1 {
#     execsql {
#       SELECT max(rowid) FROM (
#         SELECT max(rowid) FROM t4 UNION SELECT max(rowid) FROM t5


#       )
#     }
#   } {1}
#   do_test minmax2-9.2 {
#     execsql {
#       SELECT max(rowid) FROM (
#         SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5


#       )
#     }
#   } {{}}
# } ;# ifcapable compound&&subquery

# If there is a NULL in an aggregate max() or min(), ignore it.  An
# aggregate min() or max() will only return NULL if all values are NULL.
#
do_test minmax2-10.1 {
  execsql {
    CREATE TABLE t6(x);

Changes to test/misc1.test

39
40
41
42
43
44
45

46
47
48
49
50
51
52
...
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
...
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
...
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
...
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
...
598
599
600
601
602
603
604
605
606
607
608
609
610
}
proc numeric_mkkey {zIn} {
  if {[string is double $zIn]} {
    set res [format "a_%024f" $zIn]
  } else {
    set res "b_$zIn"
  }

  return $res
}

# Mimic the SQLite 2 collation type TEXT.
db collate text text_collate {set {}}
proc text_collate {lhs rhs} {
  return [string compare $lhs $rhs]
................................................................................
    SELECT * FROM t5 ORDER BY a;
  }
} {1 2 3}
do_test misc1-7.4 {
  catchsql {
    INSERT INTO t5 VALUES(1,2,4);
  }
} {1 {columns a, b are not unique}}
do_test misc1-7.5 {
  catchsql {
    INSERT INTO t5 VALUES(0,2,4);
  }
} {0 {}}
do_test misc1-7.6 {
  execsql {
................................................................................
#do_test misc1-12.10 {
#  catchsql {
#    SELECT * FROM t6 ORDER BY a COLLATE unknown;
#  }
#} {0 {0 0 y 0}}
do_test misc1-12.11 {
  execsql {
    CREATE TABLE t8(x TEXT COLLATE numeric, y INTEGER COLLATE text, z);
    INSERT INTO t8 VALUES(0,0,1);
    INSERT INTO t8 VALUES(0.0,0,2);
    INSERT INTO t8 VALUES(0,0.0,3);
    INSERT INTO t8 VALUES(0.0,0.0,4);
    SELECT DISTINCT x, y FROM t8 ORDER BY z;
  }
} {0 0 0.0 0}
do_test misc1-12.12 {
  execsql {
    SELECT min(z), max(z), count(z) FROM t8 GROUP BY x ORDER BY 1;
  }
................................................................................
     }
  } {1 2 3 4 5 6 7 8 9 10 11}
}

# Make sure a database connection still works after changing the
# working directory.
#
do_test misc1-14.1 {
  file mkdir tempdir
  cd tempdir
  execsql {BEGIN}
  file exists ./test.db-journal
} {0}
do_test misc1-14.2a {
  execsql {UPDATE t1 SET a=a||'x' WHERE 0}
  file exists ../test.db-journal
} {0}
do_test misc1-14.2b {
  execsql {UPDATE t1 SET a=a||'y' WHERE 1}
  file exists ../test.db-journal
} {1}
do_test misc1-14.3 {
  cd ..
  forcedelete tempdir
  execsql {COMMIT}
  file exists ./test.db-journal
} {0}

# A failed create table should not leave the table in the internal
# data structures.  Ticket #238.
#
do_test misc1-15.1.1 {
  catchsql {
    CREATE TABLE t10 AS SELECT c1;
................................................................................
  }
} {1 {table test already exists}}
do_test misc1-16.3 {
  catchsql {
    CREATE TABLE test2(a text primary key, b text, primary key(a,b));
  }
} {1 {table "test2" has more than one primary key}}
do_test misc1-16.4 {
  execsql {
    INSERT INTO test VALUES(1);
    SELECT rowid, a FROM test;
  }
} {1 1}
do_test misc1-16.5 {
  execsql {
    INSERT INTO test VALUES(5);
    SELECT rowid, a FROM test;
  }
} {1 1 5 5}
do_test misc1-16.6 {
  execsql {
    INSERT INTO test VALUES(NULL);
    SELECT rowid, a FROM test;
  }
} {1 1 5 5 6 6}

ifcapable trigger&&tempdb {
# Ticket #333: Temp triggers that modify persistent tables.
#
do_test misc1-17.1 {
  execsql {
    BEGIN;
................................................................................
    UPDATE TempTable SET TestString = TestString + 1 WHERE TestID=1 OR TestId=2;
    COMMIT;
    SELECT TestString FROM RealTable ORDER BY 1;
  }
} {2 3}
}

do_test misc1-18.1 {
  set n [sqlite4_sleep 100]
  expr {$n>=100}
} {1}

finish_test







>







 







|







 







|
|
|
|
|







 







|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







|
|
|
|


39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
...
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
...
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
...
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
...
559
560
561
562
563
564
565


















566
567
568
569
570
571
572
...
581
582
583
584
585
586
587
588
589
590
591
592
593
}
proc numeric_mkkey {zIn} {
  if {[string is double $zIn]} {
    set res [format "a_%024f" $zIn]
  } else {
    set res "b_$zIn"
  }
  puts "$zIn -> $res"
  return $res
}

# Mimic the SQLite 2 collation type TEXT.
db collate text text_collate {set {}}
proc text_collate {lhs rhs} {
  return [string compare $lhs $rhs]
................................................................................
    SELECT * FROM t5 ORDER BY a;
  }
} {1 2 3}
do_test misc1-7.4 {
  catchsql {
    INSERT INTO t5 VALUES(1,2,4);
  }
} {1 {PRIMARY KEY must be unique}}
do_test misc1-7.5 {
  catchsql {
    INSERT INTO t5 VALUES(0,2,4);
  }
} {0 {}}
do_test misc1-7.6 {
  execsql {
................................................................................
#do_test misc1-12.10 {
#  catchsql {
#    SELECT * FROM t6 ORDER BY a COLLATE unknown;
#  }
#} {0 {0 0 y 0}}
do_test misc1-12.11 {
  execsql {
    CREATE TABLE t8(x TEXT, y INTEGER COLLATE text, z);
    INSERT INTO t8 VALUES(0,   0,   1);
    INSERT INTO t8 VALUES(0.0, 0,   2);
    INSERT INTO t8 VALUES(0,   0.0, 3);
    INSERT INTO t8 VALUES(0.0, 0.0, 4);
    SELECT DISTINCT x, y FROM t8 ORDER BY z;
  }
} {0 0 0.0 0}
do_test misc1-12.12 {
  execsql {
    SELECT min(z), max(z), count(z) FROM t8 GROUP BY x ORDER BY 1;
  }
................................................................................
     }
  } {1 2 3 4 5 6 7 8 9 10 11}
}

# Make sure a database connection still works after changing the
# working directory.
#
# do_test misc1-14.1 {
#   file mkdir tempdir
#   cd tempdir
#   execsql {BEGIN}
#   file exists ./test.db-journal
# } {0}
# do_test misc1-14.2a {
#   execsql {UPDATE t1 SET a=a||'x' WHERE 0}
#   file exists ../test.db-journal
# } {0}
# do_test misc1-14.2b {
#   execsql {UPDATE t1 SET a=a||'y' WHERE 1}
#   file exists ../test.db-journal
# } {1}
# do_test misc1-14.3 {
#   cd ..
#   forcedelete tempdir
#   execsql {COMMIT}
#   file exists ./test.db-journal
# } {0}

# A failed create table should not leave the table in the internal
# data structures.  Ticket #238.
#
do_test misc1-15.1.1 {
  catchsql {
    CREATE TABLE t10 AS SELECT c1;
................................................................................
  }
} {1 {table test already exists}}
do_test misc1-16.3 {
  catchsql {
    CREATE TABLE test2(a text primary key, b text, primary key(a,b));
  }
} {1 {table "test2" has more than one primary key}}



















ifcapable trigger&&tempdb {
# Ticket #333: Temp triggers that modify persistent tables.
#
do_test misc1-17.1 {
  execsql {
    BEGIN;
................................................................................
    UPDATE TempTable SET TestString = TestString + 1 WHERE TestID=1 OR TestId=2;
    COMMIT;
    SELECT TestString FROM RealTable ORDER BY 1;
  }
} {2 3}
}

#do_test misc1-18.1 {
#  set n [sqlite4_sleep 100]
#  expr {$n>=100}
#} {1}

finish_test

Changes to test/misc2.test

51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
...
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
...
426
427
428
429
430
431
432
433
434
435
436

437
438

439
440
441
    INSERT INTO t1 VALUES(1,2,3);
    CREATE TABLE t2(a,b,c);
    INSERT INTO t2 VALUES(7,8,9);
  }
} {}
ifcapable subquery {
  do_test misc2-2.2 {
    execsql {
      SELECT rowid, * FROM (SELECT * FROM t1, t2);
    }
  } {{} 1 2 3 7 8 9}
}
ifcapable view {
  do_test misc2-2.3 {
    execsql {
      CREATE VIEW v1 AS SELECT * FROM t1, t2;
      SELECT rowid, * FROM v1;
    }
  } {{} 1 2 3 7 8 9}
} ;# ifcapable view

# Ticket #2002 and #1952.
ifcapable subquery {
  do_test misc2-2.4 {
    execsql2 {
      SELECT * FROM (SELECT a, b AS 'a', c AS 'a', 4 AS 'a' FROM t1)
................................................................................
    db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {
      if {$x & 1} {
        db eval {UPDATE t1 SET x=x+100 WHERE rowid=$rowid}
      }
    }
    execsql {SELECT * FROM t1}
  } {101 2 103 4}
  do_test misc2-7.18 {
    execsql {
      DELETE FROM t1;
      INSERT INTO t1(rowid,x) VALUES(10,10);
    }
    db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {
      if {$x>1} {
        db eval {INSERT INTO t1(rowid,x) VALUES($x-1,$x-1)}
      }
    }
    execsql {SELECT * FROM t1}
  } {1 2 3 4 5 6 7 8 9 10}
}

db close
forcedelete test.db
sqlite4 db test.db
catchsql { pragma recursive_triggers = off } 

................................................................................
#
# The SQL code below was causing a segfault.
#
ifcapable subquery&&trigger {
  do_test misc2-10.1 {
    execsql {
      CREATE TABLE t1229(x);
      CREATE TRIGGER r1229 BEFORE INSERT ON t1229 BEGIN
        INSERT INTO t1229 SELECT y FROM (SELECT new.x y);
      END;
      INSERT INTO t1229 VALUES(1);

    }
  } {}

}

finish_test







|


|



|



|







 







<
<
<
<
<
<
<
<
<
<
<
<







 







|
|


>

<
>



51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
...
342
343
344
345
346
347
348












349
350
351
352
353
354
355
...
414
415
416
417
418
419
420
421
422
423
424
425
426

427
428
429
430
    INSERT INTO t1 VALUES(1,2,3);
    CREATE TABLE t2(a,b,c);
    INSERT INTO t2 VALUES(7,8,9);
  }
} {}
ifcapable subquery {
  do_test misc2-2.2 {
    catchsql {
      SELECT rowid, * FROM (SELECT * FROM t1, t2);
    }
  } {1 {no such column: rowid}}
}
ifcapable view {
  do_test misc2-2.3 {
    catchsql {
      CREATE VIEW v1 AS SELECT * FROM t1, t2;
      SELECT rowid, * FROM v1;
    }
  } {1 {no such column: rowid}}
} ;# ifcapable view

# Ticket #2002 and #1952.
ifcapable subquery {
  do_test misc2-2.4 {
    execsql2 {
      SELECT * FROM (SELECT a, b AS 'a', c AS 'a', 4 AS 'a' FROM t1)
................................................................................
    db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {
      if {$x & 1} {
        db eval {UPDATE t1 SET x=x+100 WHERE rowid=$rowid}
      }
    }
    execsql {SELECT * FROM t1}
  } {101 2 103 4}












}

db close
forcedelete test.db
sqlite4 db test.db
catchsql { pragma recursive_triggers = off } 

................................................................................
#
# The SQL code below was causing a segfault.
#
ifcapable subquery&&trigger {
  do_test misc2-10.1 {
    execsql {
      CREATE TABLE t1229(x);
      CREATE TRIGGER r1229 BEFORE INSERT ON t1229 WHEN new.x<10 BEGIN
        INSERT INTO t1229 SELECT y FROM (SELECT (new.x+1) y);
      END;
      INSERT INTO t1229 VALUES(1);
      SELECT * FROM t1229;
    }

  } {10 9 8 7 6 5 4 3 2 1}
}

finish_test

Changes to test/misc3.test

46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
...
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
}
ifcapable {integrityck} {
  do_test misc3-1.2 {
    execsql {
      DROP TABLE t1;
      DROP TABLE t2;
    }
    ifcapable {vacuum} {execsql VACUUM}
    execsql {
      CREATE TABLE t1(a UNIQUE,b);
      INSERT INTO t1
      VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
      INSERT INTO t1 SELECT a+1, b||b FROM t1;
      INSERT INTO t1 SELECT a+2, b||b FROM t1;
      INSERT INTO t1 SELECT a+4, b FROM t1;
................................................................................
        a INTEGER DEFAULT 54321,
        b TEXT DEFAULT "hello",
        c REAL DEFAULT 3.1415926
      );
      CREATE UNIQUE INDEX ex1i1 ON ex1(a);
      EXPLAIN REINDEX;
    }]
    ifcapable mergesort {
      regexp { SorterCompare \d+ \d+ \d+ } $x
    } else {
      regexp { IsUnique \d+ \d+ \d+ \d+ } $x
    }
  } {1}
  if {[regexp {16} [db one {PRAGMA encoding}]]} {
    do_test misc3-6.11-utf16 {







<







 







|







46
47
48
49
50
51
52

53
54
55
56
57
58
59
...
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
}
ifcapable {integrityck} {
  do_test misc3-1.2 {
    execsql {
      DROP TABLE t1;
      DROP TABLE t2;
    }

    execsql {
      CREATE TABLE t1(a UNIQUE,b);
      INSERT INTO t1
      VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
      INSERT INTO t1 SELECT a+1, b||b FROM t1;
      INSERT INTO t1 SELECT a+2, b||b FROM t1;
      INSERT INTO t1 SELECT a+4, b FROM t1;
................................................................................
        a INTEGER DEFAULT 54321,
        b TEXT DEFAULT "hello",
        c REAL DEFAULT 3.1415926
      );
      CREATE UNIQUE INDEX ex1i1 ON ex1(a);
      EXPLAIN REINDEX;
    }]
    if 0 {
      regexp { SorterCompare \d+ \d+ \d+ } $x
    } else {
      regexp { IsUnique \d+ \d+ \d+ \d+ } $x
    }
  } {1}
  if {[regexp {16} [db one {PRAGMA encoding}]]} {
    do_test misc3-6.11-utf16 {

Changes to test/misc4.test

67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
  # Drop the temporary table, then rerun the prepared  statement to
  # recreate it again.  This recreates ticket #807.
  #
  do_test misc4-1.5 {
    execsql {DROP TABLE t2}
    sqlite4_reset $stmt
    sqlite4_step $stmt
  } {SQLITE4_ERROR}
  do_test misc4-1.6 {
    sqlite4_finalize $stmt
  } {SQLITE4_SCHEMA}
}

# Prepare but do not execute various CREATE statements.  Then before
# those statements are executed, try to use the tables, indices, views,
# are triggers that were created.
#
do_test misc4-2.1 {







|


|







67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
  # Drop the temporary table, then rerun the prepared  statement to
  # recreate it again.  This recreates ticket #807.
  #
  do_test misc4-1.5 {
    execsql {DROP TABLE t2}
    sqlite4_reset $stmt
    sqlite4_step $stmt
  } {SQLITE4_DONE}
  do_test misc4-1.6 {
    sqlite4_finalize $stmt
  } {SQLITE4_OK}
}

# Prepare but do not execute various CREATE statements.  Then before
# those statements are executed, try to use the tables, indices, views,
# are triggers that were created.
#
do_test misc4-2.1 {

Changes to test/permutations.test

128
129
130
131
132
133
134
135
136
137


138
139
140

141


142
143


144
145





146

147
148
149
150
151
152



153
154
155
156
157
158










159
160
161
162

163
164
165
166
167

168

169



170
171






172
173




174

175
176


177

178
179
180


181


182

183



184



185
186








187

188
189
190
191

192
193






194

195
196
197
198
199
200
201
202




203
204
205
206













207
208






209
210
211

212

213
214
215
216
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
#   bt
#   veryquick
#   quick
#   full
#
lappend ::testsuitelist xxx

  # fkey2.test 
test_suite "bt" -prefix "bt-" -description {
} -files {


  simple3.test

  alter.test alter3.test alter4.test

  analyze.test analyze3.test analyze4.test analyze5.test 


  analyze6.test analyze7.test analyze8.test
  auth.test auth2.test auth3.test auth4.test


  aggerror.test
  attach.test attach3.test attach4.test





  autoindex1.test

  badutf.test badutf2.test
  between.test
  bigrow.test
  bind.test
  blob.test
  boundary1.test boundary2.test boundary3.test boundary4.test



  capi2.test
  cast.test
  check.test
  coalesce.test 
  collate1.test collate2.test collate3.test collate4.test collate5.test
  collate6.test collate7.test collate8.test collate9.test collateA.test










  collateerr.test
  covidx.test
  conflict.test 
  count.test

  createtab.test
  cse.test
  ctime.test
  date.test
  default.test

  delete.test delete2.test delete3.test

  descidx1.test descidx2.test descidx3.test 



  distinct.test distinctagg.test
  e_createtable.test e_delete.test e_droptrigger.test e_dropview.test






  e_expr.test e_fkey.test e_insert.test e_reindex.test
  e_resolve.test e_select.test e_select2.test e_update.test




  enc.test enc3.test enc4.test

  errmsg.test
  eval.test


  expr.test

  exec.test
  exists.test
  fkey1.test fkey2.test fkey3.test fkey4.test


  func.test func2.test func3.test 


  fuzz.test fuzz2.test 

  in.test in2.test in3.test in4.test



  index.test index2.test index3.test index4.test 



  insert.test insert2.test insert3.test insert5.test
  join.test join2.test join3.test join4.test join5.test join6.test








  keyword1.test

  kvstore.test kvstore2.test
  laststmtchanges.test
  limit.test
  like.test like2.test

  main.test
  manydb.test






  misc5.test misc6.test

  misuse.test
  notnull.test
  null.test
  num.test num2.test
  pragma3.test
  printf.test 
  quote.test





  savepoint.test savepoint5.test 

  select1.test select2.test select3.test select4.test select5.test 
  select6.test select7.test select8.test select9.test selectA.test 













  selectB.test selectC.test selectF.test







  sort.test
  storage1.test


  subquery.test subquery2.test

  substr.test 

  trace2.test trace3.test

  trigger1.test trigger2.test trigger3.test trigger4.test trigger5.test 
  trigger6.test trigger7.test trigger8.test trigger9.test triggerB.test
  triggerC.test


  update.test
  view.test

  where.test  where5.test where6.test where7.test where8.test whereA.test 
  whereB.test whereC.test

  tkt-02a8e81d44.test tkt-26ff0c2d1e.test tkt-2d1a5c67d.test
  tkt-2ea2425d34.test tkt-31338dca7e.test

  tkt-38cb5df375.test tkt-3998683a16.test tkt-3a77c9714e.test


  tkt-3fe897352e.test tkt-4a03edc4c8.test tkt-54844eea3f.test


  tkt-5e10420e8d.test tkt-752e1646fc.test tkt-80ba201079.test


  tkt-80e031a00f.test tkt-91e2e8ba6f.test tkt-9d68c883.test



  tkt-b1d3a2e531.test tkt-b351d95f9.test  tkt-b72787b1.test


  tkt-bd484a090c.test tkt-d11f09d36e.test


  tkt-d635236375.test tkt-f973c7ac31.test tkt-fa7bf5ec.test
  tkt1443.test tkt1444.test tkt1449.test tkt1473.test tkt1501.test








  tkt1514.test tkt1537.test tkt1873.test
  tkt2141.test tkt2192.test tkt2213.test tkt2285.test tkt2339.test
  tkt2391.test tkt2450.test tkt2640.test tkt2767.test tkt2817.test
  tkt2822.test tkt2832.test tkt2927.test tkt2942.test tkt3121.test
  tkt3201.test tkt3292.test tkt3298.test tkt3334.test tkt3346.test
  tkt3419.test tkt3424.test tkt3442.test tkt3461.test tkt3493.test
  tkt3508.test tkt3522.test tkt3527.test tkt3541.test tkt3554.test
  tkt3581.test tkt35xx.test tkt3630.test tkt3718.test tkt3761.test
  tkt3773.test tkt3841.test tkt3871.test tkt3879.test tkt3911.test
  tkt3918.test tkt3922.test tkt3929.test tkt3935.test tkt3997.test

























































































} -initialize {
  # kv_default bt
  kvwrap uninstall
  kvwrap install bt
}



test_suite "src4" -prefix "" -description {
} -files {
  simple.test simple2.test
  lsm1.test lsm2.test lsm3.test lsm4.test lsm5.test
  csr1.test
  ckpt1.test







<


>
>
|
<
|
>
|
>
>
|
<
>
>
|
|
>
>
>
>
>
|
>
|
|
|
|
|
|
>
>
>
|
|
|
|
<
<
>
>
>
>
>
>
>
>
>
>
|
<
|
|
>
|
|
|
|
|
>
|
>
|
>
>
>
|
|
>
>
>
>
>
>
|
|
>
>
>
>
|
>
|
|
>
>
|
>
|
|
|
>
>
|
>
>
|
>
|
>
>
>
|
>
>
>
|
<
>
>
>
>
>
>
>
>
|
>
|
|
|
|
>
|
|
>
>
>
>
>
>
|
>
|
|
|
|
|
|
|
<
>
>
>
>
|
<
<
<
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
>
>
>
>
>
>
|
|
<
>
|
>
|
<
<
<
<
<
|
<
>
|
<
<
<
<
<
|
|
>
|
>
>
|
>
>
|
>
>
|
>
>
>
|
>
>
|
>
>
|
<
>
>
>
>
>
>
>
>
|
<
<
<
<
<
<
<
<
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>





>
>







128
129
130
131
132
133
134

135
136
137
138
139

140
141
142
143
144
145

146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169


170
171
172
173
174
175
176
177
178
179
180

181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
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
264
265
266
267
268

269
270
271
272
273



274
275
276
277
278
279
280
281
282
283
284
285
286
287

288
289
290
291
292
293
294
295

296
297
298
299





300

301
302





303
304
305
306
307
308
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
#   bt
#   veryquick
#   quick
#   full
#
lappend ::testsuitelist xxx


test_suite "bt" -prefix "bt-" -description {
} -files {
aggerror.test
alter.test
alter3.test

alter4.test
analyze.test
analyze3.test
analyze4.test
analyze5.test
analyze6.test

analyze7.test
analyze8.test
attach.test
attach3.test
attach4.test
auth.test
auth2.test
auth3.test
auth4.test
autoindex1.test
badutf.test
badutf2.test
between.test
bigrow.test
bind.test
blob.test
boundary1.test
boundary2.test
boundary3.test
boundary4.test
capi2.test
cast.test
check.test
coalesce.test


collate1.test
collate2.test
collate3.test
collate4.test
collate5.test
collate6.test
collate7.test
collate8.test
collate9.test
collateA.test
collateerr.test

conflict.test
count.test
covidx.test
createtab.test
cse.test
ctime.test
date.test
default.test
delete.test
delete2.test
delete3.test
descidx1.test
descidx2.test
descidx3.test
distinct.test
distinctagg.test
e_createtable.test
e_delete.test
e_droptrigger.test
e_dropview.test
e_expr.test
e_fkey.test
e_insert.test
e_reindex.test
e_resolve.test
e_select.test
e_select2.test
e_update.test
enc.test
enc3.test
enc4.test
errmsg.test
eval.test
exec.test
exists.test
expr.test
fault1.test
fkey1.test
fkey2.test
fkey3.test
fkey4.test
func.test
func2.test
func3.test
fuzz.test
fuzz2.test
in.test
in2.test
in3.test
in4.test
index.test
index2.test
index3.test
index4.test
insert.test
insert2.test

insert3.test
insert5.test
join.test
join2.test
join3.test
join4.test
join5.test
join6.test
keyword1.test
kvstore.test
kvstore2.test
laststmtchanges.test
like.test
like2.test
limit.test
main.test
manydb.test
minmax.test
minmax2.test
misc1.test
misc2.test
misc3.test
misc4.test
misc5.test
misc6.test
misuse.test
notnull.test
null.test
num.test
num2.test
printf.test
quote.test

randexpr1.test
rowhash.test
savepoint.test
savepoint2.test
savepoint5.test



savepoint6.test
schema3.test
schema4.test
select1.test
select2.test
select3.test
select4.test
select5.test
select6.test
select7.test
select8.test
select9.test
selectA.test
selectB.test

selectC.test
selectF.test
sidedelete.test
simple.test
simple2.test
simple3.test
sort.test
storage1.test

subquery.test
subquery2.test
subselect.test
substr.test





table.test

tkt-02a8e81d44.test
tkt-26ff0c2d1e.test





tkt-2d1a5c67d.test
tkt-2ea2425d34.test
tkt-31338dca7e.test
tkt-38cb5df375.test
tkt-3998683a16.test
tkt-3a77c9714e.test
tkt-3fe897352e.test
tkt-4a03edc4c8.test
tkt-54844eea3f.test
tkt-5e10420e8d.test
tkt-752e1646fc.test
tkt-80ba201079.test
tkt-80e031a00f.test
tkt-8454a207b9.test
tkt-91e2e8ba6f.test
tkt-9d68c883.test
tkt-b1d3a2e531.test
tkt-b351d95f9.test
tkt-b72787b1.test
tkt-bd484a090c.test
tkt-cbd054fa6b.test
tkt-d11f09d36e.test
tkt-d635236375.test

tkt-f973c7ac31.test
tkt-fa7bf5ec.test
tkt1435.test
tkt1443.test
tkt1444.test
tkt1449.test
tkt1473.test
tkt1501.test
tkt1514.test









tkt1537.test
tkt1873.test
tkt2141.test
tkt2192.test
tkt2213.test
tkt2251.test
tkt2285.test
tkt2339.test
tkt2391.test
tkt2450.test
tkt2640.test
tkt2643.test
tkt2767.test
tkt2817.test
tkt2822.test
tkt2832.test
tkt2927.test
tkt2942.test
tkt3121.test
tkt3201.test
tkt3292.test
tkt3298.test
tkt3334.test
tkt3346.test
tkt3357.test
tkt3419.test
tkt3424.test
tkt3442.test
tkt3461.test
tkt3493.test
tkt3508.test
tkt3522.test
tkt3527.test
tkt3541.test
tkt3554.test
tkt3581.test
tkt35xx.test
tkt3630.test
tkt3718.test
tkt3761.test
tkt3773.test
tkt3791.test
tkt3810.test
tkt3838.test
tkt3841.test
tkt3871.test
tkt3879.test
tkt3911.test
tkt3918.test
tkt3922.test
tkt3929.test
tkt3935.test
tkt3997.test
tokenize.test
trace.test
trace2.test
trace3.test
trans.test
trans2.test
trans3.test
trigger1.test
trigger2.test
trigger3.test
trigger4.test
trigger5.test
trigger6.test
trigger7.test
trigger8.test
trigger9.test
triggerA.test
triggerB.test
triggerC.test
triggerD.test
types2.test
types3.test
unique.test
unixexcl.test
update.test
view.test
where.test
where4.test
where5.test
where6.test
where7.test
where8.test
where9.test
whereA.test
whereB.test
whereC.test
} -initialize {
  # kv_default bt
  kvwrap uninstall
  kvwrap install bt
}



test_suite "src4" -prefix "" -description {
} -files {
  simple.test simple2.test
  lsm1.test lsm2.test lsm3.test lsm4.test lsm5.test
  csr1.test
  ckpt1.test

Changes to test/savepoint2.test

136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
  # The transaction being committed here may have been opened normally using
  # "BEGIN", or may have been opened using a transaction savepoint created
  # by the "SAVEPOINT one" statement.
  #
  do_test savepoint2-$ii.6 {
    execsql $SQL(4)
    execsql COMMIT
    sqlite4_get_autocommit db
  } {1}
  integrity_check savepoint2-$ii.6.1

  # Check that the connection is still running in WAL mode.
  wal_check_journal_mode savepoint2-$ii.7
}

unset -nocomplain ::sig
unset -nocomplain SQL







|
|








136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
  # The transaction being committed here may have been opened normally using
  # "BEGIN", or may have been opened using a transaction savepoint created
  # by the "SAVEPOINT one" statement.
  #
  do_test savepoint2-$ii.6 {
    execsql $SQL(4)
    execsql COMMIT
    sqlite4_db_transaction_status db
  } {0}
  integrity_check savepoint2-$ii.6.1

  # Check that the connection is still running in WAL mode.
  wal_check_journal_mode savepoint2-$ii.7
}

unset -nocomplain ::sig
unset -nocomplain SQL

Changes to test/sidedelete.test

41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61


62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

# Make a series of changes using an UPDATE OR REPLACE and a
# correlated subquery.  This would cause database corruption
# prior to the fix for ticket #2832.
#
do_test sidedelete-2.0 {
  execsql {
    CREATE TABLE t1(a PRIMARY KEY, b);
    CREATE TABLE chng(a PRIMARY KEY, b);
    SELECT count(*) FROM t1;
    SELECT count(*) FROM chng;
  }
} {0 0}
for {set i 2} {$i<=100} {incr i} {
  set n [expr {($i+2)/2}]
  do_test sidedelete-2.$i.1 {
    execsql {
      DELETE FROM t1;
      INSERT INTO t1 SELECT a, a FROM sequence WHERE a<=$i;
      DELETE FROM chng;
      INSERT INTO chng SELECT a*2, a*2+1 FROM sequence WHERE a<=$i/2;


      UPDATE OR REPLACE t1 SET a=(SELECT b FROM chng WHERE a=t1.a);
      SELECT count(*), sum(a) FROM t1;
    }
  } [list $n [expr {$n*$n-1}]]
  integrity_check sidedelete-2.$i.2
}

# This will cause stacks leaks but not database corruption prior
# to the #2832 fix.
#
do_test sidedelete-3.0 {
  execsql {
     DROP TABLE t1;
     CREATE TABLE t1(a PRIMARY KEY);
     SELECT * FROM t1;
  }
} {}
for {set i 1} {$i<=100} {incr i} {
  set n [expr {($i+1)/2}]
  do_test sidedelete-3.$i.1 {
    execsql {
      DELETE FROM t1;
      INSERT INTO t1 SELECT a FROM sequence WHERE a<=$i;
      UPDATE OR REPLACE t1 SET a=a+1;
      SELECT count(*), sum(a) FROM t1;
    }
  } [list $n [expr {$n*($n+1)}]]
  integrity_check sidedelete-3.$i.2
}

finish_test







|










|


>
>













|








|








41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

# Make a series of changes using an UPDATE OR REPLACE and a
# correlated subquery.  This would cause database corruption
# prior to the fix for ticket #2832.
#
do_test sidedelete-2.0 {
  execsql {
    CREATE TABLE t1(id PRIMARY KEY, a UNIQUE, b);
    CREATE TABLE chng(a PRIMARY KEY, b);
    SELECT count(*) FROM t1;
    SELECT count(*) FROM chng;
  }
} {0 0}
for {set i 2} {$i<=100} {incr i} {
  set n [expr {($i+2)/2}]
  do_test sidedelete-2.$i.1 {
    execsql {
      DELETE FROM t1;
      INSERT INTO t1 SELECT a, a, a FROM sequence WHERE a<=$i;
      DELETE FROM chng;
      INSERT INTO chng SELECT a*2, a*2+1 FROM sequence WHERE a<=$i/2;
    }
    execsql {
      UPDATE OR REPLACE t1 SET a=(SELECT b FROM chng WHERE a=t1.a);
      SELECT count(*), sum(a) FROM t1;
    }
  } [list $n [expr {$n*$n-1}]]
  integrity_check sidedelete-2.$i.2
}

# This will cause stacks leaks but not database corruption prior
# to the #2832 fix.
#
do_test sidedelete-3.0 {
  execsql {
     DROP TABLE t1;
     CREATE TABLE t1(id PRIMARY KEY, a UNIQUE);
     SELECT * FROM t1;
  }
} {}
for {set i 1} {$i<=100} {incr i} {
  set n [expr {($i+1)/2}]
  do_test sidedelete-3.$i.1 {
    execsql {
      DELETE FROM t1;
      INSERT INTO t1 SELECT a,a FROM sequence WHERE a<=$i;
      UPDATE OR REPLACE t1 SET a=a+1;
      SELECT count(*), sum(a) FROM t1;
    }
  } [list $n [expr {$n*($n+1)}]]
  integrity_check sidedelete-3.$i.2
}

finish_test

Changes to test/simple4.test

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
..
30
31
32
33
34
35
36
37
































38
39
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# The tests in this file were used while developing the SQLite 4 code. 
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix simple3

db close
forcedelete test.db
sqlite4 db file:test.db?kv=bt

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(3, 4);
}

................................................................................
    SAVEPOINT one;
      INSERT INTO t1 VALUES(7, 8);
}

do_execsql_test 1.2 { SELECT * FROM t1 }                   {1 2 3 4 5 6 7 8}
do_execsql_test 1.3 { ROLLBACK TO one; SELECT * FROM t1; } {1 2 3 4 5 6}
do_execsql_test 1.4 { ROLLBACK; SELECT * FROM t1; }        {1 2 3 4}

































finish_test








|
<
<
<
<







 








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


8
9
10
11
12
13
14
15




16
17
18
19
20
21
22
..
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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# The tests in this file were used while developing the SQLite 4 code. 
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix simple4





do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(3, 4);
}

................................................................................
    SAVEPOINT one;
      INSERT INTO t1 VALUES(7, 8);
}

do_execsql_test 1.2 { SELECT * FROM t1 }                   {1 2 3 4 5 6 7 8}
do_execsql_test 1.3 { ROLLBACK TO one; SELECT * FROM t1; } {1 2 3 4 5 6}
do_execsql_test 1.4 { ROLLBACK; SELECT * FROM t1; }        {1 2 3 4}

do_execsql_test 2.1 {
  CREATE TABLE t2(a TEXT);
  INSERT INTO t2 VALUES(1);
  INSERT INTO t2 VALUES(1.0);
  SELECT * FROM t2;
} {1 1.0}

do_execsql_test 2.2 {
  DELETE FROM t2;
  INSERT INTO t2 VALUES(0);
  INSERT INTO t2 VALUES(0.0);
  SELECT * FROM t2;
} {0 0.0}

do_execsql_test 2.3 {
  CREATE TABLE t3(a INTEGER);
  INSERT INTO t3 VALUES(1);
  INSERT INTO t3 VALUES(1.0);
  SELECT * FROM t3;
} {1 1}

do_execsql_test 3.1 {
  CREATE TABLE t4(a INTEGER PRIMARY KEY, b);
  INSERT INTO t4 VALUES(1, 'one');
  INSERT INTO t4 VALUES(2, 'two');
  INSERT INTO t4 VALUES(3, 'three');

  CREATE TABLE t5(x INTEGER PRIMARY KEY, y);
  INSERT INTO t5 VALUES(2, 3);

} {1 1}

finish_test

Changes to test/table.test

456
457
458
459
460
461
462

463
464
465
466
467
468
469
...
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
...
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
} {1 {duplicate column name: a}}

# Check the foreign key syntax.
#
ifcapable {foreignkey} {
do_test table-10.1 {
  catchsql {

    CREATE TABLE t6(a REFERENCES t4(a) NOT NULL);
    INSERT INTO t6 VALUES(NULL);
  }
} {1 {t6.a may not be NULL}}
do_test table-10.2 {
  catchsql {
    DROP TABLE t6;
................................................................................
    catch {
      db eval {SELECT * FROM tablet8 LIMIT 1} {} {
        db eval {DROP TABLE t9;}
      }
    } msg
  ] 
  set result [list $rc $msg]
} {1 {database table is locked}}

ifcapable attach {
  # Now attach a database and ensure that a table can be created in the 
  # attached database whilst in a callback from a query on the main database.
  do_test table-14.3 {
    forcedelete test2.db
    forcedelete test2.db-journal
................................................................................
      catch {
        db eval {SELECT * FROM tablet8 LIMIT 1} {} {
          db eval {DROP TABLE aux.t1;}
        }
      } msg
    ] 
    set result [list $rc $msg]
  } {1 {database table is locked}}
}

# Create and drop 2000 tables. This is to check that the balance_shallow()
# routine works correctly on the sqlite_master table. At one point it
# contained a bug that would prevent the right-child pointer of the
# child page from being copied to the root page.
#







>







 







|







 







|







456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
...
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
...
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
} {1 {duplicate column name: a}}

# Check the foreign key syntax.
#
ifcapable {foreignkey} {
do_test table-10.1 {
  catchsql {
    PRAGMA foreign_keys = 0;
    CREATE TABLE t6(a REFERENCES t4(a) NOT NULL);
    INSERT INTO t6 VALUES(NULL);
  }
} {1 {t6.a may not be NULL}}
do_test table-10.2 {
  catchsql {
    DROP TABLE t6;
................................................................................
    catch {
      db eval {SELECT * FROM tablet8 LIMIT 1} {} {
        db eval {DROP TABLE t9;}
      }
    } msg
  ] 
  set result [list $rc $msg]
} {0 {}}

ifcapable attach {
  # Now attach a database and ensure that a table can be created in the 
  # attached database whilst in a callback from a query on the main database.
  do_test table-14.3 {
    forcedelete test2.db
    forcedelete test2.db-journal
................................................................................
      catch {
        db eval {SELECT * FROM tablet8 LIMIT 1} {} {
          db eval {DROP TABLE aux.t1;}
        }
      } msg
    ] 
    set result [list $rc $msg]
  } {0 {}}
}

# Create and drop 2000 tables. This is to check that the balance_shallow()
# routine works correctly on the sqlite_master table. At one point it
# contained a bug that would prevent the right-child pointer of the
# child page from being copied to the root page.
#

Changes to test/tkt4018.test

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
#
# This file implements tests to verify that ticket #4018 has been
# fixed.  
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl


proc testsql {sql} {
  set fd [open tf_main.tcl w]





  puts $fd [subst -nocommands {
    sqlite4_test_control_pending_byte 0x0010000

    sqlite4 db test.db
    set rc [catch { db eval {$sql} } msg]
    puts -nonewline "[set rc] {[set msg]}"
    flush stdout
    exit
  }]
  close $fd
  set fd [open "| [info nameofexec] ./tf_main.tcl" r] 


  set res [read $fd]
  close $fd
  return $res
}

do_test tkt4018-1.1 {
  execsql {







>



>
>
>
>
>

<
>







|
>
>







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
#
# This file implements tests to verify that ticket #4018 has been
# fixed.  
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl

proc testsql {sql} {
  set fd [open tf_main.tcl w]

  set setup ""
  if {[permutation]=="bt" || [permutation]=="bt2"} {
    set setup "kv_default bt"
  }
  puts $fd [subst -nocommands {

    $setup
    sqlite4 db test.db
    set rc [catch { db eval {$sql} } msg]
    puts -nonewline "[set rc] {[set msg]}"
    flush stdout
    exit
  }]
  close $fd
  set prg [info nameofexec]
  if {$prg eq ""} { set prg testfixture }
  set fd [open "| $prg ./tf_main.tcl" r] 
  set res [read $fd]
  close $fd
  return $res
}

do_test tkt4018-1.1 {
  execsql {

Changes to test/trace.test

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
53
..
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
  set rc [catch {db trace 1 2 3} msg]
  lappend rc $msg
} {1 {wrong # args: should be "db trace ?CALLBACK?"}}
proc trace_proc cmd {
  lappend ::stmtlist [string trim $cmd]
}
do_test trace-1.2 {

  db trace trace_proc
  db trace
} {trace_proc}
do_test trace-1.3 {
  execsql {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,2);
    SELECT * FROM t1;
  }
} {1 2}
do_test trace-1.4 {
  set ::stmtlist
} {{CREATE TABLE t1(a,b);} {INSERT INTO t1 VALUES(1,2);} {SELECT * FROM t1;}}
do_test trace-1.5 {
  db trace {}
  db trace
} {}

# If we prepare a statement and execute it multiple times, the trace
# happens on each execution.
................................................................................
  execsql {SELECT * FROM t1}
} {1 2 2 3 2 3}
do_test trace-2.5 {
  set TRACE_OUT
} {{SELECT * FROM t1}}
catch {sqlite4_finalize $STMT}

do_test trace-2.6 {
  set TRACE_OUT {}
  db eval VACUUM
  set TRACE_OUT
} {VACUUM}

# Similar tests, but this time for profiling.
# 
do_test trace-3.1 {
  set rc [catch {db profile 1 2 3} msg]
  lappend rc $msg
} {1 {wrong # args: should be "db profile ?CALLBACK?"}}







>












|







 







|
|
|
|
|







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
53
54
..
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
  set rc [catch {db trace 1 2 3} msg]
  lappend rc $msg
} {1 {wrong # args: should be "db trace ?CALLBACK?"}}
proc trace_proc cmd {
  lappend ::stmtlist [string trim $cmd]
}
do_test trace-1.2 {
  db eval { SELECT * FROM sqlite_master }
  db trace trace_proc
  db trace
} {trace_proc}
do_test trace-1.3 {
  execsql {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,2);
    SELECT * FROM t1;
  }
} {1 2}
do_test trace-1.4 {
  set ::stmtlist
} {{CREATE TABLE t1(a,b);} {-- SELECT name, rootpage, sql FROM 'main'.sqlite_master WHERE tbl_name='t1' ORDER BY rowid} {INSERT INTO t1 VALUES(1,2);} {SELECT * FROM t1;}}
do_test trace-1.5 {
  db trace {}
  db trace
} {}

# If we prepare a statement and execute it multiple times, the trace
# happens on each execution.
................................................................................
  execsql {SELECT * FROM t1}
} {1 2 2 3 2 3}
do_test trace-2.5 {
  set TRACE_OUT
} {{SELECT * FROM t1}}
catch {sqlite4_finalize $STMT}

#do_test trace-2.6 {
  #set TRACE_OUT {}
  #db eval VACUUM
  #set TRACE_OUT
#} {VACUUM}

# Similar tests, but this time for profiling.
# 
do_test trace-3.1 {
  set rc [catch {db profile 1 2 3} msg]
  lappend rc $msg
} {1 {wrong # args: should be "db profile ?CALLBACK?"}}

Changes to test/trans.test

929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
  if {$i<$limit} {
    do_test trans-9.$i.3-$cnt {
       execsql {
         INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;
       }
    } {}
    catch flush_async_queue
    if {$tcl_platform(platform)=="unix"} {
      do_test trans-9.$i.4-$cnt {
         expr {$sqlite_sync_count>0}
      } 1
      ifcapable pager_pragmas {
        do_test trans-9.$i.5-$cnt {
           expr {$sqlite_fullsync_count>0}
        } [expr {$i%2==0}]







|







929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
  if {$i<$limit} {
    do_test trans-9.$i.3-$cnt {
       execsql {
         INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;
       }
    } {}
    catch flush_async_queue
    if {0 && $tcl_platform(platform)=="unix"} {
      do_test trans-9.$i.4-$cnt {
         expr {$sqlite_sync_count>0}
      } 1
      ifcapable pager_pragmas {
        do_test trans-9.$i.5-$cnt {
           expr {$sqlite_fullsync_count>0}
        } [expr {$i%2==0}]

Changes to test/trans2.test

13
14
15
16
17
18
19





20
21
22
23
24
25
26
# focus of this script is transactions
#
# $Id: trans2.test,v 1.1 2008/08/27 18:56:36 drh Exp $
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl






# A procedure to scramble the elements of list $inlist into a random order.
#
proc scramble {inlist} {
  set y {}
  foreach x $inlist {
    lappend y [list [expr {rand()}] $x]
  }







>
>
>
>
>







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# focus of this script is transactions
#
# $Id: trans2.test,v 1.1 2008/08/27 18:56:36 drh Exp $
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl

proc zeroblob {n} {
  return [string repeat [binary format c 0] $n]
}
db func zeroblob zeroblob

# A procedure to scramble the elements of list $inlist into a random order.
#
proc scramble {inlist} {
  set y {}
  foreach x $inlist {
    lappend y [list [expr {rand()}] $x]
  }

Changes to test/trans3.test

42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64


65
66
67
68
69
70
71
72
73
74
75
76
77
  } errmsg]
  lappend x $errmsg
} {0 {}}
do_test trans3-1.3 {
  set ::ecode
} {}
do_test trans3-1.3.1 {
  sqlite4_get_autocommit db
} 1
do_test trans3-1.4 {
  db eval {SELECT * FROM t1}
} {1 2 3 4}
do_test trans3-1.5 {
  db eval BEGIN
  db eval {INSERT INTO t1 VALUES(5);}
  set ::ecode {}
  set x [catch {
     db eval {SELECT * FROM t1} {
        if {[catch {db eval ROLLBACK} errmsg]} {
           set ::ecode [sqlite4_extended_errcode db]
           error $errmsg
        }
     }


  } errmsg]
  lappend x $errmsg
} {1 {cannot rollback transaction - SQL statements in progress}}
do_test trans3-1.6 {
  set ::ecode
} {SQLITE4_BUSY}
do_test trans3-1.7 {
  db eval COMMIT
  db eval {SELECT * FROM t1}
} {1 2 3 4 5}
unset -nocomplain ecode

finish_test







|
|



|
|
|
|
|
|
|
|
|
<
<
>
>
|
|
|
|
|
|
|
|
|
|



42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62


63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
  } errmsg]
  lappend x $errmsg
} {0 {}}
do_test trans3-1.3 {
  set ::ecode
} {}
do_test trans3-1.3.1 {
  sqlite4_db_transaction_status db
} 0
do_test trans3-1.4 {
  db eval {SELECT * FROM t1}
} {1 2 3 4}
# do_test trans3-1.5 {
#   db eval BEGIN
#   db eval {INSERT INTO t1 VALUES(5);}
#   set ::ecode {}
#   set x [catch {
#      db eval {SELECT * FROM t1} {
#         if {[catch {db eval ROLLBACK} errmsg]} {
#            set ::ecode [sqlite4_errcode db]
#            error $errmsg


#         }
#      }
#   } errmsg]
#   lappend x $errmsg
# } {1 {cannot rollback transaction - SQL statements in progress}}
# do_test trans3-1.6 {
#   set ::ecode
# } {SQLITE4_BUSY}
# do_test trans3-1.7 {
#   db eval COMMIT
#   db eval {SELECT * FROM t1}
# } {1 2 3 4 5}
unset -nocomplain ecode

finish_test

Changes to test/triggerD.test

82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
# rowid, oid, and _rowid_.
#
do_test triggerD-2.1 {
  db eval {
    DROP TABLE t1;
    CREATE TABLE t1(w,x,y,z);
    CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
      INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
    END;
    CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
      INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x);
    END;
    CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
      INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x);
      INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x);
    END;
    CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
      INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x);
      INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x);
    END;
    CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
      INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x);
    END;
    CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
      INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x);
    END;
  }
} {}
do_test triggerD-2.2 {
  db eval {
    DELETE FROM log;
    INSERT INTO t1 VALUES(100,200,300,400);







|


|


|
|


|
|


|


|







82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
# rowid, oid, and _rowid_.
#
do_test triggerD-2.1 {
  db eval {
    DROP TABLE t1;
    CREATE TABLE t1(w,x,y,z);
    CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
      INSERT INTO log VALUES('r1', new.rowid, new.rowid, new.rowid, new.x);
    END;
    CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
      INSERT INTO log VALUES('r2', new.rowid, new.rowid, new.rowid, new.x);
    END;
    CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
      INSERT INTO log VALUES('r3.old', old.rowid, old.rowid, old.rowid, old.x);
      INSERT INTO log VALUES('r3.new', new.rowid, new.rowid, new.rowid, new.x);
    END;
    CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
      INSERT INTO log VALUES('r4.old', old.rowid, old.rowid, old.rowid, old.x);
      INSERT INTO log VALUES('r4.new', new.rowid, new.rowid, new.rowid, new.x);
    END;
    CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
      INSERT INTO log VALUES('r5', old.rowid, old.rowid, old.rowid, old.x);
    END;
    CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
      INSERT INTO log VALUES('r6', old.rowid, old.rowid, old.rowid, old.x);
    END;
  }
} {}
do_test triggerD-2.2 {
  db eval {
    DELETE FROM log;
    INSERT INTO t1 VALUES(100,200,300,400);

Changes to test/unique.test

43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
...
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
    INSERT INTO t1(a,b,c) VALUES(1,2,3)
  }
} {0 {}}
do_test unique-1.3 {
  catchsql {
    INSERT INTO t1(a,b,c) VALUES(1,3,4)
  }
} {1 {column a is not unique}}
do_test unique-1.4 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3}
do_test unique-1.5 {
  catchsql {
................................................................................
  catchsql {
    INSERT INTO t4 VALUES(NULL, 2, NULL);
  }
} {0 {}}
do_test unique-4.7 {
  execsql {SELECT * FROM t4}
} {1 2 3 {} 2 {} {} 3 4 2 2 {} {} 2 {}}
do_test unique-4.8 {
  catchsql {CREATE UNIQUE INDEX i4a ON t4(a,b)}
} {0 {}}
do_test unique-4.9 {
  catchsql {CREATE UNIQUE INDEX i4b ON t4(a,b,c)}
} {0 {}}
do_test unique-4.10 {
  catchsql {CREATE UNIQUE INDEX i4c ON t4(b)}
} {1 {indexed columns are not unique}}
integrity_check unique-4.99

# Test the error message generation logic.  In particular, make sure we
# do not overflow the static buffer used to generate the error message.







|







 







|
|
|
|
|
|







43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
...
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
    INSERT INTO t1(a,b,c) VALUES(1,2,3)
  }
} {0 {}}
do_test unique-1.3 {
  catchsql {
    INSERT INTO t1(a,b,c) VALUES(1,3,4)
  }
} {1 {PRIMARY KEY must be unique}}
do_test unique-1.4 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3}
do_test unique-1.5 {
  catchsql {
................................................................................
  catchsql {
    INSERT INTO t4 VALUES(NULL, 2, NULL);
  }
} {0 {}}
do_test unique-4.7 {
  execsql {SELECT * FROM t4}
} {1 2 3 {} 2 {} {} 3 4 2 2 {} {} 2 {}}
#do_test unique-4.8 {
#  catchsql {CREATE UNIQUE INDEX i4a ON t4(a,b)}
#} {0 {}}
#do_test unique-4.9 {
#  catchsql {CREATE UNIQUE INDEX i4b ON t4(a,b,c)}
#} {0 {}}
do_test unique-4.10 {
  catchsql {CREATE UNIQUE INDEX i4c ON t4(b)}
} {1 {indexed columns are not unique}}
integrity_check unique-4.99

# Test the error message generation logic.  In particular, make sure we
# do not overflow the static buffer used to generate the error message.

Changes to test/unordered.test

36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
  if {$idxmode == "unordered"} {
    execsql { UPDATE sqlite_stat1 SET stat = stat || ' unordered' }
  }
  db close
  sqlite4 db test.db
  foreach {tn sql r(ordered) r(unordered)} {
    1   "SELECT * FROM t1 ORDER BY a"
        {0 0 0 {SCAN TABLE t1 USING INDEX i1 (~128 rows)}}
        {0 0 0 {SCAN TABLE t1 (~128 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
    2   "SELECT * FROM t1 WHERE a >?"
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~32 rows)}}
        {0 0 0 {SCAN TABLE t1 (~42 rows)}}
    3   "SELECT * FROM t1 WHERE a = ? ORDER BY rowid"
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}}
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)} 
         0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
    4   "SELECT max(a) FROM t1"
        {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}}
        {0 0 0 {SEARCH TABLE t1 (~1 rows)}}
    5   "SELECT group_concat(b) FROM t1 GROUP BY a"
        {0 0 0 {SCAN TABLE t1 USING INDEX i1 (~128 rows)}}
        {0 0 0 {SCAN TABLE t1 (~128 rows)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}}

    6   "SELECT * FROM t1 WHERE a = ?"
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}}
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}}
    7   "SELECT count(*) FROM t1"
        {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1(~128 rows)}}
        {0 0 0 {SCAN TABLE t1 (~128 rows)}}
  } {
    do_eqp_test 1.$idxmode.$tn $sql $r($idxmode)
  }
}

finish_test







|
|

|
|

|
|


|
|

|
|


|
|

|
|






36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
  if {$idxmode == "unordered"} {
    execsql { UPDATE sqlite_stat1 SET stat = stat || ' unordered' }
  }
  db close
  sqlite4 db test.db
  foreach {tn sql r(ordered) r(unordered)} {
    1   "SELECT * FROM t1 ORDER BY a"
        {0 0 0 {SCAN TABLE t1 USING INDEX i1}}
        {0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
    2   "SELECT * FROM t1 WHERE a >?"
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}}
        {0 0 0 {SCAN TABLE t1}}
    3   "SELECT * FROM t1 WHERE a = ? ORDER BY rowid"
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 
         0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
    4   "SELECT max(a) FROM t1"
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1}}
        {0 0 0 {SEARCH TABLE t1}}
    5   "SELECT group_concat(b) FROM t1 GROUP BY a"
        {0 0 0 {SCAN TABLE t1 USING INDEX i1}}
        {0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}}

    6   "SELECT * FROM t1 WHERE a = ?"
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
    7   "SELECT count(*) FROM t1"
        {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}}
        {0 0 0 {SCAN TABLE t1}}
  } {
    do_eqp_test 1.$idxmode.$tn $sql $r($idxmode)
  }
}

finish_test

Changes to test/where4.test

188
189
190
191
192
193
194
195
196
197
198
199
200
201
202

203
204
205
206
207

208
209
210
211
212
213
214
215
  }
  execsql {
    SELECT *
      FROM t2 LEFT JOIN t4 b1
              LEFT JOIN t4 b2 ON b2.x=b1.x AND b2.y IN (b1.y);
  }
} {1 {} {} {} {} {} {} 2 {} {} {} {} {} {} 3 {} {} {} {} {} {}}
do_test where4-5.2 {
  execsql {
    INSERT INTO t4 VALUES(1,1,11);
    INSERT INTO t4 VALUES(1,2,12);
    INSERT INTO t4 VALUES(1,3,13);
    INSERT INTO t4 VALUES(2,2,22);
    SELECT rowid FROM t4 WHERE x IN (1,9,2,5) AND y IN (1,3,NULL,2) AND z!=13;
  }

} {1 2 4}
do_test where4-5.3 {
  execsql {
    SELECT rowid FROM t4 WHERE x IN (1,9,NULL,2) AND y IN (1,3,2) AND z!=13;
  }

} {1 2 4}
do_test where4-6.1 {
  execsql {
    CREATE TABLE t5(a,b,c,d,e,f,UNIQUE(a,b,c,d,e,f));
    INSERT INTO t5 VALUES(1,1,1,1,1,11111);
    INSERT INTO t5 VALUES(2,2,2,2,2,22222);
    INSERT INTO t5 VALUES(1,2,3,4,5,12345);
    INSERT INTO t5 VALUES(2,3,4,5,6,23456);







|
|
|
|
|
|
|
<
>
|
|
|
|
<
>
|







188
189
190
191
192
193
194
195
196
197
198
199
200
201

202
203
204
205
206

207
208
209
210
211
212
213
214
215
  }
  execsql {
    SELECT *
      FROM t2 LEFT JOIN t4 b1
              LEFT JOIN t4 b2 ON b2.x=b1.x AND b2.y IN (b1.y);
  }
} {1 {} {} {} {} {} {} 2 {} {} {} {} {} {} 3 {} {} {} {} {} {}}
#do_test where4-5.2 {
#  execsql {
#    INSERT INTO t4 VALUES(1,1,11);
#    INSERT INTO t4 VALUES(1,2,12);
#    INSERT INTO t4 VALUES(1,3,13);
#    INSERT INTO t4 VALUES(2,2,22);
#    SELECT rowid FROM t4 WHERE x IN (1,9,2,5) AND y IN (1,3,NULL,2) AND z!=13;

#  }
#} {1 2 4}
#do_test where4-5.3 {
#  execsql {
#    SELECT rowid FROM t4 WHERE x IN (1,9,NULL,2) AND y IN (1,3,2) AND z!=13;

#  }
#} {1 2 4}
do_test where4-6.1 {
  execsql {
    CREATE TABLE t5(a,b,c,d,e,f,UNIQUE(a,b,c,d,e,f));
    INSERT INTO t5 VALUES(1,1,1,1,1,11111);
    INSERT INTO t5 VALUES(2,2,2,2,2,22222);
    INSERT INTO t5 VALUES(1,2,3,4,5,12345);
    INSERT INTO t5 VALUES(2,3,4,5,6,23456);

Changes to test/where9.test

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
...
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
...
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
...
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
...
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
...
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783










784



























785
786
787
788
789
790
791
...
809
810
811
812
813
814
815





816
817
818
819
820
821
822
...
851
852
853
854
855
856
857
858


859

























































860
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the multi-index OR clause optimizer.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !or_opt {
  finish_test
  return
}

# Evaluate SQL.  Return the result set followed by the
# and the number of full-scan steps.
#
................................................................................
        OR (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
    ORDER BY a
  }
} {90 91 92 97 scan 98 sort 0}
do_test where9-1.3.4 {
  count_steps {
    SELECT a FROM t4
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
    ORDER BY a
  }
} {90 91 92 97 scan 98 sort 0}

................................................................................

ifcapable explain {
  do_execsql_test where9-3.1 {
    EXPLAIN QUERY PLAN
    SELECT t2.a FROM t1, t2
    WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)}
  }
  do_execsql_test where9-3.2 {
    EXPLAIN QUERY PLAN
    SELECT coalesce(t2.a,9999)
    FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
    WHERE t1.a=80
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)}
  }
} 

# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {
................................................................................
do_test where9-4.5 {
  catchsql {
    SELECT a FROM t1 INDEXED BY t1b
     WHERE +b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
} {1 {cannot use index: t1b}}
do_test where9-4.6 {
  count_steps {
    SELECT a FROM t1 NOT INDEXED
     WHERE b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
................................................................................
do_test where9-4.7 {
  catchsql {
    SELECT a FROM t1 INDEXED BY t1c
     WHERE b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
} {1 {cannot use index: t1c}}
do_test where9-4.8 {
  catchsql {
    SELECT a FROM t1 INDEXED BY t1d
     WHERE b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
} {1 {cannot use index: t1d}}

ifcapable explain {
  # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
  # the former is an equality test which is expected to return fewer rows.
  #
  do_execsql_test where9-5.1 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~2 rows)} 
    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~2 rows)}
  }

  # In contrast, b=1000 is preferred over any OR-clause.
  #
  do_execsql_test where9-5.2 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~5 rows)}
  }

  # Likewise, inequalities in an AND are preferred over inequalities in
  # an OR.
  #
  do_execsql_test where9-5.3 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?) (~125000 rows)}
  }
}

############################################################################
# Make sure OR-clauses work correctly on UPDATE and DELETE statements.

do_test where9-6.2.1 {
................................................................................
        OR f='fghijklmn'
        OR g='hgfedcb'
  }
} {scan 0 sort 0}   ;#  Add 100 to rowids 5 31 57 82 83 84 85 86 87
do_test where9-6.5.4 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87);
    ROLLBACK;
  }
} {99 105 131 157 182 183 184 185 186 187}

do_test where9-6.6.1 {
  count_steps {
    BEGIN;
................................................................................
    ROLLBACK;
  }
} {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}

do_test where9-6.8.1 {
  catchsql {
    DELETE FROM t1 INDEXED BY t1b
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {1 {cannot use index: t1b}}
do_test where9-6.8.2 {
  catchsql {
    UPDATE t1 INDEXED BY t1b SET a=a+100
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {1 {cannot use index: t1b}}






































############################################################################
# Test cases where terms inside an OR series are combined with AND terms
# external to the OR clause.  In other words, cases where
#
#              x AND (y OR z)
#
# is able to use indices on x,y and x,z, or indices y,x and z,x.
................................................................................
    CREATE INDEX t5yd ON t5(y, d);
    CREATE INDEX t5ye ON t5(y, e);
    CREATE INDEX t5yf ON t5(y, f);
    CREATE INDEX t5yg ON t5(y, g);
    CREATE TABLE t6(a, b, c, e, d, f, g, x, y);
    INSERT INTO t6 SELECT * FROM t5;
    ANALYZE t5;





  }
} {}
do_test where9-7.1.1 {
  count_steps {
    SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
  }
} {79 81 83 scan 0 sort 1}
................................................................................
  }
} {79 81 scan 0 sort 1}
do_test where9-7.3.2 {
  execsql {
    SELECT a FROM t6 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
  }
} {79 81}





























































finish_test







|







 







|







 







|
|
|







|
|
|







 







|







 







|







|








|
|







|








|







 







|







 







|



|



|



|
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>







 








>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
...
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
...
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
...
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
...
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
...
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
...
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
...
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the multi-index OR clause optimizer.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !or_opt||!compound {
  finish_test
  return
}

# Evaluate SQL.  Return the result set followed by the
# and the number of full-scan steps.
#
................................................................................
        OR (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
    ORDER BY a
  }
} {90 91 92 97 scan 98 sort 0}
do_test where9-1.3.4 {
  count_steps {
    SELECT a FROM (t4)
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
    ORDER BY a
  }
} {90 91 92 97 scan 98 sort 0}

................................................................................

ifcapable explain {
  do_execsql_test where9-3.1 {
    EXPLAIN QUERY PLAN
    SELECT t2.a FROM t1, t2
    WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
  } {
    0 0 0 {SEARCH TABLE t1 USING PRIMARY KEY (a=?)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2f (f=?)}
  }
  do_execsql_test where9-3.2 {
    EXPLAIN QUERY PLAN
    SELECT coalesce(t2.a,9999)
    FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
    WHERE t1.a=80
  } {
    0 0 0 {SEARCH TABLE t1 USING PRIMARY KEY (a=?)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2f (f=?)}
  }
} 

# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {
................................................................................
do_test where9-4.5 {
  catchsql {
    SELECT a FROM t1 INDEXED BY t1b
     WHERE +b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
} {1 {no query solution}}
do_test where9-4.6 {
  count_steps {
    SELECT a FROM t1 NOT INDEXED
     WHERE b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
................................................................................
do_test where9-4.7 {
  catchsql {
    SELECT a FROM t1 INDEXED BY t1c
     WHERE b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
} {1 {no query solution}}
do_test where9-4.8 {
  catchsql {
    SELECT a FROM t1 INDEXED BY t1d
     WHERE b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
} {1 {no query solution}}

ifcapable explain {
  # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
  # the former is an equality test which is expected to return fewer rows.
  #
  do_execsql_test where9-5.1 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)} 
    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?)}
  }

  # In contrast, b=1000 is preferred over any OR-clause.
  #
  do_execsql_test where9-5.2 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}
  }

  # Likewise, inequalities in an AND are preferred over inequalities in
  # an OR.
  #
  do_execsql_test where9-5.3 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?)}
  }
}

############################################################################
# Make sure OR-clauses work correctly on UPDATE and DELETE statements.

do_test where9-6.2.1 {
................................................................................
        OR f='fghijklmn'
        OR g='hgfedcb'
  }
} {scan 0 sort 0}   ;#  Add 100 to rowids 5 31 57 82 83 84 85 86 87
do_test where9-6.5.4 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87) ORDER BY a;
    ROLLBACK;
  }
} {99 105 131 157 182 183 184 185 186 187}

do_test where9-6.6.1 {
  count_steps {
    BEGIN;
................................................................................
    ROLLBACK;
  }
} {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}

do_test where9-6.8.1 {
  catchsql {
    DELETE FROM t1 INDEXED BY t1b
     WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {1 {no query solution}}
do_test where9-6.8.2 {
  catchsql {
    UPDATE t1 INDEXED BY t1b SET a=a+100
     WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {1 {no query solution}}
ifcapable stat3 {
  # When STAT3 is enabled, the "b NOT NULL" terms get translated
  # into b>NULL, which can be satified by the index t1b.  It is a very
  # expensive way to do the query, but it works, and so a solution is possible.
  do_test where9-6.8.3-stat4 {
    catchsql {
      UPDATE t1 INDEXED BY t1b SET a=a+100
       WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
          OR (b NOT NULL AND c IS NULL AND d NOT NULL)
          OR (b NOT NULL AND c NOT NULL AND d IS NULL)
    }
  } {0 {}}
  do_test where9-6.8.4-stat4 {
    catchsql {
      DELETE FROM t1 INDEXED BY t1b
       WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
          OR (b NOT NULL AND c IS NULL AND d NOT NULL)
          OR (b NOT NULL AND c NOT NULL AND d IS NULL)
    }
  } {0 {}}
} else {
  do_test where9-6.8.3 {
    catchsql {
      UPDATE t1 INDEXED BY t1b SET a=a+100
       WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
          OR (b NOT NULL AND c IS NULL AND d NOT NULL)
          OR (b NOT NULL AND c NOT NULL AND d IS NULL)
    }
  } {1 {no query solution}}
  do_test where9-6.8.4 {
    catchsql {
      DELETE FROM t1 INDEXED BY t1b
       WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
          OR (b NOT NULL AND c IS NULL AND d NOT NULL)
          OR (b NOT NULL AND c NOT NULL AND d IS NULL)
    }
  } {1 {no query solution}}
}
############################################################################
# Test cases where terms inside an OR series are combined with AND terms
# external to the OR clause.  In other words, cases where
#
#              x AND (y OR z)
#
# is able to use indices on x,y and x,z, or indices y,x and z,x.
................................................................................
    CREATE INDEX t5yd ON t5(y, d);
    CREATE INDEX t5ye ON t5(y, e);
    CREATE INDEX t5yf ON t5(y, f);
    CREATE INDEX t5yg ON t5(y, g);
    CREATE TABLE t6(a, b, c, e, d, f, g, x, y);
    INSERT INTO t6 SELECT * FROM t5;
    ANALYZE t5;
  }
  ifcapable stat3 {
    sqlite3 db2 test.db
    db2 eval { DROP TABLE IF EXISTS sqlite_stat3 }
    db2 close
  }
} {}
do_test where9-7.1.1 {
  count_steps {
    SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
  }
} {79 81 83 scan 0 sort 1}
................................................................................
  }
} {79 81 scan 0 sort 1}
do_test where9-7.3.2 {
  execsql {
    SELECT a FROM t6 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
  }
} {79 81}

# Fix for ticket [b7c8682cc17f32903f03a610bd0d35ffd3c1e6e4]
# "Incorrect result from LEFT JOIN with OR in the WHERE clause"
#
do_test where9-8.1 {
  db eval {
    CREATE TABLE t81(a INTEGER PRIMARY KEY, b, c, d);
    CREATE TABLE t82(x INTEGER PRIMARY KEY, y);
    CREATE TABLE t83(p INTEGER PRIMARY KEY, q);
    
    INSERT INTO t81 VALUES(2,3,4,5);
    INSERT INTO t81 VALUES(3,4,5,6);
    INSERT INTO t82 VALUES(2,4);
    INSERT INTO t83 VALUES(5,55);
    
    SELECT *
      FROM t81 LEFT JOIN t82 ON y=b JOIN t83
     WHERE c==p OR d==p
     ORDER BY +a;
  }
} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
do_test where9-8.2 {
  db eval {
    SELECT *
      FROM t81 LEFT JOIN (t82) ON y=b JOIN t83
     WHERE c==p OR d==p
     ORDER BY +a;
  }
} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
do_test where9-8.3 {
  db eval {
    SELECT *
      FROM (t81) LEFT JOIN (main.t82) ON y=b JOIN t83
     WHERE c==p OR d==p
     ORDER BY +a;
  }
} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}

# Fix for ticket [f2369304e47167e3e644e2f1fe9736063391d7b7]
# Incorrect results when OR is used in the ON clause of a LEFT JOIN 
#
do_test where9-9.1 {
  db eval {
    CREATE TABLE t91(x); INSERT INTO t91 VALUES(1);
    CREATE TABLE t92(y INTEGER PRIMARY KEY,a,b);
    INSERT INTO t92 VALUES(1,2,3);
    SELECT 1 FROM t91 LEFT JOIN t92 ON a=2 OR b=3;
    SELECT 2 FROM t91 LEFT JOIN t92 ON a=2 AND b=3;
    SELECT 3 FROM t91 LEFT JOIN t92 ON (a=2 OR b=3) AND y IS NULL;
    SELECT 4 FROM t91 LEFT JOIN t92 ON (a=2 AND b=3) AND y IS NULL;
    CREATE TEMP TABLE x9 AS SELECT * FROM t91 LEFT JOIN t92 ON a=2 OR b=3;
    SELECT 5 FROM x9 WHERE y IS NULL;
    SELECT 6 FROM t91 LEFT JOIN t92 ON a=2 OR b=3 WHERE y IS NULL;
    SELECT 7 FROM t91 LEFT JOIN t92 ON a=2 AND b=3 WHERE y IS NULL;
    SELECT 8 FROM t91 LEFT JOIN t92 ON a=22 OR b=33 WHERE y IS NULL;
    SELECT 9 FROM t91 LEFT JOIN t92 ON a=22 AND b=33 WHERE y IS NULL;
  }
} {1 2 3 4 8 9}



finish_test