/ Check-in [c02268bd]
Login

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

Overview
Comment::-) (CVS 52)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c02268bdf4c28edc2542ce0ca1ba24fd6b5058fa
User & Date: drh 2000-06-05 18:54:46
Context
2000-06-05
18:56
:-) (CVS 53) check-in: 3dbfa558 user: drh tags: trunk
18:54
:-) (CVS 52) check-in: c02268bd user: drh tags: trunk
16:01
separate Select structure (CVS 51) check-in: ce45dea9 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

    29     29   **     DROP TABLE
    30     30   **     CREATE INDEX
    31     31   **     DROP INDEX
    32     32   **     creating expressions and ID lists
    33     33   **     COPY
    34     34   **     VACUUM
    35     35   **
    36         -** $Id: build.c,v 1.14 2000/06/03 18:06:52 drh Exp $
           36  +** $Id: build.c,v 1.15 2000/06/05 18:54:46 drh Exp $
    37     37   */
    38     38   #include "sqliteInt.h"
    39     39   
    40     40   /*
    41     41   ** This routine is called after a single SQL statement has been
    42     42   ** parsed and we want to execute the code to implement 
    43     43   ** the statement.  Prior action routines should have already
................................................................................
   104    104   /*
   105    105   ** Recursively delete an expression tree.
   106    106   */
   107    107   void sqliteExprDelete(Expr *p){
   108    108     if( p==0 ) return;
   109    109     if( p->pLeft ) sqliteExprDelete(p->pLeft);
   110    110     if( p->pRight ) sqliteExprDelete(p->pRight);
          111  +  if( p->pList ) sqliteExprListDelete(p->pList);
          112  +  if( p->pSelect ) sqliteSelectDelete(p->pSelect);
   111    113     sqliteFree(p);
   112    114   }
   113    115   
   114    116   /*
   115    117   ** Locate the in-memory structure that describes the
   116    118   ** format of a particular database table given the name
   117    119   ** of that table.  Return NULL if not found.

Changes to src/expr.c.

    19     19   ** Author contact information:
    20     20   **   drh@hwaci.com
    21     21   **   http://www.hwaci.com/drh/
    22     22   **
    23     23   *************************************************************************
    24     24   ** This file contains C code routines used for processing expressions
    25     25   **
    26         -** $Id: expr.c,v 1.5 2000/06/04 12:58:37 drh Exp $
           26  +** $Id: expr.c,v 1.6 2000/06/05 18:54:46 drh Exp $
    27     27   */
    28     28   #include "sqliteInt.h"
    29     29   
    30     30   /*
    31     31   ** This routine walks an expression tree and resolves references to
    32     32   ** table fields.  Nodes of the form ID.ID or ID resolve into an
    33     33   ** index to the table in the table list and a field offset.  The opcode
    34     34   ** for such nodes is changed to TK_FIELD.  The iTable value is changed
    35         -** to the index of the referenced table in pTabList, and the iField value
    36         -** is changed to the index of the field of the referenced table.
           35  +** to the index of the referenced table in pTabList plus the pParse->nTab
           36  +** value.  The iField value is changed to the index of the field of the 
           37  +** referenced table.
           38  +**
           39  +** This routine also looks for SELECTs that are part of an expression.
           40  +** If it finds any, it generates code to write the value of that select
           41  +** into a memory cell.
    37     42   **
    38     43   ** Unknown fields or tables provoke an error.  The function returns
    39     44   ** the number of errors seen and leaves an error message on pParse->zErrMsg.
    40     45   */
    41     46   int sqliteExprResolveIds(Parse *pParse, IdList *pTabList, Expr *pExpr){
    42     47     if( pExpr==0 ) return 0;
    43     48     switch( pExpr->op ){
................................................................................
    50     55         for(i=0; i<pTabList->nId; i++){
    51     56           int j;
    52     57           Table *pTab = pTabList->a[i].pTab;
    53     58           if( pTab==0 ) continue;
    54     59           for(j=0; j<pTab->nCol; j++){
    55     60             if( sqliteStrICmp(pTab->aCol[j].zName, z)==0 ){
    56     61               cnt++;
    57         -            pExpr->iTable = i;
           62  +            pExpr->iTable = i + pParse->nTab;
    58     63               pExpr->iField = j;
    59     64             }
    60     65           }
    61     66         }
    62     67         sqliteFree(z);
    63     68         if( cnt==0 ){
    64     69           sqliteSetNString(&pParse->zErrMsg, "no such field: ", -1,  
................................................................................
   100    105           }else{
   101    106             zTab = pTab->zName;
   102    107           }
   103    108           if( sqliteStrICmp(zTab, zLeft)!=0 ) continue;
   104    109           for(j=0; j<pTab->nCol; j++){
   105    110             if( sqliteStrICmp(pTab->aCol[j].zName, zRight)==0 ){
   106    111               cnt++;
   107         -            pExpr->iTable = i;
          112  +            pExpr->iTable = i + pParse->nTab;
   108    113               pExpr->iField = j;
   109    114             }
   110    115           }
   111    116         }
   112    117         sqliteFree(zLeft);
   113    118         sqliteFree(zRight);
   114    119         if( cnt==0 ){
................................................................................
   127    132         sqliteExprDelete(pLeft);
   128    133         pExpr->pLeft = 0;
   129    134         sqliteExprDelete(pRight);
   130    135         pExpr->pRight = 0;
   131    136         pExpr->op = TK_FIELD;
   132    137         break;
   133    138       }
          139  +
          140  +    case TK_SELECT: {
          141  +      pExpr->iField = pParse->nMem++;
          142  +      if( sqliteSelect(pParse, pExpr->pSelect, -1, pExpr->iField) ){
          143  +        return 1;
          144  +      }
          145  +      break;
          146  +    }
   134    147   
   135    148       /* For all else, just recursively walk the tree */
   136    149       default: {
   137    150         if( pExpr->pLeft 
   138    151               && sqliteExprResolveIds(pParse, pTabList, pExpr->pLeft) ){
   139    152           return 1;
   140    153         }
................................................................................
   380    393         for(i=0; i<pList->nExpr; i++){
   381    394           sqliteExprCode(pParse, pList->a[i].pExpr);
   382    395           if( i>0 ){
   383    396             sqliteVdbeAddOp(v, op, 0, 0, 0, 0);
   384    397           }
   385    398         }
   386    399         break;
          400  +    }
          401  +    case TK_SELECT: {
          402  +      sqliteVdbeAddOp(v, OP_MemLoad, pExpr->iField, 0, 0, 0);
          403  +      break;
   387    404       }
   388    405     }
   389    406     return;
   390    407   }
   391    408   
   392    409   /*
   393    410   ** Generate code for a boolean expression such that a jump is made

Changes to src/insert.c.

    20     20   **   drh@hwaci.com
    21     21   **   http://www.hwaci.com/drh/
    22     22   **
    23     23   *************************************************************************
    24     24   ** This file contains C code routines that are called by the parser
    25     25   ** to handle INSERT statements.
    26     26   **
    27         -** $Id: insert.c,v 1.5 2000/06/04 12:58:38 drh Exp $
           27  +** $Id: insert.c,v 1.6 2000/06/05 18:54:46 drh Exp $
    28     28   */
    29     29   #include "sqliteInt.h"
    30     30   
    31     31   /*
    32     32   ** This routine is call to handle SQL of the following form:
    33     33   **
    34     34   **    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
    97     97           sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
    98     98              " has no column named ", pField->a[i].zName, 0);
    99     99           pParse->nErr++;
   100    100           goto insert_cleanup;
   101    101         }
   102    102       }
   103    103     }
   104         -  v = pParse->pVdbe = sqliteVdbeCreate(pParse->db->pBe);
          104  +  v = pParse->pVdbe;
          105  +  if( v==0 ){
          106  +    v = pParse->pVdbe = sqliteVdbeCreate(pParse->db->pBe);
          107  +  }
   105    108     if( v ){
   106    109       Index *pIdx;
   107    110       sqliteVdbeAddOp(v, OP_Open, 0, 1, pTab->zName, 0);
   108    111       for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
   109    112         sqliteVdbeAddOp(v, OP_Open, idx, 1, pIdx->zName, 0);
   110    113       }
   111    114       sqliteVdbeAddOp(v, OP_New, 0, 0, 0, 0);

Changes to src/parse.y.

    22     22   **
    23     23   *************************************************************************
    24     24   ** This file contains SQLite's grammar for SQL.  Process this file
    25     25   ** using the lemon parser generator to generate C code that runs
    26     26   ** the parser.  Lemon will also generate a header file containing
    27     27   ** numeric codes for all of the tokens.
    28     28   **
    29         -** @(#) $Id: parse.y,v 1.9 2000/06/05 16:01:39 drh Exp $
           29  +** @(#) $Id: parse.y,v 1.10 2000/06/05 18:54:46 drh Exp $
    30     30   */
    31     31   %token_prefix TK_
    32     32   %token_type {Token}
    33     33   %extra_argument {Parse *pParse}
    34     34   %syntax_error {
    35     35     sqliteSetNString(&pParse->zErrMsg,"syntax error near \"",0,TOKEN.z,TOKEN.n,
    36     36                      "\"", 1, 0);
................................................................................
   129    129   // The next command format is dropping tables.
   130    130   //
   131    131   cmd ::= DROP TABLE id(X).          {sqliteDropTable(pParse,&X);}
   132    132   
   133    133   // The select statement
   134    134   //
   135    135   cmd ::= select(X).  {
   136         -  sqliteSelect(pParse, X, 0, 0);
          136  +  sqliteSelect(pParse, X, -1, -1);
   137    137     sqliteSelectDelete(X);
   138    138   }
   139    139   
   140    140   %type select {Select*}
   141    141   %destructor select {sqliteSelectDelete($$);}
   142    142   
   143    143   select(A) ::= SELECT distinct(D) selcollist(W) from(X) where_opt(Y)
................................................................................
   307    307   expr(A) ::= expr(X) STAR expr(Y).  {A = sqliteExpr(TK_STAR, X, Y, 0);}
   308    308   expr(A) ::= expr(X) SLASH expr(Y). {A = sqliteExpr(TK_SLASH, X, Y, 0);}
   309    309   expr(A) ::= expr(X) ISNULL.        {A = sqliteExpr(TK_ISNULL, X, 0, 0);}
   310    310   expr(A) ::= expr(X) NOTNULL.       {A = sqliteExpr(TK_NOTNULL, X, 0, 0);}
   311    311   expr(A) ::= NOT expr(X).           {A = sqliteExpr(TK_NOT, X, 0, 0);}
   312    312   expr(A) ::= MINUS expr(X). [UMINUS]   {A = sqliteExpr(TK_UMINUS, X, 0, 0);}
   313    313   expr(A) ::= PLUS expr(X). [UMINUS]    {A = X;}
          314  +expr(A) ::= LP select(X) RP. {
          315  +  A = sqliteExpr(TK_SELECT, 0, 0, 0);
          316  +  A->pSelect = X;
          317  +}
   314    318   
   315    319   %type exprlist {ExprList*}
   316    320   %destructor exprlist {sqliteExprListDelete($$);}
   317    321   %type expritem {Expr*}
   318    322   %destructor expritem {sqliteExprDelete($$);}
   319    323   
   320    324   exprlist(A) ::= exprlist(X) COMMA expritem(Y). 

Changes to src/select.c.

    20     20   **   drh@hwaci.com
    21     21   **   http://www.hwaci.com/drh/
    22     22   **
    23     23   *************************************************************************
    24     24   ** This file contains C code routines that are called by the parser
    25     25   ** to handle SELECT statements.
    26     26   **
    27         -** $Id: select.c,v 1.7 2000/06/05 16:01:39 drh Exp $
           27  +** $Id: select.c,v 1.8 2000/06/05 18:54:46 drh Exp $
    28     28   */
    29     29   #include "sqliteInt.h"
    30     30   
    31     31   
    32     32   /*
    33     33   ** Allocate a new Select structure and return a pointer to that
    34     34   ** structure.
................................................................................
    67     67     sqliteExprListDelete(p->pOrderBy);
    68     68     sqliteFree(p);
    69     69   }
    70     70   
    71     71   /*
    72     72   ** Generate code for the given SELECT statement.
    73     73   **
    74         -** If pDest==0 and iMem<0, then the results of the query are sent to
    75         -** the callback function.  If pDest!=0 then the results are written to
    76         -** the single table specified.  If pDest==0 and iMem>=0 then the result
    77         -** should be a single value which is then stored in memory location iMem
    78         -** of the virtual machine.
           74  +** If iDest<0 and iMem<0, then the results of the query are sent to
           75  +** the callback function.  If iDest>=0 then the results are written to
           76  +** an open cursor with the index iDest.  The calling function is
           77  +** responsible for having that cursor open.  If iDest<0 and iMem>=0 
           78  +** then the result should be a single value which is then stored in 
           79  +** memory location iMem of the virtual machine.
    79     80   **
    80     81   ** This routine returns the number of errors.  If any errors are
    81     82   ** encountered, then an appropriate error message is left in
    82     83   ** pParse->zErrMsg.
    83     84   **
    84     85   ** This routine does NOT free the Select structure passed in.  The
    85     86   ** calling function needs to do that.
    86     87   */
    87     88   int sqliteSelect(
    88     89     Parse *pParse,         /* The parser context */
    89     90     Select *p,             /* The SELECT statement being coded. */
    90         -  Table *pDest,          /* Write results here, if not NULL */
           91  +  int iDest,             /* Write results to this cursor */
    91     92     int iMem               /* Save result in this memory location, if >=0 */
    92     93   ){
    93     94     int i, j;
    94     95     WhereInfo *pWInfo;
    95     96     Vdbe *v;
    96     97     int isAgg = 0;         /* True for select lists like "count(*)" */
    97     98     ExprList *pEList;      /* List of fields to extract.  NULL means "*" */
    98     99     IdList *pTabList;      /* List of tables to select from */
    99    100     Expr *pWhere;          /* The WHERE clause.  May be NULL */
   100    101     ExprList *pOrderBy;    /* The ORDER BY clause.  May be NULL */
   101         -  int distinct;          /* If true, only output distinct results */
   102         -
          102  +  int isDistinct;        /* True if the DISTINCT keyword is present */
          103  +  int distinct;          /* Table to use for the distinct set */
   103    104   
   104    105     pEList = p->pEList;
   105    106     pTabList = p->pSrc;
   106    107     pWhere = p->pWhere;
   107    108     pOrderBy = p->pOrderBy;
   108         -  distinct = p->isDistinct;
          109  +  isDistinct = p->isDistinct;
   109    110   
   110    111     /* 
   111    112     ** Do not even attempt to generate any code if we have already seen
   112    113     ** errors before this routine starts.
   113    114     */
   114    115     if( pParse->nErr>0 ) return 0;
   115    116   
................................................................................
   120    121       if( pTabList->a[i].pTab==0 ){
   121    122         sqliteSetString(&pParse->zErrMsg, "no such table: ", 
   122    123            pTabList->a[i].zName, 0);
   123    124         pParse->nErr++;
   124    125         return 1;
   125    126       }
   126    127     }
          128  +
          129  +  /* Allocate a temporary table to use for the DISTINCT set, if
          130  +  ** necessary.
          131  +  */
          132  +  if( isDistinct ){
          133  +    distinct = pParse->nTab++;
          134  +  }
   127    135   
   128    136     /* If the list of fields to retrieve is "*" then replace it with
   129    137     ** a list of all fields from all tables.
   130    138     */
   131    139     if( pEList==0 ){
   132    140       for(i=0; i<pTabList->nId; i++){
   133    141         Table *pTab = pTabList->a[i].pTab;
   134    142         for(j=0; j<pTab->nCol; j++){
   135    143           Expr *pExpr = sqliteExpr(TK_FIELD, 0, 0, 0);
   136         -        pExpr->iTable = i;
          144  +        pExpr->iTable = i + pParse->nTab;
   137    145           pExpr->iField = j;
   138    146           pEList = sqliteExprListAppend(pEList, pExpr, 0);
   139    147         }
   140    148       }
   141    149     }
          150  +
          151  +  /* If writing to memory, only a single column may be output.
          152  +  */
          153  +  if( iMem>=0 && pEList->nExpr>1 ){
          154  +    sqliteSetString(&pParse->zErrMsg, "only a single result allowed for "
          155  +       "a SELECT that is part of an expression", 0);
          156  +    pParse->nErr++;
          157  +    return 1;
          158  +  }
   142    159   
   143    160     /* Resolve the field names and do a semantics check on all the expressions.
   144    161     */
   145    162     for(i=0; i<pEList->nExpr; i++){
   146    163       if( sqliteExprResolveIds(pParse, pTabList, pEList->a[i].pExpr) ){
   147    164         return 1;
   148    165       }
................................................................................
   176    193         }
   177    194         if( sqliteExprCheck(pParse, pOrderBy->a[i].pExpr, 0, 0) ){
   178    195           return 1;
   179    196         }
   180    197       }
   181    198     }
   182    199   
   183         -  /* ORDER BY is ignored if this is an aggregate query like count(*)
   184         -  ** since only one row will be returned.
          200  +  /* ORDER BY is ignored if 
          201  +  **
          202  +  **   (1) this is an aggregate query like count(*)
          203  +  **       since only one row will be returned.
          204  +  **
          205  +  **   (2) We are writing the result to another table, since the
          206  +  **       order will get scrambled again after inserting.
          207  +  **
          208  +  **   (3) We are writing to a memory cell, since there is only
          209  +  **       one result.
   185    210     */
   186         -  if( isAgg && pOrderBy ){
          211  +  if( isAgg || iDest>=0 || iMem>=0 ){
   187    212       pOrderBy = 0;
   188    213     }
   189    214   
   190         -  /* Turn off distinct if this is an aggregate
          215  +  /* Turn off distinct if this is an aggregate or writing to memory.
   191    216     */
   192         -  if( isAgg ){
   193         -    distinct = 0;
          217  +  if( isAgg || iMem>=0 ){
          218  +    isDistinct = 0;
   194    219     }
   195    220   
   196    221     /* Begin generating code.
   197    222     */
   198    223     v = pParse->pVdbe;
   199    224     if( v==0 ){
   200    225       v = pParse->pVdbe = sqliteVdbeCreate(pParse->db->pBe);
................................................................................
   204    229       pParse->nErr++;
   205    230       return 1;
   206    231     }
   207    232     if( pOrderBy ){
   208    233       sqliteVdbeAddOp(v, OP_SortOpen, 0, 0, 0, 0);
   209    234     }
   210    235   
   211         -  /* Identify column names
          236  +  /* Identify column names if we will be using a callback.  This
          237  +  ** step is skipped if the output is going to a table or a memory cell.
   212    238     */
   213         -  sqliteVdbeAddOp(v, OP_ColumnCount, pEList->nExpr, 0, 0, 0);
   214         -  for(i=0; i<pEList->nExpr; i++){
   215         -    Expr *p;
   216         -    if( pEList->a[i].zName ){
   217         -      char *zName = pEList->a[i].zName;
   218         -      int addr = sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
   219         -      if( zName[0]=='\'' || zName[0]=='"' ){
   220         -        sqliteVdbeDequoteP3(v, addr);
          239  +  if( iDest<0 && iMem<0 ){
          240  +    sqliteVdbeAddOp(v, OP_ColumnCount, pEList->nExpr, 0, 0, 0);
          241  +    for(i=0; i<pEList->nExpr; i++){
          242  +      Expr *p;
          243  +      if( pEList->a[i].zName ){
          244  +        char *zName = pEList->a[i].zName;
          245  +        int addr = sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
          246  +        if( zName[0]=='\'' || zName[0]=='"' ){
          247  +          sqliteVdbeDequoteP3(v, addr);
          248  +        }
          249  +        continue;
   221    250         }
   222         -      continue;
   223         -    }
   224         -    p = pEList->a[i].pExpr;
   225         -    if( p->op!=TK_FIELD ){
   226         -      char zName[30];
   227         -      sprintf(zName, "field%d", i+1);
   228         -      sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
   229         -    }else{
   230         -      if( pTabList->nId>1 ){
   231         -        char *zName = 0;
   232         -        Table *pTab = pTabList->a[p->iTable].pTab;
   233         -        char *zTab;
   234         -
   235         -        zTab = pTabList->a[p->iTable].zAlias;
   236         -        if( zTab==0 ) zTab = pTab->zName;
   237         -        sqliteSetString(&zName, zTab, ".", pTab->aCol[p->iField].zName, 0);
          251  +      p = pEList->a[i].pExpr;
          252  +      if( p->op!=TK_FIELD ){
          253  +        char zName[30];
          254  +        sprintf(zName, "field%d", i+1);
   238    255           sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
   239         -        sqliteFree(zName);
   240    256         }else{
   241         -        Table *pTab = pTabList->a[0].pTab;
   242         -        sqliteVdbeAddOp(v, OP_ColumnName, i, 0, pTab->aCol[p->iField].zName, 0);
          257  +        if( pTabList->nId>1 ){
          258  +          char *zName = 0;
          259  +          Table *pTab = pTabList->a[p->iTable].pTab;
          260  +          char *zTab;
          261  +  
          262  +          zTab = pTabList->a[p->iTable].zAlias;
          263  +          if( zTab==0 ) zTab = pTab->zName;
          264  +          sqliteSetString(&zName, zTab, ".", pTab->aCol[p->iField].zName, 0);
          265  +          sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
          266  +          sqliteFree(zName);
          267  +        }else{
          268  +          Table *pTab = pTabList->a[0].pTab;
          269  +          char *zName = pTab->aCol[p->iField].zName;
          270  +          sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
          271  +        }
   243    272         }
   244    273       }
   245    274     }
   246    275   
   247    276     /* Initialize the stack to contain aggregate seed values
   248    277     */
   249    278     if( isAgg ){
................................................................................
   258    287           default: {
   259    288             sqliteVdbeAddOp(v, OP_Integer, 0, 0, 0, 0);
   260    289             break;
   261    290           }
   262    291         }
   263    292       }
   264    293     }
          294  +
          295  +  /* Initialize the memory cell to NULL
          296  +  */
          297  +  if( iMem>=0 ){
          298  +    sqliteVdbeAddOp(v, OP_Null, 0, 0, 0, 0);
          299  +    sqliteVdbeAddOp(v, OP_MemStore, iMem, 0, 0, 0);
          300  +  }
   265    301   
   266    302     /* Begin the database scan
   267    303     */
   268         -  if( distinct ){
   269         -    distinct = pTabList->nId*2+1;
          304  +  if( isDistinct ){
   270    305       sqliteVdbeAddOp(v, OP_Open, distinct, 1, 0, 0);
   271    306     }
   272    307     pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 0);
   273    308     if( pWInfo==0 ) return 1;
   274    309   
   275    310     /* Pull the requested fields.
   276    311     */
................................................................................
   279    314         sqliteExprCode(pParse, pEList->a[i].pExpr);
   280    315       }
   281    316     }
   282    317   
   283    318     /* If the current result is not distinct, script the remainder
   284    319     ** of this processing.
   285    320     */
   286         -  if( distinct ){
   287         -    int isDistinct = sqliteVdbeMakeLabel(v);
          321  +  if( isDistinct ){
          322  +    int lbl = sqliteVdbeMakeLabel(v);
   288    323       sqliteVdbeAddOp(v, OP_MakeKey, pEList->nExpr, 1, 0, 0);
   289         -    sqliteVdbeAddOp(v, OP_Distinct, distinct, isDistinct, 0, 0);
          324  +    sqliteVdbeAddOp(v, OP_Distinct, distinct, lbl, 0, 0);
   290    325       sqliteVdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0, 0, 0);
   291    326       sqliteVdbeAddOp(v, OP_Goto, 0, pWInfo->iContinue, 0, 0);
   292         -    sqliteVdbeAddOp(v, OP_String, 0, 0, "", isDistinct);
          327  +    sqliteVdbeAddOp(v, OP_String, 0, 0, "", lbl);
   293    328       sqliteVdbeAddOp(v, OP_Put, distinct, 0, 0, 0);
   294    329     }
   295    330     
   296    331     /* If there is no ORDER BY clause, then we can invoke the callback
   297    332     ** right away.  If there is an ORDER BY, then we need to put the
   298    333     ** data into an appropriate sorter record.
   299    334     */
................................................................................
   326    361           case FN_Count: op = OP_AddImm; p1 = 1; break;
   327    362           case FN_Sum:   op = OP_Add;    p1 = 0; break;
   328    363           case FN_Min:   op = OP_Min;    p1 = 1; break;
   329    364           case FN_Max:   op = OP_Max;    p1 = 0; break;
   330    365         }
   331    366         sqliteVdbeAddOp(v, op, p1, 0, 0, 0);
   332    367       }
          368  +  }else if( iDest>=0 ){
          369  +    sqliteVdbeAddOp(v, OP_MakeRecord, pEList->nExpr, 0, 0, 0);
          370  +    sqliteVdbeAddOp(v, OP_New, iDest, 0, 0, 0);
          371  +    sqliteVdbeAddOp(v, OP_Pull, 1, 0, 0, 0);
          372  +    sqliteVdbeAddOp(v, OP_Put, iDest, 0, 0, 0);
          373  +  }else if( iMem>=0 ){
          374  +    sqliteVdbeAddOp(v, OP_MemStore, iMem, 0, 0, 0);
          375  +    sqliteVdbeAddOp(v, OP_Goto, 0, pWInfo->iBreak, 0, 0);
   333    376     }else{
   334    377       sqliteVdbeAddOp(v, OP_Callback, pEList->nExpr, 0, 0, 0);
   335    378     }
   336    379   
   337    380     /* End the database scan loop.
   338    381     */
   339    382     sqliteWhereEnd(pWInfo);
................................................................................
   344    387     if( pOrderBy ){
   345    388       int end = sqliteVdbeMakeLabel(v);
   346    389       int addr;
   347    390       sqliteVdbeAddOp(v, OP_Sort, 0, 0, 0, 0);
   348    391       addr = sqliteVdbeAddOp(v, OP_SortNext, 0, end, 0, 0);
   349    392       sqliteVdbeAddOp(v, OP_SortCallback, pEList->nExpr, 0, 0, 0);
   350    393       sqliteVdbeAddOp(v, OP_Goto, 0, addr, 0, 0);
   351         -    sqliteVdbeAddOp(v, OP_Noop, 0, 0, 0, end);
          394  +    sqliteVdbeAddOp(v, OP_SortClose, 0, 0, 0, end);
   352    395     }
   353    396   
   354    397     /* If this is an aggregate, then we need to invoke the callback
   355    398     ** exactly once.
   356    399     */
   357    400     if( isAgg ){
   358         -    sqliteVdbeAddOp(v, OP_Callback, pEList->nExpr, 0, 0, 0);
          401  +    if( iDest>=0 ){
          402  +      sqliteVdbeAddOp(v, OP_MakeRecord, pEList->nExpr, 0, 0, 0);
          403  +      sqliteVdbeAddOp(v, OP_New, iDest, 0, 0, 0);
          404  +      sqliteVdbeAddOp(v, OP_Pull, 1, 0, 0, 0);
          405  +      sqliteVdbeAddOp(v, OP_Put, iDest, 0, 0, 0);
          406  +    }else if( iMem>=0 ){
          407  +      sqliteVdbeAddOp(v, OP_MemStore, iMem, 0, 0, 0);
          408  +    }else{
          409  +      sqliteVdbeAddOp(v, OP_Callback, pEList->nExpr, 0, 0, 0);
          410  +    }
   359    411     }
   360    412     return 0;
   361    413   }

Changes to src/sqliteInt.h.

    19     19   ** Author contact information:
    20     20   **   drh@hwaci.com
    21     21   **   http://www.hwaci.com/drh/
    22     22   **
    23     23   *************************************************************************
    24     24   ** Internal interface definitions for SQLite.
    25     25   **
    26         -** @(#) $Id: sqliteInt.h,v 1.14 2000/06/05 16:01:39 drh Exp $
           26  +** @(#) $Id: sqliteInt.h,v 1.15 2000/06/05 18:54:46 drh Exp $
    27     27   */
    28     28   #include "sqlite.h"
    29     29   #include "dbbe.h"
    30     30   #include "vdbe.h"
    31     31   #include "parse.h"
    32     32   #include <gdbm.h>
    33     33   #include <stdio.h>
................................................................................
   158    158   struct Expr {
   159    159     int op;                /* Operation performed by this node */
   160    160     Expr *pLeft, *pRight;  /* Left and right subnodes */
   161    161     ExprList *pList;       /* A list of expressions used as a function argument */
   162    162     Token token;           /* An operand token */
   163    163     int iTable, iField;    /* When op==TK_FIELD, then this node means the
   164    164                            ** iField-th field of the iTable-th table */
          165  +  Select *pSelect;       /* When the expression is a sub-select */
   165    166   };
   166    167   
   167    168   /*
   168    169   ** A list of expressions.  Each expression may optionally have a
   169    170   ** name.  An expr/name combination can be used in several ways, such
   170    171   ** as the list of "expr AS ID" fields following a "SELECT" or in the
   171    172   ** list of "ID = expr" items in an UPDATE.  A list of expressions can
................................................................................
   187    188   */
   188    189   struct IdList {
   189    190     int nId;         /* Number of identifiers on the list */
   190    191     struct {
   191    192       char *zName;      /* Text of the identifier. */
   192    193       char *zAlias;     /* The "B" part of a "A AS B" phrase.  zName is the "A" */
   193    194       Table *pTab;      /* Table corresponding to zName */
   194         -    int idx;          /* Index of a field name in the table */
          195  +    int idx;          /* Index of a field named zName in a table */
   195    196     } *a;            /* One entry for each identifier on the list */
   196    197   };
   197    198   
   198    199   /*
   199    200   ** The WHERE clause processing routine has two halves.  The
   200    201   ** first part does the start of the WHERE loop and the second
   201    202   ** half does the tail of the WHERE loop.  An instance of
   202    203   ** this structure is returned by the first half and passed
   203    204   ** into the second half to give some continuity.
   204    205   */
   205    206   struct WhereInfo {
   206    207     Parse *pParse;
   207         -  IdList *pTabList;
   208         -  int iContinue;
   209         -  int iBreak;
          208  +  IdList *pTabList;    /* List of tables in the join */
          209  +  int iContinue;       /* Jump here to continue with next record */
          210  +  int iBreak;          /* Jump here to break out of the loop */
          211  +  int base;            /* Index of first Open opcode */
          212  +  Index *aIdx[32];     /* Indices used for each table */
   210    213   };
   211    214   
   212    215   /*
   213    216   ** An instance of the following structure contains all information
   214    217   ** needed to generate code for a single SELECT statement.
   215    218   */
   216    219   struct Select {
................................................................................
   235    238     Token sFirstToken;   /* The first token parsed */
   236    239     Token sLastToken;    /* The last token parsed */
   237    240     Table *pNewTable;    /* A table being constructed by CREATE TABLE */
   238    241     Vdbe *pVdbe;         /* An engine for executing database bytecode */
   239    242     int explain;         /* True if the EXPLAIN flag is found on the query */
   240    243     int initFlag;        /* True if reparsing CREATE TABLEs */
   241    244     int nErr;            /* Number of errors seen */
          245  +  int nTab;            /* Number of previously allocated cursors */
          246  +  int nMem;            /* Number of memory cells used so far */
   242    247   };
   243    248   
   244    249   /*
   245    250   ** Internal function prototypes
   246    251   */
   247    252   int sqliteStrICmp(const char *, const char *);
   248    253   int sqliteStrNICmp(const char *, const char *, int);
................................................................................
   277    282   void sqliteDeleteTable(sqlite*, Table*);
   278    283   void sqliteInsert(Parse*, Token*, ExprList*, IdList*);
   279    284   IdList *sqliteIdListAppend(IdList*, Token*);
   280    285   void sqliteIdListAddAlias(IdList*, Token*);
   281    286   void sqliteIdListDelete(IdList*);
   282    287   void sqliteCreateIndex(Parse*, Token*, Token*, IdList*, Token*, Token*);
   283    288   void sqliteDropIndex(Parse*, Token*);
   284         -int sqliteSelect(Parse*, Select*, Table*, int);
          289  +int sqliteSelect(Parse*, Select*, int, int);
   285    290   Select *sqliteSelectNew(ExprList*,IdList*,Expr*,ExprList*,Expr*,ExprList*,int);
   286    291   void sqliteSelectDelete(Select*);
   287    292   void sqliteDeleteFrom(Parse*, Token*, Expr*);
   288    293   void sqliteUpdate(Parse*, Token*, ExprList*, Expr*);
   289    294   WhereInfo *sqliteWhereBegin(Parse*, IdList*, Expr*, int);
   290    295   void sqliteWhereEnd(WhereInfo*);
   291    296   void sqliteExprCode(Parse*, Expr*);

Changes to src/vdbe.c.

    37     37   ** inplicit conversion from one type to the other occurs as necessary.
    38     38   ** 
    39     39   ** Most of the code in this file is taken up by the sqliteVdbeExec()
    40     40   ** function which does the work of interpreting a VDBE program.
    41     41   ** But other routines are also provided to help in building up
    42     42   ** a program instruction by instruction.
    43     43   **
    44         -** $Id: vdbe.c,v 1.15 2000/06/05 16:01:39 drh Exp $
           44  +** $Id: vdbe.c,v 1.16 2000/06/05 18:54:47 drh Exp $
    45     45   */
    46     46   #include "sqliteInt.h"
    47     47   #include <unistd.h>
    48     48   
    49     49   /*
    50     50   ** SQL is translated into a sequence of instructions to be
    51     51   ** executed by a virtual machine.  Each instruction is an instance
................................................................................
    88     88   ** strings separately is so that they can be easily passed
    89     89   ** to the callback function.
    90     90   */
    91     91   struct Stack {
    92     92     int i;         /* Integer value */
    93     93     int n;         /* Number of characters in string value, including '\0' */
    94     94     int flags;     /* Some combination of STK_Null, STK_Str, STK_Dyn, etc. */
    95         -  double r;      /* Real value */      
           95  +  double r;      /* Real value */
    96     96   };
    97     97   typedef struct Stack Stack;
    98     98   
           99  +/*
          100  +** Memory cells use the same structure as the stack except that space
          101  +** for an arbitrary string is added.
          102  +*/
          103  +struct Mem {
          104  +  Stack s;       /* All values of the memory cell besides string */
          105  +  char *z;       /* String value for this memory cell */
          106  +};
          107  +typedef struct Mem Mem;
          108  +
    99    109   /*
   100    110   ** Allowed values for Stack.flags
   101    111   */
   102    112   #define STK_Null      0x0001   /* Value is NULL */
   103    113   #define STK_Str       0x0002   /* Value is a string */
   104    114   #define STK_Int       0x0004   /* Value is an integer */
   105    115   #define STK_Real      0x0008   /* Value is a real number */
................................................................................
   129    139     int nSort;          /* Number of slots in apSort[] */
   130    140     Sorter **apSort;    /* An open sorter list */
   131    141     FILE *pFile;        /* At most one open file handler */
   132    142     int nField;         /* Number of file fields */
   133    143     char **azField;     /* Data for each file field */
   134    144     char *zLine;        /* A single line from the input file */
   135    145     int nLineAlloc;     /* Number of spaces allocated for zLine */
          146  +  int nMem;           /* Number of memory locations currently allocated */
          147  +  Mem *aMem;          /* The memory locations */
   136    148   };
   137    149   
   138    150   /*
   139    151   ** Create a new virtual database engine.
   140    152   */
   141    153   Vdbe *sqliteVdbeCreate(Dbbe *pBe){
   142    154     Vdbe *p;
................................................................................
   460    472         sqliteDbbeCloseTable(p->aTab[i].pTable);
   461    473         p->aTab[i].pTable = 0;
   462    474       }
   463    475     }
   464    476     sqliteFree(p->aTab);
   465    477     p->aTab = 0;
   466    478     p->nTable = 0;
          479  +  for(i=0; i<p->nMem; i++){
          480  +    if( p->aMem[i].s.flags & STK_Dyn ){
          481  +      sqliteFree(p->aMem[i].z);
          482  +    }
          483  +  }
          484  +  sqliteFree(p->aMem);
          485  +  p->aMem = 0;
          486  +  p->nMem = 0;
   467    487     for(i=0; i<p->nList; i++){
   468    488       if( p->apList[i] ){
   469    489         sqliteDbbeCloseTempFile(p->pBe, p->apList[i]);
   470    490         p->apList[i] = 0;
   471    491       }
   472    492     }
   473    493     sqliteFree(p->apList);
................................................................................
   532    552   ** this array, then copy and paste it into this file, if you want.
   533    553   */
   534    554   static char *zOpName[] = { 0,
   535    555     "Open",           "Close",          "Fetch",          "New",
   536    556     "Put",            "Distinct",       "Delete",         "Field",
   537    557     "Key",            "Rewind",         "Next",           "Destroy",
   538    558     "Reorganize",     "ResetIdx",       "NextIdx",        "PutIdx",
   539         -  "DeleteIdx",      "ListOpen",       "ListWrite",      "ListRewind",
   540         -  "ListRead",       "ListClose",      "SortOpen",       "SortPut",
   541         -  "SortMakeRec",    "SortMakeKey",    "Sort",           "SortNext",
   542         -  "SortKey",        "SortCallback",   "SortClose",      "FileOpen",
   543         -  "FileRead",       "FileField",      "FileClose",      "MakeRecord",
   544         -  "MakeKey",        "Goto",           "If",             "Halt",
   545         -  "ColumnCount",    "ColumnName",     "Callback",       "Integer",
   546         -  "String",         "Null",           "Pop",            "Dup",
   547         -  "Pull",           "Add",            "AddImm",         "Subtract",
   548         -  "Multiply",       "Divide",         "Min",            "Max",
   549         -  "Like",           "Glob",           "Eq",             "Ne",
   550         -  "Lt",             "Le",             "Gt",             "Ge",
   551         -  "IsNull",         "NotNull",        "Negative",       "And",
   552         -  "Or",             "Not",            "Concat",         "Noop",
          559  +  "DeleteIdx",      "MemLoad",        "MemStore",       "ListOpen",
          560  +  "ListWrite",      "ListRewind",     "ListRead",       "ListClose",
          561  +  "SortOpen",       "SortPut",        "SortMakeRec",    "SortMakeKey",
          562  +  "Sort",           "SortNext",       "SortKey",        "SortCallback",
          563  +  "SortClose",      "FileOpen",       "FileRead",       "FileField",
          564  +  "FileClose",      "MakeRecord",     "MakeKey",        "Goto",
          565  +  "If",             "Halt",           "ColumnCount",    "ColumnName",
          566  +  "Callback",       "Integer",        "String",         "Null",
          567  +  "Pop",            "Dup",            "Pull",           "Add",
          568  +  "AddImm",         "Subtract",       "Multiply",       "Divide",
          569  +  "Min",            "Max",            "Like",           "Glob",
          570  +  "Eq",             "Ne",             "Lt",             "Le",
          571  +  "Gt",             "Ge",             "IsNull",         "NotNull",
          572  +  "Negative",       "And",            "Or",             "Not",
          573  +  "Concat",         "Noop",         
   553    574   };
   554    575   
   555    576   /*
   556    577   ** Given the name of an opcode, return its number.  Return 0 if
   557    578   ** there is no match.
   558    579   **
   559    580   ** This routine is used for testing and debugging.
................................................................................
  2397   2418           if( z==0 ) z = "";
  2398   2419           p->tos++;
  2399   2420           p->aStack[p->tos].n = strlen(z) + 1;
  2400   2421           p->zStack[p->tos] = z;
  2401   2422           p->aStack[p->tos].flags = STK_Str;
  2402   2423           break;
  2403   2424         }
         2425  +
         2426  +      /* Opcode: MemStore P1 * *
         2427  +      **
         2428  +      ** Pop a single value of the stack and store that value into memory
         2429  +      ** location P1.  P1 should be a small integer since space is allocated
         2430  +      ** for all memory locations between 0 and P1 inclusive.
         2431  +      */
         2432  +      case OP_MemStore: {
         2433  +        int i = pOp->p1;
         2434  +        int tos = p->tos;
         2435  +        Mem *pMem;
         2436  +        if( tos<0 ) goto not_enough_stack;
         2437  +        if( i>=p->nMem ){
         2438  +          int nOld = p->nMem;
         2439  +          p->nMem = i + 5;
         2440  +          p->aMem = sqliteRealloc(p->aMem, p->nMem*sizeof(p->aMem[0]));
         2441  +          if( p->aMem==0 ) goto no_mem;
         2442  +          if( nOld<p->nMem ){
         2443  +            memset(&p->aMem[nOld], 0, sizeof(p->aMem[0])*(p->nMem-nOld));
         2444  +          }
         2445  +        }
         2446  +        pMem = &p->aMem[i];
         2447  +        if( pMem->s.flags & STK_Dyn ){
         2448  +          sqliteFree(pMem->z);
         2449  +        }
         2450  +        pMem->s = p->aStack[tos];
         2451  +        if( pMem->s.flags & STK_Str ){
         2452  +          pMem->z = 0;
         2453  +          sqliteSetString(&pMem->z, p->zStack[tos], 0);
         2454  +          pMem->s.flags |= STK_Dyn;
         2455  +        }
         2456  +        PopStack(p, 1);
         2457  +        break;
         2458  +      }
         2459  +
         2460  +      /* Opcode: MemLoad P1 * *
         2461  +      **
         2462  +      ** Push a copy of the value in memory location P1 onto the stack.
         2463  +      */
         2464  +      case OP_MemLoad: {
         2465  +        int tos = ++p->tos;
         2466  +        int i = pOp->p1;
         2467  +        if( NeedStack(p, tos) ) goto no_mem;
         2468  +        if( i<0 || i>=p->nMem ){
         2469  +          p->aStack[tos].flags = STK_Null;
         2470  +          p->zStack[tos] = 0;
         2471  +        }else{
         2472  +          p->aStack[tos] = p->aMem[i].s;
         2473  +          if( p->aStack[tos].flags & STK_Str ){
         2474  +            char *z = sqliteMalloc(p->aStack[tos].n);
         2475  +            if( z==0 ) goto no_mem;
         2476  +            memcpy(z, p->aMem[i].z, p->aStack[tos].n);
         2477  +            p->zStack[tos] = z;
         2478  +            p->aStack[tos].flags |= STK_Dyn;
         2479  +          }
         2480  +        }
         2481  +        break;
         2482  +      }
  2404   2483   
  2405   2484         /* An other opcode is illegal...
  2406   2485         */
  2407   2486         default: {
  2408   2487           sprintf(zBuf,"%d",pOp->opcode);
  2409   2488           sqliteSetString(pzErrMsg, "unknown opcode ", zBuf, 0);
  2410   2489           rc = SQLITE_INTERNAL;

Changes to src/vdbe.h.

    23     23   *************************************************************************
    24     24   ** Header file for the Virtual DataBase Engine (VDBE)
    25     25   **
    26     26   ** This header defines the interface to the virtual database engine
    27     27   ** or VDBE.  The VDBE implements an abstract machine that runs a
    28     28   ** simple program to access and modify the underlying database.
    29     29   **
    30         -** $Id: vdbe.h,v 1.5 2000/06/04 12:58:39 drh Exp $
           30  +** $Id: vdbe.h,v 1.6 2000/06/05 18:54:47 drh Exp $
    31     31   */
    32     32   #ifndef _SQLITE_VDBE_H_
    33     33   #define _SQLITE_VDBE_H_
    34     34   #include <stdio.h>
    35     35   
    36     36   /*
    37     37   ** A single VDBE is an opaque structure named "Vdbe".  Only routines
................................................................................
    87     87   #define OP_Reorganize         13
    88     88   
    89     89   #define OP_ResetIdx           14
    90     90   #define OP_NextIdx            15
    91     91   #define OP_PutIdx             16
    92     92   #define OP_DeleteIdx          17
    93     93   
    94         -#define OP_ListOpen           18
    95         -#define OP_ListWrite          19
    96         -#define OP_ListRewind         20
    97         -#define OP_ListRead           21
    98         -#define OP_ListClose          22
           94  +#define OP_MemLoad            18
           95  +#define OP_MemStore           19
           96  +
           97  +#define OP_ListOpen           20
           98  +#define OP_ListWrite          21
           99  +#define OP_ListRewind         22
          100  +#define OP_ListRead           23
          101  +#define OP_ListClose          24
          102  +
          103  +#define OP_SortOpen           25
          104  +#define OP_SortPut            26
          105  +#define OP_SortMakeRec        27
          106  +#define OP_SortMakeKey        28
          107  +#define OP_Sort               29
          108  +#define OP_SortNext           30
          109  +#define OP_SortKey            31
          110  +#define OP_SortCallback       32
          111  +#define OP_SortClose          33
          112  +
          113  +#define OP_FileOpen           34
          114  +#define OP_FileRead           35
          115  +#define OP_FileField          36
          116  +#define OP_FileClose          37
          117  +
          118  +#define OP_MakeRecord         38
          119  +#define OP_MakeKey            39
          120  +
          121  +#define OP_Goto               40
          122  +#define OP_If                 41
          123  +#define OP_Halt               42
    99    124   
   100         -#define OP_SortOpen           23
   101         -#define OP_SortPut            24
   102         -#define OP_SortMakeRec        25
   103         -#define OP_SortMakeKey        26
   104         -#define OP_Sort               27
   105         -#define OP_SortNext           28
   106         -#define OP_SortKey            29
   107         -#define OP_SortCallback       30
   108         -#define OP_SortClose          31
          125  +#define OP_ColumnCount        43
          126  +#define OP_ColumnName         44
          127  +#define OP_Callback           45
   109    128   
   110         -#define OP_FileOpen           32
   111         -#define OP_FileRead           33
   112         -#define OP_FileField          34
   113         -#define OP_FileClose          35
          129  +#define OP_Integer            46
          130  +#define OP_String             47
          131  +#define OP_Null               48
          132  +#define OP_Pop                49
          133  +#define OP_Dup                50
          134  +#define OP_Pull               51
   114    135   
   115         -#define OP_MakeRecord         36
   116         -#define OP_MakeKey            37
   117         -
   118         -#define OP_Goto               38
   119         -#define OP_If                 39
   120         -#define OP_Halt               40
   121         -
   122         -#define OP_ColumnCount        41
   123         -#define OP_ColumnName         42
   124         -#define OP_Callback           43
   125         -
   126         -#define OP_Integer            44
   127         -#define OP_String             45
   128         -#define OP_Null               46
   129         -#define OP_Pop                47
   130         -#define OP_Dup                48
   131         -#define OP_Pull               49
          136  +#define OP_Add                52
          137  +#define OP_AddImm             53
          138  +#define OP_Subtract           54
          139  +#define OP_Multiply           55
          140  +#define OP_Divide             56
          141  +#define OP_Min                57
          142  +#define OP_Max                58
          143  +#define OP_Like               59
          144  +#define OP_Glob               60
          145  +#define OP_Eq                 61
          146  +#define OP_Ne                 62
          147  +#define OP_Lt                 63
          148  +#define OP_Le                 64
          149  +#define OP_Gt                 65
          150  +#define OP_Ge                 66
          151  +#define OP_IsNull             67
          152  +#define OP_NotNull            68
          153  +#define OP_Negative           69
          154  +#define OP_And                70
          155  +#define OP_Or                 71
          156  +#define OP_Not                72
          157  +#define OP_Concat             73
          158  +#define OP_Noop               74
   132    159   
   133         -#define OP_Add                50
   134         -#define OP_AddImm             51
   135         -#define OP_Subtract           52
   136         -#define OP_Multiply           53
   137         -#define OP_Divide             54
   138         -#define OP_Min                55
   139         -#define OP_Max                56
   140         -#define OP_Like               57
   141         -#define OP_Glob               58
   142         -#define OP_Eq                 59
   143         -#define OP_Ne                 60
   144         -#define OP_Lt                 61
   145         -#define OP_Le                 62
   146         -#define OP_Gt                 63
   147         -#define OP_Ge                 64
   148         -#define OP_IsNull             65
   149         -#define OP_NotNull            66
   150         -#define OP_Negative           67
   151         -#define OP_And                68
   152         -#define OP_Or                 69
   153         -#define OP_Not                70
   154         -#define OP_Concat             71
   155         -#define OP_Noop               72
   156         -
   157         -#define OP_MAX                72
          160  +#define OP_MAX                74
   158    161   
   159    162   /*
   160    163   ** Prototypes for the VDBE interface.  See comments on the implementation
   161    164   ** for a description of what each of these routines does.
   162    165   */
   163    166   Vdbe *sqliteVdbeCreate(Dbbe*);
   164    167   int sqliteVdbeAddOp(Vdbe*,int,int,int,const char*,int);

Changes to src/where.c.

    21     21   **   http://www.hwaci.com/drh/
    22     22   **
    23     23   *************************************************************************
    24     24   ** This module contains C code that generates VDBE code used to process
    25     25   ** the WHERE clause of SQL statements.  Also found here are subroutines
    26     26   ** to generate VDBE code to evaluate expressions.
    27     27   **
    28         -** $Id: where.c,v 1.5 2000/05/31 15:34:54 drh Exp $
           28  +** $Id: where.c,v 1.6 2000/06/05 18:54:47 drh Exp $
    29     29   */
    30     30   #include "sqliteInt.h"
    31     31   
    32     32   /*
    33     33   ** The query generator uses an array of instances of this structure to
    34     34   ** help it analyze the subexpressions of the WHERE clause.  Each WHERE
    35     35   ** clause subexpression is separated from the others by an AND operator.
................................................................................
    82     82   ** a bitmask indicating which tables are used in that expression
    83     83   ** tree.  Bit 0 of the mask is set if table 0 is used.  But 1 is set
    84     84   ** if table 1 is used.  And so forth.
    85     85   **
    86     86   ** In order for this routine to work, the calling function must have
    87     87   ** previously invoked sqliteExprResolveIds() on the expression.  See
    88     88   ** the header comment on that routine for additional information.
           89  +**
           90  +** "base" is the cursor number (the value of the iTable field) that
           91  +** corresponds to the first entry in the table list.  This is the
           92  +** same as pParse->nTab.
    89     93   */
    90         -static int exprTableUsage(Expr *p){
           94  +static int exprTableUsage(int base, Expr *p){
    91     95     unsigned int mask = 0;
    92     96     if( p==0 ) return 0;
    93     97     if( p->op==TK_FIELD ){
    94         -    return 1<<p->iTable;
           98  +    return 1<< (p->iTable - base);
    95     99     }
    96    100     if( p->pRight ){
    97         -    mask = exprTableUsage(p->pRight);
          101  +    mask = exprTableUsage(base, p->pRight);
    98    102     }
    99    103     if( p->pLeft ){
   100         -    mask |= exprTableUsage(p->pLeft);
          104  +    mask |= exprTableUsage(base, p->pLeft);
   101    105     }
   102    106     return mask;
   103    107   }
   104    108   
   105    109   /*
   106    110   ** The input to this routine is an ExprInfo structure with only the
   107    111   ** "p" field filled in.  The job of this routine is to analyze the
   108    112   ** subexpression and populate all the other fields of the ExprInfo
   109    113   ** structure.
          114  +**
          115  +** "base" is the cursor number (the value of the iTable field) that
          116  +** corresponds to the first entyr in the table list.  This is the
          117  +** same as pParse->nTab.
   110    118   */
   111         -static void exprAnalyze(ExprInfo *pInfo){
          119  +static void exprAnalyze(int base, ExprInfo *pInfo){
   112    120     Expr *pExpr = pInfo->p;
   113         -  pInfo->prereqLeft = exprTableUsage(pExpr->pLeft);
   114         -  pInfo->prereqRight = exprTableUsage(pExpr->pRight);
          121  +  pInfo->prereqLeft = exprTableUsage(base, pExpr->pLeft);
          122  +  pInfo->prereqRight = exprTableUsage(base, pExpr->pRight);
   115    123     pInfo->indexable = 0;
   116    124     pInfo->idxLeft = -1;
   117    125     pInfo->idxRight = -1;
   118    126     if( pExpr->op==TK_EQ && (pInfo->prereqRight & pInfo->prereqLeft)==0 ){
   119    127       if( pExpr->pRight->op==TK_FIELD ){
   120         -      pInfo->idxRight = pExpr->pRight->iTable;
          128  +      pInfo->idxRight = pExpr->pRight->iTable - base;
   121    129         pInfo->indexable = 1;
   122    130       }
   123    131       if( pExpr->pLeft->op==TK_FIELD ){
   124         -      pInfo->idxLeft = pExpr->pLeft->iTable;
          132  +      pInfo->idxLeft = pExpr->pLeft->iTable - base;
   125    133         pInfo->indexable = 1;
   126    134       }
   127    135     }
   128    136   }
   129    137   
   130    138   /*
   131    139   ** Generating the beginning of the loop used for WHERE clause processing.
................................................................................
   146    154     WhereInfo *pWInfo;         /* Will become the return value of this function */
   147    155     Vdbe *v = pParse->pVdbe;   /* The virtual database engine */
   148    156     int brk, cont;             /* Addresses used during code generation */
   149    157     int *aOrder;         /* Order in which pTabList entries are searched */
   150    158     int nExpr;           /* Number of subexpressions in the WHERE clause */
   151    159     int loopMask;        /* One bit set for each outer loop */
   152    160     int haveKey;         /* True if KEY is on the stack */
          161  +  int base;            /* First available index for OP_Open opcodes */
   153    162     Index *aIdx[32];     /* Index to use on each nested loop.  */
   154    163     ExprInfo aExpr[50];  /* The WHERE clause is divided into these expressions */
   155    164   
   156    165     /* Allocate space for aOrder[]. */
   157    166     aOrder = sqliteMalloc( sizeof(int) * pTabList->nId );
   158    167   
   159    168     /* Allocate and initialize the WhereInfo structure that will become the
................................................................................
   162    171     pWInfo = sqliteMalloc( sizeof(WhereInfo) );
   163    172     if( pWInfo==0 ){
   164    173       sqliteFree(aOrder);
   165    174       return 0;
   166    175     }
   167    176     pWInfo->pParse = pParse;
   168    177     pWInfo->pTabList = pTabList;
          178  +  base = pWInfo->base = pParse->nTab;
   169    179   
   170    180     /* Split the WHERE clause into as many as 32 separate subexpressions
   171    181     ** where each subexpression is separated by an AND operator.  Any additional
   172    182     ** subexpressions are attached in the aExpr[32] and will not enter
   173    183     ** into the query optimizer computations.  32 is chosen as the cutoff
   174    184     ** since that is the number of bits in an integer that we use for an
   175    185     ** expression-used mask.  
................................................................................
   176    186     */
   177    187     memset(aExpr, 0, sizeof(aExpr));
   178    188     nExpr = exprSplit(ARRAYSIZE(aExpr), aExpr, pWhere);
   179    189   
   180    190     /* Analyze all of the subexpressions.
   181    191     */
   182    192     for(i=0; i<nExpr; i++){
   183         -    exprAnalyze(&aExpr[i]);
          193  +    exprAnalyze(pParse->nTab, &aExpr[i]);
   184    194     }
   185    195   
   186    196     /* Figure out a good nesting order for the tables.  aOrder[0] will
   187    197     ** be the index in pTabList of the outermost table.  aOrder[1] will
   188    198     ** be the first nested loop and so on.  aOrder[pTabList->nId-1] will
   189    199     ** be the innermost loop.
   190    200     **
................................................................................
   257    267       aIdx[i] = pBestIdx;
   258    268       loopMask |= 1<<idx;
   259    269     }
   260    270   
   261    271     /* Open all tables in the pTabList and all indices in aIdx[].
   262    272     */
   263    273     for(i=0; i<pTabList->nId; i++){
   264         -    sqliteVdbeAddOp(v, OP_Open, i, 0, pTabList->a[i].pTab->zName, 0);
          274  +    sqliteVdbeAddOp(v, OP_Open, base+i, 0, pTabList->a[i].pTab->zName, 0);
   265    275       if( i<ARRAYSIZE(aIdx) && aIdx[i]!=0 ){
   266         -      sqliteVdbeAddOp(v, OP_Open, pTabList->nId+i, 0, aIdx[i]->zName, 0);
          276  +      sqliteVdbeAddOp(v, OP_Open, base+pTabList->nId+i, 0, aIdx[i]->zName, 0);
   267    277       }
   268    278     }
          279  +  memcpy(pWInfo->aIdx, aIdx, sizeof(aIdx));
   269    280   
   270    281     /* Generate the code to do the search
   271    282     */
   272    283     pWInfo->iBreak = brk = sqliteVdbeMakeLabel(v);
   273    284     loopMask = 0;
   274    285     for(i=0; i<pTabList->nId; i++){
   275    286       int j, k;
................................................................................
   277    288       Index *pIdx = i<ARRAYSIZE(aIdx) ? aIdx[i] : 0;
   278    289   
   279    290       cont = sqliteVdbeMakeLabel(v);
   280    291       if( pIdx==0 ){
   281    292         /* Case 1:  There was no usable index.  We must do a complete
   282    293         ** scan of the table.
   283    294         */
   284         -      sqliteVdbeAddOp(v, OP_Next, idx, brk, 0, cont);
          295  +      sqliteVdbeAddOp(v, OP_Next, base+idx, brk, 0, cont);
   285    296         haveKey = 0;
   286    297       }else{
   287    298         /* Case 2:  We do have a usable index in pIdx.
   288    299         */
   289    300         for(j=0; j<pIdx->nField; j++){
   290    301           for(k=0; k<nExpr; k++){
   291    302             if( aExpr[k].p==0 ) continue;
................................................................................
   304    315               sqliteExprCode(pParse, aExpr[k].p->pLeft);
   305    316               aExpr[k].p = 0;
   306    317               break;
   307    318             }
   308    319           }
   309    320         }
   310    321         sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nField, 0, 0, 0);
   311         -      sqliteVdbeAddOp(v, OP_Fetch, pTabList->nId+i, 0, 0, 0);
   312         -      sqliteVdbeAddOp(v, OP_NextIdx, pTabList->nId+i, brk, 0, cont);
          322  +      sqliteVdbeAddOp(v, OP_Fetch, base+pTabList->nId+i, 0, 0, 0);
          323  +      sqliteVdbeAddOp(v, OP_NextIdx, base+pTabList->nId+i, brk, 0, cont);
   313    324         if( i==pTabList->nId-1 && pushKey ){
   314    325           haveKey = 1;
   315    326         }else{
   316    327           sqliteVdbeAddOp(v, OP_Fetch, idx, 0, 0, 0);
   317    328           haveKey = 0;
   318    329         }
   319    330       }
................................................................................
   323    334       ** computed using the current set of tables.
   324    335       */
   325    336       for(j=0; j<nExpr; j++){
   326    337         if( aExpr[j].p==0 ) continue;
   327    338         if( (aExpr[j].prereqRight & loopMask)!=aExpr[j].prereqRight ) continue;
   328    339         if( (aExpr[j].prereqLeft & loopMask)!=aExpr[j].prereqLeft ) continue;
   329    340         if( haveKey ){
   330         -        sqliteVdbeAddOp(v, OP_Fetch, idx, 0, 0, 0);
          341  +        sqliteVdbeAddOp(v, OP_Fetch, base+idx, 0, 0, 0);
   331    342           haveKey = 0;
   332    343         }
   333    344         sqliteExprIfFalse(pParse, aExpr[j].p, cont);
   334    345         aExpr[j].p = 0;
   335    346       }
   336    347       brk = cont;
   337    348     }
................................................................................
   344    355   }
   345    356   
   346    357   /*
   347    358   ** Generate the end of the WHERE loop.
   348    359   */
   349    360   void sqliteWhereEnd(WhereInfo *pWInfo){
   350    361     Vdbe *v = pWInfo->pParse->pVdbe;
          362  +  int i;
          363  +  int brk = pWInfo->iBreak;
          364  +  int base = pWInfo->base;
          365  +
   351    366     sqliteVdbeAddOp(v, OP_Goto, 0, pWInfo->iContinue, 0, 0);
   352         -  sqliteVdbeAddOp(v, OP_Noop, 0, 0, 0, pWInfo->iBreak);
          367  +  for(i=0; i<pWInfo->pTabList->nId; i++){
          368  +    sqliteVdbeAddOp(v, OP_Close, base+i, 0, 0, brk);
          369  +    brk = 0;
          370  +    if( i<ARRAYSIZE(pWInfo->aIdx) && pWInfo->aIdx[i]!=0 ){
          371  +      sqliteVdbeAddOp(v, OP_Close, base+pWInfo->pTabList->nId+i, 0, 0, 0);
          372  +    }
          373  +  }
          374  +  if( brk!=0 ){
          375  +    sqliteVdbeAddOp(v, OP_Noop, 0, 0, 0, brk);
          376  +  }
   353    377     sqliteFree(pWInfo);
   354    378     return;
   355    379   }

Changes to test/copy.test.

    19     19   #   drh@hwaci.com
    20     20   #   http://www.hwaci.com/drh/
    21     21   #
    22     22   #***********************************************************************
    23     23   # This file implements regression tests for SQLite library.  The
    24     24   # focus of this file is testing the COPY statement.
    25     25   #
    26         -# $Id: copy.test,v 1.3 2000/06/05 02:07:05 drh Exp $
           26  +# $Id: copy.test,v 1.4 2000/06/05 18:54:47 drh Exp $
    27     27   
    28     28   set testdir [file dirname $argv0]
    29     29   source $testdir/tester.tcl
    30     30   
    31     31   # Create a file of data from which to copy.
    32     32   #
    33     33   set f [open data1.txt w]
................................................................................
    77     77   # Make sure input terminates at \.
    78     78   #
    79     79   do_test copy-1.4 {
    80     80     execsql {DELETE FROM test1}
    81     81     execsql {COPY test1 FROM 'data2.txt'}
    82     82     execsql {SELECT * FROM test1 ORDER BY one}
    83     83   } {11 22 33}
    84         -return
    85     84   
    86     85   # Test out the USING DELIMITERS clause
    87     86   #
    88     87   do_test copy-1.5 {
    89     88     execsql {DELETE FROM test1}
    90     89     execsql {COPY test1 FROM 'data4.txt' USING DELIMITERS ' | '}
    91     90     execsql {SELECT * FROM test1 ORDER BY one}

Changes to test/select2.test.

    19     19   #   drh@hwaci.com
    20     20   #   http://www.hwaci.com/drh/
    21     21   #
    22     22   #***********************************************************************
    23     23   # This file implements regression tests for SQLite library.  The
    24     24   # focus of this file is testing the SELECT statement.
    25     25   #
    26         -# $Id: select2.test,v 1.4 2000/06/02 15:05:33 drh Exp $
           26  +# $Id: select2.test,v 1.5 2000/06/05 18:54:47 drh Exp $
    27     27   
    28     28   set testdir [file dirname $argv0]
    29     29   source $testdir/tester.tcl
    30     30   
    31     31   # Create a table with some data
    32     32   #
    33     33   execsql {CREATE TABLE tbl1(f1 int, f2 int)}
................................................................................
    67     67       }
    68     68     }
    69     69     set r
    70     70   } {4: 2 3 4}
    71     71   
    72     72   # Create a largish table
    73     73   #
    74         -execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int)}
    75         -set f [open ./testdata1.txt w]
    76         -for {set i 1} {$i<=30000} {incr i} {
    77         -  puts $f "$i\t[expr {$i*2}]\t[expr {$i*3}]"
    78         -}
    79         -close $f
    80         -execsql {COPY tbl2 FROM './testdata1.txt'}
    81         -file delete -force ./testdata1.txt
           74  +do_test select2-2.0 {
           75  +  execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int)}
           76  +  set f [open ./testdata1.txt w]
           77  +  for {set i 1} {$i<=30000} {incr i} {
           78  +    puts $f "$i\t[expr {$i*2}]\t[expr {$i*3}]"
           79  +  }
           80  +  close $f
           81  +  execsql {COPY tbl2 FROM './testdata1.txt'}
           82  +  file delete -force ./testdata1.txt
           83  +} {}
    82     84   
    83     85   do_test select2-2.1 {
    84     86     execsql {SELECT count(*) FROM tbl2}
    85     87   } {30000}
    86     88   do_test select2-2.2 {
    87     89     execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
    88     90   } {29500}
    89     91   
    90     92   do_test select2-3.1 {
    91     93     execsql {SELECT f1 FROM tbl2 WHERE f2==1000}
    92     94   } {500}
    93     95   
    94         -execsql {CREATE INDEX idx1 ON tbl2(f2)}
           96  +do_test select2-3.2a {
           97  +  execsql {CREATE INDEX idx1 ON tbl2(f2)}
           98  +} {}
    95     99   
    96         -do_test select2-3.2 {
          100  +do_test select2-3.2b {
    97    101     execsql {SELECT f1 FROM tbl2 WHERE f2==1000}
    98    102   } {500}
    99    103   
   100    104   # Make sure queries run faster with an index than without
   101    105   #
   102    106   do_test select2-3.3 {
   103    107     set t1 [lindex [time {execsql {SELECT f1 from tbl2 WHERE f2==2000}} 1] 0]
   104    108     execsql {DROP INDEX idx1}
   105    109     set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2==2000}} 1] 0]
   106    110     expr {$t1*25 < $t2}
   107    111   } {1}
   108    112   
   109    113   finish_test

Added test/subselect.test.

            1  +# Copyright (c) 1999, 2000 D. Richard Hipp
            2  +#
            3  +# This program is free software; you can redistribute it and/or
            4  +# modify it under the terms of the GNU General Public
            5  +# License as published by the Free Software Foundation; either
            6  +# version 2 of the License, or (at your option) any later version.
            7  +#
            8  +# This program is distributed in the hope that it will be useful,
            9  +# but WITHOUT ANY WARRANTY; without even the implied warranty of
           10  +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
           11  +# General Public License for more details.
           12  +# 
           13  +# You should have received a copy of the GNU General Public
           14  +# License along with this library; if not, write to the
           15  +# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
           16  +# Boston, MA  02111-1307, USA.
           17  +#
           18  +# Author contact information:
           19  +#   drh@hwaci.com
           20  +#   http://www.hwaci.com/drh/
           21  +#
           22  +#***********************************************************************
           23  +# This file implements regression tests for SQLite library.  The
           24  +# focus of this file is testing SELECT statements that are part of
           25  +# expressions.
           26  +#
           27  +# $Id: subselect.test,v 1.1 2000/06/05 18:54:47 drh Exp $
           28  +
           29  +set testdir [file dirname $argv0]
           30  +source $testdir/tester.tcl
           31  +
           32  +# Basic sanity checking.  Try a simple subselect.
           33  +#
           34  +do_test subselect-1.1 {
           35  +  execsql {
           36  +    CREATE TABLE t1(a int, b int);
           37  +    INSERT INTO t1 VALUES(1,2);
           38  +    INSERT INTO t1 VALUES(3,4);
           39  +    INSERT INTO t1 VALUES(5,6);
           40  +  }
           41  +  execsql {SELECT * FROM t1 WHERE a = (SELECT count(*) FROM t1)}
           42  +} {3 4}
           43  +
           44  +# Try a select with more than one result column.
           45  +#
           46  +do_test subselect-1.2 {
           47  +  set v [catch {execsql {SELECT * FROM t1 WHERE a = (SELECT * FROM t1)}} msg]
           48  +  lappend v $msg
           49  +} {1 {only a single result allowed for a SELECT this is part of an expression}}
           50  +
           51  +# A subselect without an aggregate.
           52  +#
           53  +do_test subselect-1.3a {
           54  +  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=2)}
           55  +} {2}
           56  +do_test subselect-1.3b {
           57  +  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=4)}
           58  +} {4}
           59  +do_test subselect-1.3c {
           60  +  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=6)}
           61  +} {6}
           62  +do_test subselect-1.3c {
           63  +  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=8)}
           64  +} {}
           65  +
           66  +# What if the subselect doesn't return any value.  We should get
           67  +# NULL as the result.  Check it out.
           68  +#
           69  +do_test subselect-1.4 {
           70  +  execsql {INSERT INTO t1 VALUES(NULL,8)}
           71  +  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=5)}
           72  +} {8}
           73  +
           74  +# Try multiple subselects within a single expression.
           75  +#
           76  +do_test subselect-1.5 {
           77  +  execsql {
           78  +    CREATE TABLE t2(x int, y int);
           79  +    INSERT INTO t2 VALUES(1,2);
           80  +    INSERT INTO t2 VALUES(2,4);
           81  +    INSERT INTO t2 VALUES(3,8);
           82  +    INSERT INTO t2 VALUES(4,16);
           83  +  }
           84  +  execsql {
           85  +    SELECT y from t2 
           86  +    WHERE x = (SELECT sum(b) FROM t1 where a notnull) - (SELECT sum(a) FROM t1)
           87  +  }
           88  +} {8}
           89  +
           90  +finish_test