xref: /sqlite-3.40.0/test/wherefault.test (revision 37d4ec86) 
1# 2008 December 23
2#
3# The author disclaims copyright to this source code.  In place of
4# a legal notice, here is a blessing:
5#
6#    May you do good and not evil.
7#    May you find forgiveness for yourself and forgive others.
8#    May you share freely, never taking more than you give.
9#
10#***********************************************************************
11# This file implements regression tests for SQLite library. The focus
12# is testing of where.c. More specifically, the focus is on handling OOM
13# errors within the code that optimizes WHERE clauses that feature the
14# OR operator.
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20source $testdir/malloc_common.tcl
21
22set testprefix wherefault
23
24do_malloc_test 1 -sqlprep {
25  CREATE TABLE t1(a, b, c);
26  CREATE INDEX i1 ON t1(a);
27  CREATE INDEX i2 ON t1(b);
28} -sqlbody {
29  SELECT c FROM t1
30  WHERE
31    a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 OR
32    b = 'seven' OR a = 8 OR b = 'nine' OR a = 10
33  ORDER BY rowid;
34
35  SELECT c FROM t1 WHERE
36    a = 1 OR a = 2 OR a = 3 OR a = 4 OR a = 5 OR a = 6;
37
38  SELECT c FROM t1 WHERE
39    a BETWEEN 1 AND 3  AND b < 5 AND b > 2 AND c = 4;
40}
41
42do_malloc_test 2 -tclprep {
43  db eval {
44    BEGIN;
45    CREATE TABLE t1(a, b, c);
46    CREATE INDEX i1 ON t1(a);
47    CREATE INDEX i2 ON t1(b);
48  }
49  for {set i 0} {$i < 1000} {incr i} {
50    set ii [expr $i*$i]
51    set iii [expr $i*$i]
52    db eval { INSERT INTO t1 VALUES($i, $ii, $iii) }
53  }
54  db eval COMMIT
55} -sqlbody {
56  SELECT count(*) FROM t1 WHERE a BETWEEN 5 AND 995 OR b BETWEEN 5 AND 900000;
57}
58
59reset_db
60do_execsql_test 3.0 {
61  PRAGMA writable_schema = 1;
62  BEGIN TRANSACTION;
63    CREATE TABLE t1(
64      a INT AS (c*11),
65      b TEXT AS (substr(d,1,3)) STORED,
66      c INTEGEB PRIMARI KEY, d TEXT
67    );
68    CREATE INDEX t1a ON t1(a);
69  COMMIT;
70}
71faultsim_save_and_close
72
73do_faultsim_test 3.1 -faults oom* -prep {
74  faultsim_restore_and_reopen
75} -body {
76  execsql {
77    SELECT * FROM (SELECT a FROM t1 NATURAL JOIN t1 WHERE a IN (SELECT b FROM t1 ORDER BY b)) WHERE (SELECT a FROM t1 NATURAL JOIN (SELECT * FROM (SELECT a FROM t1 NATURAL JOIN t1 WHERE a IN (SELECT CASE b WHEN 82 THEN 207 WHEN 869 THEN 406 WHEN 85 THEN 83 WHEN 705 THEN 698 ELSE 1992229051 END%5 FROM t1 ORDER BY b)) WHERE (SELECT a FROM t1 NATURAL JOIN (SELECT b FROM t1 ORDER BY b) WHERE a IN (SELECT b FROM t1 ORDER BY b))) WHERE a );
78  }
79} -test {
80  faultsim_test_result {0 {}}
81}
82
83finish_test
84