sl@0: # 2007 May 12
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file tests a special case in the b-tree code that can be
sl@0: # hit by the "IN" operator (or EXISTS, NOT IN, etc.).
sl@0: #
sl@0: # $Id: in2.test,v 1.3 2008/07/12 14:52:20 drh Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: do_test in2-1 {
sl@0:   execsql {
sl@0:     CREATE TABLE a(i INTEGER PRIMARY KEY, a);
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: set ::N 2000
sl@0: 
sl@0: do_test in2-2 {
sl@0:   db transaction {
sl@0:     for {set ::ii 0} {$::ii < $::N} {incr ::ii} {
sl@0:       execsql {INSERT INTO a VALUES($::ii, $::ii)}
sl@0:     }
sl@0:     execsql {INSERT INTO a VALUES(4000, '')}
sl@0: 
sl@0:     for {set ::ii 0} {$::ii < $::N} {incr ::ii} {
sl@0:       set ::t [format "x%04d" $ii]
sl@0:       execsql {INSERT INTO a VALUES(NULL, $::t)}
sl@0:     }
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: # Each iteration of this loop builds a slightly different b-tree to
sl@0: # evaluate the "IN (...)" operator in the SQL statement. The contents
sl@0: # of the b-tree are (in sorted order):
sl@0: #
sl@0: #     $::ii integers.
sl@0: #     a string of zero length.
sl@0: #     $::N short strings.
sl@0: #
sl@0: # Records are inserted in sorted order.
sl@0: #
sl@0: # The string of zero-length is stored in a b-tree cell with 3 bytes
sl@0: # of payload. Moving this cell from a leaf node to a internal node 
sl@0: # during b-tree balancing was causing an assertion failure. 
sl@0: #
sl@0: # This bug only applied to b-trees generated to evaluate IN (..) 
sl@0: # clauses, as it is impossible for persistent b-trees (SQL tables + 
sl@0: # indices) to contain cells smaller than 4 bytes.
sl@0: #
sl@0: for {set ::ii 3} {$::ii < $::N} {incr ::ii} {
sl@0:   do_test in2-$::ii {
sl@0:     execsql {
sl@0:       SELECT 1 IN (SELECT a FROM a WHERE (i < $::ii) OR (i >= $::N))
sl@0:     }
sl@0:   } {1}
sl@0: }
sl@0: 
sl@0: finish_test