1 # 2008 June 24

     2 #

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

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

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

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

     9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library. 

    12 #

    13 # $Id: selectB.test,v 1.9 2008/08/04 03:51:24 danielk1977 Exp $

    14 

    15 set testdir [file dirname $argv0]

    16 source $testdir/tester.tcl

    17 

    18 ifcapable !compound {

    19   finish_test

    20   return

    21 }

    22 

    23 proc test_transform {testname sql1 sql2 results} {

    24   set ::vdbe1 [list]

    25   set ::vdbe2 [list]

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

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

    28 

    29   do_test $testname.transform {

    30     set ::vdbe1

    31   } $::vdbe2

    32 

    33   set ::sql1 $sql1

    34   do_test $testname.sql1 {

    35     execsql $::sql1

    36   } $results

    37 

    38   set ::sql2 $sql2

    39   do_test $testname.sql2 {

    40     execsql $::sql2

    41   } $results

    42 }

    43 

    44 do_test selectB-1.1 {

    45   execsql {

    46     CREATE TABLE t1(a, b, c);

    47     CREATE TABLE t2(d, e, f);

    48 

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

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

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

    52 

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

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

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

    56   }

    57 } {}

    58 

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

    60 

    61   if {$ii == 2} {

    62     do_test selectB-2.1 {

    63       execsql {

    64         CREATE INDEX i1 ON t1(a);

    65         CREATE INDEX i2 ON t2(d);

    66       }

    67     } {}

    68   }

    69 

    70   test_transform selectB-$ii.2 {

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

    72   } {

    73     SELECT a FROM t1 UNION ALL SELECT d FROM t2

    74   } {2 8 14 3 12 21}

    75   

    76   test_transform selectB-$ii.3 {

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

    78   } {

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

    80   } {2 3 8 12 14 21}

    81   

    82   test_transform selectB-$ii.4 {

    83     SELECT * FROM 

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

    85     WHERE a>10 ORDER BY 1

    86   } {

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

    88   } {12 14 21}

    89   

    90   test_transform selectB-$ii.5 {

    91     SELECT * FROM 

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

    93     WHERE a>10 ORDER BY a

    94   } {

    95     SELECT a FROM t1 WHERE a>10 

    96       UNION ALL 

    97     SELECT d FROM t2 WHERE d>10 

    98     ORDER BY a

    99   } {12 14 21}

   100   

   101   test_transform selectB-$ii.6 {

   102     SELECT * FROM 

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

   104     WHERE a>10 ORDER BY a

   105   } {

   106     SELECT a FROM t1 WHERE a>10

   107       UNION ALL 

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

   109     ORDER BY a

   110   } {14 21}

   111   

   112   test_transform selectB-$ii.7 {

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

   114     LIMIT 2

   115   } {

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

   117   } {2 3}

   118   

   119   test_transform selectB-$ii.8 {

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

   121     LIMIT 2 OFFSET 3

   122   } {

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

   124   } {12 14}

   125   

   126   test_transform selectB-$ii.9 {

   127     SELECT * FROM (

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

   129     ) 

   130   } {

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

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

   133   

   134   test_transform selectB-$ii.10 {

   135     SELECT * FROM (

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

   137     ) ORDER BY 1

   138   } {

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

   140     ORDER BY 1

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

   142   

   143   test_transform selectB-$ii.11 {

   144     SELECT * FROM (

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

   146     ) WHERE a>=10 ORDER BY 1 LIMIT 3

   147   } {

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

   149     UNION ALL SELECT c FROM t1 WHERE c>=10

   150     ORDER BY 1 LIMIT 3

   151   } {12 12 14}

   152 

   153   test_transform selectB-$ii.12 {

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

   155   } {

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

   157   } {2 8}

   158 

   159   test_transform selectB-$ii.13 {

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

   161   } {

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

   163   } {2 3 8 12 14 21}

   164 

   165   test_transform selectB-$ii.14 {

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

   167   } {

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

   169   } {21 14 12 8 3 2}

   170 

   171   test_transform selectB-$ii.14 {

   172     SELECT * FROM (

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

   174     ) LIMIT 2 OFFSET 2

   175   } {

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

   177   } {12 8}

   178 

   179   test_transform selectB-$ii.15 {

   180     SELECT * FROM (

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

   182     )

   183   } {

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

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

   186 }

   187 

   188 do_test selectB-3.0 {

   189   execsql {

   190     DROP INDEX i1;

   191     DROP INDEX i2;

   192   }

   193 } {}

   194 

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

   196 

   197   if {$ii == 4} {

   198     do_test selectB-4.0 {

   199       execsql {

   200         CREATE INDEX i1 ON t1(a);

   201         CREATE INDEX i2 ON t1(b);

   202         CREATE INDEX i3 ON t1(c);

   203         CREATE INDEX i4 ON t2(d);

   204         CREATE INDEX i5 ON t2(e);

   205         CREATE INDEX i6 ON t2(f);

   206       }

   207     } {}

   208   }

   209 

   210   do_test selectB-$ii.1 {

   211     execsql {

   212       SELECT DISTINCT * FROM 

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

   214       ORDER BY 1;

   215     }

   216   } {6 12 15 18 24}

   217   

   218   do_test selectB-$ii.2 {

   219     execsql {

   220       SELECT c, count(*) FROM 

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

   222       GROUP BY c ORDER BY 1;

   223     }

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

   225   do_test selectB-$ii.3 {

   226     execsql {

   227       SELECT c, count(*) FROM 

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

   229       GROUP BY c HAVING count(*)>1;

   230     }

   231   } {6 2}

   232   do_test selectB-$ii.4 {

   233     execsql {

   234       SELECT t4.c, t3.a FROM 

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

   236       WHERE t3.a=14

   237       ORDER BY 1

   238     }

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

   240   

   241   do_test selectB-$ii.5 {

   242     execsql {

   243       SELECT d FROM t2 

   244       EXCEPT 

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

   246     }

   247   } {}

   248   do_test selectB-$ii.6 {

   249     execsql {

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

   251       EXCEPT 

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

   253     }

   254   } {}

   255   do_test selectB-$ii.7 {

   256     execsql {

   257       SELECT c FROM t1

   258       EXCEPT 

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

   260     }

   261   } {12}

   262   do_test selectB-$ii.8 {

   263     execsql {

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

   265       EXCEPT 

   266       SELECT c FROM t1

   267     }

   268   } {9 15 24 27}

   269   do_test selectB-$ii.9 {

   270     execsql {

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

   272       EXCEPT 

   273       SELECT c FROM t1

   274       ORDER BY c DESC

   275     }

   276   } {27 24 15 9}

   277   

   278   do_test selectB-$ii.10 {

   279     execsql {

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

   281       UNION 

   282       SELECT c FROM t1

   283       ORDER BY c DESC

   284     }

   285   } {27 24 18 15 12 9 6}

   286   do_test selectB-$ii.11 {

   287     execsql {

   288       SELECT c FROM t1

   289       UNION 

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

   291       ORDER BY c

   292     }

   293   } {6 9 12 15 18 24 27}

   294   do_test selectB-$ii.12 {

   295     execsql {

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

   297       ORDER BY c

   298     }

   299   } {6 9 12 15 18 18 24 27}

   300   do_test selectB-$ii.13 {

   301     execsql {

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

   303       UNION 

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

   305       ORDER BY 1

   306     }

   307   } {6 9 15 18 24 27}

   308   

   309   do_test selectB-$ii.14 {

   310     execsql {

   311       SELECT c FROM t1

   312       INTERSECT 

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

   314       ORDER BY 1

   315     }

   316   } {6 18}

   317   do_test selectB-$ii.15 {

   318     execsql {

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

   320       INTERSECT 

   321       SELECT c FROM t1

   322       ORDER BY 1

   323     }

   324   } {6 18}

   325   do_test selectB-$ii.16 {

   326     execsql {

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

   328       INTERSECT 

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

   330       ORDER BY 1

   331     }

   332   } {6 9 15 18 24 27}

   333 

   334   do_test selectB-$ii.17 {

   335     execsql {

   336       SELECT * FROM (

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

   338       ) LIMIT 2

   339     }

   340   } {2 8}

   341 

   342   do_test selectB-$ii.18 {

   343     execsql {

   344       SELECT * FROM (

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

   346       ) LIMIT 2

   347     }

   348   } {14 3}

   349 

   350   do_test selectB-$ii.19 {

   351     execsql {

   352       SELECT * FROM (

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

   354       )

   355     }

   356   } {0 1 0 1}

   357 

   358   do_test selectB-$ii.20 {

   359     execsql {

   360       SELECT DISTINCT * FROM (

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

   362       )

   363     }

   364   } {0 1}

   365 

   366   do_test selectB-$ii.21 {

   367     execsql {

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

   369     }

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

   371 

   372   do_test selectB-$ii.21 {

   373     execsql {

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

   375     }

   376   } {3 12 21 345}

   377 }

   378 

   379 finish_test