# 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: selectB.test,v 1.9 2008/08/04 03:51:24 danielk1977 Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

ifcapable !compound {

  finish_test

  return

}

proc test_transform {testname sql1 sql2 results} {

  set ::vdbe1 [list]

  set ::vdbe2 [list]

  db eval "explain $sql1" { lappend ::vdbe1 $opcode }

  db eval "explain $sql2" { lappend ::vdbe2 $opcode }

  do_test $testname.transform {

    set ::vdbe1

  } $::vdbe2

  set ::sql1 $sql1

  do_test $testname.sql1 {

    execsql $::sql1

  } $results

  set ::sql2 $sql2

  do_test $testname.sql2 {

    execsql $::sql2

  } $results

}

do_test selectB-1.1 {

  execsql {

    CREATE TABLE t1(a, b, c);

    CREATE TABLE t2(d, e, f);

    INSERT INTO t1 VALUES( 2,  4,  6);

    INSERT INTO t1 VALUES( 8, 10, 12);

    INSERT INTO t1 VALUES(14, 16, 18);

    INSERT INTO t2 VALUES(3,   6,  9);

    INSERT INTO t2 VALUES(12, 15, 18);

    INSERT INTO t2 VALUES(21, 24, 27);

  }

} {}

for {set ii 1} {$ii <= 2} {incr ii} {

  if {$ii == 2} {

    do_test selectB-2.1 {

      execsql {

        CREATE INDEX i1 ON t1(a);

        CREATE INDEX i2 ON t2(d);

      }

    } {}

  }

  test_transform selectB-$ii.2 {

    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)

  } {

    SELECT a FROM t1 UNION ALL SELECT d FROM t2

  } {2 8 14 3 12 21}

  test_transform selectB-$ii.3 {

    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1

  } {

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

  } {2 3 8 12 14 21}

  test_transform selectB-$ii.4 {

    SELECT * FROM 

      (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 

    WHERE a>10 ORDER BY 1

  } {

    SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1

  } {12 14 21}

  test_transform selectB-$ii.5 {

    SELECT * FROM 

      (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 

    WHERE a>10 ORDER BY a

  } {

    SELECT a FROM t1 WHERE a>10 

      UNION ALL 

    SELECT d FROM t2 WHERE d>10 

    ORDER BY a

  } {12 14 21}

  test_transform selectB-$ii.6 {

    SELECT * FROM 

      (SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12) 

    WHERE a>10 ORDER BY a

  } {

    SELECT a FROM t1 WHERE a>10

      UNION ALL 

    SELECT d FROM t2 WHERE d>12 AND d>10

    ORDER BY a

  } {14 21}

  test_transform selectB-$ii.7 {

    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 

    LIMIT 2

  } {

    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2

  } {2 3}

  test_transform selectB-$ii.8 {

    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 

    LIMIT 2 OFFSET 3

  } {

    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3

  } {12 14}

  test_transform selectB-$ii.9 {

    SELECT * FROM (

      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1

    ) 

  } {

    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1

  } {2 8 14 3 12 21 6 12 18}

  test_transform selectB-$ii.10 {

    SELECT * FROM (

      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1

    ) ORDER BY 1

  } {

    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1

    ORDER BY 1

  } {2 3 6 8 12 12 14 18 21}

  test_transform selectB-$ii.11 {

    SELECT * FROM (

      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1

    ) WHERE a>=10 ORDER BY 1 LIMIT 3

  } {

    SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10

    UNION ALL SELECT c FROM t1 WHERE c>=10

    ORDER BY 1 LIMIT 3

  } {12 12 14}

  test_transform selectB-$ii.12 {

    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2)

  } {

    SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2

  } {2 8}

  test_transform selectB-$ii.13 {

    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC)

  } {

    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC

  } {2 3 8 12 14 21}

  test_transform selectB-$ii.14 {

    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC)

  } {

    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC

  } {21 14 12 8 3 2}

  test_transform selectB-$ii.14 {

    SELECT * FROM (

      SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC

    ) LIMIT 2 OFFSET 2

  } {

    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC LIMIT 2 OFFSET 2

  } {12 8}

  test_transform selectB-$ii.15 {

    SELECT * FROM (

      SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC

    )

  } {

    SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC

  } {2 4 3 6 8 10 12 15 14 16 21 24}

}

do_test selectB-3.0 {

  execsql {

    DROP INDEX i1;

    DROP INDEX i2;

  }

} {}

for {set ii 3} {$ii <= 4} {incr ii} {

  if {$ii == 4} {

    do_test selectB-4.0 {

      execsql {

        CREATE INDEX i1 ON t1(a);

        CREATE INDEX i2 ON t1(b);

        CREATE INDEX i3 ON t1(c);

        CREATE INDEX i4 ON t2(d);

        CREATE INDEX i5 ON t2(e);

        CREATE INDEX i6 ON t2(f);

      }

    } {}

  }

  do_test selectB-$ii.1 {

    execsql {

      SELECT DISTINCT * FROM 

        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 

      ORDER BY 1;

    }

  } {6 12 15 18 24}

  do_test selectB-$ii.2 {

    execsql {

      SELECT c, count(*) FROM 

        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 

      GROUP BY c ORDER BY 1;

    }

  } {6 2 12 1 15 1 18 1 24 1}

  do_test selectB-$ii.3 {

    execsql {

      SELECT c, count(*) FROM 

        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 

      GROUP BY c HAVING count(*)>1;

    }

  } {6 2}

  do_test selectB-$ii.4 {

    execsql {

      SELECT t4.c, t3.a FROM 

        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3

      WHERE t3.a=14

      ORDER BY 1

    }

  } {6 14 6 14 12 14 15 14 18 14 24 14}

  do_test selectB-$ii.5 {

    execsql {

      SELECT d FROM t2 

      EXCEPT 

      SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)

    }

  } {}

  do_test selectB-$ii.6 {

    execsql {

      SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)

      EXCEPT 

      SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)

    }

  } {}

  do_test selectB-$ii.7 {

    execsql {

      SELECT c FROM t1

      EXCEPT 

      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)

    }

  } {12}

  do_test selectB-$ii.8 {

    execsql {

      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)

      EXCEPT 

      SELECT c FROM t1

    }

  } {9 15 24 27}

  do_test selectB-$ii.9 {

    execsql {

      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)

      EXCEPT 

      SELECT c FROM t1

      ORDER BY c DESC

    }

  } {27 24 15 9}

  do_test selectB-$ii.10 {

    execsql {

      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)

      UNION 

      SELECT c FROM t1

      ORDER BY c DESC

    }

  } {27 24 18 15 12 9 6}

  do_test selectB-$ii.11 {

    execsql {

      SELECT c FROM t1

      UNION 

      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)

      ORDER BY c

    }

  } {6 9 12 15 18 24 27}

  do_test selectB-$ii.12 {

    execsql {

      SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2

      ORDER BY c

    }

  } {6 9 12 15 18 18 24 27}

  do_test selectB-$ii.13 {

    execsql {

      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)

      UNION 

      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)

      ORDER BY 1

    }

  } {6 9 15 18 24 27}

  do_test selectB-$ii.14 {

    execsql {

      SELECT c FROM t1

      INTERSECT 

      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)

      ORDER BY 1

    }

  } {6 18}

  do_test selectB-$ii.15 {

    execsql {

      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)

      INTERSECT 

      SELECT c FROM t1

      ORDER BY 1

    }

  } {6 18}

  do_test selectB-$ii.16 {

    execsql {

      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)

      INTERSECT 

      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)

      ORDER BY 1

    }

  } {6 9 15 18 24 27}

  do_test selectB-$ii.17 {

    execsql {

      SELECT * FROM (

        SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4

      ) LIMIT 2

    }

  } {2 8}

  do_test selectB-$ii.18 {

    execsql {

      SELECT * FROM (

        SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2

      ) LIMIT 2

    }

  } {14 3}

  do_test selectB-$ii.19 {

    execsql {

      SELECT * FROM (

        SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2

      )

    }

  } {0 1 0 1}

  do_test selectB-$ii.20 {

    execsql {

      SELECT DISTINCT * FROM (

        SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2

      )

    }

  } {0 1}

  do_test selectB-$ii.21 {

    execsql {

      SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b

    }

  } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}

  do_test selectB-$ii.21 {

    execsql {

      SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;

    }

  } {3 12 21 345}

}

finish_test