/ Check-in [edca8913]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:More efficient handling of the LIMIT clause. Scalar subqueries and EXISTS on compound SELECT statements now working properly. Ticket #1473. (CVS 2747)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: edca8913ca012fc0c17343a27f819de95147b1bd
User & Date: drh 2005-10-06 16:53:15
References
2010-04-15
14:43 Ticket [02a8e81d] LIMIT clause on sub-select in FROM clause of a SELECT in a UNION ALL interpreted incorrectly status still Open with 3 other changes artifact: c271e305 user: drh
Context
2005-10-10
00:05
Make the default TEMP_STORE=1 (TEMP tables stored on disk) in the configure script. (CVS 2748) check-in: 9753af53 user: drh tags: trunk
2005-10-06
16:53
More efficient handling of the LIMIT clause. Scalar subqueries and EXISTS on compound SELECT statements now working properly. Ticket #1473. (CVS 2747) check-in: edca8913 user: drh tags: trunk
13:59
Check-in (2744) as incomplete and broke pragma integrity_check. This completes the change and fixes the problem. (CVS 2746) check-in: 4862eaaf user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains routines used for analyzing expressions and
    13     13   ** for generating VDBE code that evaluates expressions in SQLite.
    14     14   **
    15         -** $Id: expr.c,v 1.230 2005/09/23 21:11:54 drh Exp $
           15  +** $Id: expr.c,v 1.231 2005/10/06 16:53:15 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   #include <ctype.h>
    19     19   
    20     20   /*
    21     21   ** Return the 'affinity' of the expression pExpr if any.
    22     22   **
................................................................................
  1386   1386   
  1387   1387       case TK_EXISTS:
  1388   1388       case TK_SELECT: {
  1389   1389         /* This has to be a scalar SELECT.  Generate code to put the
  1390   1390         ** value of this select in a memory cell and record the number
  1391   1391         ** of the memory cell in iColumn.
  1392   1392         */
  1393         -      int sop;
         1393  +      static const Token one = { "1", 0, 1 };
  1394   1394         Select *pSel;
         1395  +      int iMem;
         1396  +      int sop;
  1395   1397   
  1396         -      pExpr->iColumn = pParse->nMem++;
         1398  +      pExpr->iColumn = iMem = pParse->nMem++;
  1397   1399         pSel = pExpr->pSelect;
  1398   1400         if( pExpr->op==TK_SELECT ){
  1399   1401           sop = SRT_Mem;
         1402  +        sqlite3VdbeAddOp(v, OP_MemNull, iMem, 0);
         1403  +        VdbeComment((v, "# Init subquery result"));
  1400   1404         }else{
  1401         -        static const Token one = { "1", 0, 1 };
  1402   1405           sop = SRT_Exists;
  1403         -        sqlite3ExprListDelete(pSel->pEList);
  1404         -        pSel->pEList = sqlite3ExprListAppend(0, 
  1405         -                          sqlite3Expr(TK_INTEGER, 0, 0, &one), 0);
         1406  +        sqlite3VdbeAddOp(v, OP_MemInt, 0, iMem);
         1407  +        VdbeComment((v, "# Init EXISTS result"));
  1406   1408         }
  1407         -      sqlite3Select(pParse, pSel, sop, pExpr->iColumn, 0, 0, 0, 0);
         1409  +      sqlite3ExprDelete(pSel->pLimit);
         1410  +      pSel->pLimit = sqlite3Expr(TK_INTEGER, 0, 0, &one);
         1411  +      sqlite3Select(pParse, pSel, sop, iMem, 0, 0, 0, 0);
  1408   1412         break;
  1409   1413       }
  1410   1414     }
  1411   1415   
  1412   1416     if( testAddr ){
  1413   1417       sqlite3VdbeJumpHere(v, testAddr);
  1414   1418     }

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.276 2005/09/20 18:13:24 drh Exp $
           15  +** $Id: select.c,v 1.277 2005/10/06 16:53:15 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Allocate a new Select structure and return a pointer to that
    22     22   ** structure.
................................................................................
   356    356     sqlite3VdbeAddOp(v, OP_Sequence, pOrderBy->iECursor, 0);
   357    357     sqlite3VdbeAddOp(v, OP_Pull, pOrderBy->nExpr + 1, 0);
   358    358     sqlite3VdbeAddOp(v, OP_MakeRecord, pOrderBy->nExpr + 2, 0);
   359    359     sqlite3VdbeAddOp(v, OP_IdxInsert, pOrderBy->iECursor, 0);
   360    360   }
   361    361   
   362    362   /*
   363         -** Add code to implement the OFFSET and LIMIT
          363  +** Add code to implement the OFFSET
   364    364   */
   365         -static void codeLimiter(
          365  +static void codeOffset(
   366    366     Vdbe *v,          /* Generate code into this VM */
   367    367     Select *p,        /* The SELECT statement being coded */
   368    368     int iContinue,    /* Jump here to skip the current record */
   369         -  int iBreak,       /* Jump here to end the loop */
   370    369     int nPop          /* Number of times to pop stack when jumping */
   371    370   ){
   372    371     if( p->iOffset>=0 && iContinue!=0 ){
   373    372       int addr = sqlite3VdbeCurrentAddr(v) + 3;
   374    373       if( nPop>0 ) addr++;
   375    374       sqlite3VdbeAddOp(v, OP_MemIncr, p->iOffset, 0);
   376    375       sqlite3VdbeAddOp(v, OP_IfMemPos, p->iOffset, addr);
   377    376       if( nPop>0 ){
   378    377         sqlite3VdbeAddOp(v, OP_Pop, nPop, 0);
   379    378       }
   380    379       sqlite3VdbeAddOp(v, OP_Goto, 0, iContinue);
   381    380       VdbeComment((v, "# skip OFFSET records"));
   382    381     }
   383         -  if( p->iLimit>=0 && iBreak!=0 ){
   384         -    sqlite3VdbeAddOp(v, OP_MemIncr, p->iLimit, iBreak);
   385         -    VdbeComment((v, "# exit when LIMIT reached"));
   386         -  }
   387    382   }
   388    383   
   389    384   /*
   390    385   ** Add code that will check to make sure the top N elements of the
   391    386   ** stack are distinct.  iTab is a sorting index that holds previously
   392    387   ** seen combinations of the N values.  A new entry is made in iTab
   393    388   ** if the current N values are new.
................................................................................
   445    440     assert( pEList!=0 );
   446    441   
   447    442     /* If there was a LIMIT clause on the SELECT statement, then do the check
   448    443     ** to see if this row should be output.
   449    444     */
   450    445     hasDistinct = distinct>=0 && pEList && pEList->nExpr>0;
   451    446     if( pOrderBy==0 && !hasDistinct ){
   452         -    codeLimiter(v, p, iContinue, iBreak, 0);
          447  +    codeOffset(v, p, iContinue, 0);
   453    448     }
   454    449   
   455    450     /* Pull the requested columns.
   456    451     */
   457    452     if( nColumn>0 ){
   458    453       for(i=0; i<nColumn; i++){
   459    454         sqlite3VdbeAddOp(v, OP_Column, srcTab, i);
................................................................................
   467    462     ** and this row has been seen before, then do not make this row
   468    463     ** part of the result.
   469    464     */
   470    465     if( hasDistinct ){
   471    466       int n = pEList->nExpr;
   472    467       codeDistinct(v, distinct, iContinue, n, n+1);
   473    468       if( pOrderBy==0 ){
   474         -      codeLimiter(v, p, iContinue, iBreak, nColumn);
          469  +      codeOffset(v, p, iContinue, nColumn);
   475    470       }
   476    471     }
   477    472   
   478    473     switch( eDest ){
   479    474       /* In this mode, write each query result to the key of the temporary
   480    475       ** table iParm.
   481    476       */
................................................................................
   542    537           aff = sqlite3CompareAffinity(pEList->a[0].pExpr, aff);
   543    538           sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, &aff, 1);
   544    539           sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0);
   545    540         }
   546    541         sqlite3VdbeJumpHere(v, addr2);
   547    542         break;
   548    543       }
          544  +
          545  +    /* If any row exists in the result set, record that fact and abort.
          546  +    */
          547  +    case SRT_Exists: {
          548  +      sqlite3VdbeAddOp(v, OP_MemInt, 1, iParm);
          549  +      sqlite3VdbeAddOp(v, OP_Pop, nColumn, 0);
          550  +      /* The LIMIT clause will terminate the loop for us */
          551  +      break;
          552  +    }
   549    553   
   550    554       /* If this is a scalar select that is part of an expression, then
   551    555       ** store the results in the appropriate memory cell and break out
   552    556       ** of the scan loop.
   553    557       */
   554         -    case SRT_Exists:
   555    558       case SRT_Mem: {
   556    559         assert( nColumn==1 );
   557    560         if( pOrderBy ){
   558    561           pushOntoSorter(pParse, v, pOrderBy);
   559    562         }else{
   560    563           sqlite3VdbeAddOp(v, OP_MemStore, iParm, 1);
   561         -        sqlite3VdbeAddOp(v, OP_Goto, 0, iBreak);
          564  +        /* The LIMIT clause will jump out of the loop for us */
   562    565         }
   563    566         break;
   564    567       }
   565    568   #endif /* #ifndef SQLITE_OMIT_SUBQUERY */
   566    569   
   567    570       /* Send the data to the callback function or to a subroutine.  In the
   568    571       ** case of a subroutine, the subroutine itself is responsible for
................................................................................
   590    593       default: {
   591    594         assert( eDest==SRT_Discard );
   592    595         sqlite3VdbeAddOp(v, OP_Pop, nColumn, 0);
   593    596         break;
   594    597       }
   595    598   #endif
   596    599     }
          600  +
          601  +  /* Jump to the end of the loop if the LIMIT is reached.
          602  +  */
          603  +  if( p->iLimit>=0 && pOrderBy==0 ){
          604  +    sqlite3VdbeAddOp(v, OP_MemIncr, p->iLimit, 0);
          605  +    sqlite3VdbeAddOp(v, OP_IfMemZero, p->iLimit, iBreak);
          606  +  }
   597    607     return 0;
   598    608   }
   599    609   
   600    610   /*
   601    611   ** Given an expression list, generate a KeyInfo structure that records
   602    612   ** the collating sequence for each expression in that expression list.
   603    613   **
................................................................................
   657    667     int cont = sqlite3VdbeMakeLabel(v);
   658    668     int addr;
   659    669     int iTab;
   660    670     ExprList *pOrderBy = p->pOrderBy;
   661    671   
   662    672     iTab = pOrderBy->iECursor;
   663    673     addr = 1 + sqlite3VdbeAddOp(v, OP_Sort, iTab, brk);
   664         -  codeLimiter(v, p, cont, brk, 0);
          674  +  codeOffset(v, p, cont, 0);
   665    675     sqlite3VdbeAddOp(v, OP_Column, iTab, pOrderBy->nExpr + 1);
   666    676     switch( eDest ){
   667    677       case SRT_Table:
   668    678       case SRT_VirtualTab: {
   669    679         sqlite3VdbeAddOp(v, OP_NewRowid, iParm, 0);
   670    680         sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
   671    681         sqlite3VdbeAddOp(v, OP_Insert, iParm, 0);
................................................................................
   677    687         sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);
   678    688         sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
   679    689         sqlite3VdbeAddOp(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+3);
   680    690         sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, "n", P3_STATIC);
   681    691         sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0);
   682    692         break;
   683    693       }
   684         -    case SRT_Exists:
   685    694       case SRT_Mem: {
   686    695         assert( nColumn==1 );
   687    696         sqlite3VdbeAddOp(v, OP_MemStore, iParm, 1);
   688         -      sqlite3VdbeAddOp(v, OP_Goto, 0, brk);
          697  +      /* The LIMIT clause will terminate the loop for us */
   689    698         break;
   690    699       }
   691    700   #endif
   692    701       case SRT_Callback:
   693    702       case SRT_Subroutine: {
   694    703         int i;
   695    704         sqlite3VdbeAddOp(v, OP_Integer, p->pEList->nExpr, 0);
................................................................................
   706    715         break;
   707    716       }
   708    717       default: {
   709    718         /* Do nothing */
   710    719         break;
   711    720       }
   712    721     }
          722  +
          723  +  /* Jump to the end of the loop when the LIMIT is reached
          724  +  */
          725  +  if( p->iLimit>=0 ){
          726  +    sqlite3VdbeAddOp(v, OP_MemIncr, p->iLimit, 0);
          727  +    sqlite3VdbeAddOp(v, OP_IfMemZero, p->iLimit, brk);
          728  +  }
          729  +
          730  +  /* The bottom of the loop
          731  +  */
   713    732     sqlite3VdbeResolveLabel(v, cont);
   714    733     sqlite3VdbeAddOp(v, OP_Next, iTab, addr);
   715    734     sqlite3VdbeResolveLabel(v, brk);
   716    735   }
   717    736   
   718    737   /*
   719    738   ** Return a pointer to a string containing the 'declaration type' of the
................................................................................
  1324   1343       v = pParse->pVdbe = sqlite3VdbeCreate(pParse->db);
  1325   1344     }
  1326   1345     return v;
  1327   1346   }
  1328   1347   
  1329   1348   /*
  1330   1349   ** Compute the iLimit and iOffset fields of the SELECT based on the
  1331         -** pLimit and pOffset expressions.  nLimit and nOffset hold the expressions
         1350  +** pLimit and pOffset expressions.  pLimit and pOffset hold the expressions
  1332   1351   ** that appear in the original SQL statement after the LIMIT and OFFSET
  1333   1352   ** keywords.  Or NULL if those keywords are omitted. iLimit and iOffset 
  1334   1353   ** are the integer memory register numbers for counters used to compute 
  1335   1354   ** the limit and offset.  If there is no limit and/or offset, then 
  1336   1355   ** iLimit and iOffset are negative.
  1337   1356   **
  1338   1357   ** This routine changes the values if iLimit and iOffset only if
  1339         -** a limit or offset is defined by nLimit and nOffset.  iLimit and
         1358  +** a limit or offset is defined by pLimit and pOffset.  iLimit and
  1340   1359   ** iOffset should have been preset to appropriate default values
  1341   1360   ** (usually but not always -1) prior to calling this routine.
  1342         -** Only if nLimit>=0 or nOffset>0 do the limit registers get
         1361  +** Only if pLimit!=0 or pOffset!=0 do the limit registers get
  1343   1362   ** redefined.  The UNION ALL operator uses this property to force
  1344   1363   ** the reuse of the same limit and offset registers across multiple
  1345   1364   ** SELECT statements.
  1346   1365   */
  1347         -static void computeLimitRegisters(Parse *pParse, Select *p){
         1366  +static void computeLimitRegisters(Parse *pParse, Select *p, int iBreak){
  1348   1367     /* 
  1349   1368     ** "LIMIT -1" always shows all rows.  There is some
  1350   1369     ** contraversy about what the correct behavior should be.
  1351   1370     ** The current implementation interprets "LIMIT 0" to mean
  1352   1371     ** no rows.
  1353   1372     */
  1354   1373     if( p->pLimit ){
................................................................................
  1356   1375       Vdbe *v = sqlite3GetVdbe(pParse);
  1357   1376       if( v==0 ) return;
  1358   1377       sqlite3ExprCode(pParse, p->pLimit);
  1359   1378       sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);
  1360   1379       sqlite3VdbeAddOp(v, OP_Negative, 0, 0);
  1361   1380       sqlite3VdbeAddOp(v, OP_MemStore, iMem, 1);
  1362   1381       VdbeComment((v, "# LIMIT counter"));
         1382  +    sqlite3VdbeAddOp(v, OP_IfMemZero, iMem, iBreak);
  1363   1383       p->iLimit = iMem;
  1364   1384     }
  1365   1385     if( p->pOffset ){
  1366   1386       int iMem = pParse->nMem++;
  1367   1387       Vdbe *v = sqlite3GetVdbe(pParse);
  1368   1388       if( v==0 ) return;
  1369   1389       sqlite3ExprCode(pParse, p->pOffset);
................................................................................
  1515   1535   
  1516   1536     /* Generate code for the left and right SELECT statements.
  1517   1537     */
  1518   1538     pOrderBy = p->pOrderBy;
  1519   1539     switch( p->op ){
  1520   1540       case TK_ALL: {
  1521   1541         if( pOrderBy==0 ){
         1542  +        int addr = 0;
  1522   1543           assert( !pPrior->pLimit );
  1523   1544           pPrior->pLimit = p->pLimit;
  1524   1545           pPrior->pOffset = p->pOffset;
  1525   1546           rc = sqlite3Select(pParse, pPrior, eDest, iParm, 0, 0, 0, aff);
  1526   1547           if( rc ){
  1527   1548             goto multi_select_end;
  1528   1549           }
  1529   1550           p->pPrior = 0;
  1530   1551           p->iLimit = pPrior->iLimit;
  1531   1552           p->iOffset = pPrior->iOffset;
  1532   1553           p->pLimit = 0;
  1533   1554           p->pOffset = 0;
         1555  +        if( p->iLimit>=0 ){
         1556  +          addr = sqlite3VdbeAddOp(v, OP_IfMemZero, p->iLimit, 0);
         1557  +          VdbeComment((v, "# Jump ahead if LIMIT reached"));
         1558  +        }
  1534   1559           rc = sqlite3Select(pParse, p, eDest, iParm, 0, 0, 0, aff);
  1535   1560           p->pPrior = pPrior;
  1536   1561           if( rc ){
  1537   1562             goto multi_select_end;
  1538   1563           }
         1564  +        if( addr ){
         1565  +          sqlite3VdbeJumpHere(v, addr);
         1566  +        }
  1539   1567           break;
  1540   1568         }
  1541   1569         /* For UNION ALL ... ORDER BY fall through to the next case */
  1542   1570       }
  1543   1571       case TK_EXCEPT:
  1544   1572       case TK_UNION: {
  1545   1573         int unionTab;    /* Cursor number of the temporary table holding result */
................................................................................
  1618   1646           int iCont, iBreak, iStart;
  1619   1647           assert( p->pEList );
  1620   1648           if( eDest==SRT_Callback ){
  1621   1649             generateColumnNames(pParse, 0, p->pEList);
  1622   1650           }
  1623   1651           iBreak = sqlite3VdbeMakeLabel(v);
  1624   1652           iCont = sqlite3VdbeMakeLabel(v);
         1653  +        computeLimitRegisters(pParse, p, iBreak);
  1625   1654           sqlite3VdbeAddOp(v, OP_Rewind, unionTab, iBreak);
  1626         -        computeLimitRegisters(pParse, p);
  1627   1655           iStart = sqlite3VdbeCurrentAddr(v);
  1628   1656           rc = selectInnerLoop(pParse, p, p->pEList, unionTab, p->pEList->nExpr,
  1629   1657                                pOrderBy, -1, eDest, iParm, 
  1630   1658                                iCont, iBreak, 0);
  1631   1659           if( rc ){
  1632   1660             rc = 1;
  1633   1661             goto multi_select_end;
................................................................................
  1694   1722         */
  1695   1723         assert( p->pEList );
  1696   1724         if( eDest==SRT_Callback ){
  1697   1725           generateColumnNames(pParse, 0, p->pEList);
  1698   1726         }
  1699   1727         iBreak = sqlite3VdbeMakeLabel(v);
  1700   1728         iCont = sqlite3VdbeMakeLabel(v);
         1729  +      computeLimitRegisters(pParse, p, iBreak);
  1701   1730         sqlite3VdbeAddOp(v, OP_Rewind, tab1, iBreak);
  1702         -      computeLimitRegisters(pParse, p);
  1703   1731         iStart = sqlite3VdbeAddOp(v, OP_RowKey, tab1, 0);
  1704   1732         sqlite3VdbeAddOp(v, OP_NotFound, tab2, iCont);
  1705   1733         rc = selectInnerLoop(pParse, p, p->pEList, tab1, p->pEList->nExpr,
  1706   1734                                pOrderBy, -1, eDest, iParm, 
  1707   1735                                iCont, iBreak, 0);
  1708   1736         if( rc ){
  1709   1737           rc = 1;
................................................................................
  2162   2190     Expr *pExpr;
  2163   2191     int iCol;
  2164   2192     Table *pTab;
  2165   2193     Index *pIdx;
  2166   2194     int base;
  2167   2195     Vdbe *v;
  2168   2196     int seekOp;
  2169         -  int cont;
  2170   2197     ExprList *pEList, *pList, eList;
  2171   2198     struct ExprList_item eListItem;
  2172   2199     SrcList *pSrc;
         2200  +  int brk;
  2173   2201   
  2174   2202     /* Check to see if this query is a simple min() or max() query.  Return
  2175   2203     ** zero if it is  not.
  2176   2204     */
  2177   2205     if( p->pGroupBy || p->pHaving || p->pWhere ) return 0;
  2178   2206     pSrc = p->pSrc;
  2179   2207     if( pSrc->nSrc!=1 ) return 0;
................................................................................
  2229   2257     /* Generating code to find the min or the max.  Basically all we have
  2230   2258     ** to do is find the first or the last entry in the chosen index.  If
  2231   2259     ** the min() or max() is on the INTEGER PRIMARY KEY, then find the first
  2232   2260     ** or last entry in the main table.
  2233   2261     */
  2234   2262     sqlite3CodeVerifySchema(pParse, pTab->iDb);
  2235   2263     base = pSrc->a[0].iCursor;
  2236         -  computeLimitRegisters(pParse, p);
         2264  +  brk = sqlite3VdbeMakeLabel(v);
         2265  +  computeLimitRegisters(pParse, p, brk);
  2237   2266     if( pSrc->a[0].pSelect==0 ){
  2238   2267       sqlite3OpenTableForReading(v, base, pTab);
  2239   2268     }
  2240         -  cont = sqlite3VdbeMakeLabel(v);
  2241   2269     if( pIdx==0 ){
  2242   2270       sqlite3VdbeAddOp(v, seekOp, base, 0);
  2243   2271     }else{
  2244   2272       /* Even though the cursor used to open the index here is closed
  2245   2273       ** as soon as a single value has been read from it, allocate it
  2246   2274       ** using (pParse->nTab++) to prevent the cursor id from being 
  2247   2275       ** reused. This is important for statements of the form 
................................................................................
  2262   2290       sqlite3VdbeAddOp(v, OP_Close, iIdx, 0);
  2263   2291       sqlite3VdbeAddOp(v, OP_MoveGe, base, 0);
  2264   2292     }
  2265   2293     eList.nExpr = 1;
  2266   2294     memset(&eListItem, 0, sizeof(eListItem));
  2267   2295     eList.a = &eListItem;
  2268   2296     eList.a[0].pExpr = pExpr;
  2269         -  selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, cont, cont, 0);
  2270         -  sqlite3VdbeResolveLabel(v, cont);
         2297  +  selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, brk, brk, 0);
         2298  +  sqlite3VdbeResolveLabel(v, brk);
  2271   2299     sqlite3VdbeAddOp(v, OP_Close, base, 0);
  2272   2300     
  2273   2301     return 1;
  2274   2302   }
  2275   2303   
  2276   2304   /*
  2277   2305   ** Analyze and ORDER BY or GROUP BY clause in a SELECT statement.  Return
................................................................................
  2611   2639     ExprList *pGroupBy;    /* The GROUP BY clause.  May be NULL */
  2612   2640     Expr *pHaving;         /* The HAVING clause.  May be NULL */
  2613   2641     int isDistinct;        /* True if the DISTINCT keyword is present */
  2614   2642     int distinct;          /* Table to use for the distinct set */
  2615   2643     int rc = 1;            /* Value to return from this function */
  2616   2644     int addrSortIndex;     /* Address of an OP_OpenVirtual instruction */
  2617   2645     AggInfo sAggInfo;      /* Information used by aggregate queries */
         2646  +  int iEnd;              /* Address of the end of the query */
  2618   2647   
  2619   2648     if( sqlite3_malloc_failed || pParse->nErr || p==0 ) return 1;
  2620   2649     if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  2621   2650     memset(&sAggInfo, 0, sizeof(sAggInfo));
  2622   2651   
  2623   2652   #ifndef SQLITE_OMIT_COMPOUND_SELECT
  2624   2653     /* If there is are a sequence of queries, do the earlier ones first.
................................................................................
  2659   2688     ** errors before this routine starts.
  2660   2689     */
  2661   2690     if( pParse->nErr>0 ) goto select_end;
  2662   2691   
  2663   2692     /* If writing to memory or generating a set
  2664   2693     ** only a single column may be output.
  2665   2694     */
  2666         -  assert( eDest!=SRT_Exists || pEList->nExpr==1 );
  2667   2695   #ifndef SQLITE_OMIT_SUBQUERY
  2668   2696     if( (eDest==SRT_Mem || eDest==SRT_Set) && pEList->nExpr>1 ){
  2669   2697       sqlite3ErrorMsg(pParse, "only a single result allowed for "
  2670   2698          "a SELECT that is part of an expression");
  2671   2699       goto select_end;
  2672   2700     }
  2673   2701   #endif
................................................................................
  2768   2796                           (char*)pKeyInfo, P3_KEYINFO_HANDOFF);
  2769   2797     }else{
  2770   2798       addrSortIndex = -1;
  2771   2799     }
  2772   2800   
  2773   2801     /* Set the limiter.
  2774   2802     */
  2775         -  computeLimitRegisters(pParse, p);
         2803  +  iEnd = sqlite3VdbeMakeLabel(v);
         2804  +  computeLimitRegisters(pParse, p, iEnd);
  2776   2805   
  2777   2806     /* If the output is destined for a temporary table, open that table.
  2778   2807     */
  2779   2808     if( eDest==SRT_VirtualTab ){
  2780   2809       sqlite3VdbeAddOp(v, OP_OpenVirtual, iParm, pEList->nExpr);
  2781   2810     }
  2782   2811   
  2783         -
  2784         -  /* Initialize the memory cell to NULL for SRT_Mem or 0 for SRT_Exists
  2785         -  */
  2786         -  if( eDest==SRT_Mem ){
  2787         -    sqlite3VdbeAddOp(v, OP_MemNull, iParm, 0);
  2788         -  }else if( eDest==SRT_Exists ){
  2789         -    sqlite3VdbeAddOp(v, OP_MemInt, 0, iParm);
  2790         -  }
  2791         -
  2792   2812     /* Open a virtual index to use for the distinct set.
  2793   2813     */
  2794   2814     if( isDistinct ){
  2795   2815       KeyInfo *pKeyInfo;
  2796   2816       distinct = pParse->nTab++;
  2797   2817       pKeyInfo = keyInfoFromExprList(pParse, p->pEList);
  2798   2818       sqlite3VdbeOp3(v, OP_OpenVirtual, distinct, 0, 
................................................................................
  3108   3128       assert( pParent->pSrc->nSrc>parentTab );
  3109   3129       assert( pParent->pSrc->a[parentTab].pSelect==p );
  3110   3130       sqlite3SelectDelete(p);
  3111   3131       pParent->pSrc->a[parentTab].pSelect = 0;
  3112   3132     }
  3113   3133   #endif
  3114   3134   
         3135  +  /* Jump here to skip this query
         3136  +  */
         3137  +  sqlite3VdbeResolveLabel(v, iEnd);
         3138  +
  3115   3139     /* The SELECT was successfully coded.   Set the return code to 0
  3116   3140     ** to indicate no errors.
  3117   3141     */
  3118   3142     rc = 0;
  3119   3143   
  3120   3144     /* Control jumps to here if an error is encountered above, or upon
  3121   3145     ** successful coding of the SELECT.
  3122   3146     */
  3123   3147   select_end:
  3124   3148     sqliteFree(sAggInfo.aCol);
  3125   3149     sqliteFree(sAggInfo.aFunc);
  3126   3150     return rc;
  3127   3151   }

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.421 2005/09/19 21:05:49 drh Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.422 2005/10/06 16:53:15 drh Exp $
    15     15   */
    16     16   #ifndef _SQLITEINT_H_
    17     17   #define _SQLITEINT_H_
    18     18   
    19     19   /*
    20     20   ** Many people are failing to set -DNDEBUG=1 when compiling SQLite.
    21     21   ** Setting NDEBUG makes the code smaller and run faster.  So the following
................................................................................
  1145   1145   
  1146   1146   #define SRT_Callback     4  /* Invoke a callback with each row of result */
  1147   1147   #define SRT_Mem          5  /* Store result in a memory cell */
  1148   1148   #define SRT_Set          6  /* Store non-null results as keys in an index */
  1149   1149   #define SRT_Table        7  /* Store result as data with an automatic rowid */
  1150   1150   #define SRT_VirtualTab   8  /* Create virtual table and store like SRT_Table */
  1151   1151   #define SRT_Subroutine   9  /* Call a subroutine to handle results */
  1152         -#define SRT_Exists      10  /* Put 0 or 1 in a memory cell */
         1152  +#define SRT_Exists      10  /* Store 1 if the result is not empty */
  1153   1153   
  1154   1154   /*
  1155   1155   ** An SQL parser context.  A copy of this structure is passed through
  1156   1156   ** the parser and down into all the parser action routine in order to
  1157   1157   ** carry around information that is global to the entire parse.
  1158   1158   **
  1159   1159   ** The structure is divided into two parts.  When the parser and code

Changes to src/table.c.

   192    192       if( azResult==0 ) return;
   193    193       n = (int)azResult[0];
   194    194       for(i=1; i<n; i++){ if( azResult[i] ) free(azResult[i]); }
   195    195       free(azResult);
   196    196     }
   197    197   }
   198    198   
   199         -#endif SQLITE_OMIT_GET_TABLE
          199  +#endif /* SQLITE_OMIT_GET_TABLE */

Changes to src/vdbe.c.

    39     39   **
    40     40   ** Various scripts scan this source file in order to generate HTML
    41     41   ** documentation, headers files, or other derived files.  The formatting
    42     42   ** of the code in this file is, therefore, important.  See other comments
    43     43   ** in this file for details.  If in doubt, do not deviate from existing
    44     44   ** commenting and indentation practices when changing or adding code.
    45     45   **
    46         -** $Id: vdbe.c,v 1.491 2005/09/20 17:42:23 drh Exp $
           46  +** $Id: vdbe.c,v 1.492 2005/10/06 16:53:16 drh Exp $
    47     47   */
    48     48   #include "sqliteInt.h"
    49     49   #include "os.h"
    50     50   #include <ctype.h>
    51     51   #include "vdbeInt.h"
    52     52   
    53     53   /*
................................................................................
  4137   4137     break;
  4138   4138   }
  4139   4139   #endif /* SQLITE_OMIT_AUTOINCREMENT */
  4140   4140   
  4141   4141   /* Opcode: MemIncr P1 P2 *
  4142   4142   **
  4143   4143   ** Increment the integer valued memory cell P1 by 1.  If P2 is not zero
  4144         -** and the result after the increment is exactly 1, then jump
         4144  +** and the result after the increment is exactly 0, then jump
  4145   4145   ** to P2.
  4146   4146   **
  4147   4147   ** This instruction throws an error if the memory cell is not initially
  4148   4148   ** an integer.
  4149   4149   */
  4150   4150   case OP_MemIncr: {        /* no-push */
  4151   4151     int i = pOp->p1;
  4152   4152     Mem *pMem;
  4153   4153     assert( i>=0 && i<p->nMem );
  4154   4154     pMem = &p->aMem[i];
  4155   4155     assert( pMem->flags==MEM_Int );
  4156   4156     pMem->i++;
  4157         -  if( pOp->p2>0 && pMem->i==1 ){
         4157  +  if( pOp->p2>0 && pMem->i==0 ){
  4158   4158        pc = pOp->p2 - 1;
  4159   4159     }
  4160   4160     break;
  4161   4161   }
  4162   4162   
  4163   4163   /* Opcode: IfMemPos P1 P2 *
  4164   4164   **
  4165         -** If the value of memory cell P1 is 1 or greater, jump to P2. This
  4166         -** opcode assumes that memory cell P1 holds an integer value.
         4165  +** If the value of memory cell P1 is 1 or greater, jump to P2.  If
         4166  +** the memory cell holds an integer of 0 or less or if it holds something
         4167  +** that is not an integer, then fall thru.
  4167   4168   */
  4168   4169   case OP_IfMemPos: {        /* no-push */
  4169   4170     int i = pOp->p1;
  4170   4171     Mem *pMem;
  4171   4172     assert( i>=0 && i<p->nMem );
  4172   4173     pMem = &p->aMem[i];
  4173         -  assert( pMem->flags==MEM_Int );
  4174         -  if( pMem->i>0 ){
         4174  +  if( pMem->flags==MEM_Int && pMem->i>0 ){
         4175  +     pc = pOp->p2 - 1;
         4176  +  }
         4177  +  break;
         4178  +}
         4179  +
         4180  +/* Opcode: IfMemZero P1 P2 *
         4181  +**
         4182  +** If the value of memory cell P1 is exactly 0, jump to P2.
         4183  +*/
         4184  +case OP_IfMemZero: {        /* no-push */
         4185  +  int i = pOp->p1;
         4186  +  Mem *pMem;
         4187  +  assert( i>=0 && i<p->nMem );
         4188  +  pMem = &p->aMem[i];
         4189  +  if( pMem->flags==MEM_Int && pMem->i==0 ){
  4175   4190        pc = pOp->p2 - 1;
  4176   4191     }
  4177   4192     break;
  4178   4193   }
  4179   4194   
  4180   4195   /* Opcode: MemNull P1 * *
  4181   4196   **

Added test/tkt1473.test.

            1  +# 2005 September 19
            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  +# This file implements tests to verify that ticket #1473 has been
           14  +# fixed.  
           15  +#
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +do_test tkt1473-1.1 {
           21  +  execsql {
           22  +    CREATE TABLE t1(a,b);
           23  +    INSERT INTO t1 VALUES(1,2);
           24  +    INSERT INTO t1 VALUES(3,4);
           25  +    SELECT * FROM t1
           26  +  }
           27  +} {1 2 3 4}
           28  +
           29  +do_test tkt1473-1.2 {
           30  +  execsql {
           31  +    SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=0
           32  +  }
           33  +} {1}
           34  +do_test tkt1473-1.3 {
           35  +  execsql {
           36  +    SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=0
           37  +  }
           38  +} {1}
           39  +do_test tkt1473-1.4 {
           40  +  execsql {
           41  +    SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=4
           42  +  }
           43  +} {1 2}
           44  +do_test tkt1473-1.5 {
           45  +  execsql {
           46  +    SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=4
           47  +  }
           48  +} {1 2}
           49  +do_test tkt1473-1.6 {
           50  +  execsql {
           51  +    SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=4
           52  +  }
           53  +} {2}
           54  +do_test tkt1473-1.7 {
           55  +  execsql {
           56  +    SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=4
           57  +  }
           58  +} {2}
           59  +do_test tkt1473-1.8 {
           60  +  execsql {
           61  +    SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=0
           62  +  }
           63  +} {}
           64  +do_test tkt1473-1.9 {
           65  +  execsql {
           66  +    SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=0
           67  +  }
           68  +} {}
           69  +
           70  +do_test tkt1473-2.2 {
           71  +  execsql {
           72  +    SELECT (SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=0)
           73  +  }
           74  +} {1}
           75  +do_test tkt1473-2.3 {
           76  +  execsql {
           77  +    SELECT (SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=0)
           78  +  }
           79  +} {1}
           80  +do_test tkt1473-2.4 {
           81  +  execsql {
           82  +    SELECT (SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=4)
           83  +  }
           84  +} {1}
           85  +do_test tkt1473-2.5 {
           86  +  execsql {
           87  +    SELECT (SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=4)
           88  +  }
           89  +} {1}
           90  +do_test tkt1473-2.6 {
           91  +  execsql {
           92  +    SELECT (SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=4)
           93  +  }
           94  +} {2}
           95  +do_test tkt1473-2.7 {
           96  +  execsql {
           97  +    SELECT (SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=4)
           98  +  }
           99  +} {2}
          100  +do_test tkt1473-2.8 {
          101  +  execsql {
          102  +    SELECT (SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=0)
          103  +  }
          104  +} {{}}
          105  +do_test tkt1473-2.9 {
          106  +  execsql {
          107  +    SELECT (SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=0)
          108  +  }
          109  +} {{}}
          110  +
          111  +do_test tkt1473-3.2 {
          112  +  execsql {
          113  +    SELECT EXISTS
          114  +      (SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=0)
          115  +  }
          116  +} {1}
          117  +do_test tkt1473-3.3 {
          118  +  execsql {
          119  +    SELECT EXISTS
          120  +      (SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=0)
          121  +  }
          122  +} {1}
          123  +do_test tkt1473-3.4 {
          124  +  execsql {
          125  +    SELECT EXISTS
          126  +      (SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=4)
          127  +  }
          128  +} {1}
          129  +do_test tkt1473-3.5 {
          130  +  execsql {
          131  +    SELECT EXISTS
          132  +      (SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=4)
          133  +  }
          134  +} {1}
          135  +do_test tkt1473-3.6 {
          136  +  execsql {
          137  +    SELECT EXISTS
          138  +      (SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=4)
          139  +  }
          140  +} {1}
          141  +do_test tkt1473-3.7 {
          142  +  execsql {
          143  +    SELECT EXISTS
          144  +      (SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=4)
          145  +  }
          146  +} {1}
          147  +do_test tkt1473-3.8 {
          148  +  execsql {
          149  +    SELECT EXISTS
          150  +      (SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=0)
          151  +  }
          152  +} {0}
          153  +do_test tkt1473-3.9 {
          154  +  execsql {
          155  +    SELECT EXISTS
          156  +      (SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=0)
          157  +  }
          158  +} {0}
          159  +
          160  +do_test tkt1473-4.1 {
          161  +  execsql {
          162  +    CREATE TABLE t2(x,y);
          163  +    INSERT INTO t2 VALUES(1,2);
          164  +    INSERT INTO t2 SELECT x+2, y+2 FROM t2;
          165  +    INSERT INTO t2 SELECT x+4, y+4 FROM t2;
          166  +    INSERT INTO t2 SELECT x+8, y+8 FROM t2;
          167  +    INSERT INTO t2 SELECT x+16, y+16 FROM t2;
          168  +    INSERT INTO t2 SELECT x+32, y+32 FROM t2;
          169  +    INSERT INTO t2 SELECT x+64, y+64 FROM t2;
          170  +    SELECT count(*), sum(x), sum(y) FROM t2;
          171  +  }
          172  +} {64 4096 4160}
          173  +do_test tkt1473-4.2 {
          174  +  execsql {
          175  +    SELECT 1 FROM t2 WHERE x=0
          176  +    UNION ALL
          177  +    SELECT 2 FROM t2 WHERE x=1
          178  +    UNION ALL
          179  +    SELECT 3 FROM t2 WHERE x=2
          180  +    UNION ALL
          181  +    SELECT 4 FROM t2 WHERE x=3
          182  +    UNION ALL
          183  +    SELECT 5 FROM t2 WHERE x=4
          184  +    UNION ALL
          185  +    SELECT 6 FROM t2 WHERE y=0
          186  +    UNION ALL
          187  +    SELECT 7 FROM t2 WHERE y=1
          188  +    UNION ALL
          189  +    SELECT 8 FROM t2 WHERE y=2
          190  +    UNION ALL
          191  +    SELECT 9 FROM t2 WHERE y=3
          192  +    UNION ALL
          193  +    SELECT 10 FROM t2 WHERE y=4
          194  +  }
          195  +} {2 4 8 10}
          196  +do_test tkt1473-4.3 {
          197  +  execsql {
          198  +    SELECT (
          199  +      SELECT 1 FROM t2 WHERE x=0
          200  +      UNION ALL
          201  +      SELECT 2 FROM t2 WHERE x=1
          202  +      UNION ALL
          203  +      SELECT 3 FROM t2 WHERE x=2
          204  +      UNION ALL
          205  +      SELECT 4 FROM t2 WHERE x=3
          206  +      UNION ALL
          207  +      SELECT 5 FROM t2 WHERE x=4
          208  +      UNION ALL
          209  +      SELECT 6 FROM t2 WHERE y=0
          210  +      UNION ALL
          211  +      SELECT 7 FROM t2 WHERE y=1
          212  +      UNION ALL
          213  +      SELECT 8 FROM t2 WHERE y=2
          214  +      UNION ALL
          215  +      SELECT 9 FROM t2 WHERE y=3
          216  +      UNION ALL
          217  +      SELECT 10 FROM t2 WHERE y=4
          218  +    )
          219  +  }
          220  +} {2}
          221  +do_test tkt1473-4.4 {
          222  +  execsql {
          223  +    SELECT (
          224  +      SELECT 1 FROM t2 WHERE x=0
          225  +      UNION ALL
          226  +      SELECT 2 FROM t2 WHERE x=-1
          227  +      UNION ALL
          228  +      SELECT 3 FROM t2 WHERE x=2
          229  +      UNION ALL
          230  +      SELECT 4 FROM t2 WHERE x=3
          231  +      UNION ALL
          232  +      SELECT 5 FROM t2 WHERE x=4
          233  +      UNION ALL
          234  +      SELECT 6 FROM t2 WHERE y=0
          235  +      UNION ALL
          236  +      SELECT 7 FROM t2 WHERE y=1
          237  +      UNION ALL
          238  +      SELECT 8 FROM t2 WHERE y=2
          239  +      UNION ALL
          240  +      SELECT 9 FROM t2 WHERE y=3
          241  +      UNION ALL
          242  +      SELECT 10 FROM t2 WHERE y=4
          243  +    )
          244  +  }
          245  +} {4}
          246  +do_test tkt1473-4.5 {
          247  +  execsql {
          248  +    SELECT (
          249  +      SELECT 1 FROM t2 WHERE x=0
          250  +      UNION ALL
          251  +      SELECT 2 FROM t2 WHERE x=-1
          252  +      UNION ALL
          253  +      SELECT 3 FROM t2 WHERE x=2
          254  +      UNION ALL
          255  +      SELECT 4 FROM t2 WHERE x=-1
          256  +      UNION ALL
          257  +      SELECT 5 FROM t2 WHERE x=4
          258  +      UNION ALL
          259  +      SELECT 6 FROM t2 WHERE y=0
          260  +      UNION ALL
          261  +      SELECT 7 FROM t2 WHERE y=1
          262  +      UNION ALL
          263  +      SELECT 8 FROM t2 WHERE y=2
          264  +      UNION ALL
          265  +      SELECT 9 FROM t2 WHERE y=3
          266  +      UNION ALL
          267  +      SELECT 10 FROM t2 WHERE y=-4
          268  +    )
          269  +  }
          270  +} {8}
          271  +do_test tkt1473-4.6 {
          272  +  execsql {
          273  +    SELECT (
          274  +      SELECT 1 FROM t2 WHERE x=0
          275  +      UNION ALL
          276  +      SELECT 2 FROM t2 WHERE x=-1
          277  +      UNION ALL
          278  +      SELECT 3 FROM t2 WHERE x=2
          279  +      UNION ALL
          280  +      SELECT 4 FROM t2 WHERE x=-2
          281  +      UNION ALL
          282  +      SELECT 5 FROM t2 WHERE x=4
          283  +      UNION ALL
          284  +      SELECT 6 FROM t2 WHERE y=0
          285  +      UNION ALL
          286  +      SELECT 7 FROM t2 WHERE y=1
          287  +      UNION ALL
          288  +      SELECT 8 FROM t2 WHERE y=-3
          289  +      UNION ALL
          290  +      SELECT 9 FROM t2 WHERE y=3
          291  +      UNION ALL
          292  +      SELECT 10 FROM t2 WHERE y=4
          293  +    )
          294  +  }
          295  +} {10}
          296  +do_test tkt1473-4.7 {
          297  +  execsql {
          298  +    SELECT (
          299  +      SELECT 1 FROM t2 WHERE x=0
          300  +      UNION ALL
          301  +      SELECT 2 FROM t2 WHERE x=-1
          302  +      UNION ALL
          303  +      SELECT 3 FROM t2 WHERE x=2
          304  +      UNION ALL
          305  +      SELECT 4 FROM t2 WHERE x=-2
          306  +      UNION ALL
          307  +      SELECT 5 FROM t2 WHERE x=4
          308  +      UNION ALL
          309  +      SELECT 6 FROM t2 WHERE y=0
          310  +      UNION ALL
          311  +      SELECT 7 FROM t2 WHERE y=1
          312  +      UNION ALL
          313  +      SELECT 8 FROM t2 WHERE y=-3
          314  +      UNION ALL
          315  +      SELECT 9 FROM t2 WHERE y=3
          316  +      UNION ALL
          317  +      SELECT 10 FROM t2 WHERE y=-4
          318  +    )
          319  +  }
          320  +} {{}}
          321  +
          322  +do_test tkt1473-5.3 {
          323  +  execsql {
          324  +    SELECT EXISTS (
          325  +      SELECT 1 FROM t2 WHERE x=0
          326  +      UNION ALL
          327  +      SELECT 2 FROM t2 WHERE x=1
          328  +      UNION ALL
          329  +      SELECT 3 FROM t2 WHERE x=2
          330  +      UNION ALL
          331  +      SELECT 4 FROM t2 WHERE x=3
          332  +      UNION ALL
          333  +      SELECT 5 FROM t2 WHERE x=4
          334  +      UNION ALL
          335  +      SELECT 6 FROM t2 WHERE y=0
          336  +      UNION ALL
          337  +      SELECT 7 FROM t2 WHERE y=1
          338  +      UNION ALL
          339  +      SELECT 8 FROM t2 WHERE y=2
          340  +      UNION ALL
          341  +      SELECT 9 FROM t2 WHERE y=3
          342  +      UNION ALL
          343  +      SELECT 10 FROM t2 WHERE y=4
          344  +    )
          345  +  }
          346  +} {1}
          347  +do_test tkt1473-5.4 {
          348  +  execsql {
          349  +    SELECT EXISTS (
          350  +      SELECT 1 FROM t2 WHERE x=0
          351  +      UNION ALL
          352  +      SELECT 2 FROM t2 WHERE x=-1
          353  +      UNION ALL
          354  +      SELECT 3 FROM t2 WHERE x=2
          355  +      UNION ALL
          356  +      SELECT 4 FROM t2 WHERE x=3
          357  +      UNION ALL
          358  +      SELECT 5 FROM t2 WHERE x=4
          359  +      UNION ALL
          360  +      SELECT 6 FROM t2 WHERE y=0
          361  +      UNION ALL
          362  +      SELECT 7 FROM t2 WHERE y=1
          363  +      UNION ALL
          364  +      SELECT 8 FROM t2 WHERE y=2
          365  +      UNION ALL
          366  +      SELECT 9 FROM t2 WHERE y=3
          367  +      UNION ALL
          368  +      SELECT 10 FROM t2 WHERE y=4
          369  +    )
          370  +  }
          371  +} {1}
          372  +
          373  +do_test tkt1473-5.5 {
          374  +  execsql {
          375  +    SELECT EXISTS (
          376  +      SELECT 1 FROM t2 WHERE x=0
          377  +      UNION ALL
          378  +      SELECT 2 FROM t2 WHERE x=-1
          379  +      UNION ALL
          380  +      SELECT 3 FROM t2 WHERE x=2
          381  +      UNION ALL
          382  +      SELECT 4 FROM t2 WHERE x=-1
          383  +      UNION ALL
          384  +      SELECT 5 FROM t2 WHERE x=4
          385  +      UNION ALL
          386  +      SELECT 6 FROM t2 WHERE y=0
          387  +      UNION ALL
          388  +      SELECT 7 FROM t2 WHERE y=1
          389  +      UNION ALL
          390  +      SELECT 8 FROM t2 WHERE y=2
          391  +      UNION ALL
          392  +      SELECT 9 FROM t2 WHERE y=3
          393  +      UNION ALL
          394  +      SELECT 10 FROM t2 WHERE y=-4
          395  +    )
          396  +  }
          397  +} {1}
          398  +do_test tkt1473-5.6 {
          399  +  execsql {
          400  +    SELECT EXISTS (
          401  +      SELECT 1 FROM t2 WHERE x=0
          402  +      UNION ALL
          403  +      SELECT 2 FROM t2 WHERE x=-1
          404  +      UNION ALL
          405  +      SELECT 3 FROM t2 WHERE x=2
          406  +      UNION ALL
          407  +      SELECT 4 FROM t2 WHERE x=-2
          408  +      UNION ALL
          409  +      SELECT 5 FROM t2 WHERE x=4
          410  +      UNION ALL
          411  +      SELECT 6 FROM t2 WHERE y=0
          412  +      UNION ALL
          413  +      SELECT 7 FROM t2 WHERE y=1
          414  +      UNION ALL
          415  +      SELECT 8 FROM t2 WHERE y=-3
          416  +      UNION ALL
          417  +      SELECT 9 FROM t2 WHERE y=3
          418  +      UNION ALL
          419  +      SELECT 10 FROM t2 WHERE y=4
          420  +    )
          421  +  }
          422  +} {1}
          423  +do_test tkt1473-5.7 {
          424  +  execsql {
          425  +    SELECT EXISTS (
          426  +      SELECT 1 FROM t2 WHERE x=0
          427  +      UNION ALL
          428  +      SELECT 2 FROM t2 WHERE x=-1
          429  +      UNION ALL
          430  +      SELECT 3 FROM t2 WHERE x=2
          431  +      UNION ALL
          432  +      SELECT 4 FROM t2 WHERE x=-2
          433  +      UNION ALL
          434  +      SELECT 5 FROM t2 WHERE x=4
          435  +      UNION ALL
          436  +      SELECT 6 FROM t2 WHERE y=0
          437  +      UNION ALL
          438  +      SELECT 7 FROM t2 WHERE y=1
          439  +      UNION ALL
          440  +      SELECT 8 FROM t2 WHERE y=-3
          441  +      UNION ALL
          442  +      SELECT 9 FROM t2 WHERE y=3
          443  +      UNION ALL
          444  +      SELECT 10 FROM t2 WHERE y=-4
          445  +    )
          446  +  }
          447  +} {0}
          448  +
          449  +do_test tkt1473-6.3 {
          450  +  execsql {
          451  +    SELECT EXISTS (
          452  +      SELECT 1 FROM t2 WHERE x=0
          453  +      UNION
          454  +      SELECT 2 FROM t2 WHERE x=1
          455  +      UNION
          456  +      SELECT 3 FROM t2 WHERE x=2
          457  +      UNION
          458  +      SELECT 4 FROM t2 WHERE x=3
          459  +      UNION
          460  +      SELECT 5 FROM t2 WHERE x=4
          461  +      UNION
          462  +      SELECT 6 FROM t2 WHERE y=0
          463  +      UNION
          464  +      SELECT 7 FROM t2 WHERE y=1
          465  +      UNION
          466  +      SELECT 8 FROM t2 WHERE y=2
          467  +      UNION
          468  +      SELECT 9 FROM t2 WHERE y=3
          469  +      UNION
          470  +      SELECT 10 FROM t2 WHERE y=4
          471  +    )
          472  +  }
          473  +} {1}
          474  +do_test tkt1473-6.4 {
          475  +  execsql {
          476  +    SELECT EXISTS (
          477  +      SELECT 1 FROM t2 WHERE x=0
          478  +      UNION
          479  +      SELECT 2 FROM t2 WHERE x=-1
          480  +      UNION
          481  +      SELECT 3 FROM t2 WHERE x=2
          482  +      UNION
          483  +      SELECT 4 FROM t2 WHERE x=3
          484  +      UNION
          485  +      SELECT 5 FROM t2 WHERE x=4
          486  +      UNION
          487  +      SELECT 6 FROM t2 WHERE y=0
          488  +      UNION
          489  +      SELECT 7 FROM t2 WHERE y=1
          490  +      UNION
          491  +      SELECT 8 FROM t2 WHERE y=2
          492  +      UNION
          493  +      SELECT 9 FROM t2 WHERE y=3
          494  +      UNION
          495  +      SELECT 10 FROM t2 WHERE y=4
          496  +    )
          497  +  }
          498  +} {1}
          499  +
          500  +do_test tkt1473-6.5 {
          501  +  execsql {
          502  +    SELECT EXISTS (
          503  +      SELECT 1 FROM t2 WHERE x=0
          504  +      UNION
          505  +      SELECT 2 FROM t2 WHERE x=-1
          506  +      UNION
          507  +      SELECT 3 FROM t2 WHERE x=2
          508  +      UNION
          509  +      SELECT 4 FROM t2 WHERE x=-1
          510  +      UNION
          511  +      SELECT 5 FROM t2 WHERE x=4
          512  +      UNION
          513  +      SELECT 6 FROM t2 WHERE y=0
          514  +      UNION
          515  +      SELECT 7 FROM t2 WHERE y=1
          516  +      UNION
          517  +      SELECT 8 FROM t2 WHERE y=2
          518  +      UNION
          519  +      SELECT 9 FROM t2 WHERE y=3
          520  +      UNION
          521  +      SELECT 10 FROM t2 WHERE y=-4
          522  +    )
          523  +  }
          524  +} {1}
          525  +do_test tkt1473-6.6 {
          526  +  execsql {
          527  +    SELECT EXISTS (
          528  +      SELECT 1 FROM t2 WHERE x=0
          529  +      UNION
          530  +      SELECT 2 FROM t2 WHERE x=-1
          531  +      UNION
          532  +      SELECT 3 FROM t2 WHERE x=2
          533  +      UNION
          534  +      SELECT 4 FROM t2 WHERE x=-2
          535  +      UNION
          536  +      SELECT 5 FROM t2 WHERE x=4
          537  +      UNION
          538  +      SELECT 6 FROM t2 WHERE y=0
          539  +      UNION
          540  +      SELECT 7 FROM t2 WHERE y=1
          541  +      UNION
          542  +      SELECT 8 FROM t2 WHERE y=-3
          543  +      UNION
          544  +      SELECT 9 FROM t2 WHERE y=3
          545  +      UNION
          546  +      SELECT 10 FROM t2 WHERE y=4
          547  +    )
          548  +  }
          549  +} {1}
          550  +do_test tkt1473-6.7 {
          551  +  execsql {
          552  +    SELECT EXISTS (
          553  +      SELECT 1 FROM t2 WHERE x=0
          554  +      UNION
          555  +      SELECT 2 FROM t2 WHERE x=-1
          556  +      UNION
          557  +      SELECT 3 FROM t2 WHERE x=2
          558  +      UNION
          559  +      SELECT 4 FROM t2 WHERE x=-2
          560  +      UNION
          561  +      SELECT 5 FROM t2 WHERE x=4
          562  +      UNION
          563  +      SELECT 6 FROM t2 WHERE y=0
          564  +      UNION
          565  +      SELECT 7 FROM t2 WHERE y=1
          566  +      UNION
          567  +      SELECT 8 FROM t2 WHERE y=-3
          568  +      UNION
          569  +      SELECT 9 FROM t2 WHERE y=3
          570  +      UNION
          571  +      SELECT 10 FROM t2 WHERE y=-4
          572  +    )
          573  +  }
          574  +} {0}
          575  +do_test tkt1473-6.8 {
          576  +  execsql {
          577  +    SELECT EXISTS (
          578  +      SELECT 1 FROM t2 WHERE x=0
          579  +      UNION
          580  +      SELECT 2 FROM t2 WHERE x=-1
          581  +      UNION
          582  +      SELECT 3 FROM t2 WHERE x=2
          583  +      UNION
          584  +      SELECT 4 FROM t2 WHERE x=-2
          585  +      UNION
          586  +      SELECT 5 FROM t2 WHERE x=4
          587  +      UNION ALL
          588  +      SELECT 6 FROM t2 WHERE y=0
          589  +      UNION
          590  +      SELECT 7 FROM t2 WHERE y=1
          591  +      UNION
          592  +      SELECT 8 FROM t2 WHERE y=-3
          593  +      UNION
          594  +      SELECT 9 FROM t2 WHERE y=3
          595  +      UNION
          596  +      SELECT 10 FROM t2 WHERE y=4
          597  +    )
          598  +  }
          599  +} {1}
          600  +do_test tkt1473-6.9 {
          601  +  execsql {
          602  +    SELECT EXISTS (
          603  +      SELECT 1 FROM t2 WHERE x=0
          604  +      UNION
          605  +      SELECT 2 FROM t2 WHERE x=-1
          606  +      UNION
          607  +      SELECT 3 FROM t2 WHERE x=2
          608  +      UNION
          609  +      SELECT 4 FROM t2 WHERE x=-2
          610  +      UNION
          611  +      SELECT 5 FROM t2 WHERE x=4
          612  +      UNION ALL
          613  +      SELECT 6 FROM t2 WHERE y=0
          614  +      UNION
          615  +      SELECT 7 FROM t2 WHERE y=1
          616  +      UNION
          617  +      SELECT 8 FROM t2 WHERE y=-3
          618  +      UNION
          619  +      SELECT 9 FROM t2 WHERE y=3
          620  +      UNION
          621  +      SELECT 10 FROM t2 WHERE y=-4
          622  +    )
          623  +  }
          624  +} {0}
          625  +
          626  +do_test tkt1473-7.1 {
          627  +  execsql {
          628  +    SELECT 1 FROM t2 WHERE x=1 EXCEPT SELECT 2 FROM t2 WHERE y=2
          629  +  }
          630  +} {1}
          631  +do_test tkt1473-7.2 {
          632  +  execsql {
          633  +    SELECT (
          634  +      SELECT 1 FROM t2 WHERE x=1 EXCEPT SELECT 2 FROM t2 WHERE y=2
          635  +    )
          636  +  }
          637  +} {1}
          638  +do_test tkt1473-7.3 {
          639  +  execsql {
          640  +    SELECT EXISTS (
          641  +      SELECT 1 FROM t2 WHERE x=1 EXCEPT SELECT 2 FROM t2 WHERE y=2
          642  +    )
          643  +  }
          644  +} {1}
          645  +do_test tkt1473-7.4 {
          646  +  execsql {
          647  +    SELECT (
          648  +      SELECT 1 FROM t2 WHERE x=0 EXCEPT SELECT 2 FROM t2 WHERE y=2
          649  +    )
          650  +  }
          651  +} {{}}
          652  +do_test tkt1473-7.5 {
          653  +  execsql {
          654  +    SELECT EXISTS (
          655  +      SELECT 1 FROM t2 WHERE x=0 EXCEPT SELECT 2 FROM t2 WHERE y=2
          656  +    )
          657  +  }
          658  +} {0}
          659  +
          660  +do_test tkt1473-8.1 {
          661  +  execsql {
          662  +    SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 2 FROM t2 WHERE y=2
          663  +  }
          664  +} {}
          665  +do_test tkt1473-8.1 {
          666  +  execsql {
          667  +    SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 1 FROM t2 WHERE y=2
          668  +  }
          669  +} {1}
          670  +do_test tkt1473-8.3 {
          671  +  execsql {
          672  +    SELECT (
          673  +      SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 2 FROM t2 WHERE y=2
          674  +    )
          675  +  }
          676  +} {{}}
          677  +do_test tkt1473-8.4 {
          678  +  execsql {
          679  +    SELECT (
          680  +      SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 1 FROM t2 WHERE y=2
          681  +    )
          682  +  }
          683  +} {1}
          684  +do_test tkt1473-8.5 {
          685  +  execsql {
          686  +    SELECT EXISTS (
          687  +      SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 2 FROM t2 WHERE y=2
          688  +    )
          689  +  }
          690  +} {0}
          691  +do_test tkt1473-8.6 {
          692  +  execsql {
          693  +    SELECT EXISTS (
          694  +      SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 1 FROM t2 WHERE y=2
          695  +    )
          696  +  }
          697  +} {1}
          698  +do_test tkt1473-8.7 {
          699  +  execsql {
          700  +    SELECT (
          701  +      SELECT 1 FROM t2 WHERE x=0 INTERSECT SELECT 1 FROM t2 WHERE y=2
          702  +    )
          703  +  }
          704  +} {{}}
          705  +do_test tkt1473-8.8 {
          706  +  execsql {
          707  +    SELECT EXISTS (
          708  +      SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 1 FROM t2 WHERE y=0
          709  +    )
          710  +  }
          711  +} {0}
          712  +
          713  +
          714  +
          715  +
          716  +finish_test

Changes to test/where.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the use of indices in WHERE clases.
    13     13   #
    14         -# $Id: where.test,v 1.36 2005/09/08 10:37:02 drh Exp $
           14  +# $Id: where.test,v 1.37 2005/10/06 16:53:17 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Build some test data
    20     20   #
    21     21   do_test where-1.0 {
................................................................................
   302    302       SELECT * FROM t1 WHERE 0
   303    303     }
   304    304   } {0}
   305    305   do_test where-4.2 {
   306    306     count {
   307    307       SELECT * FROM t1 WHERE 1 LIMIT 1
   308    308     }
   309         -} {1 0 4 1}
          309  +} {1 0 4 0}
   310    310   do_test where-4.3 {
   311    311     execsql {
   312    312       SELECT 99 WHERE 0
   313    313     }
   314    314   } {}
   315    315   do_test where-4.4 {
   316    316     execsql {