AryaWu/sqlite

1# 2007 May 62#3# The author disclaims copyright to this source code.  In place of4# 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.tcl17 18ifcapable !subquery||!compound {19  finish_test20  return21}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    UNION39    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 ALL46    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 ALL53    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 ALL60    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    UNION67    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    EXCEPT74    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    UNION81    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    UNION88    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    UNION95    SELECT * FROM (SELECT * FROM t2 LIMIT 2)96  }97} {3 4 11 12}98 99 100finish_test101