:-) (CVS 62)

FossilOrigin-Name: f4d9089c5d69b16fee5feb49b02e524499e6328d

6 files changed, 390 insertions, 179 deletions

diff --git a/src/parse.y b/src/parse.y
index 082ac270c..e42adedd8 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -26,7 +26,7 @@
 ** the parser.  Lemon will also generate a header file containing
 ** numeric codes for all of the tokens.
 **
-** @(#) $Id: parse.y,v 1.13 2000/06/06 17:27:05 drh Exp $
+** @(#) $Id: parse.y,v 1.14 2000/06/06 21:56:08 drh Exp $
 */
 %token_prefix TK_
 %token_type {Token}
@@ -139,9 +139,22 @@ cmd ::= select(X).  {
 
 %type select {Select*}
 %destructor select {sqliteSelectDelete($$);}
-
-select(A) ::= SELECT distinct(D) selcollist(W) from(X) where_opt(Y)
-              groupby_opt(P) having_opt(Q) orderby_opt(Z). {
+%type oneselect {Select*}
+%destructor oneselect {sqliteSelectDelete($$);}
+
+select(A) ::= oneselect(X).                      {A = X;}
+select(A) ::= select(X) joinop(Y) oneselect(Z).  {
+    Z->op = Y;
+    Z->pPrior = X;
+    A = Z;
+}
+%type joinop {int}
+joinop(A) ::= UNION.      {A = TK_UNION;}
+joinop(A) ::= UNION ALL.  {A = TK_ALL;}
+joinop(A) ::= INTERSECT.  {A = TK_INTERSECT;}
+joinop(A) ::= EXCEPT.     {A = TK_EXCEPT;}
+oneselect(A) ::= SELECT distinct(D) selcollist(W) from(X) where_opt(Y)
+                 groupby_opt(P) having_opt(Q) orderby_opt(Z). {
   A = sqliteSelectNew(W,X,Y,P,Q,Z,D);
 }
 
@@ -222,6 +235,7 @@ groupby_opt(A) ::= GROUP BY exprlist(X).  {A = X;}
 having_opt(A) ::= .      {A = 0;}
 having_opt(A) ::= HAVING expr(X).  {A = X;}
 
+
 cmd ::= DELETE FROM ID(X) where_opt(Y).
     {sqliteDeleteFrom(pParse, &X, Y);}
 
diff --git a/src/select.c b/src/select.c
index 8d04d9dc0..bcfcd69be 100644
--- a/src/select.c
+++ b/src/select.c
@@ -24,7 +24,7 @@
 ** This file contains C code routines that are called by the parser
 ** to handle SELECT statements.
 **
-** $Id: select.c,v 1.12 2000/06/06 18:00:16 drh Exp $
+** $Id: select.c,v 1.13 2000/06/06 21:56:08 drh Exp $
 */
 #include "sqliteInt.h"
 
@@ -51,6 +51,7 @@ Select *sqliteSelectNew(
   pNew->pHaving = pHaving;
   pNew->pOrderBy = pOrderBy;
   pNew->isDistinct = isDistinct;
+  pNew->op = TK_SELECT;
   return pNew;
 }
 
@@ -58,12 +59,14 @@ Select *sqliteSelectNew(
 ** Delete the given Select structure and all of its substructures.
 */
 void sqliteSelectDelete(Select *p){
+  if( p==0 ) return;
   sqliteExprListDelete(p->pEList);
   sqliteIdListDelete(p->pSrc);
   sqliteExprDelete(p->pWhere);
   sqliteExprListDelete(p->pGroupBy);
   sqliteExprDelete(p->pHaving);
   sqliteExprListDelete(p->pOrderBy);
+  sqliteSelectDelete(p->pPrior);
   sqliteFree(p);
 }
 
@@ -81,10 +84,16 @@ void sqliteParseInfoReset(Parse *pParse){
 /*
 ** This routine generates the code for the inside of the inner loop
 ** of a SELECT.
+**
+** The pEList is used to determine the values for each column in the
+** result row.  Except  if pEList==NULL, then we just read nField
+** elements from the srcTab table.
 */
 static int selectInnerLoop(
   Parse *pParse,          /* The parser context */
   ExprList *pEList,       /* List of values being extracted */
+  int srcTab,             /* Pull data from this table */
+  int nField,             /* Number of fields in the source table */
   ExprList *pOrderBy,     /* If not NULL, sort results using this key */
   int distinct,           /* If >=0, make sure results are distinct */
   int eDest,              /* How to dispose of the results */
@@ -97,8 +106,15 @@ static int selectInnerLoop(
 
   /* Pull the requested fields.
   */
-  for(i=0; i<pEList->nExpr; i++){
-    sqliteExprCode(pParse, pEList->a[i].pExpr);
+  if( pEList ){
+    for(i=0; i<pEList->nExpr; i++){
+      sqliteExprCode(pParse, pEList->a[i].pExpr);
+    }
+    nField = pEList->nExpr;
+  }else{
+    for(i=0; i<nField; i++){
+      sqliteVdbeAddOp(v, OP_Field, srcTab, i, 0, 0);
+    }
   }
 
   /* If the current result is not distinct, skip the rest
@@ -113,6 +129,7 @@ static int selectInnerLoop(
     sqliteVdbeAddOp(v, OP_String, 0, 0, "", lbl);
     sqliteVdbeAddOp(v, OP_Put, distinct, 0, 0, 0);
   }
+
   /* If there is an ORDER BY clause, then store the results
   ** in a sorter.
   */
@@ -130,12 +147,22 @@ static int selectInnerLoop(
     sqliteVdbeAddOp(v, OP_SortPut, 0, 0, 0, 0);
   }else 
 
-  /* If we are writing to a table, then write the results to the table.
+  /* In this mode, write each query result to the key of the temporary
+  ** table iParm.
   */
-  if( eDest==SRT_Table ){
-    sqliteVdbeAddOp(v, OP_MakeRecord, pEList->nExpr, 0, 0, 0);
-    sqliteVdbeAddOp(v, OP_New, iParm, 0, 0, 0);
-    sqliteVdbeAddOp(v, OP_Pull, 1, 0, 0, 0);
+  if( eDest==SRT_Union ){
+    sqliteVdbeAddOp(v, OP_MakeRecord, nField, 0, 0, 0);
+    sqliteVdbeAddOp(v, OP_String, iParm, 0, "", 0);
+    sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
+  }else 
+
+  /* Construct a record from the query result, but instead of
+  ** saving that record, use it as a key to delete elements from
+  ** the temporary table iParm.
+  */
+  if( eDest==SRT_Except ){
+    assert( pEList->nExpr==1 );
+    sqliteVdbeAddOp(v, OP_String, 0, 0, "", 0);
     sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
   }else 
 
@@ -149,6 +176,7 @@ static int selectInnerLoop(
     sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
   }else 
 
+
   /* If this is a scalar select that is part of an expression, then
   ** store the results in the appropriate memory cell and break out
   ** of the scan loop.
@@ -161,7 +189,160 @@ static int selectInnerLoop(
   /* If none of the above, send the data to the callback function.
   */
   {
-    sqliteVdbeAddOp(v, OP_Callback, pEList->nExpr, 0, 0, 0);
+    sqliteVdbeAddOp(v, OP_Callback, nField, 0, 0, 0);
+  }
+  return 0;
+}
+
+/*
+** Generate code that will tell the VDBE how many columns there
+** are in the result and the name for each column.  This information
+** is used to provide "argc" and "azCol[]" values in the callback.
+*/
+static void generateColumnNames(Vdbe *v, IdList *pTabList, ExprList *pEList){
+  int i;
+  sqliteVdbeAddOp(v, OP_ColumnCount, pEList->nExpr, 0, 0, 0);
+  for(i=0; i<pEList->nExpr; i++){
+    Expr *p;
+    if( pEList->a[i].zName ){
+      char *zName = pEList->a[i].zName;
+      int addr = sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
+      if( zName[0]=='\'' || zName[0]=='"' ){
+        sqliteVdbeDequoteP3(v, addr);
+      }
+      continue;
+    }
+    p = pEList->a[i].pExpr;
+    if( p->op!=TK_FIELD || pTabList==0 ){
+      char zName[30];
+      sprintf(zName, "field%d", i+1);
+      sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
+    }else{
+      if( pTabList->nId>1 ){
+        char *zName = 0;
+        Table *pTab = pTabList->a[p->iTable].pTab;
+        char *zTab;
+ 
+        zTab = pTabList->a[p->iTable].zAlias;
+        if( zTab==0 ) zTab = pTab->zName;
+        sqliteSetString(&zName, zTab, ".", pTab->aCol[p->iField].zName, 0);
+        sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
+        sqliteFree(zName);
+      }else{
+        Table *pTab = pTabList->a[0].pTab;
+        char *zName = pTab->aCol[p->iField].zName;
+        sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
+      }
+    }
+  }
+}
+
+/*
+** This routine is called to process a query that is really the union
+** or intersection of two or more separate queries.
+*/
+static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){
+  int rc;
+  Select *pPrior;
+  Vdbe *v;
+  int i;
+
+  /* Make sure we have a valid query engine.  If not, create a new one.
+  */
+  v = pParse->pVdbe;
+  if( v==0 ){
+    v = pParse->pVdbe = sqliteVdbeCreate(pParse->db->pBe);
+  }
+  if( v==0 ){
+    sqliteSetString(&pParse->zErrMsg, "out of memory", 0);
+    pParse->nErr++;
+    return 1;
+  }
+
+  assert( p->pPrior!=0 );
+  pPrior = p->pPrior;
+  switch( p->op ){
+    case TK_ALL: {
+      rc = sqliteSelect(pParse, pPrior, eDest, iParm);
+      if( rc ) return rc;
+      p->pPrior = 0;
+      rc = sqliteSelect(pParse, p, eDest, iParm);
+      p->pPrior = pPrior;
+      break;
+    }
+    case TK_EXCEPT:
+    case TK_UNION: {
+      int unionTab;
+      int op;
+
+      if( eDest==SRT_Union ){
+        unionTab = iParm;
+      }else{
+        unionTab = pParse->nTab++;          
+        sqliteVdbeAddOp(v, OP_Open, unionTab, 1, 0, 0);
+        sqliteVdbeAddOp(v, OP_KeyAsData, unionTab, 1, 0, 0);
+      }
+      rc = sqliteSelect(pParse, pPrior, SRT_Union, unionTab);
+      if( rc ) return rc;
+      op = p->op==TK_EXCEPT ? SRT_Except : SRT_Union;
+      p->pPrior = 0;
+      rc = sqliteSelect(pParse, p, op, unionTab);
+      p->pPrior = pPrior;
+      if( rc ) return rc;
+      if( eDest!=SRT_Union ){
+        int iCont, iBreak;
+        assert( p->pEList );
+        generateColumnNames(v, 0, p->pEList);
+        iBreak = sqliteVdbeMakeLabel(v);
+        iCont = sqliteVdbeAddOp(v, OP_Next, unionTab, iBreak, 0, 0);
+        rc = selectInnerLoop(pParse, 0, unionTab, p->pEList->nExpr,
+                             0, -1, eDest, iParm, 
+                             iCont, iBreak);
+        if( rc ) return 1;
+        sqliteVdbeAddOp(v, OP_Goto, 0, iCont, 0, 0);
+        sqliteVdbeAddOp(v, OP_Close, unionTab, 0, 0, iBreak);
+      }
+      break;
+    }
+    case TK_INTERSECT: {
+      int tab1, tab2;
+      Select *pPrior;
+      int iCont, iBreak;
+
+      tab1 = pParse->nTab++;
+      tab2 = pParse->nTab++;
+      sqliteVdbeAddOp(v, OP_Open, tab1, 1, 0, 0);
+      sqliteVdbeAddOp(v, OP_KeyAsData, tab1, 1, 0, 0);
+      rc = sqliteSelect(pParse, pPrior, SRT_Union, tab1);
+      if( rc ) return rc;
+      sqliteVdbeAddOp(v, OP_Open, tab2, 1, 0, 0);
+      sqliteVdbeAddOp(v, OP_KeyAsData, tab2, 1, 0, 0);
+      p->pPrior = 0;
+      rc = sqliteSelect(pParse, p, SRT_Union, tab2);
+      p->pPrior = pPrior;
+      if( rc ) return rc;
+      assert( p->pEList );
+      generateColumnNames(v, 0, p->pEList);
+      iBreak = sqliteVdbeMakeLabel(v);
+      iCont = sqliteVdbeAddOp(v, OP_Next, tab1, iBreak, 0, 0);
+      sqliteVdbeAddOp(v, OP_Key, tab1, 0, 0, 0);
+      sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont, 0, 0);
+      rc = selectInnerLoop(pParse, 0, tab1, p->pEList->nExpr,
+                             0, -1, eDest, iParm, 
+                             iCont, iBreak);
+      if( rc ) return 1;
+      sqliteVdbeAddOp(v, OP_Goto, 0, iCont, 0, 0);
+      sqliteVdbeAddOp(v, OP_Close, tab2, 0, 0, iBreak);
+      sqliteVdbeAddOp(v, OP_Close, tab1, 0, 0, 0);
+      break;
+    }
+  }
+  assert( p->pEList && pPrior->pEList );
+  if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
+    sqliteSetString(&pParse->zErrMsg, "SELECTs have different numbers "
+       "of columns and therefore cannot be joined", 0);
+    pParse->nErr++;
+    return 1;
   }
   return 0;
 }
@@ -180,7 +361,9 @@ static int selectInnerLoop(
 **
 **     SRT_Set         Store results as keys of a table with cursor iParm
 **
-**     SRT_Table       Store results in a regular table with cursor iParm
+**     SRT_Union       Store results as a key in a temporary table iParm
+**
+**     SRT_Except      Remove results form the temporary talbe iParm.
 **
 ** This routine returns the number of errors.  If any errors are
 ** encountered, then an appropriate error message is left in
@@ -192,7 +375,7 @@ static int selectInnerLoop(
 int sqliteSelect(
   Parse *pParse,         /* The parser context */
   Select *p,             /* The SELECT statement being coded. */
-  int eDest,             /* One of SRT_Callback, SRT_Mem, SRT_Set, SRT_Table */
+  int eDest,             /* One of: SRT_Callback Mem Set Union Except */
   int iParm              /* Save result in this memory location, if >=0 */
 ){
   int i, j;
@@ -208,6 +391,14 @@ int sqliteSelect(
   int isDistinct;        /* True if the DISTINCT keyword is present */
   int distinct;          /* Table to use for the distinct set */
 
+  /* If there is are a sequence of queries, do the earlier ones first.
+  */
+  if( p->pPrior ){
+    return multiSelect(pParse, p, eDest, iParm);
+  }
+
+  /* Make local copies of the parameters for this query.
+  */
   pEList = p->pEList;
   pTabList = p->pSrc;
   pWhere = p->pWhere;
@@ -255,7 +446,7 @@ int sqliteSelect(
         Expr *pExpr = sqliteExpr(TK_FIELD, 0, 0, 0);
         pExpr->iTable = i + pParse->nTab;
         pExpr->iField = j;
-        pEList = sqliteExprListAppend(pEList, pExpr, 0);
+        p->pEList = pEList = sqliteExprListAppend(pEList, pExpr, 0);
       }
     }
   }
@@ -388,40 +579,7 @@ int sqliteSelect(
   ** step is skipped if the output is going to a table or a memory cell.
   */
   if( eDest==SRT_Callback ){
-    sqliteVdbeAddOp(v, OP_ColumnCount, pEList->nExpr, 0, 0, 0);
-    for(i=0; i<pEList->nExpr; i++){
-      Expr *p;
-      if( pEList->a[i].zName ){
-        char *zName = pEList->a[i].zName;
-        int addr = sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
-        if( zName[0]=='\'' || zName[0]=='"' ){
-          sqliteVdbeDequoteP3(v, addr);
-        }
-        continue;
-      }
-      p = pEList->a[i].pExpr;
-      if( p->op!=TK_FIELD ){
-        char zName[30];
-        sprintf(zName, "field%d", i+1);
-        sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
-      }else{
-        if( pTabList->nId>1 ){
-          char *zName = 0;
-          Table *pTab = pTabList->a[p->iTable].pTab;
-          char *zTab;
-  
-          zTab = pTabList->a[p->iTable].zAlias;
-          if( zTab==0 ) zTab = pTab->zName;
-          sqliteSetString(&zName, zTab, ".", pTab->aCol[p->iField].zName, 0);
-          sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
-          sqliteFree(zName);
-        }else{
-          Table *pTab = pTabList->a[0].pTab;
-          char *zName = pTab->aCol[p->iField].zName;
-          sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
-        }
-      }
-    }
+    generateColumnNames(v, pTabList, pEList);
   }
 
   /* Reset the aggregator
@@ -449,7 +607,7 @@ int sqliteSelect(
   ** aggregates
   */
   if( !isAgg ){
-    if( selectInnerLoop(pParse, pEList, pOrderBy, distinct, eDest, iParm,
+    if( selectInnerLoop(pParse, pEList, 0, 0, pOrderBy, distinct, eDest, iParm,
                     pWInfo->iContinue, pWInfo->iBreak) ){
        return 1;
     }
@@ -528,7 +686,7 @@ int sqliteSelect(
     if( pHaving ){
       sqliteExprIfFalse(pParse, pHaving, startagg);
     }
-    if( selectInnerLoop(pParse, pEList, pOrderBy, distinct, eDest, iParm,
+    if( selectInnerLoop(pParse, pEList, 0, 0, pOrderBy, distinct, eDest, iParm,
                     startagg, endagg) ){
       return 1;
     }
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 8b1f94f97..ebe04dfb5 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -23,7 +23,7 @@
 *************************************************************************
 ** Internal interface definitions for SQLite.
 **
-** @(#) $Id: sqliteInt.h,v 1.18 2000/06/06 17:27:05 drh Exp $
+** @(#) $Id: sqliteInt.h,v 1.19 2000/06/06 21:56:08 drh Exp $
 */
 #include "sqlite.h"
 #include "dbbe.h"
@@ -228,6 +228,8 @@ struct Select {
   ExprList *pGroupBy;    /* The GROUP BY clause */
   Expr *pHaving;         /* The HAVING clause */
   ExprList *pOrderBy;    /* The ORDER BY clause */
+  int op;                /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
+  Select *pPrior;        /* Prior select to which this one joins */
 };
 
 /*
@@ -235,8 +237,9 @@ struct Select {
 */
 #define SRT_Callback     1  /* Invoke a callback with each row of result */
 #define SRT_Mem          2  /* Store result in a memory cell */
-#define SRT_Set          3  /* Store result in a table for use with "IN" */
-#define SRT_Table        4  /* Store result in a regular table */
+#define SRT_Set          3  /* Store result as unique keys in a table */
+#define SRT_Union        5  /* Store result as keys in a table */
+#define SRT_Except       6  /* Remove result from a UNION table */
 
 /*
 ** When a SELECT uses aggregate functions (like "count(*)" or "avg(f1)")
diff --git a/src/tokenize.c b/src/tokenize.c
index b64e32507..f750debe1 100644
--- a/src/tokenize.c
+++ b/src/tokenize.c
@@ -27,7 +27,7 @@
 ** individual tokens and sends those tokens one-by-one over to the
 ** parser for analysis.
 **
-** $Id: tokenize.c,v 1.7 2000/06/06 17:27:06 drh Exp $
+** $Id: tokenize.c,v 1.8 2000/06/06 21:56:08 drh Exp $
 */
 #include "sqliteInt.h"
 #include <ctype.h>
@@ -65,6 +65,7 @@ static Keyword aKeywordTable[] = {
   { "DESC",              0, TK_DESC,             0 },
   { "DISTINCT",          0, TK_DISTINCT,         0 },
   { "DROP",              0, TK_DROP,             0 },
+  { "EXCEPT",            0, TK_EXCEPT,           0 },
   { "EXPLAIN",           0, TK_EXPLAIN,          0 },
   { "FROM",              0, TK_FROM,             0 },
   { "GLOB",              0, TK_GLOB,             0 },
@@ -73,6 +74,7 @@ static Keyword aKeywordTable[] = {
   { "IN",                0, TK_IN,               0 },
   { "INDEX",             0, TK_INDEX,            0 },
   { "INSERT",            0, TK_INSERT,           0 },
+  { "INTERSECT",         0, TK_INTERSECT,        0 },
   { "INTO",              0, TK_INTO,             0 },
   { "IS",                0, TK_IS,               0 },
   { "ISNULL",            0, TK_ISNULL,           0 },
@@ -88,6 +90,7 @@ static Keyword aKeywordTable[] = {
   { "SELECT",            0, TK_SELECT,           0 },
   { "SET",               0, TK_SET,              0 },
   { "TABLE",             0, TK_TABLE,            0 },
+  { "UNION",             0, TK_UNION,            0 },
   { "UNIQUE",            0, TK_UNIQUE,           0 },
   { "UPDATE",            0, TK_UPDATE,           0 },
   { "USING",             0, TK_USING,            0 },
diff --git a/src/vdbe.c b/src/vdbe.c
index f159fef54..6a9c1c7da 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -41,7 +41,7 @@
 ** But other routines are also provided to help in building up
 ** a program instruction by instruction.
 **
-** $Id: vdbe.c,v 1.22 2000/06/06 19:18:24 drh Exp $
+** $Id: vdbe.c,v 1.23 2000/06/06 21:56:08 drh Exp $
 */
 #include "sqliteInt.h"
 #include <unistd.h>
@@ -60,6 +60,7 @@ typedef struct VdbeOp Op;
 struct VdbeTable {
   DbbeTable *pTable;    /* The table structure of the backend */
   int index;            /* The next index to extract */
+  int keyAsData;        /* The OP_Field command works on key instead of data */
 };
 typedef struct VdbeTable VdbeTable;
 
@@ -735,26 +736,26 @@ void sqliteVdbeDelete(Vdbe *p){
 static char *zOpName[] = { 0,
   "Open",           "Close",          "Fetch",          "New",
   "Put",            "Distinct",       "Found",          "NotFound",
-  "Delete",         "Field",          "Key",            "Rewind",
-  "Next",           "Destroy",        "Reorganize",     "ResetIdx",
-  "NextIdx",        "PutIdx",         "DeleteIdx",      "MemLoad",
-  "MemStore",       "ListOpen",       "ListWrite",      "ListRewind",
-  "ListRead",       "ListClose",      "SortOpen",       "SortPut",
-  "SortMakeRec",    "SortMakeKey",    "Sort",           "SortNext",
-  "SortKey",        "SortCallback",   "SortClose",      "FileOpen",
-  "FileRead",       "FileField",      "FileClose",      "AggReset",
-  "AggFocus",       "AggIncr",        "AggNext",        "AggSet",
-  "AggGet",         "SetInsert",      "SetFound",       "SetNotFound",
-  "SetClear",       "MakeRecord",     "MakeKey",        "Goto",
-  "If",             "Halt",           "ColumnCount",    "ColumnName",
-  "Callback",       "Integer",        "String",         "Null",
-  "Pop",            "Dup",            "Pull",           "Add",
-  "AddImm",         "Subtract",       "Multiply",       "Divide",
-  "Min",            "Max",            "Like",           "Glob",
-  "Eq",             "Ne",             "Lt",             "Le",
-  "Gt",             "Ge",             "IsNull",         "NotNull",
-  "Negative",       "And",            "Or",             "Not",
-  "Concat",         "Noop",         
+  "Delete",         "Field",          "KeyAsData",      "Key",
+  "Rewind",         "Next",           "Destroy",        "Reorganize",
+  "ResetIdx",       "NextIdx",        "PutIdx",         "DeleteIdx",
+  "MemLoad",        "MemStore",       "ListOpen",       "ListWrite",
+  "ListRewind",     "ListRead",       "ListClose",      "SortOpen",
+  "SortPut",        "SortMakeRec",    "SortMakeKey",    "Sort",
+  "SortNext",       "SortKey",        "SortCallback",   "SortClose",
+  "FileOpen",       "FileRead",       "FileField",      "FileClose",
+  "AggReset",       "AggFocus",       "AggIncr",        "AggNext",
+  "AggSet",         "AggGet",         "SetInsert",      "SetFound",
+  "SetNotFound",    "SetClear",       "MakeRecord",     "MakeKey",
+  "Goto",           "If",             "Halt",           "ColumnCount",
+  "ColumnName",     "Callback",       "Integer",        "String",
+  "Null",           "Pop",            "Dup",            "Pull",
+  "Add",            "AddImm",         "Subtract",       "Multiply",
+  "Divide",         "Min",            "Max",            "Like",
+  "Glob",           "Eq",             "Ne",             "Lt",
+  "Le",             "Gt",             "Ge",             "IsNull",
+  "NotNull",        "Negative",       "And",            "Or",
+  "Not",            "Concat",         "Noop",         
 };
 
 /*
@@ -1882,6 +1883,22 @@ int sqliteVdbeExec(
         break;
       }
 
+      /* Opcode: KeyAsData P1 P2 *
+      **
+      ** Turn the key-as-data mode for cursor P1 either on (if P2==1) or
+      ** off (if P2==0).  In key-as-data mode, the OP_Fetch opcode pulls
+      ** data off of the key rather than the data.  This is useful for
+      ** outer joins and stuff...
+      */
+      case OP_KeyAsData: {
+        int i = pOp->p1;
+        VdbeTable *pTab;
+        if( i>=0 && i<p->nTable && p->aTab[i].pTable!=0 ){
+          p->aTab[i].keyAsData = pOp->p2;
+        }
+        break;
+      }
+
       /* Opcode: Field P1 P2 *
       **
       ** Push onto the stack the value of the P2-th field from the
@@ -1903,17 +1920,32 @@ int sqliteVdbeExec(
 
         if( NeedStack(p, tos) ) goto no_mem;
         if( i>=0 && i<p->nTable && (pTab = p->aTab[i].pTable)!=0 ){
-          amt = sqliteDbbeDataLength(pTab);
-          if( amt<=sizeof(int)*(p2+1) ){
-            p->aStack[tos].flags = STK_Null;
-            break;
-          }
-          pAddr = (int*)sqliteDbbeReadData(pTab, sizeof(int)*p2);
-          if( *pAddr==0 ){
-            p->aStack[tos].flags = STK_Null;
-            break;
+          if( p->aTab[i].keyAsData ){
+            amt = sqliteDbbeKeyLength(pTab);
+            if( amt<=sizeof(int)*(p2+1) ){
+              p->aStack[tos].flags = STK_Null;
+              break;
+            }
+            pAddr = (int*)sqliteDbbeReadKey(pTab, sizeof(int)*p2);
+            if( *pAddr==0 ){
+              p->aStack[tos].flags = STK_Null;
+              break;
+            }
+            z = sqliteDbbeReadKey(pTab, *pAddr);
+          }else{
+            amt = sqliteDbbeDataLength(pTab);
+            if( amt<=sizeof(int)*(p2+1) ){
+              p->aStack[tos].flags = STK_Null;
+              break;
+            }
+            pAddr = (int*)sqliteDbbeReadData(pTab, sizeof(int)*p2);
+            if( *pAddr==0 ){
+              p->aStack[tos].flags = STK_Null;
+              break;
+            }
+            z = sqliteDbbeReadData(pTab, *pAddr);
           }
-          p->zStack[tos] = z = sqliteDbbeReadData(pTab, *pAddr);
+          p->zStack[tos] = z;
           p->aStack[tos].n = strlen(z) + 1;
           p->aStack[tos].flags = STK_Str;
         }
diff --git a/src/vdbe.h b/src/vdbe.h
index ce2f9734e..35fa0854c 100644
--- a/src/vdbe.h
+++ b/src/vdbe.h
@@ -27,7 +27,7 @@
 ** or VDBE.  The VDBE implements an abstract machine that runs a
 ** simple program to access and modify the underlying database.
 **
-** $Id: vdbe.h,v 1.8 2000/06/06 01:50:44 drh Exp $
+** $Id: vdbe.h,v 1.9 2000/06/06 21:56:08 drh Exp $
 */
 #ifndef _SQLITE_VDBE_H_
 #define _SQLITE_VDBE_H_
@@ -81,97 +81,98 @@ typedef struct VdbeOp VdbeOp;
 #define OP_NotFound            8
 #define OP_Delete              9
 #define OP_Field              10
-#define OP_Key                11
-#define OP_Rewind             12
-#define OP_Next               13
-
-#define OP_Destroy            14
-#define OP_Reorganize         15
-
-#define OP_ResetIdx           16
-#define OP_NextIdx            17
-#define OP_PutIdx             18
-#define OP_DeleteIdx          19
-
-#define OP_MemLoad            20
-#define OP_MemStore           21
-
-#define OP_ListOpen           22
-#define OP_ListWrite          23
-#define OP_ListRewind         24
-#define OP_ListRead           25
-#define OP_ListClose          26
-
-#define OP_SortOpen           27
-#define OP_SortPut            28
-#define OP_SortMakeRec        29
-#define OP_SortMakeKey        30
-#define OP_Sort               31
-#define OP_SortNext           32
-#define OP_SortKey            33
-#define OP_SortCallback       34
-#define OP_SortClose          35
-
-#define OP_FileOpen           36
-#define OP_FileRead           37
-#define OP_FileField          38
-#define OP_FileClose          39
-
-#define OP_AggReset           40
-#define OP_AggFocus           41
-#define OP_AggIncr            42
-#define OP_AggNext            43
-#define OP_AggSet             44
-#define OP_AggGet             45
-
-#define OP_SetInsert          46
-#define OP_SetFound           47
-#define OP_SetNotFound        48
-#define OP_SetClear           49
-
-#define OP_MakeRecord         50
-#define OP_MakeKey            51
-
-#define OP_Goto               52
-#define OP_If                 53
-#define OP_Halt               54
-
-#define OP_ColumnCount        55
-#define OP_ColumnName         56
-#define OP_Callback           57
-
-#define OP_Integer            58
-#define OP_String             59
-#define OP_Null               60
-#define OP_Pop                61
-#define OP_Dup                62
-#define OP_Pull               63
-
-#define OP_Add                64
-#define OP_AddImm             65
-#define OP_Subtract           66
-#define OP_Multiply           67
-#define OP_Divide             68
-#define OP_Min                69
-#define OP_Max                70
-#define OP_Like               71
-#define OP_Glob               72
-#define OP_Eq                 73
-#define OP_Ne                 74
-#define OP_Lt                 75
-#define OP_Le                 76
-#define OP_Gt                 77
-#define OP_Ge                 78
-#define OP_IsNull             79
-#define OP_NotNull            80
-#define OP_Negative           81
-#define OP_And                82
-#define OP_Or                 83
-#define OP_Not                84
-#define OP_Concat             85
-#define OP_Noop               86
-
-#define OP_MAX                86
+#define OP_KeyAsData          11
+#define OP_Key                12
+#define OP_Rewind             13
+#define OP_Next               14
+
+#define OP_Destroy            15
+#define OP_Reorganize         16
+
+#define OP_ResetIdx           17
+#define OP_NextIdx            18
+#define OP_PutIdx             19
+#define OP_DeleteIdx          20
+
+#define OP_MemLoad            21
+#define OP_MemStore           22
+
+#define OP_ListOpen           23
+#define OP_ListWrite          24
+#define OP_ListRewind         25
+#define OP_ListRead           26
+#define OP_ListClose          27
+
+#define OP_SortOpen           28
+#define OP_SortPut            29
+#define OP_SortMakeRec        30
+#define OP_SortMakeKey        31
+#define OP_Sort               32
+#define OP_SortNext           33
+#define OP_SortKey            34
+#define OP_SortCallback       35
+#define OP_SortClose          36
+
+#define OP_FileOpen           37
+#define OP_FileRead           38
+#define OP_FileField          39
+#define OP_FileClose          40
+
+#define OP_AggReset           41
+#define OP_AggFocus           42
+#define OP_AggIncr            43
+#define OP_AggNext            44
+#define OP_AggSet             45
+#define OP_AggGet             46
+
+#define OP_SetInsert          47
+#define OP_SetFound           48
+#define OP_SetNotFound        49
+#define OP_SetClear           50
+
+#define OP_MakeRecord         51
+#define OP_MakeKey            52
+
+#define OP_Goto               53
+#define OP_If                 54
+#define OP_Halt               55
+
+#define OP_ColumnCount        56
+#define OP_ColumnName         57
+#define OP_Callback           58
+
+#define OP_Integer            59
+#define OP_String             60
+#define OP_Null               61
+#define OP_Pop                62
+#define OP_Dup                63
+#define OP_Pull               64
+
+#define OP_Add                65
+#define OP_AddImm             66
+#define OP_Subtract           67
+#define OP_Multiply           68
+#define OP_Divide             69
+#define OP_Min                70
+#define OP_Max                71
+#define OP_Like               72
+#define OP_Glob               73
+#define OP_Eq                 74
+#define OP_Ne                 75
+#define OP_Lt                 76
+#define OP_Le                 77
+#define OP_Gt                 78
+#define OP_Ge                 79
+#define OP_IsNull             80
+#define OP_NotNull            81
+#define OP_Negative           82
+#define OP_And                83
+#define OP_Or                 84
+#define OP_Not                85
+#define OP_Concat             86
+#define OP_Noop               87
+
+#define OP_MAX                87
 
 /*
 ** Prototypes for the VDBE interface.  See comments on the implementation