/ Check-in [a286e54e]
Login

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

Overview
Comment:Allow WHERE clause terms on the left table of a LEFT OUTER JOIN to contain aggregate subqueries. Ticket #1697. (CVS 3117)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a286e54e26f9a364127649eeea160d5fa8928faf
User & Date: drh 2006-03-02 04:44:24
Context
2006-03-03
19:12
Ignore leading spaces on text to numeric conversions. Ticket #1662. Fixes to test cases broken by the recent changes to round(). (CVS 3118) check-in: cdca3383 user: drh tags: trunk
2006-03-02
04:44
Allow WHERE clause terms on the left table of a LEFT OUTER JOIN to contain aggregate subqueries. Ticket #1697. (CVS 3117) check-in: a286e54e user: drh tags: trunk
03:02
Change the ROUND() function to return a REAL value instead of TEXT. Ticket #1699. (CVS 3116) check-in: 9dbadfb2 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.254 2006/02/10 07:07:15 danielk1977 Exp $
           15  +** $Id: expr.c,v 1.255 2006/03/02 04:44:24 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   #include <ctype.h>
    19     19   
    20     20   /*
    21     21   ** Return the 'affinity' of the expression pExpr if any.
    22     22   **
................................................................................
  1688   1688         sqlite3VdbeOp3(v, OP_Function, constMask, nExpr, (char*)pDef, P3_FUNCDEF);
  1689   1689         stackChng = 1-nExpr;
  1690   1690         break;
  1691   1691       }
  1692   1692   #ifndef SQLITE_OMIT_SUBQUERY
  1693   1693       case TK_EXISTS:
  1694   1694       case TK_SELECT: {
  1695         -      sqlite3CodeSubselect(pParse, pExpr);
         1695  +      if( pExpr->iColumn==0 ){
         1696  +        sqlite3CodeSubselect(pParse, pExpr);
         1697  +      }
  1696   1698         sqlite3VdbeAddOp(v, OP_MemLoad, pExpr->iColumn, 0);
  1697   1699         VdbeComment((v, "# load subquery result"));
  1698   1700         break;
  1699   1701       }
  1700   1702       case TK_IN: {
  1701   1703         int addr;
  1702   1704         char affinity;

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.20 2005/06/06 17:11:46 drh Exp $
           15  +# $Id: join.test,v 1.21 2006/03/02 04:44:24 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);
................................................................................
   438    438         SELECT * FROM t12 NATURAL LEFT JOIN t13
   439    439           EXCEPT
   440    440           SELECT * FROM t12 NATURAL LEFT JOIN v13;
   441    441       }
   442    442     } {}
   443    443   } ;# ifcapable view
   444    444   } ;# ifcapable compound
          445  +
          446  +# Ticket #1697:  Left Join WHERE clause terms that contain an
          447  +# aggregate subquery.
          448  +#
          449  +do_test join-10.1 {
          450  +  execsql {
          451  +    CREATE TABLE t21(a,b,c);
          452  +    CREATE TABLE t22(p,q);
          453  +    CREATE INDEX i22 ON t22(q);
          454  +    SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q=
          455  +       (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1);
          456  +  }  
          457  +} {}
   445    458   
   446    459   finish_test