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

Overview
Comment:Run extra tests with the bt backend.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1aefe7ef1ec4c406ee826e448a1b89c5d4c8b036
User & Date: dan 2014-02-14 16:21:11.178
Context
2014-02-14
18:53
Fix memory leaks in test suite. check-in: f4d0f55571 user: dan tags: trunk
16:21
Run extra tests with the bt backend. check-in: 1aefe7ef1e user: dan tags: trunk
2014-02-08
10:51
Fixes so that tcl tests run with bt. check-in: b7f86e5617 user: dan tags: trunk
Changes
Unified Diff 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

      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 {








|








745
746
747
748
749
750
751
752
753
754
755
756
757
758
759

      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 {
︙︙
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800

      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}
}









|








786
787
788
789
790
791
792
793
794
795
796
797
798
799
800

      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

    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}








|








103
104
105
106
107
108
109
110
111
112
113
114
115
116
117

    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}
︙︙
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155

    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')
    }








|








141
142
143
144
145
146
147
148
149
150
151
152
153
154
155

    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')
    }
︙︙
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

    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);








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








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

    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


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)}
  }








>




|







<








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


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)}
  }
︙︙
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145

    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')
    }








|








131
132
133
134
135
136
137
138
139
140
141
142
143
144
145

    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')
    }
︙︙
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

    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);








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








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

    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

}
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]








>








39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

}
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]
︙︙
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266

    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 {








|








253
254
255
256
257
258
259
260
261
262
263
264
265
266
267

    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 {
︙︙
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448

#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;
  }








|
|
|
|
|








431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449

#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;
  }
︙︙
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

     }
  } {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;








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








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

     }
  } {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;
︙︙
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

  }
} {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;








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








559
560
561
562
563
564
565


















566
567
568
569
570
571
572

  }
} {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;
︙︙
598
599
600
601
602
603
604
605
606
607
608
609
610

    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








|
|
|
|



581
582
583
584
585
586
587
588
589
590
591
592
593

    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

    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)








|


|



|



|








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

    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)
︙︙
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

    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 } 









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








342
343
344
345
346
347
348












349
350
351
352
353
354
355

    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 }
︙︙
426
427
428
429
430
431
432
433
434
435
436

437
438
439
440
441

#
# 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








|
|


>

|




414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430

#
# 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

}
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;








<








46
47
48
49
50
51
52

53
54
55
56
57
58
59

}
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;
︙︙
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280

        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 {








|








265
266
267
268
269
270
271
272
273
274
275
276
277
278
279

        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

  # 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








|
|








136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151

  # 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
︙︙
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


# 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
}









|










|


>
>













|








|








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


# 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
}
︙︙
Changes to test/simple4.test.
︙︙
8
9
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

#    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);
}

do_execsql_test 1.1 {
  BEGIN;
    INSERT INTO t1 VALUES(5, 6);
    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
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
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);
}

do_execsql_test 1.1 {
  BEGIN;
    INSERT INTO t1 VALUES(5, 6);
    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

} {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;








>








456
457
458
459
460
461
462
463
464
465
466
467
468
469
470

} {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;
︙︙
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683

    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








|








670
671
672
673
674
675
676
677
678
679
680
681
682
683
684

    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
︙︙
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712

      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.
#








|








699
700
701
702
703
704
705
706
707
708
709
710
711
712
713

      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

  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.








>












|








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

  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.
︙︙
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97

  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?"}}








|
|
|
|
|








80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

  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

    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 {








|








43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

    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 {
︙︙
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223

  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.








|
|
|
|
|
|








204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223

  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

# 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.
#








|








11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 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.
#
︙︙
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242

        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}









|








228
229
230
231
232
233
234
235
236
237
238
239
240
241
242

        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}
︙︙
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


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 {








|
|
|







|
|
|








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


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 {
︙︙
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

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
  }
} {92 93 97 scan 98 sort 1}
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 {








|















|







|








|
|







|








|








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

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
  }
} {92 93 97 scan 98 sort 1}
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 {
︙︙
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702

        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;








|








688
689
690
691
692
693
694
695
696
697
698
699
700
701
702

        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;
︙︙
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

    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.








|



|



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








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

    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.
︙︙
809
810
811
812
813
814
815





816
817
818
819
820
821
822

    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}








>
>
>
>
>








846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864

    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}
︙︙
851
852
853
854
855
856
857
858


859

























































860

  }
} {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









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


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

  }
} {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