/ Check-in [3626f6d4]
Login

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

Overview
Comment:Add the ability to group FROM terms using parentheses. Names of columns in a join no longer include the table name. (CVS 1197)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3626f6d4a1adb4209d5bd9e6477343b52bddbdf2
User & Date: drh 2004-01-24 20:18:13
Context
2004-01-25
22:44
Modularize the column name resolution code so that it is smaller, faster, and ready for some enhancements that will fix long-standing name resolutions problems. (CVS 1198) check-in: d3648034 user: drh tags: trunk
2004-01-24
20:18
Add the ability to group FROM terms using parentheses. Names of columns in a join no longer include the table name. (CVS 1197) check-in: 3626f6d4 user: drh tags: trunk
2004-01-22
23:38
Fix typo. Ticket #577. (CVS 1196) check-in: 3dc95195 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains SQLite's grammar for SQL.  Process this file
    13     13   ** using the lemon parser generator to generate C code that runs
    14     14   ** the parser.  Lemon will also generate a header file containing
    15     15   ** numeric codes for all of the tokens.
    16     16   **
    17         -** @(#) $Id: parse.y,v 1.106 2004/01/15 03:30:25 drh Exp $
           17  +** @(#) $Id: parse.y,v 1.107 2004/01/24 20:18:13 drh Exp $
    18     18   */
    19     19   %token_prefix TK_
    20     20   %token_type {Token}
    21     21   %default_type {Token}
    22     22   %extra_argument {Parse *pParse}
    23     23   %syntax_error {
    24     24     if( pParse->zErrMsg==0 ){
................................................................................
   362    362       else { sqliteExprDelete(N); }
   363    363     }
   364    364     if( U ){
   365    365       if( A && A->nSrc>1 ){ A->a[A->nSrc-2].pUsing = U; }
   366    366       else { sqliteIdListDelete(U); }
   367    367     }
   368    368   }
   369         -seltablist(A) ::= stl_prefix(X) LP select(S) RP as(Z) on_opt(N) using_opt(U). {
          369  +seltablist(A) ::= stl_prefix(X) LP seltablist_paren(S) RP
          370  +                  as(Z) on_opt(N) using_opt(U). {
   370    371     A = sqliteSrcListAppend(X,0,0);
   371    372     A->a[A->nSrc-1].pSelect = S;
   372    373     if( Z.n ) sqliteSrcListAddAlias(A,&Z);
   373    374     if( N ){
   374    375       if( A && A->nSrc>1 ){ A->a[A->nSrc-2].pOn = N; }
   375    376       else { sqliteExprDelete(N); }
   376    377     }
   377    378     if( U ){
   378    379       if( A && A->nSrc>1 ){ A->a[A->nSrc-2].pUsing = U; }
   379    380       else { sqliteIdListDelete(U); }
   380    381     }
   381    382   }
          383  +
          384  +// A seltablist_paren nonterminal represents anything in a FROM that
          385  +// is contained inside parentheses.  This can be either a subquery or
          386  +// a grouping of table and subqueries.
          387  +//
          388  +%type seltablist_paren {Select*}
          389  +%destructor seltablist_paren {sqliteSelectDelete($$);}
          390  +seltablist_paren(A) ::= select(S).      {A = S;}
          391  +seltablist_paren(A) ::= seltablist(F).  {
          392  +   A = sqliteSelectNew(0,F,0,0,0,0,0,-1,0);
          393  +}
   382    394   
   383    395   %type dbnm {Token}
   384    396   dbnm(A) ::= .          {A.z=0; A.n=0;}
   385    397   dbnm(A) ::= DOT nm(X). {A = X;}
   386    398   
   387    399   %type joinop {int}
   388    400   %type joinop2 {int}
................................................................................
   662    674     A = sqliteExpr(TK_IN, X, 0, 0);
   663    675     if( A ) A->pSelect = Y;
   664    676     A = sqliteExpr(TK_NOT, A, 0, 0);
   665    677     sqliteExprSpan(A,&X->span,&E);
   666    678   }
   667    679   expr(A) ::= expr(X) IN nm(Y) dbnm(D). {
   668    680     SrcList *pSrc = sqliteSrcListAppend(0, &Y, &D);
   669         -  ExprList *pList = sqliteExprListAppend(0, sqliteExpr(TK_ALL,0,0,0), 0);
   670    681     A = sqliteExpr(TK_IN, X, 0, 0);
   671         -  if( A ) A->pSelect = sqliteSelectNew(pList,pSrc,0,0,0,0,0,-1,0);
          682  +  if( A ) A->pSelect = sqliteSelectNew(0,pSrc,0,0,0,0,0,-1,0);
   672    683     sqliteExprSpan(A,&X->span,D.z?&D:&Y);
   673    684   }
   674    685   expr(A) ::= expr(X) NOT IN nm(Y) dbnm(D). {
   675    686     SrcList *pSrc = sqliteSrcListAppend(0, &Y, &D);
   676         -  ExprList *pList = sqliteExprListAppend(0, sqliteExpr(TK_ALL,0,0,0), 0);
   677    687     A = sqliteExpr(TK_IN, X, 0, 0);
   678         -  if( A ) A->pSelect = sqliteSelectNew(pList,pSrc,0,0,0,0,0,-1,0);
          688  +  if( A ) A->pSelect = sqliteSelectNew(0,pSrc,0,0,0,0,0,-1,0);
   679    689     A = sqliteExpr(TK_NOT, A, 0, 0);
   680    690     sqliteExprSpan(A,&X->span,D.z?&D:&Y);
   681    691   }
   682    692   
   683    693   
   684    694   /* CASE expressions */
   685    695   expr(A) ::= CASE(C) case_operand(X) case_exprlist(Y) case_else(Z) END(E). {

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.148 2004/01/19 04:57:53 jplyon Exp $
           15  +** $Id: select.c,v 1.149 2004/01/24 20:18:13 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Allocate a new Select structure and return a pointer to that
    22     22   ** structure.
................................................................................
    38     38       sqliteExprListDelete(pEList);
    39     39       sqliteSrcListDelete(pSrc);
    40     40       sqliteExprDelete(pWhere);
    41     41       sqliteExprListDelete(pGroupBy);
    42     42       sqliteExprDelete(pHaving);
    43     43       sqliteExprListDelete(pOrderBy);
    44     44     }else{
           45  +    if( pEList==0 ){
           46  +      pEList = sqliteExprListAppend(0, sqliteExpr(TK_ALL,0,0,0), 0);
           47  +    }
    45     48       pNew->pEList = pEList;
    46     49       pNew->pSrc = pSrc;
    47     50       pNew->pWhere = pWhere;
    48     51       pNew->pGroupBy = pGroupBy;
    49     52       pNew->pHaving = pHaving;
    50     53       pNew->pOrderBy = pOrderBy;
    51     54       pNew->isDistinct = isDistinct;
................................................................................
   773    776   
   774    777   /*
   775    778   ** Given a SELECT statement, generate a Table structure that describes
   776    779   ** the result set of that SELECT.
   777    780   */
   778    781   Table *sqliteResultSetOfSelect(Parse *pParse, char *zTabName, Select *pSelect){
   779    782     Table *pTab;
   780         -  int i;
          783  +  int i, j;
   781    784     ExprList *pEList;
          785  +  Column *aCol;
   782    786   
   783    787     if( fillInColumnList(pParse, pSelect) ){
   784    788       return 0;
   785    789     }
   786    790     pTab = sqliteMalloc( sizeof(Table) );
   787    791     if( pTab==0 ){
   788    792       return 0;
   789    793     }
   790    794     pTab->zName = zTabName ? sqliteStrDup(zTabName) : 0;
   791    795     pEList = pSelect->pEList;
   792    796     pTab->nCol = pEList->nExpr;
   793    797     assert( pTab->nCol>0 );
   794         -  pTab->aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
          798  +  pTab->aCol = aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
   795    799     for(i=0; i<pTab->nCol; i++){
   796         -    Expr *p;
          800  +    Expr *p, *pR;
   797    801       if( pEList->a[i].zName ){
   798         -      pTab->aCol[i].zName = sqliteStrDup(pEList->a[i].zName);
   799         -    }else if( (p=pEList->a[i].pExpr)->span.z && p->span.z[0] ){
          802  +      aCol[i].zName = sqliteStrDup(pEList->a[i].zName);
          803  +    }else if( (p=pEList->a[i].pExpr)->op==TK_DOT 
          804  +               && (pR=p->pRight)!=0 && pR->token.z && pR->token.z[0] ){
          805  +      int cnt;
          806  +      sqliteSetNString(&aCol[i].zName, pR->token.z, pR->token.n, 0);
          807  +      for(j=cnt=0; j<i; j++){
          808  +        if( sqliteStrICmp(aCol[j].zName, aCol[i].zName)==0 ){
          809  +          int n;
          810  +          char zBuf[30];
          811  +          sprintf(zBuf,"_%d",++cnt);
          812  +          n = strlen(zBuf);
          813  +          sqliteSetNString(&aCol[i].zName, pR->token.z, pR->token.n, zBuf, n,0);
          814  +          j = -1;
          815  +        }
          816  +      }
          817  +    }else if( p->span.z && p->span.z[0] ){
   800    818         sqliteSetNString(&pTab->aCol[i].zName, p->span.z, p->span.n, 0);
   801         -    }else if( p->op==TK_DOT && p->pRight && p->pRight->token.z &&
   802         -           p->pRight->token.z[0] ){
   803         -      sqliteSetNString(&pTab->aCol[i].zName, 
   804         -           p->pRight->token.z, p->pRight->token.n, 0);
   805    819       }else{
   806    820         char zBuf[30];
   807    821         sprintf(zBuf, "column%d", i+1);
   808    822         pTab->aCol[i].zName = sqliteStrDup(zBuf);
   809    823       }
   810    824     }
   811    825     pTab->iPKey = -1;

Added test/join2.test.

            1  +# 2002 May 24
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +# This file implements tests for joins, including outer joins.
           14  +#
           15  +# $Id: join2.test,v 1.1 2004/01/24 20:18:13 drh Exp $
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +do_test join2-1.1 {
           21  +  execsql {
           22  +    CREATE TABLE t1(a,b);
           23  +    INSERT INTO t1 VALUES(1,11);
           24  +    INSERT INTO t1 VALUES(2,22);
           25  +    INSERT INTO t1 VALUES(3,33);
           26  +    SELECT * FROM t1;
           27  +  }  
           28  +} {1 11 2 22 3 33}
           29  +do_test join2-1.2 {
           30  +  execsql {
           31  +    CREATE TABLE t2(b,c);
           32  +    INSERT INTO t2 VALUES(11,111);
           33  +    INSERT INTO t2 VALUES(33,333);
           34  +    INSERT INTO t2 VALUES(44,444);
           35  +    SELECT * FROM t2;
           36  +  }  
           37  +} {11 111 33 333 44 444};
           38  +do_test join2-1.3 {
           39  +  execsql {
           40  +    CREATE TABLE t3(c,d);
           41  +    INSERT INTO t3 VALUES(111,1111);
           42  +    INSERT INTO t3 VALUES(444,4444);
           43  +    INSERT INTO t3 VALUES(555,5555);
           44  +    SELECT * FROM t3;
           45  +  }  
           46  +} {111 1111 444 4444 555 5555}
           47  +
           48  +do_test join2-1.4 {
           49  +  execsql {
           50  +    SELECT * FROM
           51  +      t1 NATURAL JOIN t2 NATURAL JOIN t3
           52  +  }
           53  +} {1 11 111 1111}
           54  +do_test join2-1.5 {
           55  +  execsql {
           56  +    SELECT * FROM
           57  +      t1 NATURAL JOIN t2 NATURAL LEFT OUTER JOIN t3
           58  +  }
           59  +} {1 11 111 1111 3 33 333 {}}
           60  +do_test join2-1.6 {
           61  +  execsql {
           62  +    SELECT * FROM
           63  +      t1 NATURAL LEFT OUTER JOIN t2 NATURAL JOIN t3
           64  +  }
           65  +} {1 11 111 1111}
           66  +do_test join2-1.6 {
           67  +  execsql {
           68  +    SELECT * FROM
           69  +      t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3)
           70  +  }
           71  +} {1 11 111 1111 2 22 {} {} 3 33 {} {}}
           72  +
           73  +finish_test

Changes to test/select6.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing SELECT statements that contain
    13     13   # subqueries in their FROM clause.
    14     14   #
    15         -# $Id: select6.test,v 1.10 2003/05/02 16:44:25 drh Exp $
           15  +# $Id: select6.test,v 1.11 2004/01/24 20:18:13 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   do_test select6-1.0 {
    21     21     execsql {
    22     22       BEGIN;
................................................................................
   159    159   do_test sqlite6-3.2 {
   160    160     execsql {
   161    161       SELECT * FROM
   162    162         (SELECT a.q, a.p, b.r
   163    163          FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
   164    164               (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
   165    165          WHERE a.q=b.s ORDER BY a.q)
   166         -    ORDER BY [a.q]
          166  +    ORDER BY q
   167    167     }
   168    168   } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
   169    169   do_test select6-3.3 {
   170    170     execsql {
   171    171       SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
   172    172     }
   173    173   } {10.5 3.7 14.2}