/ Check-in [43c5aff5]
Login

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

Overview
Comment:Fix for ticket #73: The ORDER BY clause is significant for subqueries. This passes all regression tests, but more testing is needed to exercise all paths through the new code. (CVS 631)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:43c5aff5d078bce9292683cd40311e0dcc81ac14
User & Date: drh 2002-06-20 03:38:26
Context
2002-06-20
11:36
This patch contains the beginnings of the data-typing infrastructure. The new build-in TypeOf() function is added. New opcodes for doing pure text comparisons are added. Most changes are disabled pending the 2.6.0 release. (CVS 632) check-in: cbbc858d user: drh tags: trunk
03:38
Fix for ticket #73: The ORDER BY clause is significant for subqueries. This passes all regression tests, but more testing is needed to exercise all paths through the new code. (CVS 631) check-in: 43c5aff5 user: drh tags: trunk
2002-06-19
20:32
Fix for ticket #75: Autoincrement INTEGER PRIMARY KEY fields on an INSERT even if the data is coming from a SELECT statement. (CVS 630) check-in: d599f75b user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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.94 2002/06/19 14:27:05 drh Exp $
           15  +** $Id: select.c,v 1.95 2002/06/20 03:38:26 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Allocate a new Select structure and return a pointer to that
    21     21   ** structure.
    22     22   */
................................................................................
   279    279   */
   280    280   static void sqliteAggregateInfoReset(Parse *pParse){
   281    281     sqliteFree(pParse->aAgg);
   282    282     pParse->aAgg = 0;
   283    283     pParse->nAgg = 0;
   284    284     pParse->useAgg = 0;
   285    285   }
          286  +
          287  +/*
          288  +** Insert code into "v" that will push the record on the top of the
          289  +** stack into the sorter.
          290  +*/
          291  +static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){
          292  +  char *zSortOrder;
          293  +  int i;
          294  +  zSortOrder = sqliteMalloc( pOrderBy->nExpr + 1 );
          295  +  if( zSortOrder==0 ) return;
          296  +  for(i=0; i<pOrderBy->nExpr; i++){
          297  +    zSortOrder[i] = pOrderBy->a[i].sortOrder ? '-' : '+';
          298  +    sqliteExprCode(pParse, pOrderBy->a[i].pExpr);
          299  +  }
          300  +  zSortOrder[pOrderBy->nExpr] = 0;
          301  +  sqliteVdbeAddOp(v, OP_SortMakeKey, pOrderBy->nExpr, 0);
          302  +  sqliteVdbeChangeP3(v, -1, zSortOrder, strlen(zSortOrder));
          303  +  sqliteFree(zSortOrder);
          304  +  sqliteVdbeAddOp(v, OP_SortPut, 0, 0);
          305  +}
   286    306   
   287    307   /*
   288    308   ** This routine generates the code for the inside of the inner loop
   289    309   ** of a SELECT.
   290    310   **
   291    311   ** The pEList is used to determine the values for each column in the
   292    312   ** result row.  Except  if pEList==NULL, then we just read nColumn
................................................................................
   346    366       sqliteVdbeAddOp(v, OP_Distinct, distinct, sqliteVdbeCurrentAddr(v)+3);
   347    367       sqliteVdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0);
   348    368       sqliteVdbeAddOp(v, OP_Goto, 0, iContinue);
   349    369       sqliteVdbeAddOp(v, OP_String, 0, 0);
   350    370       sqliteVdbeAddOp(v, OP_PutStrKey, distinct, 0);
   351    371     }
   352    372   
   353         -  /* If there is an ORDER BY clause, then store the results
   354         -  ** in a sorter.
   355         -  */
   356         -  if( pOrderBy ){
   357         -    char *zSortOrder;
   358         -    sqliteVdbeAddOp(v, OP_SortMakeRec, nColumn, 0);
   359         -    zSortOrder = sqliteMalloc( pOrderBy->nExpr + 1 );
   360         -    if( zSortOrder==0 ) return 1;
   361         -    for(i=0; i<pOrderBy->nExpr; i++){
   362         -      zSortOrder[i] = pOrderBy->a[i].sortOrder ? '-' : '+';
   363         -      sqliteExprCode(pParse, pOrderBy->a[i].pExpr);
   364         -    }
   365         -    zSortOrder[pOrderBy->nExpr] = 0;
   366         -    sqliteVdbeAddOp(v, OP_SortMakeKey, pOrderBy->nExpr, 0);
   367         -    sqliteVdbeChangeP3(v, -1, zSortOrder, strlen(zSortOrder));
   368         -    sqliteFree(zSortOrder);
   369         -    sqliteVdbeAddOp(v, OP_SortPut, 0, 0);
   370         -  }else 
   371         -
   372         -  /* In this mode, write each query result to the key of the temporary
   373         -  ** table iParm.
   374         -  */
   375         -  if( eDest==SRT_Union ){
   376         -    sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, NULL_ALWAYS_DISTINCT);
   377         -    sqliteVdbeAddOp(v, OP_String, 0, 0);
   378         -    sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
   379         -  }else 
   380         -
   381         -  /* Store the result as data using a unique key.
   382         -  */
   383         -  if( eDest==SRT_Table || eDest==SRT_TempTable ){
   384         -    sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
   385         -    sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
   386         -    sqliteVdbeAddOp(v, OP_Pull, 1, 0);
   387         -    sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
   388         -  }else 
   389         -
   390         -  /* Construct a record from the query result, but instead of
   391         -  ** saving that record, use it as a key to delete elements from
   392         -  ** the temporary table iParm.
   393         -  */
   394         -  if( eDest==SRT_Except ){
   395         -    int addr;
   396         -    addr = sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, NULL_ALWAYS_DISTINCT);
   397         -    sqliteVdbeAddOp(v, OP_NotFound, iParm, addr+3);
   398         -    sqliteVdbeAddOp(v, OP_Delete, iParm, 0);
   399         -  }else 
   400         -
   401         -  /* If we are creating a set for an "expr IN (SELECT ...)" construct,
   402         -  ** then there should be a single item on the stack.  Write this
   403         -  ** item into the set table with bogus data.
   404         -  */
   405         -  if( eDest==SRT_Set ){
   406         -    assert( nColumn==1 );
   407         -    sqliteVdbeAddOp(v, OP_IsNull, -1, sqliteVdbeCurrentAddr(v)+3);
   408         -    sqliteVdbeAddOp(v, OP_String, 0, 0);
   409         -    sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
   410         -  }else 
   411         -
   412         -
   413         -  /* If this is a scalar select that is part of an expression, then
   414         -  ** store the results in the appropriate memory cell and break out
   415         -  ** of the scan loop.
   416         -  */
   417         -  if( eDest==SRT_Mem ){
   418         -    assert( nColumn==1 );
   419         -    sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);
   420         -    sqliteVdbeAddOp(v, OP_Goto, 0, iBreak);
   421         -  }else
   422         -
   423         -  /* Discard the results.  This is used for SELECT statements inside
   424         -  ** the body of a TRIGGER.  The purpose of such selects is to call
   425         -  ** user-defined functions that have side effects.  We do not care
   426         -  ** about the actual results of the select.
   427         -  */
   428         -  if( eDest==SRT_Discard ){
   429         -    sqliteVdbeAddOp(v, OP_Pop, nColumn, 0);
   430         -  }else
   431         -
   432         -  /* If none of the above, send the data to the callback function.
   433         -  */
   434         -  {
   435         -    assert( eDest==SRT_Callback );
   436         -    sqliteVdbeAddOp(v, OP_Callback, nColumn, 0);
          373  +  switch( eDest ){
          374  +    /* In this mode, write each query result to the key of the temporary
          375  +    ** table iParm.
          376  +    */
          377  +    case SRT_Union: {
          378  +      sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, NULL_ALWAYS_DISTINCT);
          379  +      sqliteVdbeAddOp(v, OP_String, 0, 0);
          380  +      sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
          381  +      break;
          382  +    }
          383  +
          384  +    /* Store the result as data using a unique key.
          385  +    */
          386  +    case SRT_Table:
          387  +    case SRT_TempTable: {
          388  +      sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
          389  +      if( pOrderBy ){
          390  +        pushOntoSorter(pParse, v, pOrderBy);
          391  +      }else{
          392  +        sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
          393  +        sqliteVdbeAddOp(v, OP_Pull, 1, 0);
          394  +        sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
          395  +      }
          396  +      break;
          397  +    }
          398  +
          399  +    /* Construct a record from the query result, but instead of
          400  +    ** saving that record, use it as a key to delete elements from
          401  +    ** the temporary table iParm.
          402  +    */
          403  +    case SRT_Except: {
          404  +      int addr;
          405  +      addr = sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, NULL_ALWAYS_DISTINCT);
          406  +      sqliteVdbeAddOp(v, OP_NotFound, iParm, addr+3);
          407  +      sqliteVdbeAddOp(v, OP_Delete, iParm, 0);
          408  +      break;
          409  +    }
          410  +
          411  +    /* If we are creating a set for an "expr IN (SELECT ...)" construct,
          412  +    ** then there should be a single item on the stack.  Write this
          413  +    ** item into the set table with bogus data.
          414  +    */
          415  +    case SRT_Set: {
          416  +      assert( nColumn==1 );
          417  +      sqliteVdbeAddOp(v, OP_IsNull, -1, sqliteVdbeCurrentAddr(v)+3);
          418  +      sqliteVdbeAddOp(v, OP_String, 0, 0);
          419  +      if( pOrderBy ){
          420  +        pushOntoSorter(pParse, v, pOrderBy);
          421  +      }else{
          422  +        sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
          423  +      }
          424  +      break;
          425  +    }
          426  +
          427  +    /* If this is a scalar select that is part of an expression, then
          428  +    ** store the results in the appropriate memory cell and break out
          429  +    ** of the scan loop.
          430  +    */
          431  +    case SRT_Mem: {
          432  +      assert( nColumn==1 );
          433  +      if( pOrderBy ){
          434  +        pushOntoSorter(pParse, v, pOrderBy);
          435  +      }else{
          436  +        sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);
          437  +        sqliteVdbeAddOp(v, OP_Goto, 0, iBreak);
          438  +      }
          439  +      break;
          440  +    }
          441  +
          442  +    /* Discard the results.  This is used for SELECT statements inside
          443  +    ** the body of a TRIGGER.  The purpose of such selects is to call
          444  +    ** user-defined functions that have side effects.  We do not care
          445  +    ** about the actual results of the select.
          446  +    */
          447  +    case SRT_Discard: {
          448  +      sqliteVdbeAddOp(v, OP_Pop, nColumn, 0);
          449  +      break;
          450  +    }
          451  +
          452  +    /* Send the data to the callback function.
          453  +    */
          454  +    default: {
          455  +      assert( eDest==SRT_Callback );
          456  +      if( pOrderBy ){
          457  +        sqliteVdbeAddOp(v, OP_SortMakeRec, nColumn, 0);
          458  +        pushOntoSorter(pParse, v, pOrderBy);
          459  +      }else{
          460  +        sqliteVdbeAddOp(v, OP_Callback, nColumn, 0);
          461  +      }
          462  +      break;
          463  +    }
   437    464     }
   438    465     return 0;
   439    466   }
   440    467   
   441    468   /*
   442    469   ** If the inner loop was generated using a non-null pOrderBy argument,
   443    470   ** then the results were placed in a sorter.  After the loop is terminated
   444    471   ** we need to run the sorter and output the results.  The following
   445    472   ** routine generates the code needed to do that.
   446    473   */
   447         -static void generateSortTail(Select *p, Vdbe *v, int nColumn){
          474  +static void generateSortTail(
          475  +  Select *p,       /* The SELECT statement */
          476  +  Vdbe *v,         /* Generate code into this VDBE */
          477  +  int nColumn,     /* Number of columns of data */
          478  +  int eDest,       /* Write the sorted results here */
          479  +  int iParm        /* Optional parameter associated with eDest */
          480  +){
   448    481     int end = sqliteVdbeMakeLabel(v);
   449    482     int addr;
   450    483     sqliteVdbeAddOp(v, OP_Sort, 0, 0);
   451    484     addr = sqliteVdbeAddOp(v, OP_SortNext, 0, end);
   452    485     if( p->nOffset>0 ){
   453    486       sqliteVdbeAddOp(v, OP_LimitCk, 1, addr);
   454    487     }
   455    488     if( p->nLimit>0 ){
   456    489       sqliteVdbeAddOp(v, OP_LimitCk, 0, end);
   457    490     }
   458         -  sqliteVdbeAddOp(v, OP_SortCallback, nColumn, 0);
          491  +  switch( eDest ){
          492  +    case SRT_Callback: {
          493  +      sqliteVdbeAddOp(v, OP_SortCallback, nColumn, 0);
          494  +      break;
          495  +    }
          496  +    case SRT_Table:
          497  +    case SRT_TempTable: {
          498  +      sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
          499  +      sqliteVdbeAddOp(v, OP_Pull, 1, 0);
          500  +      sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
          501  +      break;
          502  +    }
          503  +    case SRT_Set: {
          504  +      assert( nColumn==1 );
          505  +      sqliteVdbeAddOp(v, OP_IsNull, -1, sqliteVdbeCurrentAddr(v)+3);
          506  +      sqliteVdbeAddOp(v, OP_String, 0, 0);
          507  +      sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
          508  +      break;
          509  +    }
          510  +    case SRT_Mem: {
          511  +      assert( nColumn==1 );
          512  +      sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);
          513  +      sqliteVdbeAddOp(v, OP_Goto, 0, end);
          514  +      break;
          515  +    }
          516  +    default: {
          517  +      assert( end==0 ); /* Cannot happen */
          518  +      break;
          519  +    }
          520  +  }
   459    521     sqliteVdbeAddOp(v, OP_Goto, 0, addr);
   460    522     sqliteVdbeResolveLabel(v, end);
   461    523     sqliteVdbeAddOp(v, OP_SortReset, 0, 0);
   462    524   }
   463    525   
   464    526   /*
   465    527   ** Generate code that will tell the VDBE how many columns there
................................................................................
   894    956     return v;
   895    957   }
   896    958       
   897    959   
   898    960   /*
   899    961   ** This routine is called to process a query that is really the union
   900    962   ** or intersection of two or more separate queries.
          963  +**
          964  +** "p" points to the right-most of the two queries.  The results should
          965  +** be stored in eDest with parameter iParm.
   901    966   */
   902    967   static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){
   903    968     int rc;             /* Success code from a subroutine */
   904    969     Select *pPrior;     /* Another SELECT immediately to our left */
   905    970     Vdbe *v;            /* Generate code to this VDBE */
   906    971     int base;           /* Baseline value for pParse->nTab */
   907    972   
................................................................................
   935   1000     switch( p->op ){
   936   1001       case TK_ALL:
   937   1002       case TK_EXCEPT:
   938   1003       case TK_UNION: {
   939   1004         int unionTab;    /* Cursor number of the temporary table holding result */
   940   1005         int op;          /* One of the SRT_ operations to apply to self */
   941   1006         int priorOp;     /* The SRT_ operation to apply to prior selects */
         1007  +      ExprList *pOrderBy;  /* The ORDER BY clause for the right SELECT */
   942   1008   
   943   1009         priorOp = p->op==TK_ALL ? SRT_Table : SRT_Union;
   944         -      if( eDest==priorOp ){
         1010  +      if( eDest==priorOp && p->pOrderBy==0 ){
   945   1011           /* We can reuse a temporary table generated by a SELECT to our
   946         -        ** right.  This also means we are not the right-most select and so
   947         -        ** we cannot have an ORDER BY clause
         1012  +        ** right.
   948   1013           */
   949   1014           unionTab = iParm;
   950         -        assert( p->pOrderBy==0 );
   951   1015         }else{
   952   1016           /* We will need to create our own temporary table to hold the
   953   1017           ** intermediate results.
   954   1018           */
   955   1019           unionTab = pParse->nTab++;
   956   1020           if( p->pOrderBy 
   957   1021           && matchOrderbyToColumn(pParse, p, p->pOrderBy, unionTab, 1) ){
................................................................................
   974   1038         */
   975   1039         switch( p->op ){
   976   1040            case TK_EXCEPT:  op = SRT_Except;   break;
   977   1041            case TK_UNION:   op = SRT_Union;    break;
   978   1042            case TK_ALL:     op = SRT_Table;    break;
   979   1043         }
   980   1044         p->pPrior = 0;
         1045  +      pOrderBy = p->pOrderBy;
         1046  +      p->pOrderBy = 0;
   981   1047         rc = sqliteSelect(pParse, p, op, unionTab, 0, 0, 0);
   982   1048         p->pPrior = pPrior;
         1049  +      p->pOrderBy = pOrderBy;
   983   1050         if( rc ) return rc;
   984   1051   
   985   1052         /* Convert the data in the temporary table into whatever form
   986   1053         ** it is that we currently need.
   987   1054         */      
   988         -      if( eDest!=priorOp ){
         1055  +      if( eDest!=priorOp || unionTab!=iParm ){
   989   1056           int iCont, iBreak, iStart;
   990   1057           assert( p->pEList );
   991   1058           if( eDest==SRT_Callback ){
   992   1059             generateColumnNames(pParse, p->base, 0, p->pEList);
   993   1060           }
   994   1061           iBreak = sqliteVdbeMakeLabel(v);
   995   1062           iCont = sqliteVdbeMakeLabel(v);
................................................................................
  1000   1067                                iCont, iBreak);
  1001   1068           if( rc ) return 1;
  1002   1069           sqliteVdbeResolveLabel(v, iCont);
  1003   1070           sqliteVdbeAddOp(v, OP_Next, unionTab, iStart);
  1004   1071           sqliteVdbeResolveLabel(v, iBreak);
  1005   1072           sqliteVdbeAddOp(v, OP_Close, unionTab, 0);
  1006   1073           if( p->pOrderBy ){
  1007         -          generateSortTail(p, v, p->pEList->nExpr);
         1074  +          generateSortTail(p, v, p->pEList->nExpr, eDest, iParm);
  1008   1075           }
  1009   1076         }
  1010   1077         break;
  1011   1078       }
  1012   1079       case TK_INTERSECT: {
  1013   1080         int tab1, tab2;
  1014   1081         int iCont, iBreak, iStart;
................................................................................
  1057   1124         if( rc ) return 1;
  1058   1125         sqliteVdbeResolveLabel(v, iCont);
  1059   1126         sqliteVdbeAddOp(v, OP_Next, tab1, iStart);
  1060   1127         sqliteVdbeResolveLabel(v, iBreak);
  1061   1128         sqliteVdbeAddOp(v, OP_Close, tab2, 0);
  1062   1129         sqliteVdbeAddOp(v, OP_Close, tab1, 0);
  1063   1130         if( p->pOrderBy ){
  1064         -        generateSortTail(p, v, p->pEList->nExpr);
         1131  +        generateSortTail(p, v, p->pEList->nExpr, eDest, iParm);
  1065   1132         }
  1066   1133         break;
  1067   1134       }
  1068   1135     }
  1069   1136     assert( p->pEList && pPrior->pEList );
  1070   1137     if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
  1071   1138       sqliteSetString(&pParse->zErrMsg, "SELECTs to the left and right of ",
................................................................................
  1534   1601     if( (eDest==SRT_Mem || eDest==SRT_Set) && pEList->nExpr>1 ){
  1535   1602       sqliteSetString(&pParse->zErrMsg, "only a single result allowed for "
  1536   1603          "a SELECT that is part of an expression", 0);
  1537   1604       pParse->nErr++;
  1538   1605       goto select_end;
  1539   1606     }
  1540   1607   
  1541         -  /* ORDER BY is ignored if we are not sending the result to a callback.
         1608  +  /* ORDER BY is ignored for some destinations.
  1542   1609     */
  1543         -  if( eDest!=SRT_Callback ){
  1544         -    pOrderBy = 0;
         1610  +  switch( eDest ){
         1611  +    case SRT_Union:
         1612  +    case SRT_Except:
         1613  +    case SRT_Discard:
         1614  +      pOrderBy = 0;
         1615  +      break;
         1616  +    default:
         1617  +      break;
  1545   1618     }
  1546   1619   
  1547   1620     /* At this point, we should have allocated all the cursors that we
  1548   1621     ** need to handle subquerys and temporary tables.  
  1549   1622     **
  1550   1623     ** Resolve the column names and do a semantics check on all the expressions.
  1551   1624     */
................................................................................
  1826   1899       pParse->useAgg = 0;
  1827   1900     }
  1828   1901   
  1829   1902     /* If there is an ORDER BY clause, then we need to sort the results
  1830   1903     ** and send them to the callback one by one.
  1831   1904     */
  1832   1905     if( pOrderBy ){
  1833         -    generateSortTail(p, v, pEList->nExpr);
         1906  +    generateSortTail(p, v, pEList->nExpr, eDest, iParm);
  1834   1907     }
  1835   1908   
  1836   1909   
  1837   1910     /* Issue a null callback if that is what the user wants.
  1838   1911     */
  1839   1912     if( (pParse->db->flags & SQLITE_NullCallback)!=0 && eDest==SRT_Callback ){
  1840   1913       sqliteVdbeAddOp(v, OP_NullCallback, pEList->nExpr, 0);

Changes to test/select4.test.

     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 UNION, INTERSECT and EXCEPT operators
    13     13   # in SELECT statements.
    14     14   #
    15         -# $Id: select4.test,v 1.10 2002/06/02 16:09:03 drh Exp $
           15  +# $Id: select4.test,v 1.11 2002/06/20 03:38:26 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Build some test data
    21     21   #
    22     22   set fd [open data1.txt w]
................................................................................
    47     47     execsql {
    48     48       SELECT DISTINCT log FROM t1
    49     49       UNION ALL
    50     50       SELECT n FROM t1 WHERE log=3
    51     51       ORDER BY log;
    52     52     }
    53     53   } {0 1 2 3 4 5 5 6 7 8}
           54  +do_test select4-1.1d {
           55  +  execsql {
           56  +    CREATE TABLE t2 AS
           57  +      SELECT DISTINCT log FROM t1
           58  +      UNION ALL
           59  +      SELECT n FROM t1 WHERE log=3
           60  +      ORDER BY log;
           61  +    SELECT * FROM t2;
           62  +  }
           63  +} {0 1 2 3 4 5 5 6 7 8}
           64  +execsql {DROP TABLE t2}
           65  +do_test select4-1.1e {
           66  +  execsql {
           67  +    CREATE TABLE t2 AS
           68  +      SELECT DISTINCT log FROM t1
           69  +      UNION ALL
           70  +      SELECT n FROM t1 WHERE log=3
           71  +      ORDER BY log DESC;
           72  +    SELECT * FROM t2;
           73  +  }
           74  +} {8 7 6 5 5 4 3 2 1 0}
           75  +execsql {DROP TABLE t2}
    54     76   do_test select4-1.2 {
    55     77     execsql {
    56     78       SELECT log FROM t1 WHERE n IN 
    57     79         (SELECT DISTINCT log FROM t1 UNION ALL
    58     80          SELECT n FROM t1 WHERE log=3)
    59     81       ORDER BY log;
    60     82     }
................................................................................
    95    117       ORDER BY log;
    96    118     }} msg]
    97    119     lappend v $msg
    98    120   } {1 {ORDER BY clause should come after UNION not before}}
    99    121   
   100    122   # Except operator
   101    123   #
   102         -do_test select4-3.1 {
          124  +do_test select4-3.1.1 {
   103    125     execsql {
   104    126       SELECT DISTINCT log FROM t1
   105    127       EXCEPT
   106    128       SELECT n FROM t1 WHERE log=3
   107    129       ORDER BY log;
   108    130     }
   109    131   } {0 1 2 3 4}
          132  +do_test select4-3.1.2 {
          133  +  execsql {
          134  +    CREATE TABLE t2 AS 
          135  +      SELECT DISTINCT log FROM t1
          136  +      EXCEPT
          137  +      SELECT n FROM t1 WHERE log=3
          138  +      ORDER BY log;
          139  +    SELECT * FROM t2;
          140  +  }
          141  +} {0 1 2 3 4}
          142  +execsql {DROP TABLE t2}
          143  +do_test select4-3.1.3 {
          144  +  execsql {
          145  +    CREATE TABLE t2 AS 
          146  +      SELECT DISTINCT log FROM t1
          147  +      EXCEPT
          148  +      SELECT n FROM t1 WHERE log=3
          149  +      ORDER BY log DESC;
          150  +    SELECT * FROM t2;
          151  +  }
          152  +} {4 3 2 1 0}
          153  +execsql {DROP TABLE t2}
   110    154   do_test select4-3.2 {
   111    155     execsql {
   112    156       SELECT log FROM t1 WHERE n IN 
   113    157         (SELECT DISTINCT log FROM t1 EXCEPT
   114    158          SELECT n FROM t1 WHERE log=3)
   115    159       ORDER BY log;
   116    160     }
................................................................................
   123    167       ORDER BY log;
   124    168     }} msg]
   125    169     lappend v $msg
   126    170   } {1 {ORDER BY clause should come after EXCEPT not before}}
   127    171   
   128    172   # Intersect operator
   129    173   #
   130         -do_test select4-4.1 {
          174  +do_test select4-4.1.1 {
   131    175     execsql {
   132    176       SELECT DISTINCT log FROM t1
   133    177       INTERSECT
   134    178       SELECT n FROM t1 WHERE log=3
   135    179       ORDER BY log;
   136    180     }
   137    181   } {5}
          182  +do_test select4-4.1.2 {
          183  +  execsql {
          184  +    SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
          185  +    INTERSECT
          186  +    SELECT n FROM t1 WHERE log=3
          187  +    ORDER BY log;
          188  +  }
          189  +} {5 6}
          190  +do_test select4-4.1.3 {
          191  +  execsql {
          192  +    CREATE TABLE t2 AS
          193  +      SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
          194  +      INTERSECT
          195  +      SELECT n FROM t1 WHERE log=3
          196  +      ORDER BY log;
          197  +    SELECT * FROM t2;
          198  +  }
          199  +} {5 6}
          200  +execsql {DROP TABLE t2}
          201  +do_test select4-4.1.4 {
          202  +  execsql {
          203  +    CREATE TABLE t2 AS
          204  +      SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
          205  +      INTERSECT
          206  +      SELECT n FROM t1 WHERE log=3
          207  +      ORDER BY log DESC;
          208  +    SELECT * FROM t2;
          209  +  }
          210  +} {6 5}
          211  +execsql {DROP TABLE t2}
   138    212   do_test select4-4.2 {
   139    213     execsql {
   140    214       SELECT log FROM t1 WHERE n IN 
   141    215         (SELECT DISTINCT log FROM t1 INTERSECT
   142    216          SELECT n FROM t1 WHERE log=3)
   143    217       ORDER BY log;
   144    218     }