# 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.4 2008/07/01 14:39:35 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

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

       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

 }

 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}

 }

 do_test select9-2.X {

   execsql {

     DROP INDEX i1;

     DROP INDEX i2;

     DROP INDEX i3;

   }

 } {}

 # This procedure executes the SQL.  Then it checks the generated program

 # for the SQL and appends a "nosort" to the result if the program contains the

 # SortCallback opcode.  If the program does not contain the SortCallback

 # opcode it appends "sort"

 #

 proc cksort {sql} {

   set ::sqlite_sort_count 0

   set data [execsql $sql]

   if {$::sqlite_sort_count} {set x sort} {set x nosort}

   lappend data $x

   return $data

 }

 # If the right indexes exist, the following query:

 #

 #     SELECT t1.a FROM t1 UNION ALL SELECT t2.d FROM t2 ORDER BY 1

 #

 # can use indexes to run without doing a in-memory sort operation.

 # This block of tests (select9-3.*) is used to check if the same 

 # is possible with:

 #

 #     CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2

 #     SELECT a FROM v1 ORDER BY 1

 #

 # It turns out that it is.

 #

 do_test select9-3.1 {

   cksort { SELECT a FROM t1 ORDER BY 1 }

 } {1 2 3 4 5 6 7 8 9 10 sort}

 do_test select9-3.2 {

   execsql { CREATE INDEX i1 ON t1(a) }

   cksort { SELECT a FROM t1 ORDER BY 1 }

 } {1 2 3 4 5 6 7 8 9 10 nosort}

 do_test select9-3.3 {

   cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 }

 } {1 1 2 2 3 sort}

 do_test select9-3.4 {

   execsql { CREATE INDEX i2 ON t2(d) }

   cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 }

 } {1 1 2 2 3 nosort}

 do_test select9-3.5 {

   execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2 }

   cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 }

 } {1 1 2 2 3 nosort}

 do_test select9-3.X {

   execsql {

     DROP INDEX i1;

     DROP INDEX i2;

     DROP VIEW v1;

   }

 } {}

 # This block of tests is the same as the preceding one, except that

 # "UNION" is tested instead of "UNION ALL".

 #

 do_test select9-4.1 {

   cksort { SELECT a FROM t1 ORDER BY 1 }

 } {1 2 3 4 5 6 7 8 9 10 sort}

 do_test select9-4.2 {

   execsql { CREATE INDEX i1 ON t1(a) }

   cksort { SELECT a FROM t1 ORDER BY 1 }

 } {1 2 3 4 5 6 7 8 9 10 nosort}

 do_test select9-4.3 {

   cksort { SELECT a FROM t1 UNION SELECT d FROM t2 ORDER BY 1 LIMIT 5 }

 } {1 2 3 4 5 sort}

 do_test select9-4.4 {

   execsql { CREATE INDEX i2 ON t2(d) }

   cksort { SELECT a FROM t1 UNION SELECT d FROM t2 ORDER BY 1 LIMIT 5 }

 } {1 2 3 4 5 nosort}

 do_test select9-4.5 {

   execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION SELECT d FROM t2 }

   cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 }

 } {1 2 3 4 5 sort}

 do_test select9-4.X {

   execsql {

     DROP INDEX i1;

     DROP INDEX i2;

     DROP VIEW v1;

   }

 } {}

 finish_test