/ Check-in [8bed4cd5]
Login

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

Overview
Comment:Avoid clearing the EP_FromJoin flag from terms in ON clauses when flattening sub-selects. Possible fix for [2df0107b].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | left-join-fix
Files: files | file ages | folders
SHA1:8bed4cd549759a93c4a087d8d8c08c0068ba8d59
User & Date: dan 2016-12-01 19:38:05
Context
2016-12-01
19:58
Avoid clearing the EP_FromJoin flag from terms in ON clauses when flattening sub-selects. Possible fix for [2df0107b]. check-in: a427c405 user: dan tags: trunk
19:38
Avoid clearing the EP_FromJoin flag from terms in ON clauses when flattening sub-selects. Possible fix for [2df0107b]. Closed-Leaf check-in: 8bed4cd5 user: dan tags: left-join-fix
2016-11-30
16:54
Add the remember(V,PTR) extension function which copies an SQL value into an application variable. check-in: d2d30914 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

3160
3161
3162
3163
3164
3165
3166




3167
3168
3169
3170
3171
3172
3173
    if( pExpr->iColumn<0 ){
      pExpr->op = TK_NULL;
    }else{
      Expr *pNew;
      assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
      assert( pExpr->pLeft==0 && pExpr->pRight==0 );
      pNew = sqlite3ExprDup(db, pEList->a[pExpr->iColumn].pExpr, 0);




      sqlite3ExprDelete(db, pExpr);
      pExpr = pNew;
    }
  }else{
    pExpr->pLeft = substExpr(db, pExpr->pLeft, iTable, pEList);
    pExpr->pRight = substExpr(db, pExpr->pRight, iTable, pEList);
    if( ExprHasProperty(pExpr, EP_xIsSelect) ){







>
>
>
>







3160
3161
3162
3163
3164
3165
3166
3167
3168
3169
3170
3171
3172
3173
3174
3175
3176
3177
    if( pExpr->iColumn<0 ){
      pExpr->op = TK_NULL;
    }else{
      Expr *pNew;
      assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
      assert( pExpr->pLeft==0 && pExpr->pRight==0 );
      pNew = sqlite3ExprDup(db, pEList->a[pExpr->iColumn].pExpr, 0);
      if( pNew && (pExpr->flags & EP_FromJoin) ){
        pNew->iRightJoinTable = pExpr->iRightJoinTable;
        pNew->flags |= EP_FromJoin;
      }
      sqlite3ExprDelete(db, pExpr);
      pExpr = pNew;
    }
  }else{
    pExpr->pLeft = substExpr(db, pExpr->pLeft, iTable, pEList);
    pExpr->pRight = substExpr(db, pExpr->pRight, iTable, pEList);
    if( ExprHasProperty(pExpr, EP_xIsSelect) ){

Changes to test/join5.test.

9
10
11
12
13
14
15
16
17
18
19

20
21
22
23
24
25
26
...
179
180
181
182
183
184
185
186




























187
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for left outer joins containing ON
# clauses that restrict the scope of the left term of the join.
#
# $Id: join5.test,v 1.2 2007/06/08 00:20:48 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl



do_test join5-1.1 {
  execsql {
    BEGIN;
    CREATE TABLE t1(a integer primary key, b integer, c integer);
    CREATE TABLE t2(x integer primary key, y);
................................................................................
  ) c ON b.fruit='banana';
} {apple apple {} banana banana 1}
do_execsql_test join6-4.2 {
  SELECT *
    FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana')
         LEFT JOIN (SELECT 1) ON fruit='banana';
} {apple {} banana 1}





























finish_test







<



>







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

9
10
11
12
13
14
15

16
17
18
19
20
21
22
23
24
25
26
...
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for left outer joins containing ON
# clauses that restrict the scope of the left term of the join.
#


set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix join5


do_test join5-1.1 {
  execsql {
    BEGIN;
    CREATE TABLE t1(a integer primary key, b integer, c integer);
    CREATE TABLE t2(x integer primary key, y);
................................................................................
  ) c ON b.fruit='banana';
} {apple apple {} banana banana 1}
do_execsql_test join6-4.2 {
  SELECT *
    FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana')
         LEFT JOIN (SELECT 1) ON fruit='banana';
} {apple {} banana 1}

#-------------------------------------------------------------------------
do_execsql_test 5.0 {
  CREATE TABLE y1(x, y, z);
  INSERT INTO y1 VALUES(0, 0, 1);
  CREATE TABLE y2(a);
}

do_execsql_test 5.1 {
  SELECT count(z) FROM y1 LEFT JOIN y2 ON x GROUP BY y;
} 1

do_execsql_test 5.2 {
  SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x GROUP BY y;
} 1

do_execsql_test 5.3 {
  CREATE VIEW v1 AS SELECT x, y, z FROM y1;
  SELECT count(z) FROM v1 LEFT JOIN y2 ON x GROUP BY y;
} 1

do_execsql_test 5.4 {
  SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x
} 1

do_execsql_test 5.5 {
  SELECT * FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x
} {0 0 1 {}}

finish_test