Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -22,11 +22,11 @@ ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements. ** -** $Id: select.c,v 1.17 2000/06/08 00:19:03 drh Exp $ +** $Id: select.c,v 1.18 2000/06/08 00:28:52 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that @@ -507,10 +507,13 @@ */ if( eDest!=priorOp ){ int iCont, iBreak; assert( p->pEList ); generateColumnNames(pParse, 0, p->pEList); + if( p->pOrderBy ){ + sqliteVdbeAddOp(v, OP_SortOpen, 0, 0, 0, 0); + } iBreak = sqliteVdbeMakeLabel(v); iCont = sqliteVdbeAddOp(v, OP_Next, unionTab, iBreak, 0, 0); rc = selectInnerLoop(pParse, 0, unionTab, p->pEList->nExpr, p->pOrderBy, -1, eDest, iParm, iCont, iBreak); @@ -556,10 +559,13 @@ /* Generate code to take the intersection of the two temporary ** tables. */ assert( p->pEList ); generateColumnNames(pParse, 0, p->pEList); + if( p->pOrderBy ){ + sqliteVdbeAddOp(v, OP_SortOpen, 0, 0, 0, 0); + } iBreak = sqliteVdbeMakeLabel(v); iCont = sqliteVdbeAddOp(v, OP_Next, tab1, iBreak, 0, 0); sqliteVdbeAddOp(v, OP_Key, tab1, 0, 0, 0); sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont, 0, 0); rc = selectInnerLoop(pParse, 0, tab1, p->pEList->nExpr, ADDED test/select4.test Index: test/select4.test ================================================================== --- /dev/null +++ test/select4.test @@ -0,0 +1,131 @@ +# Copyright (c) 1999, 2000 D. Richard Hipp +# +# This program is free software; you can redistribute it and/or +# modify it under the terms of the GNU General Public +# License as published by the Free Software Foundation; either +# version 2 of the License, or (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +# General Public License for more details. +# +# You should have received a copy of the GNU General Public +# License along with this library; if not, write to the +# Free Software Foundation, Inc., 59 Temple Place - Suite 330, +# Boston, MA 02111-1307, USA. +# +# Author contact information: +# drh@hwaci.com +# http://www.hwaci.com/drh/ +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing UNION, INTERSECT and EXCEPT operators +# in SELECT statements. +# +# $Id: select4.test,v 1.1 2000/06/08 00:28:52 drh Exp $ + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# Build some test data +# +do_test select4-1.0 { + set fd [open data1.txt w] + for {set i 1} {$i<32} {incr i} { + for {set j 0} {pow(2,$j)<$i} {incr j} {} + puts $fd "$i\t$j" + } + close $fd + execsql { + CREATE TABLE t1(n int, log int); + COPY t1 FROM 'data1.txt' + } + file delete data1.txt + execsql {SELECT DISTINCT log FROM t1 ORDER BY log} +} {0 1 2 3 4 5} + +# Union All operator +# +do_test select4-1.1a { + lsort [execsql {SELECT DISTINCT log FROM t1}] +} {0 1 2 3 4 5} +do_test select4-1.1b { + lsort [execsql {SELECT n FROM t1 WHERE log=3}] +} {5 6 7 8} +do_test select4-1.1c { + execsql { + SELECT DISTINCT log FROM t1 + UNION ALL + SELECT n FROM t1 WHERE log=3 + ORDER BY log; + } +} {0 1 2 3 4 5 5 6 7 8} +do_test select4-1.2 { + execsql { + SELECT log FROM t1 WHERE n IN + (SELECT DISTINCT log FROM t1 UNION ALL + SELECT n FROM t1 WHERE log=3) + ORDER BY log; + } +} {0 1 2 2 3 3 3 3} + +# Union operator +# +do_test select4-2.1 { + execsql { + SELECT DISTINCT log FROM t1 + UNION + SELECT n FROM t1 WHERE log=3 + ORDER BY log; + } +} {0 1 2 3 4 5 6 7 8} +do_test select4-2.2 { + execsql { + SELECT log FROM t1 WHERE n IN + (SELECT DISTINCT log FROM t1 UNION + SELECT n FROM t1 WHERE log=3) + ORDER BY log; + } +} {0 1 2 2 3 3 3 3} + +# Except operator +# +do_test select4-3.1 { + execsql { + SELECT DISTINCT log FROM t1 + EXCEPT + SELECT n FROM t1 WHERE log=3 + ORDER BY log; + } +} {0 1 2 3 4} +do_test select4-3.2 { + execsql { + SELECT log FROM t1 WHERE n IN + (SELECT DISTINCT log FROM t1 EXCEPT + SELECT n FROM t1 WHERE log=3) + ORDER BY log; + } +} {0 1 2 2} + +# Intersect operator +# +do_test select4-4.1 { + execsql { + SELECT DISTINCT log FROM t1 + INTERSECT + SELECT n FROM t1 WHERE log=3 + ORDER BY log; + } +} {5} +do_test select4-4.2 { + execsql { + SELECT log FROM t1 WHERE n IN + (SELECT DISTINCT log FROM t1 INTERSECT + SELECT n FROM t1 WHERE log=3) + ORDER BY log; + } +} {3} + +finish_test