1 # The author disclaims copyright to this source code.  In place of

     2 # a legal notice, here is a blessing:

     3 #

     4 #    May you do good and not evil.

     5 #    May you find forgiveness for yourself and forgive others.

     6 #    May you share freely, never taking more than you give.

     7 #

     8 #***********************************************************************

     9 # This file implements regression tests for SQLite library.  The

    10 # focus of this file is testing compute SELECT statements and nested

    11 # views.

    12 #

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

    14 

    15 

    16 set testdir [file dirname $argv0]

    17 source $testdir/tester.tcl

    18 

    19 ifcapable compound {

    20 

    21 # A 3-way INTERSECT.  Ticket #875

    22 ifcapable tempdb {

    23   do_test select7-1.1 {

    24     execsql {

    25       create temp table t1(x);

    26       insert into t1 values('amx');

    27       insert into t1 values('anx');

    28       insert into t1 values('amy');

    29       insert into t1 values('bmy');

    30       select * from t1 where x like 'a__'

    31         intersect select * from t1 where x like '_m_'

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

    33     }

    34   } {amx}

    35 }

    36 

    37 

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

    39 #

    40 ifcapable view {

    41 do_test select7-2.1 {

    42   execsql {

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

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

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

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

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

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

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

    50     SELECT * FROM tv2;

    51   }

    52 } {1 1}

    53 } ;# ifcapable view

    54 

    55 } ;# ifcapable compound

    56 

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

    58 #

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

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

    61 #

    62 ifcapable subquery {

    63   # do_test select7-3.1 {

    64   #   catchsql {

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

    66   #   }

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

    68   do_test select7-3.1 {

    69     catchsql {

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

    71     }

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

    73 }

    74 

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

    76 # SELECT statements of a compound subquery.

    77 #

    78 ifcapable {subquery && compound} {

    79   do_test select7-4.1 {

    80     execsql {

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

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

    83     

    84       SELECT P.pk from PHOTO P WHERE NOT EXISTS ( 

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

    86            EXCEPT 

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

    88       );

    89     }

    90   } {}

    91   do_test select7-4.2 {

    92     execsql {

    93       INSERT INTO photo VALUES(1,1);

    94       INSERT INTO photo VALUES(2,2);

    95       INSERT INTO photo VALUES(3,3);

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

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

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

    99       SELECT P.pk from PHOTO P WHERE NOT EXISTS ( 

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

   101            EXCEPT 

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

   103       );

   104     }

   105   } {2 3}

   106 }

   107 

   108 # ticket #2347

   109 #

   110 ifcapable {subquery && compound} {

   111   do_test select7-5.1 {

   112     catchsql {

   113       CREATE TABLE t2(a,b);

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

   115     }

   116   } [list 1 \

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

   118   do_test select7-5.2 {

   119     catchsql {

   120       SELECT 5 IN (SELECT * FROM t2);

   121     }

   122   } [list 1 \

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

   124   do_test select7-5.3 {

   125     catchsql {

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

   127     }

   128   } [list 1 \

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

   130   do_test select7-5.4 {

   131     catchsql {

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

   133     }

   134   } [list 1 \

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

   136 }

   137 

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

   139 # compound select statement.

   140 #

   141 ifcapable compound {

   142   if {$SQLITE_MAX_COMPOUND_SELECT>0} {

   143     set sql {SELECT 0}

   144     set result 0

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

   146       append sql " UNION ALL SELECT $i"

   147       lappend result $i

   148     }

   149     do_test select7-6.1 {

   150       catchsql $sql

   151     } [list 0 $result]

   152     append sql { UNION ALL SELECT 99999999}

   153     do_test select7-6.2 {

   154       catchsql $sql

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

   156   }

   157 }

   158 

   159 finish_test