# 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 compute SELECT statements and nested

 # views.

 #

 # $Id: select7.test,v 1.11 2007/09/12 17:01:45 danielk1977 Exp $

 set testdir [file dirname $argv0]

 source $testdir/tester.tcl

 ifcapable compound {

 # A 3-way INTERSECT.  Ticket #875

 ifcapable tempdb {

   do_test select7-1.1 {

     execsql {

       create temp table t1(x);

       insert into t1 values('amx');

       insert into t1 values('anx');

       insert into t1 values('amy');

       insert into t1 values('bmy');

       select * from t1 where x like 'a__'

         intersect select * from t1 where x like '_m_'

         intersect select * from t1 where x like '__x';

     }

   } {amx}

 }

 # Nested views do not handle * properly.  Ticket #826.

 #

 ifcapable view {

 do_test select7-2.1 {

   execsql {

     CREATE TABLE x(id integer primary key, a TEXT NULL);

     INSERT INTO x (a) VALUES ('first');

     CREATE TABLE tempx(id integer primary key, a TEXT NULL);

     INSERT INTO tempx (a) VALUES ('t-first');

     CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id;

     CREATE VIEW tv1b AS SELECT x.id, tx.id FROM x JOIN tempx tx on tx.id=x.id;

     CREATE VIEW tv2 AS SELECT * FROM tv1 UNION SELECT * FROM tv1b;

     SELECT * FROM tv2;

   }

 } {1 1}

 } ;# ifcapable view

 } ;# ifcapable compound

 # Do not allow GROUP BY without an aggregate. Ticket #1039.

 #

 # Change: force any query with a GROUP BY clause to be processed as

 # an aggregate query, whether it contains aggregates or not.

 #

 ifcapable subquery {

   # do_test select7-3.1 {

   #   catchsql {

   #     SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name

   #   }

   # } {1 {GROUP BY may only be used on aggregate queries}}

   do_test select7-3.1 {

     catchsql {

       SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name

     }

   } [list 0 [execsql {SELECT * FROM sqlite_master ORDER BY name}]]

 }

 # Ticket #2018 - Make sure names are resolved correctly on all

 # SELECT statements of a compound subquery.

 #

 ifcapable {subquery && compound} {

   do_test select7-4.1 {

     execsql {

       CREATE TABLE IF NOT EXISTS photo(pk integer primary key, x);

       CREATE TABLE IF NOT EXISTS tag(pk integer primary key, fk int, name);

       SELECT P.pk from PHOTO P WHERE NOT EXISTS ( 

            SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk 

            EXCEPT 

            SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'

       );

     }

   } {}

   do_test select7-4.2 {

     execsql {

       INSERT INTO photo VALUES(1,1);

       INSERT INTO photo VALUES(2,2);

       INSERT INTO photo VALUES(3,3);

       INSERT INTO tag VALUES(11,1,'one');

       INSERT INTO tag VALUES(12,1,'two');

       INSERT INTO tag VALUES(21,1,'one-b');

       SELECT P.pk from PHOTO P WHERE NOT EXISTS ( 

            SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk 

            EXCEPT 

            SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'

       );

     }

   } {2 3}

 }

 # ticket #2347

 #

 ifcapable {subquery && compound} {

   do_test select7-5.1 {

     catchsql {

       CREATE TABLE t2(a,b);

       SELECT 5 IN (SELECT a,b FROM t2);

     }

   } [list 1 \

      {only a single result allowed for a SELECT that is part of an expression}]

   do_test select7-5.2 {

     catchsql {

       SELECT 5 IN (SELECT * FROM t2);

     }

   } [list 1 \

      {only a single result allowed for a SELECT that is part of an expression}]

   do_test select7-5.3 {

     catchsql {

       SELECT 5 IN (SELECT a,b FROM t2 UNION SELECT b,a FROM t2);

     }

   } [list 1 \

      {only a single result allowed for a SELECT that is part of an expression}]

   do_test select7-5.4 {

     catchsql {

       SELECT 5 IN (SELECT * FROM t2 UNION SELECT * FROM t2);

     }

   } [list 1 \

      {only a single result allowed for a SELECT that is part of an expression}]

 }

 # Verify that an error occurs if you have too many terms on a

 # compound select statement.

 #

 ifcapable compound {

   if {$SQLITE_MAX_COMPOUND_SELECT>0} {

     set sql {SELECT 0}

     set result 0

     for {set i 1} {$i<$SQLITE_MAX_COMPOUND_SELECT} {incr i} {

       append sql " UNION ALL SELECT $i"

       lappend result $i

     }

     do_test select7-6.1 {

       catchsql $sql

     } [list 0 $result]

     append sql { UNION ALL SELECT 99999999}

     do_test select7-6.2 {

       catchsql $sql

     } {1 {too many terms in compound SELECT}}

   }

 }

 finish_test