/ Check-in [06d206ef]
Login

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

Overview
Comment:Expand table.* properly on a USING or a NATURAL join. Ticket #3522. (CVS 5979)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 06d206ef7d5e433ccde347d63dfcd2177545e1fd
User & Date: drh 2008-12-05 00:00:07
Context
2008-12-05
02:36
Variable name changes in the query optimizer for disambiguation and clarification. Clear space in boolean vectors for new bit values to encode new query plan templates. (CVS 5980) check-in: 81bd0b5c user: drh tags: trunk
00:00
Expand table.* properly on a USING or a NATURAL join. Ticket #3522. (CVS 5979) check-in: 06d206ef user: drh tags: trunk
2008-12-04
22:17
Fix a segfault that can occur in the RowSet object following a malloc failure. (CVS 5978) check-in: cb0f1658 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.488 2008/11/21 16:22:18 danielk1977 Exp $
           15  +** $Id: select.c,v 1.489 2008/12/05 00:00:07 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Delete all the content of a Select structure but do not deallocate
    22     22   ** the select structure itself.
................................................................................
  3148   3148               ** result-set list.
  3149   3149               */
  3150   3150               if( IsHiddenColumn(&pTab->aCol[j]) ){
  3151   3151                 assert(IsVirtual(pTab));
  3152   3152                 continue;
  3153   3153               }
  3154   3154   
  3155         -            if( i>0 ){
         3155  +            if( i>0 && zTName==0 ){
  3156   3156                 struct SrcList_item *pLeft = &pTabList->a[i-1];
  3157   3157                 if( (pLeft[1].jointype & JT_NATURAL)!=0 &&
  3158   3158                           columnIndex(pLeft->pTab, zName)>=0 ){
  3159   3159                   /* In a NATURAL join, omit the join columns from the 
  3160   3160                   ** table on the right */
  3161   3161                   continue;
  3162   3162                 }

Changes to test/join.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.
    12     12   #
    13     13   # This file implements tests for joins, including outer joins.
    14     14   #
    15         -# $Id: join.test,v 1.25 2008/08/14 00:19:49 drh Exp $
           15  +# $Id: join.test,v 1.26 2008/12/05 00:00:07 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   do_test join-1.1 {
    21     21     execsql {
    22     22       CREATE TABLE t1(a,b,c);
................................................................................
    57     57     }
    58     58   } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
    59     59   do_test join-1.3.4 {
    60     60     execsql {
    61     61       SELECT b FROM t1 NATURAL JOIN t2;
    62     62     }
    63     63   } {2 3}
           64  +
           65  +# ticket #3522
           66  +do_test join-1.3.5 {
           67  +  execsql2 {
           68  +    SELECT t2.* FROM t2 NATURAL JOIN t1
           69  +  }
           70  +} {b 2 c 3 d 4 b 3 c 4 d 5}
           71  +do_test join-1.3.6 {
           72  +  execsql2 {
           73  +    SELECT xyzzy.* FROM t2 AS xyzzy NATURAL JOIN t1
           74  +  }
           75  +} {b 2 c 3 d 4 b 3 c 4 d 5}
           76  +do_test join-1.3.7 {
           77  +  execsql2 {
           78  +    SELECT t1.* FROM t2 NATURAL JOIN t1
           79  +  }
           80  +} {a 1 b 2 c 3 a 2 b 3 c 4}
           81  +do_test join-1.3.8 {
           82  +  execsql2 {
           83  +    SELECT xyzzy.* FROM t2 NATURAL JOIN t1 AS xyzzy
           84  +  }
           85  +} {a 1 b 2 c 3 a 2 b 3 c 4}
           86  +do_test join-1.3.9 {
           87  +  execsql2 {
           88  +    SELECT aaa.*, bbb.* FROM t2 AS aaa NATURAL JOIN t1 AS bbb
           89  +  }
           90  +} {b 2 c 3 d 4 a 1 b 2 c 3 b 3 c 4 d 5 a 2 b 3 c 4}
           91  +do_test join-1.3.10 {
           92  +  execsql2 {
           93  +    SELECT t1.*, t2.* FROM t2 NATURAL JOIN t1
           94  +  }
           95  +} {a 1 b 2 c 3 b 2 c 3 d 4 a 2 b 3 c 4 b 3 c 4 d 5}
           96  +
           97  +
    64     98   do_test join-1.4.1 {
    65     99     execsql2 {
    66    100       SELECT * FROM t1 INNER JOIN t2 USING(b,c);
    67    101     }
    68    102   } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
    69    103   do_test join-1.4.2 {
    70    104     execsql2 {
................................................................................
    82    116     }
    83    117   } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
    84    118   do_test join-1.4.5 {
    85    119     execsql {
    86    120       SELECT b FROM t1 JOIN t2 USING(b);
    87    121     }
    88    122   } {2 3}
          123  +
          124  +# Ticket #3522
          125  +do_test join-1.4.6 {
          126  +  execsql2 {
          127  +    SELECT t1.* FROM t1 JOIN t2 USING(b);
          128  +  }
          129  +} {a 1 b 2 c 3 a 2 b 3 c 4}
          130  +do_test join-1.4.7 {
          131  +  execsql2 {
          132  +    SELECT t2.* FROM t1 JOIN t2 USING(b);
          133  +  }
          134  +} {b 2 c 3 d 4 b 3 c 4 d 5}
          135  +
    89    136   do_test join-1.5 {
    90    137     execsql2 {
    91    138       SELECT * FROM t1 INNER JOIN t2 USING(b);
    92    139     }
    93    140   } {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5}
    94    141   do_test join-1.6 {
    95    142     execsql2 {
................................................................................
   188    235   } {1 2 3 4 5}
   189    236   
   190    237   do_test join-2.1 {
   191    238     execsql {
   192    239       SELECT * FROM t1 NATURAL LEFT JOIN t2;
   193    240     }
   194    241   } {1 2 3 4 2 3 4 5 3 4 5 {}}
          242  +
          243  +# ticket #3522
          244  +do_test join-2.1.1 {
          245  +  execsql2 {
          246  +    SELECT * FROM t1 NATURAL LEFT JOIN t2;
          247  +  }
          248  +} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5 a 3 b 4 c 5 d {}}
          249  +do_test join-2.1.2 {
          250  +  execsql2 {
          251  +    SELECT t1.* FROM t1 NATURAL LEFT JOIN t2;
          252  +  }
          253  +} {a 1 b 2 c 3 a 2 b 3 c 4 a 3 b 4 c 5}
          254  +do_test join-2.1.3 {
          255  +  execsql2 {
          256  +    SELECT t2.* FROM t1 NATURAL LEFT JOIN t2;
          257  +  }
          258  +} {b 2 c 3 d 4 b 3 c 4 d 5 b {} c {} d {}}
          259  +
   195    260   do_test join-2.2 {
   196    261     execsql {
   197    262       SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
   198    263     }
   199    264   } {1 2 3 {} 2 3 4 1 3 4 5 2}
   200    265   do_test join-2.3 {
   201    266     catchsql {

Added test/tkt3522.test.

            1  +# 2008 December 4
            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 is a verification that the bugs identified in ticket
           14  +# #3522 have been fixed.
           15  +#
           16  +# $Id: tkt3522.test,v 1.1 2008/12/05 00:00:07 drh Exp $
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +
           21  +do_test tkt3522-1.1 {
           22  +  db eval {
           23  +    CREATE TABLE tab4(
           24  +       col0 INTEGER,
           25  +       col1 INTEGER,
           26  +       col2 INTEGER,
           27  +       col3 INTEGER,
           28  +       col4 INTEGER
           29  +    );
           30  +    SELECT cor1.*
           31  +      FROM tab4 AS cor0
           32  +      JOIN tab4 AS cor1 USING ( col4, col3, col2, col1, col0 );
           33  +  }
           34  +} {}
           35  +do_test tkt3522-1.2 {
           36  +  db eval {
           37  +    CREATE TABLE tab1(col0 INTEGER);
           38  +    CREATE TABLE tab2(col0 INTEGER);
           39  +    SELECT cor0.* FROM tab1 NATURAL JOIN tab2 AS cor0;
           40  +  }
           41  +} {}
           42  +
           43  +finish_test