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

#

# The focus of this file is testing the compound-SELECT merge

# optimization.  Or, in other words, making sure that all

# possible combinations of UNION, UNION ALL, EXCEPT, and

# INTERSECT work together with an ORDER BY clause (with or w/o

# explicit sort order and explicit collating secquites) and

# with and without optional LIMIT and OFFSET clauses.

#

# $Id: selectA.test,v 1.6 2008/08/21 14:24:29 drh Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

ifcapable !compound {

  finish_test

  return

}

do_test selectA-1.0 {

  execsql {

    CREATE TABLE t1(a,b,c COLLATE NOCASE);

    INSERT INTO t1 VALUES(1,'a','a');

    INSERT INTO t1 VALUES(9.9, 'b', 'B');

    INSERT INTO t1 VALUES(NULL, 'C', 'c');

    INSERT INTO t1 VALUES('hello', 'd', 'D');

    INSERT INTO t1 VALUES(x'616263', 'e', 'e');

    SELECT * FROM t1;

  }

} {1 a a 9.9 b B {} C c hello d D abc e e}

do_test selectA-1.1 {

  execsql {

    CREATE TABLE t2(x,y,z COLLATE NOCASE);

    INSERT INTO t2 VALUES(NULL,'U','u');

    INSERT INTO t2 VALUES('mad', 'Z', 'z');

    INSERT INTO t2 VALUES(x'68617265', 'm', 'M');

    INSERT INTO t2 VALUES(5.2e6, 'X', 'x');

    INSERT INTO t2 VALUES(-23, 'Y', 'y');

    SELECT * FROM t2;

  }

} {{} U u mad Z z hare m M 5200000.0 X x -23 Y y}

do_test selectA-1.2 {

  execsql {

    CREATE TABLE t3(a,b,c COLLATE NOCASE);

    INSERT INTO t3 SELECT * FROM t1;

    INSERT INTO t3 SELECT * FROM t2;

    INSERT INTO t3 SELECT * FROM t1;

    INSERT INTO t3 SELECT * FROM t2;

    INSERT INTO t3 SELECT * FROM t1;

    INSERT INTO t3 SELECT * FROM t2;

    SELECT count(*) FROM t3;

  }

} {30}

do_test selectA-2.1 {

  execsql {

    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    ORDER BY a,b,c

  }

} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

do_test selectA-2.1.1 {   # Ticket #3314

  execsql {

    SELECT t1.a, t1.b, t1.c FROM t1 UNION ALL SELECT x,y,z FROM t2

    ORDER BY a,b,c

  }

} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

do_test selectA-2.1.2 {   # Ticket #3314

  execsql {

    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    ORDER BY t1.a, t1.b, t1.c

  }

} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

do_test selectA-2.2 {

  execsql {

    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    ORDER BY a DESC,b,c

  }

} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

do_test selectA-2.3 {

  execsql {

    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    ORDER BY a,c,b

  }

} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

do_test selectA-2.4 {

  execsql {

    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    ORDER BY b,a,c

  }

} {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

do_test selectA-2.5 {

  execsql {

    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    ORDER BY b COLLATE NOCASE,a,c

  }

} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

do_test selectA-2.6 {

  execsql {

    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    ORDER BY b COLLATE NOCASE DESC,a,c

  }

} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

do_test selectA-2.7 {

  execsql {

    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    ORDER BY c,b,a

  }

} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

do_test selectA-2.8 {

  execsql {

    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    ORDER BY c,a,b

  }

} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

do_test selectA-2.9 {

  execsql {

    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    ORDER BY c DESC,a,b

  }

} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

do_test selectA-2.10 {

  execsql {

    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    ORDER BY c COLLATE BINARY DESC,a,b

  }

} {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

do_test selectA-2.11 {

  execsql {

    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

    ORDER BY a,b,c

  }

} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

do_test selectA-2.12 {

  execsql {

    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

    ORDER BY a DESC,b,c

  }

} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

do_test selectA-2.13 {

  execsql {

    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

    ORDER BY a,c,b

  }

} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

do_test selectA-2.14 {

  execsql {

    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

    ORDER BY b,a,c

  }

} {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

do_test selectA-2.15 {

  execsql {

    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

    ORDER BY b COLLATE NOCASE,a,c

  }

} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

do_test selectA-2.16 {

  execsql {

    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

    ORDER BY b COLLATE NOCASE DESC,a,c

  }

} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

do_test selectA-2.17 {

  execsql {

    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

    ORDER BY c,b,a

  }

} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

do_test selectA-2.18 {

  execsql {

    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

    ORDER BY c,a,b

  }

} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

do_test selectA-2.19 {

  execsql {

    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

    ORDER BY c DESC,a,b

  }

} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

do_test selectA-2.20 {

  execsql {

    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

    ORDER BY c COLLATE BINARY DESC,a,b

  }

} {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

do_test selectA-2.21 {

  execsql {

    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

    ORDER BY a,b,c

  }

} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

do_test selectA-2.22 {

  execsql {

    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

    ORDER BY a DESC,b,c

  }

} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

do_test selectA-2.23 {

  execsql {

    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

    ORDER BY a,c,b

  }

} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

do_test selectA-2.24 {

  execsql {

    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

    ORDER BY b,a,c

  }

} {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

do_test selectA-2.25 {

  execsql {

    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

    ORDER BY b COLLATE NOCASE,a,c

  }

} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

do_test selectA-2.26 {

  execsql {

    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

    ORDER BY b COLLATE NOCASE DESC,a,c

  }

} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

do_test selectA-2.27 {

  execsql {

    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

    ORDER BY c,b,a

  }

} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

do_test selectA-2.28 {

  execsql {

    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

    ORDER BY c,a,b

  }

} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

do_test selectA-2.29 {

  execsql {

    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

    ORDER BY c DESC,a,b

  }

} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

do_test selectA-2.30 {

  execsql {

    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

    ORDER BY c COLLATE BINARY DESC,a,b

  }

} {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

do_test selectA-2.31 {

  execsql {

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

    ORDER BY a,b,c

  }

} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

do_test selectA-2.32 {

  execsql {

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

    ORDER BY a DESC,b,c

  }

} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

do_test selectA-2.33 {

  execsql {

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

    ORDER BY a,c,b

  }

} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

do_test selectA-2.34 {

  execsql {

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

    ORDER BY b,a,c

  }

} {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

do_test selectA-2.35 {

  execsql {

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

    ORDER BY b COLLATE NOCASE,a,c

  }

} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

do_test selectA-2.36 {

  execsql {

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

    ORDER BY b COLLATE NOCASE DESC,a,c

  }

} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

do_test selectA-2.37 {

  execsql {

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

    ORDER BY c,b,a

  }

} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

do_test selectA-2.38 {

  execsql {

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

    ORDER BY c,a,b

  }

} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

do_test selectA-2.39 {

  execsql {

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

    ORDER BY c DESC,a,b

  }

} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

do_test selectA-2.40 {

  execsql {

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

    ORDER BY c COLLATE BINARY DESC,a,b

  }

} {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

do_test selectA-2.41 {

  execsql {

    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'

    ORDER BY a,b,c

  }

} {{} C c 1 a a 9.9 b B}

do_test selectA-2.42 {

  execsql {

    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'

    ORDER BY a,b,c

  }

} {hello d D abc e e}

do_test selectA-2.43 {

  execsql {

    SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1

    ORDER BY a,b,c

  }

} {hello d D abc e e}

do_test selectA-2.44 {

  execsql {

    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

    ORDER BY a,b,c

  }

} {hello d D abc e e}

do_test selectA-2.45 {

  execsql {

    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

    ORDER BY a,b,c

  }

} {{} C c 1 a a 9.9 b B}

do_test selectA-2.46 {

  execsql {

    SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

    ORDER BY a,b,c

  }

} {{} C c 1 a a 9.9 b B}

do_test selectA-2.47 {

  execsql {

    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'

    ORDER BY a DESC

  }

} {9.9 b B 1 a a {} C c}

do_test selectA-2.48 {

  execsql {

    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'

    ORDER BY a DESC

  }

} {abc e e hello d D}

do_test selectA-2.49 {

  execsql {

    SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1

    ORDER BY a DESC

  }

} {abc e e hello d D}

do_test selectA-2.50 {

  execsql {

    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

    ORDER BY a DESC

  }

} {abc e e hello d D}

do_test selectA-2.51 {

  execsql {

    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

    ORDER BY a DESC

  }

} {9.9 b B 1 a a {} C c}

do_test selectA-2.52 {

  execsql {

    SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

    ORDER BY a DESC

  }

} {9.9 b B 1 a a {} C c}

do_test selectA-2.53 {

  execsql {

    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'

    ORDER BY b, a DESC

  }

} {{} C c 1 a a 9.9 b B}

do_test selectA-2.54 {

  execsql {

    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'

    ORDER BY b

  }

} {hello d D abc e e}

do_test selectA-2.55 {

  execsql {

    SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1

    ORDER BY b DESC, c

  }

} {abc e e hello d D}

do_test selectA-2.56 {

  execsql {

    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

    ORDER BY b, c DESC, a

  }

} {hello d D abc e e}

do_test selectA-2.57 {

  execsql {

    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

    ORDER BY b COLLATE NOCASE

  }

} {1 a a 9.9 b B {} C c}

do_test selectA-2.58 {

  execsql {

    SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

    ORDER BY b

  }

} {{} C c 1 a a 9.9 b B}

do_test selectA-2.59 {

  execsql {

    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'

    ORDER BY c, a DESC

  }

} {1 a a 9.9 b B {} C c}

do_test selectA-2.60 {

  execsql {

    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'

    ORDER BY c

  }

} {hello d D abc e e}

do_test selectA-2.61 {

  execsql {

    SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1

    ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c

  }

} {hello d D abc e e}

do_test selectA-2.62 {

  execsql {

    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

    ORDER BY c DESC, a

  }

} {abc e e hello d D}

do_test selectA-2.63 {

  execsql {

    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

    ORDER BY c COLLATE NOCASE

  }

} {1 a a 9.9 b B {} C c}

do_test selectA-2.64 {

  execsql {

    SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

    ORDER BY c

  }

} {1 a a 9.9 b B {} C c}

do_test selectA-2.65 {

  execsql {

    SELECT a,b,c FROM t3 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

    ORDER BY c COLLATE NOCASE

  }

} {1 a a 9.9 b B {} C c}

do_test selectA-2.66 {

  execsql {

    SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t3

    ORDER BY c

  }

} {1 a a 9.9 b B {} C c}

do_test selectA-2.67 {

  execsql {

    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t3 WHERE b<'d'

    ORDER BY c DESC, a

  }

} {abc e e hello d D}

do_test selectA-2.68 {

  execsql {

    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

    INTERSECT SELECT a,b,c FROM t3

    EXCEPT SELECT b,c,a FROM t3

    ORDER BY c DESC, a

  }

} {abc e e hello d D}

do_test selectA-2.69 {

  execsql {

    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

    INTERSECT SELECT a,b,c FROM t3

    EXCEPT SELECT b,c,a FROM t3

    ORDER BY c COLLATE NOCASE

  }

} {1 a a 9.9 b B {} C c}

do_test selectA-2.70 {

  execsql {

    SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

    INTERSECT SELECT a,b,c FROM t3

    EXCEPT SELECT b,c,a FROM t3

    ORDER BY c

  }

} {1 a a 9.9 b B {} C c}

do_test selectA-2.71 {

  execsql {

    SELECT a,b,c FROM t1 WHERE b<'d'

    INTERSECT SELECT a,b,c FROM t1

    INTERSECT SELECT a,b,c FROM t3

    EXCEPT SELECT b,c,a FROM t3

    INTERSECT SELECT a,b,c FROM t1

    EXCEPT SELECT x,y,z FROM t2

    INTERSECT SELECT a,b,c FROM t3

    EXCEPT SELECT y,x,z FROM t2

    INTERSECT SELECT a,b,c FROM t1

    EXCEPT SELECT c,b,a FROM t3

    ORDER BY c

  }

} {1 a a 9.9 b B {} C c}

do_test selectA-2.72 {

  execsql {

    SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2

    ORDER BY a,b,c

  }

} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

do_test selectA-2.73 {

  execsql {

    SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2

    ORDER BY a DESC,b,c

  }