SQLite

# 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.2 2008/06/24 15:39:44 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

#set ISQUICK 1

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

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

  set iLimitIncr  1
  set iOffsetIncr 1
  if {[info exists ::ISQUICK] && $::ISQUICK && $nRow>=5} {
    set iOffsetIncr [expr $nRow / 5]
    set iLimitIncr [expr $nRow / 5]
  }

  set iLimitEnd   [expr $nRow+$iLimitIncr]
  set iOffsetEnd  [expr $nRow+$iOffsetIncr]

  for {set iOffset 0} {$iOffset < $iOffsetEnd} {incr iOffset $iOffsetIncr} {
    for {set iLimit 0} {$iLimit < $iLimitEnd} {incr iLimit} {
  
      set ::compound_sql "$sql LIMIT $iLimit"
      if {$iOffset != 0} {
        append ::compound_sql " OFFSET $iOffset"
      }
  
      set iStart [expr {$iOffset*$nCol}]

  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
}

do_test select9-2.0 {
  execsql {
    DROP INDEX i1;
    DROP INDEX i2;
    DROP INDEX i3;
    DROP INDEX i4;
  }
} {}

proc reverse {lhs rhs} {
  return [string compare $rhs $lhs]
}
db collate reverse reverse

# This loop is similar to the previous one (test cases select9-1.*) 
# except that the simple select statements have WHERE clauses attached
# to them. Sometimes the WHERE clause may be satisfied using the same
# index used for ORDER BY, sometimes not.
#
set iOuterLoop 1
foreach indexes [list {
  /* Do not create any indexes. */
} {
  CREATE INDEX i1 ON t1(a)
} {
  DROP INDEX i1;
  CREATE INDEX i1 ON t1(b, a)
} {
  CREATE INDEX i2 ON t2(d DESC, e COLLATE REVERSE ASC);
} {
  CREATE INDEX i3 ON t1(a DESC);
}] {
  do_test select9-2.$iOuterLoop.1 {
    execsql $indexes
  } {}

  test_compound_select_flippable select9-2.$iOuterLoop.2 {
    SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 ORDER BY 1
  } {1 one I 2 two II 3 {} {} 4 four IV 5 ten XX 6 {} {} 7 fourteen XXVIII 8 sixteen XXXII 9 {} {} 10 twenty XL}

  test_compound_select_flippable select9-2.$iOuterLoop.2 {
    SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 ORDER BY 2, 1
  } {3 {} {} 6 {} {} 9 {} {} 4 four IV 7 fourteen XXVIII 1 one I 8 sixteen XXXII 5 ten XX 10 twenty XL 2 two II}

  test_compound_select_flippable select9-2.$iOuterLoop.3 {
    SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 
    ORDER BY 2 COLLATE reverse, 1
  } {3 {} {} 6 {} {} 9 {} {} 2 two II 10 twenty XL 5 ten XX 8 sixteen XXXII 1 one I 7 fourteen XXVIII 4 four IV}

  test_compound_select_flippable select9-2.$iOuterLoop.4 {
    SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 ORDER BY 1
  } {1 one I 2 two II 3 {} {} 4 four IV 5 ten XX 6 {} {} 7 fourteen XXVIII 8 sixteen XXXII 9 {} {} 10 twenty XL}

  test_compound_select_flippable select9-2.$iOuterLoop.5 {
    SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 ORDER BY 2, 1
  } {3 {} {} 6 {} {} 9 {} {} 4 four IV 7 fourteen XXVIII 1 one I 8 sixteen XXXII 5 ten XX 10 twenty XL 2 two II}

  test_compound_select_flippable select9-2.$iOuterLoop.6 {
    SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 
    ORDER BY 2 COLLATE reverse, 1
  } {3 {} {} 6 {} {} 9 {} {} 2 two II 10 twenty XL 5 ten XX 8 sixteen XXXII 1 one I 7 fourteen XXVIII 4 four IV}

  test_compound_select select9-2.$iOuterLoop.4 {
    SELECT a FROM t1 WHERE a<8 EXCEPT SELECT d FROM t2 WHERE d<=3 ORDER BY 1
  } {4 5 6 7}

  test_compound_select select9-2.$iOuterLoop.4 {
    SELECT a FROM t1 WHERE a<8 INTERSECT SELECT d FROM t2 WHERE d<=3 ORDER BY 1
  } {1 2 3}

}


finish_test