SQLite

#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file runs all tests.
#
# $Id: quick.test,v 1.82 2008/06/24 11:21:21 danielk1977 Exp $

proc lshift {lvar} {
  upvar $lvar l
  set ret [lindex $l 0]
  set l [lrange $l 1 end]
  return $ret
}

  memleak.test
  misc7.test
  misuse.test
  mutex2.test
  onefile.test
  permutations.test
  quick.test
  select9.test
  soak.test
  speed1.test
  speed1p.test
  speed2.test
  speed3.test
  speed4.test
  speed4p.test

# 2008 June 24
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. 
#
# $Id: select9.test,v 1.1 2008/06/24 11:21:21 danielk1977 Exp $

# The tests in this file are focused on test compound SELECT statements 
# that have any or all of an ORDER BY, LIMIT or OFFSET clauses. As of
# version 3.6.0, SQLite contains code to use SQL indexes where possible 
# to optimize such statements.
#

# TODO Points:
#
#   * Are there any "column affinity" issues to consider?

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

#-------------------------------------------------------------------------
# test_compound_select TESTNAME SELECT RESULT
#
#   This command is used to run multiple LIMIT/OFFSET test cases based on 
#   the single SELECT statement passed as the second argument. The SELECT
#   statement may not contain a LIMIT or OFFSET clause. This proc tests
#   many statements of the form:
#    
#     "$SELECT limit $X offset $Y"
#    
#   for various values of $X and $Y.
#    
#   The third argument, $RESULT, should contain the expected result of
#   the command [execsql $SELECT].
#    
#   The first argument, $TESTNAME, is used as the base test case name to
#   pass to [do_test] for each individual LIMIT OFFSET test case.
# 
proc test_compound_select {testname sql result} {

  set nCol 1
  db eval $sql A {
    set nCol [llength $A(*)]
    break
  }
  set nRow [expr {[llength $result] / $nCol}]

  set ::compound_sql $sql
  do_test $testname { 
    execsql $::compound_sql
  } $result
#return

  set iLimitIncr  1
  set iLimitEnd   [expr $nRow+1]

  set iOffsetIncr 1
  set iOffsetEnd  $nRow

  for {set iOffset 0} {$iOffset <= $iOffsetEnd} {incr iOffset $iOffsetIncr} {
    for {set iLimit 0} {$iLimit <= ($nRow+1)} {incr iLimit} {
  
      set ::compound_sql "$sql LIMIT $iLimit"
      if {$iOffset != 0} {
        append ::compound_sql " OFFSET $iOffset"
      }
  
      set iStart [expr {$iOffset*$nCol}]
      set iEnd [expr {($iOffset*$nCol) + ($iLimit*$nCol) -1}]
  
      do_test $testname.limit=$iLimit.offset=$iOffset { 
        execsql $::compound_sql
      } [lrange $result $iStart $iEnd]
    }
  }
}

#-------------------------------------------------------------------------
# test_compound_select_flippable TESTNAME SELECT RESULT
#
#   This command is for testing statements of the form:
#
#     <simple select 1> <compound op> <simple select 2> ORDER BY <order by>
#
#   where each <simple select> is a simple (non-compound) select statement
#   and <compound op> is one of "INTERSECT", "UNION ALL" or "UNION".
#
#   This proc calls [test_compound_select] twice, once with the select
#   statement as it is passed to this command, and once with the positions
#   of <select statement 1> and <select statement 2> exchanged.
#
proc test_compound_select_flippable {testname sql result} {
  test_compound_select $testname $sql $result

  set select [string trim $sql]
  set RE {(.*)(UNION ALL|INTERSECT|UNION)(.*)(ORDER BY.*)}
  set rc [regexp $RE $select -> s1 op s2 order_by]
  if {!$rc} {error "Statement is unflippable: $select"}

  set flipsql "$s2 $op $s1 $order_by"
  test_compound_select $testname.flipped $flipsql $result
}

#############################################################################
# Begin tests.
#

# Create and populate a sample database.
#
do_test select9-1.0 {
  execsql {
    CREATE TABLE t1(a, b, c);
    CREATE TABLE t2(d, e, f);
    BEGIN;
      INSERT INTO t1 VALUES(1,  'one',   'I');
      INSERT INTO t1 VALUES(3,  NULL,    NULL);
      INSERT INTO t1 VALUES(5,  'five',  'V');
      INSERT INTO t1 VALUES(7,  'seven', 'VII');
      INSERT INTO t1 VALUES(9,  NULL,    NULL);
      INSERT INTO t1 VALUES(2,  'two',   'II');
      INSERT INTO t1 VALUES(4,  'four',  'IV');
      INSERT INTO t1 VALUES(6,  NULL,    NULL);
      INSERT INTO t1 VALUES(8,  'eight', 'VIII');
      INSERT INTO t1 VALUES(10, 'ten',   'X');

      INSERT INTO t2 VALUES(1,  'two',      'IV');
      INSERT INTO t2 VALUES(2,  'four',     'VIII');
      INSERT INTO t2 VALUES(3,  NULL,       NULL);
      INSERT INTO t2 VALUES(4,  'eight',    'XVI');
      INSERT INTO t2 VALUES(5,  'ten',      'XX');
      INSERT INTO t2 VALUES(6,  NULL,       NULL);
      INSERT INTO t2 VALUES(7,  'fourteen', 'XXVIII');
      INSERT INTO t2 VALUES(8,  'sixteen',  'XXXII');
      INSERT INTO t2 VALUES(9,  NULL,       NULL);
      INSERT INTO t2 VALUES(10, 'twenty',   'XL');

    COMMIT;
  }
} {}

# Each iteration of this loop runs the same tests with a different set
# of indexes present within the database schema. The data returned by
# the compound SELECT statements in the test cases should be the same 
# in each case.
#
set iOuterLoop 1
foreach indexes [list {
  /* Do not create any indexes. */
} {
  CREATE INDEX i1 ON t1(a)
} {
  CREATE INDEX i2 ON t1(b)
} {
  CREATE INDEX i3 ON t2(d)
} {
  CREATE INDEX i4 ON t2(e)
}] {

  do_test select9-1.$iOuterLoop.1 {
    execsql $indexes
  } {}

  # Test some 2-way UNION ALL queries. No WHERE clauses.
  #
  test_compound_select select9-1.$iOuterLoop.2 {
    SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 
  } {1 one 3 {} 5 five 7 seven 9 {} 2 two 4 four 6 {} 8 eight 10 ten 1 two 2 four 3 {} 4 eight 5 ten 6 {} 7 fourteen 8 sixteen 9 {} 10 twenty}
  test_compound_select select9-1.$iOuterLoop.3 {
    SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1 
  } {1 one 1 two 2 two 2 four 3 {} 3 {} 4 four 4 eight 5 five 5 ten 6 {} 6 {} 7 seven 7 fourteen 8 eight 8 sixteen 9 {} 9 {} 10 ten 10 twenty}
  test_compound_select select9-1.$iOuterLoop.4 {
    SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2 
  } {3 {} 9 {} 6 {} 3 {} 6 {} 9 {} 8 eight 4 eight 5 five 4 four 2 four 7 fourteen 1 one 7 seven 8 sixteen 10 ten 5 ten 10 twenty 2 two 1 two}
  test_compound_select_flippable select9-1.$iOuterLoop.5 {
    SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1, 2
  } {1 one 1 two 2 four 2 two 3 {} 3 {} 4 eight 4 four 5 five 5 ten 6 {} 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 9 {} 10 ten 10 twenty}
  test_compound_select_flippable select9-1.$iOuterLoop.6 {
    SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2, 1
  } {3 {} 3 {} 6 {} 6 {} 9 {} 9 {} 4 eight 8 eight 5 five 2 four 4 four 7 fourteen 1 one 7 seven 8 sixteen 5 ten 10 ten 10 twenty 1 two 2 two}

  # Test some 2-way UNION queries.
  #
  test_compound_select select9-1.$iOuterLoop.7 {
    SELECT a, b FROM t1 UNION SELECT d, e FROM t2 
  } {1 one 1 two 2 four 2 two 3 {} 4 eight 4 four 5 five 5 ten 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 10 ten 10 twenty}

  test_compound_select select9-1.$iOuterLoop.8 {
    SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1 
  } {1 one 1 two 2 four 2 two 3 {} 4 eight 4 four 5 five 5 ten 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 10 ten 10 twenty}

  test_compound_select select9-1.$iOuterLoop.9 {
    SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2 
  } {3 {} 6 {} 9 {} 4 eight 8 eight 5 five 2 four 4 four 7 fourteen 1 one 7 seven 8 sixteen 5 ten 10 ten 10 twenty 1 two 2 two}

  test_compound_select_flippable select9-1.$iOuterLoop.10 {
    SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1, 2
  } {1 one 1 two 2 four 2 two 3 {} 4 eight 4 four 5 five 5 ten 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 10 ten 10 twenty}

  test_compound_select_flippable select9-1.$iOuterLoop.11 {
    SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2, 1
  } {3 {} 6 {} 9 {} 4 eight 8 eight 5 five 2 four 4 four 7 fourteen 1 one 7 seven 8 sixteen 5 ten 10 ten 10 twenty 1 two 2 two}

  # Test some 2-way INTERSECT queries.
  #
  test_compound_select select9-1.$iOuterLoop.11 {
    SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 
  } {3 {} 6 {} 9 {}}
  test_compound_select_flippable select9-1.$iOuterLoop.12 {
    SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1
  } {3 {} 6 {} 9 {}}
  test_compound_select select9-1.$iOuterLoop.13 {
    SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2
  } {3 {} 6 {} 9 {}}
  test_compound_select_flippable select9-1.$iOuterLoop.14 {
    SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2, 1
  } {3 {} 6 {} 9 {}}
  test_compound_select_flippable select9-1.$iOuterLoop.15 {
    SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1, 2
  } {3 {} 6 {} 9 {}}

  # Test some 2-way EXCEPT queries.
  #
  test_compound_select select9-1.$iOuterLoop.16 {
    SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 
  } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten}

  test_compound_select select9-1.$iOuterLoop.17 {
    SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1 
  } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten}

  test_compound_select select9-1.$iOuterLoop.18 {
    SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2 
  } {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two}

  test_compound_select select9-1.$iOuterLoop.19 {
    SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1, 2
  } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten}

  test_compound_select select9-1.$iOuterLoop.20 {
    SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2, 1
  } {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two}

  incr iOuterLoop
}

finish_test

#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file runs all tests.
#
# $Id: veryquick.test,v 1.7 2008/06/24 11:21:21 danielk1977 Exp $

proc lshift {lvar} {
  upvar $lvar l
  set ret [lindex $l 0]
  set l [lrange $l 1 end]
  return $ret
}

  memleak.test
  misc7.test
  misuse.test
  mutex2.test
  onefile.test
  permutations.test
  quick.test
  select9.test
  soak.test
  speed1.test
  speed1p.test
  speed2.test
  speed3.test
  speed4.test
  speed4p.test