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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18ifcapable !subquery||!compound { 19 finish_test 20 return 21} 22 23do_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} 42do_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} 49do_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} 56do_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} 63do_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} 70do_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} 77do_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} 84do_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} 91do_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 100finish_test 101