/ Check-in [93710f7e]
Login

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

Overview
Comment:Fix for ticket #100: Correctly handle ON and USING clauses of JOINs within a VIEW. (CVS 679)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:93710f7ed7e1baa6acbf4bc32982e046f61ffa44
User & Date: drh 2002-07-16 02:05:44
Context
2002-07-16
17:22
Fix for ticket #105: Fix the UPDATE command so that it works properly with indexed tables when there is a subquery in the WHERE clause. Add tests to verify correct operation. (CVS 680) check-in: bbca16f8 user: drh tags: trunk
02:05
Fix for ticket #100: Correctly handle ON and USING clauses of JOINs within a VIEW. (CVS 679) check-in: 93710f7e user: drh tags: trunk
2002-07-15
20:58
Fix a syntax error in the tclsqlite.c file. (CVS 678) check-in: 47997d7f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains routines used for analyzing expressions and
    13     13   ** for generating VDBE code that evaluates expressions in SQLite.
    14     14   **
    15         -** $Id: expr.c,v 1.77 2002/07/02 13:05:05 drh Exp $
           15  +** $Id: expr.c,v 1.78 2002/07/16 02:05:44 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   #include <ctype.h>
    19     19   
    20     20   /*
    21     21   ** Construct a new expression node and return a pointer to it.  Memory
    22     22   ** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
   132    132   }
   133    133   void sqliteExprListMoveStrings(ExprList *pList, int offset){
   134    134     int i;
   135    135     if( pList==0 ) return;
   136    136     for(i=0; i<pList->nExpr; i++){
   137    137       sqliteExprMoveStrings(pList->a[i].pExpr, offset);
   138    138     }
          139  +}
          140  +static void sqliteSrcListMoveStrings(SrcList *pSrc, int offset){
          141  +  int i;
          142  +  if( pSrc==0 ) return;
          143  +  for(i=0; i<pSrc->nSrc; i++){
          144  +    sqliteSelectMoveStrings(pSrc->a[i].pSelect, offset);
          145  +    sqliteExprMoveStrings(pSrc->a[i].pOn, offset);
          146  +  }
   139    147   }
   140    148   void sqliteSelectMoveStrings(Select *pSelect, int offset){
   141    149     if( pSelect==0 ) return;
   142    150     sqliteExprListMoveStrings(pSelect->pEList, offset);
          151  +  sqliteSrcListMoveStrings(pSelect->pSrc, offset);
   143    152     sqliteExprMoveStrings(pSelect->pWhere, offset);
   144    153     sqliteExprListMoveStrings(pSelect->pGroupBy, offset);
   145    154     sqliteExprMoveStrings(pSelect->pHaving, offset);
   146    155     sqliteExprListMoveStrings(pSelect->pOrderBy, offset);
   147    156     sqliteSelectMoveStrings(pSelect->pPrior, offset);
   148    157   }
   149    158   

Changes to test/view.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing VIEW statements.
    13     13   #
    14         -# $Id: view.test,v 1.7 2002/07/02 13:05:05 drh Exp $
           14  +# $Id: view.test,v 1.8 2002/07/16 02:05:45 drh Exp $
    15     15   set testdir [file dirname $argv0]
    16     16   source $testdir/tester.tcl
    17     17   
    18     18   do_test view-1.0 {
    19     19     execsql {
    20     20       CREATE TABLE t1(a,b,c);
    21     21       INSERT INTO t1 VALUES(1,2,3);
................................................................................
   209    209   } {7 8 9 10 27}
   210    210   do_test view-6.2 {
   211    211     execsql {
   212    212       SELECT max(x), max(a), max(b), max(c), max(a+b+c) FROM v2;
   213    213     }
   214    214   } {11 12 13 14 39}
   215    215   
          216  +do_test view-7.1 {
          217  +  execsql {
          218  +    CREATE TABLE test1(id integer primary key, a);
          219  +    CREATE TABLE test2(id integer, b);
          220  +    INSERT INTO test1 VALUES(1,2);
          221  +    INSERT INTO test2 VALUES(1,3);
          222  +    CREATE VIEW test AS
          223  +      SELECT test1.id, a, b
          224  +      FROM test1 JOIN test2 ON test2.id=test1.id;
          225  +    SELECT * FROM test;
          226  +  }
          227  +} {1 2 3}
          228  +do_test view-7.2 {
          229  +  db close
          230  +  sqlite db test.db
          231  +  execsql {
          232  +    SELECT * FROM test;
          233  +  }
          234  +} {1 2 3}
          235  +do_test view-7.3 {
          236  +  execsql {
          237  +    DROP VIEW test;
          238  +    CREATE VIEW test AS
          239  +      SELECT test1.id, a, b
          240  +      FROM test1 JOIN test2 USING(id);
          241  +    SELECT * FROM test;
          242  +  }
          243  +} {1 2 3}
          244  +do_test view-7.4 {
          245  +  db close
          246  +  sqlite db test.db
          247  +  execsql {
          248  +    SELECT * FROM test;
          249  +  }
          250  +} {1 2 3}
          251  +do_test view-7.5 {
          252  +  execsql {
          253  +    DROP VIEW test;
          254  +    CREATE VIEW test AS
          255  +      SELECT test1.id, a, b
          256  +      FROM test1 NATURAL JOIN test2;
          257  +    SELECT * FROM test;
          258  +  }
          259  +} {1 2 3}
          260  +do_test view-7.6 {
          261  +  db close
          262  +  sqlite db test.db
          263  +  execsql {
          264  +    SELECT * FROM test;
          265  +  }
          266  +} {1 2 3}
   216    267   
   217    268   finish_test