#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.9 2002/04/30 19:20:29 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test select6-1.0 {
  execsql {
    BEGIN;
................................................................................
  }
} {}
do_test select6-7.4 {
  execsql2 {
    SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1)
  }
} {c abc b 2 a 1 a 1 b 2 c abc}










































































finish_test

#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.10 2003/05/02 16:44:25 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test select6-1.0 {
  execsql {
    BEGIN;
................................................................................
  }
} {}
do_test select6-7.4 {
  execsql2 {
    SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1)
  }
} {c abc b 2 a 1 a 1 b 2 c abc}

# The following procedure compiles the SQL given as an argument and returns
# TRUE if that SQL uses any transient tables and returns FALSE if no
# transient tables are used.  This is used to make sure that the
# sqliteFlattenSubquery() routine in select.c is doing its job.
#
proc is_flat {sql} {
  return [expr 0>[lsearch [execsql "EXPLAIN $sql"] OpenTemp]]
}

# Check that the flattener works correctly for deeply nested subqueries
# involving joins.
#
do_test select6-8.1 {
  execsql {
    BEGIN;
    CREATE TABLE t3(p,q);
    INSERT INTO t3 VALUES(1,11);
    INSERT INTO t3 VALUES(2,22);
    CREATE TABLE t4(q,r);
    INSERT INTO t4 VALUES(11,111);
    INSERT INTO t4 VALUES(22,222);
    COMMIT;
    SELECT * FROM t3 NATURAL JOIN t4;
  }
} {1 11 111 2 22 222}
do_test select6-8.2 {
  execsql {
    SELECT y, p, q, r FROM
       (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
       (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
    WHERE  y=p
  }
} {1 1 11 111 2 2 22 222 2 2 22 222}
do_test select6-8.3 {
  is_flat {
    SELECT y, p, q, r FROM
       (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
       (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
    WHERE  y=p
  }
} {1}
do_test select6-8.4 {
  execsql {
    SELECT DISTINCT y, p, q, r FROM
       (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
       (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
    WHERE  y=p
  }
} {1 1 11 111 2 2 22 222}
do_test select6-8.5 {
  execsql {
    SELECT * FROM 
      (SELECT y, p, q, r FROM
         (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
         (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
      WHERE  y=p) AS e,
      (SELECT r AS z FROM t4 WHERE q=11) AS f
    WHERE e.r=f.z
  }
} {1 1 11 111 111}
do_test select6-8.6 {
  is_flat {
    SELECT * FROM 
      (SELECT y, p, q, r FROM
         (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
         (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
      WHERE  y=p) AS e,
      (SELECT r AS z FROM t4 WHERE q=11) AS f
    WHERE e.r=f.z
  }
} {1}


finish_test