/ Check-in [dd8943e8]
Login

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

Overview
Comment:Do not flatten a subquery which is the right term of a LEFT OUTER JOIN if the subquery contains a WHERE clause. Ticket #350. (CVS 1027)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: dd8943e8583cf7ed3b662570a5607856fd246dac
User & Date: drh 2003-06-16 00:40:35
Context
2003-06-16
03:08
Add the %z format to the sqlite_mprintf() and related functions. (CVS 1028) check-in: eca1398e user: drh tags: trunk
00:40
Do not flatten a subquery which is the right term of a LEFT OUTER JOIN if the subquery contains a WHERE clause. Ticket #350. (CVS 1027) check-in: dd8943e8 user: drh tags: trunk
00:16
Preserve blank lines in the middle of SQL statements in the shell. Ticket #352 (CVS 1026) check-in: bcf5eeec 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.140 2003/05/31 16:21:13 drh Exp $
           15  +** $Id: select.c,v 1.141 2003/06/16 00:40:35 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.
................................................................................
  1573   1573   **   (9)  The subquery does not use LIMIT or the outer query does not use
  1574   1574   **        aggregates.
  1575   1575   **
  1576   1576   **  (10)  The subquery does not use aggregates or the outer query does not
  1577   1577   **        use LIMIT.
  1578   1578   **
  1579   1579   **  (11)  The subquery and the outer query do not both have ORDER BY clauses.
         1580  +**
         1581  +**  (12)  The subquery is not the right term of a LEFT OUTER JOIN or the
         1582  +**        subquery has no WHERE clause.  (added by ticket #350)
  1580   1583   **
  1581   1584   ** In this routine, the "p" parameter is a pointer to the outer query.
  1582   1585   ** The subquery is p->pSrc->a[iFrom].  isAgg is true if the outer query
  1583   1586   ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
  1584   1587   **
  1585   1588   ** If flattening is not attempted, this routine is a no-op and returns 0.
  1586   1589   ** If flattening is attempted this routine returns 1.
................................................................................
  1632   1635     **         (t1 LEFT OUTER JOIN t2) JOIN t3
  1633   1636     **
  1634   1637     ** which is not at all the same thing.
  1635   1638     */
  1636   1639     if( pSubSrc->nSrc>1 && iFrom>0 && (pSrc->a[iFrom-1].jointype & JT_OUTER)!=0 ){
  1637   1640       return 0;
  1638   1641     }
         1642  +
         1643  +  /* Restriction 12:  If the subquery is the right operand of a left outer
         1644  +  ** join, make sure the subquery has no WHERE clause.
         1645  +  ** An examples of why this is not allowed:
         1646  +  **
         1647  +  **         t1 LEFT OUTER JOIN (SELECT * FROM t2 WHERE t2.x>0)
         1648  +  **
         1649  +  ** If we flatten the above, we would get
         1650  +  **
         1651  +  **         (t1 LEFT OUTER JOIN t2) WHERE t2.x>0
         1652  +  **
         1653  +  ** But the t2.x>0 test will always fail on a NULL row of t2, which
         1654  +  ** effectively converts the OUTER JOIN into an INNER JOIN.
         1655  +  */
         1656  +  if( iFrom>0 && (pSrc->a[iFrom-1].jointype & JT_OUTER)!=0 
         1657  +      && pSub->pWhere!=0 ){
         1658  +    return 0;
         1659  +  }
  1639   1660   
  1640   1661     /* If we reach this point, it means flattening is permitted for the
  1641   1662     ** iFrom-th entry of the FROM clause in the outer query.
  1642   1663     */
  1643   1664   
  1644   1665     /* Move all of the FROM elements of the subquery into the
  1645   1666     ** the FROM clause of the outer query.  Before doing this, remember

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.9 2003/05/06 20:35:17 drh Exp $
           15  +# $Id: join.test,v 1.10 2003/06/16 00:40:35 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);
................................................................................
   394    394       INSERT INTO t11 VALUES(3,333);    
   395    395       CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p;
   396    396       COMMIT;
   397    397       SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x );
   398    398     }
   399    399   } {1 11 1 111 2 22 {} {}}
   400    400   do_test join-8.2 {
          401  +  execsql {
          402  +    SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p)
          403  +         ON( a=x);
          404  +  }
          405  +} {1 11 1 111 2 22 {} {}}
          406  +do_test join-8.3 {
   401    407     execsql {
   402    408       SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );
   403    409     }
   404    410   } {1 111 1 11 3 333 {} {}}
          411  +
          412  +# Ticket #350 describes a scenario where LEFT OUTER JOIN does not
          413  +# function correctly if the right table in the join is really
          414  +# subquery.
          415  +#
          416  +# To test the problem, we generate the same LEFT OUTER JOIN in two
          417  +# separate selects but with on using a subquery and the other calling
          418  +# the table directly.  Then connect the two SELECTs using an EXCEPT.
          419  +# Both queries should generate the same results so the answer should
          420  +# be an empty set.
          421  +#
          422  +do_test join-9.1 {
          423  +  execsql {
          424  +    BEGIN;
          425  +    CREATE TABLE t12(a,b);
          426  +    INSERT INTO t12 VALUES(1,11);
          427  +    INSERT INTO t12 VALUES(2,22);
          428  +    CREATE TABLE t13(b,c);
          429  +    INSERT INTO t13 VALUES(22,222);
          430  +    COMMIT;
          431  +    SELECT * FROM t12 NATURAL LEFT JOIN t13
          432  +      EXCEPT
          433  +      SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0);
          434  +  }
          435  +} {}
          436  +do_test join-9.2 {
          437  +  execsql {
          438  +    CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0;
          439  +    SELECT * FROM t12 NATURAL LEFT JOIN t13
          440  +      EXCEPT
          441  +      SELECT * FROM t12 NATURAL LEFT JOIN v13;
          442  +  }
          443  +} {}
   405    444   
   406    445   finish_test