xref: /sqlite-3.40.0/test/in2.test (revision 4aeff62e) 
1# 2007 May 12
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 tests a special case in the b-tree code that can be
12# hit by the "IN" operator (or EXISTS, NOT IN, etc.).
13#
14# $Id: in2.test,v 1.1 2007/05/12 09:30:47 danielk1977 Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19do_test in2-1 {
20  execsql {
21    CREATE TABLE a(i INTEGER PRIMARY KEY, a);
22  }
23} {}
24
25do_test in2-2 {
26  db transaction {
27    for {set ::ii 0} {$::ii < 1000} {incr ::ii} {
28      execsql {INSERT INTO a VALUES($::ii, $::ii)}
29    }
30    execsql {INSERT INTO a VALUES(1000, '')}
31
32    for {set ::ii 0} {$::ii < 1000} {incr ::ii} {
33      execsql {INSERT INTO a VALUES(NULL, 'x' || $::ii)}
34    }
35  }
36} {}
37
38# Each iteration of this loop builds a slightly different b-tree to
39# evaluate the "IN (...)" operator in the SQL statement. The contents
40# of the b-tree are (in sorted order):
41#
42#     $::ii integers.
43#     a string of zero length.
44#     1000 short strings.
45#
46# Records are inserted in sorted order.
47#
48# The string of zero-length is stored in a b-tree cell with 3 bytes
49# of payload. Moving this cell from a leaf node to a internal node
50# during b-tree balancing was causing an assertion failure.
51#
52# This bug only applied to b-trees generated to evaluate IN (..)
53# clauses, as it is impossible for persistent b-trees (SQL tables +
54# indices) to contain cells smaller than 4 bytes.
55#
56for {set ::ii 3} {$::ii < 1000} {incr ::ii} {
57  do_test in2-$::ii {
58    execsql {
59      SELECT 1 IN (SELECT a FROM a WHERE (i < $::ii) OR (i >= 1000))
60    }
61  } {1}
62}
63
64finish_test
65
66