Home | History | Annotate | Download | only in test 
      1 # 2007 May 6
      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 #
     12 # $Id: tkt2339.test,v 1.2 2007/09/12 17:01:45 danielk1977 Exp $
     13 #
     14 
     15 set testdir [file dirname $argv0]
     16 source $testdir/tester.tcl
     17 
     18 ifcapable !subquery||!compound {
     19   finish_test
     20   return
     21 }
     22 
     23 do_test tkt2339.1 {
     24   execsql {
     25     create table t1(num int);
     26     insert into t1 values (1);
     27     insert into t1 values (2);
     28     insert into t1 values (3);
     29     insert into t1 values (4);
     30     
     31     create table t2(num int);
     32     insert into t2 values (11);
     33     insert into t2 values (12);
     34     insert into t2 values (13);
     35     insert into t2 values (14);
     36     
     37     SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC LIMIT 2)
     38     UNION
     39     SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC LIMIT 2)
     40   }
     41 } {3 4 13 14}
     42 do_test tkt2339.2 {
     43   execsql {
     44     SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC LIMIT 2)
     45     UNION ALL
     46     SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC LIMIT 2)
     47   }
     48 } {4 3 14 13}
     49 do_test tkt2339.3 {
     50   execsql {
     51     SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC)
     52     UNION ALL
     53     SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC LIMIT 2)
     54   }
     55 } {4 3 2 1 14 13}
     56 do_test tkt2339.4 {
     57   execsql {
     58     SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC LIMIT 2)
     59     UNION ALL
     60     SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC)
     61   }
     62 } {4 3 14 13 12 11}
     63 do_test tkt2339.5 {
     64   execsql {
     65     SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC LIMIT 2)
     66     UNION
     67     SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC)
     68   }
     69 } {3 4 11 12 13 14}
     70 do_test tkt2339.6 {
     71   execsql {
     72     SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC LIMIT 2)
     73     EXCEPT
     74     SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC)
     75   }
     76 } {3 4}
     77 do_test tkt2339.7 {
     78   execsql {
     79     SELECT * FROM (SELECT * FROM t1 LIMIT 2)
     80     UNION
     81     SELECT * FROM (SELECT * FROM t2 ORDER BY num DESC LIMIT 2)
     82   }
     83 } {1 2 13 14}
     84 do_test tkt2339.8 {
     85   execsql {
     86     SELECT * FROM (SELECT * FROM t1 LIMIT 2)
     87     UNION
     88     SELECT * FROM (SELECT * FROM t2 LIMIT 2)
     89   }
     90 } {1 2 11 12}
     91 do_test tkt2339.9 {
     92   execsql {
     93     SELECT * FROM (SELECT * FROM t1 ORDER BY num DESC LIMIT 2)
     94     UNION
     95     SELECT * FROM (SELECT * FROM t2 LIMIT 2)
     96   }
     97 } {3 4 11 12}
     98 
     99 
    100 finish_test
    101