sl@0: # 2008 June 24
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 implements regression tests for SQLite library. 
sl@0: #
sl@0: # $Id: selectB.test,v 1.9 2008/08/04 03:51:24 danielk1977 Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: ifcapable !compound {
sl@0:   finish_test
sl@0:   return
sl@0: }
sl@0: 
sl@0: proc test_transform {testname sql1 sql2 results} {
sl@0:   set ::vdbe1 [list]
sl@0:   set ::vdbe2 [list]
sl@0:   db eval "explain $sql1" { lappend ::vdbe1 $opcode }
sl@0:   db eval "explain $sql2" { lappend ::vdbe2 $opcode }
sl@0: 
sl@0:   do_test $testname.transform {
sl@0:     set ::vdbe1
sl@0:   } $::vdbe2
sl@0: 
sl@0:   set ::sql1 $sql1
sl@0:   do_test $testname.sql1 {
sl@0:     execsql $::sql1
sl@0:   } $results
sl@0: 
sl@0:   set ::sql2 $sql2
sl@0:   do_test $testname.sql2 {
sl@0:     execsql $::sql2
sl@0:   } $results
sl@0: }
sl@0: 
sl@0: do_test selectB-1.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t1(a, b, c);
sl@0:     CREATE TABLE t2(d, e, f);
sl@0: 
sl@0:     INSERT INTO t1 VALUES( 2,  4,  6);
sl@0:     INSERT INTO t1 VALUES( 8, 10, 12);
sl@0:     INSERT INTO t1 VALUES(14, 16, 18);
sl@0: 
sl@0:     INSERT INTO t2 VALUES(3,   6,  9);
sl@0:     INSERT INTO t2 VALUES(12, 15, 18);
sl@0:     INSERT INTO t2 VALUES(21, 24, 27);
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: for {set ii 1} {$ii <= 2} {incr ii} {
sl@0: 
sl@0:   if {$ii == 2} {
sl@0:     do_test selectB-2.1 {
sl@0:       execsql {
sl@0:         CREATE INDEX i1 ON t1(a);
sl@0:         CREATE INDEX i2 ON t2(d);
sl@0:       }
sl@0:     } {}
sl@0:   }
sl@0: 
sl@0:   test_transform selectB-$ii.2 {
sl@0:     SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
sl@0:   } {
sl@0:     SELECT a FROM t1 UNION ALL SELECT d FROM t2
sl@0:   } {2 8 14 3 12 21}
sl@0:   
sl@0:   test_transform selectB-$ii.3 {
sl@0:     SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
sl@0:   } {
sl@0:     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1
sl@0:   } {2 3 8 12 14 21}
sl@0:   
sl@0:   test_transform selectB-$ii.4 {
sl@0:     SELECT * FROM 
sl@0:       (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 
sl@0:     WHERE a>10 ORDER BY 1
sl@0:   } {
sl@0:     SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1
sl@0:   } {12 14 21}
sl@0:   
sl@0:   test_transform selectB-$ii.5 {
sl@0:     SELECT * FROM 
sl@0:       (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 
sl@0:     WHERE a>10 ORDER BY a
sl@0:   } {
sl@0:     SELECT a FROM t1 WHERE a>10 
sl@0:       UNION ALL 
sl@0:     SELECT d FROM t2 WHERE d>10 
sl@0:     ORDER BY a
sl@0:   } {12 14 21}
sl@0:   
sl@0:   test_transform selectB-$ii.6 {
sl@0:     SELECT * FROM 
sl@0:       (SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12) 
sl@0:     WHERE a>10 ORDER BY a
sl@0:   } {
sl@0:     SELECT a FROM t1 WHERE a>10
sl@0:       UNION ALL 
sl@0:     SELECT d FROM t2 WHERE d>12 AND d>10
sl@0:     ORDER BY a
sl@0:   } {14 21}
sl@0:   
sl@0:   test_transform selectB-$ii.7 {
sl@0:     SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 
sl@0:     LIMIT 2
sl@0:   } {
sl@0:     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2
sl@0:   } {2 3}
sl@0:   
sl@0:   test_transform selectB-$ii.8 {
sl@0:     SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 
sl@0:     LIMIT 2 OFFSET 3
sl@0:   } {
sl@0:     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3
sl@0:   } {12 14}
sl@0:   
sl@0:   test_transform selectB-$ii.9 {
sl@0:     SELECT * FROM (
sl@0:       SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
sl@0:     ) 
sl@0:   } {
sl@0:     SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
sl@0:   } {2 8 14 3 12 21 6 12 18}
sl@0:   
sl@0:   test_transform selectB-$ii.10 {
sl@0:     SELECT * FROM (
sl@0:       SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
sl@0:     ) ORDER BY 1
sl@0:   } {
sl@0:     SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
sl@0:     ORDER BY 1
sl@0:   } {2 3 6 8 12 12 14 18 21}
sl@0:   
sl@0:   test_transform selectB-$ii.11 {
sl@0:     SELECT * FROM (
sl@0:       SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
sl@0:     ) WHERE a>=10 ORDER BY 1 LIMIT 3
sl@0:   } {
sl@0:     SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10
sl@0:     UNION ALL SELECT c FROM t1 WHERE c>=10
sl@0:     ORDER BY 1 LIMIT 3
sl@0:   } {12 12 14}
sl@0: 
sl@0:   test_transform selectB-$ii.12 {
sl@0:     SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2)
sl@0:   } {
sl@0:     SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2
sl@0:   } {2 8}
sl@0: 
sl@0:   test_transform selectB-$ii.13 {
sl@0:     SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC)
sl@0:   } {
sl@0:     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC
sl@0:   } {2 3 8 12 14 21}
sl@0: 
sl@0:   test_transform selectB-$ii.14 {
sl@0:     SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC)
sl@0:   } {
sl@0:     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
sl@0:   } {21 14 12 8 3 2}
sl@0: 
sl@0:   test_transform selectB-$ii.14 {
sl@0:     SELECT * FROM (
sl@0:       SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC
sl@0:     ) LIMIT 2 OFFSET 2
sl@0:   } {
sl@0:     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC LIMIT 2 OFFSET 2
sl@0:   } {12 8}
sl@0: 
sl@0:   test_transform selectB-$ii.15 {
sl@0:     SELECT * FROM (
sl@0:       SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
sl@0:     )
sl@0:   } {
sl@0:     SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
sl@0:   } {2 4 3 6 8 10 12 15 14 16 21 24}
sl@0: }
sl@0: 
sl@0: do_test selectB-3.0 {
sl@0:   execsql {
sl@0:     DROP INDEX i1;
sl@0:     DROP INDEX i2;
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: for {set ii 3} {$ii <= 4} {incr ii} {
sl@0: 
sl@0:   if {$ii == 4} {
sl@0:     do_test selectB-4.0 {
sl@0:       execsql {
sl@0:         CREATE INDEX i1 ON t1(a);
sl@0:         CREATE INDEX i2 ON t1(b);
sl@0:         CREATE INDEX i3 ON t1(c);
sl@0:         CREATE INDEX i4 ON t2(d);
sl@0:         CREATE INDEX i5 ON t2(e);
sl@0:         CREATE INDEX i6 ON t2(f);
sl@0:       }
sl@0:     } {}
sl@0:   }
sl@0: 
sl@0:   do_test selectB-$ii.1 {
sl@0:     execsql {
sl@0:       SELECT DISTINCT * FROM 
sl@0:         (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
sl@0:       ORDER BY 1;
sl@0:     }
sl@0:   } {6 12 15 18 24}
sl@0:   
sl@0:   do_test selectB-$ii.2 {
sl@0:     execsql {
sl@0:       SELECT c, count(*) FROM 
sl@0:         (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
sl@0:       GROUP BY c ORDER BY 1;
sl@0:     }
sl@0:   } {6 2 12 1 15 1 18 1 24 1}
sl@0:   do_test selectB-$ii.3 {
sl@0:     execsql {
sl@0:       SELECT c, count(*) FROM 
sl@0:         (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
sl@0:       GROUP BY c HAVING count(*)>1;
sl@0:     }
sl@0:   } {6 2}
sl@0:   do_test selectB-$ii.4 {
sl@0:     execsql {
sl@0:       SELECT t4.c, t3.a FROM 
sl@0:         (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
sl@0:       WHERE t3.a=14
sl@0:       ORDER BY 1
sl@0:     }
sl@0:   } {6 14 6 14 12 14 15 14 18 14 24 14}
sl@0:   
sl@0:   do_test selectB-$ii.5 {
sl@0:     execsql {
sl@0:       SELECT d FROM t2 
sl@0:       EXCEPT 
sl@0:       SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
sl@0:     }
sl@0:   } {}
sl@0:   do_test selectB-$ii.6 {
sl@0:     execsql {
sl@0:       SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
sl@0:       EXCEPT 
sl@0:       SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
sl@0:     }
sl@0:   } {}
sl@0:   do_test selectB-$ii.7 {
sl@0:     execsql {
sl@0:       SELECT c FROM t1
sl@0:       EXCEPT 
sl@0:       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
sl@0:     }
sl@0:   } {12}
sl@0:   do_test selectB-$ii.8 {
sl@0:     execsql {
sl@0:       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
sl@0:       EXCEPT 
sl@0:       SELECT c FROM t1
sl@0:     }
sl@0:   } {9 15 24 27}
sl@0:   do_test selectB-$ii.9 {
sl@0:     execsql {
sl@0:       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
sl@0:       EXCEPT 
sl@0:       SELECT c FROM t1
sl@0:       ORDER BY c DESC
sl@0:     }
sl@0:   } {27 24 15 9}
sl@0:   
sl@0:   do_test selectB-$ii.10 {
sl@0:     execsql {
sl@0:       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
sl@0:       UNION 
sl@0:       SELECT c FROM t1
sl@0:       ORDER BY c DESC
sl@0:     }
sl@0:   } {27 24 18 15 12 9 6}
sl@0:   do_test selectB-$ii.11 {
sl@0:     execsql {
sl@0:       SELECT c FROM t1
sl@0:       UNION 
sl@0:       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
sl@0:       ORDER BY c
sl@0:     }
sl@0:   } {6 9 12 15 18 24 27}
sl@0:   do_test selectB-$ii.12 {
sl@0:     execsql {
sl@0:       SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2
sl@0:       ORDER BY c
sl@0:     }
sl@0:   } {6 9 12 15 18 18 24 27}
sl@0:   do_test selectB-$ii.13 {
sl@0:     execsql {
sl@0:       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
sl@0:       UNION 
sl@0:       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
sl@0:       ORDER BY 1
sl@0:     }
sl@0:   } {6 9 15 18 24 27}
sl@0:   
sl@0:   do_test selectB-$ii.14 {
sl@0:     execsql {
sl@0:       SELECT c FROM t1
sl@0:       INTERSECT 
sl@0:       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
sl@0:       ORDER BY 1
sl@0:     }
sl@0:   } {6 18}
sl@0:   do_test selectB-$ii.15 {
sl@0:     execsql {
sl@0:       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
sl@0:       INTERSECT 
sl@0:       SELECT c FROM t1
sl@0:       ORDER BY 1
sl@0:     }
sl@0:   } {6 18}
sl@0:   do_test selectB-$ii.16 {
sl@0:     execsql {
sl@0:       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
sl@0:       INTERSECT 
sl@0:       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
sl@0:       ORDER BY 1
sl@0:     }
sl@0:   } {6 9 15 18 24 27}
sl@0: 
sl@0:   do_test selectB-$ii.17 {
sl@0:     execsql {
sl@0:       SELECT * FROM (
sl@0:         SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4
sl@0:       ) LIMIT 2
sl@0:     }
sl@0:   } {2 8}
sl@0: 
sl@0:   do_test selectB-$ii.18 {
sl@0:     execsql {
sl@0:       SELECT * FROM (
sl@0:         SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2
sl@0:       ) LIMIT 2
sl@0:     }
sl@0:   } {14 3}
sl@0: 
sl@0:   do_test selectB-$ii.19 {
sl@0:     execsql {
sl@0:       SELECT * FROM (
sl@0:         SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
sl@0:       )
sl@0:     }
sl@0:   } {0 1 0 1}
sl@0: 
sl@0:   do_test selectB-$ii.20 {
sl@0:     execsql {
sl@0:       SELECT DISTINCT * FROM (
sl@0:         SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
sl@0:       )
sl@0:     }
sl@0:   } {0 1}
sl@0: 
sl@0:   do_test selectB-$ii.21 {
sl@0:     execsql {
sl@0:       SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b
sl@0:     }
sl@0:   } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}
sl@0: 
sl@0:   do_test selectB-$ii.21 {
sl@0:     execsql {
sl@0:       SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;
sl@0:     }
sl@0:   } {3 12 21 345}
sl@0: }
sl@0: 
sl@0: finish_test