SQLite

Check-in [a427c40528]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a427c4052820d90c3c8c23abf9097f973e564010
User & Date: dan 2016-12-01 19:58:01.439
Context
2016-12-02
02:19
Test cases for the improved LIKE/GLOB matcher. (check-in: 0bdb7ae126 user: drh tags: trunk)
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: a427c40528 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: 8bed4cd549 user: dan tags: left-join-fix)
18:57
Performance improvement for GLOB and LIKE matching for patterns with two or more multi-character wildcards ("*" or "%"). (check-in: 2df0ebf95f user: drh tags: trunk)
Changes
Unified Diff 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

#
#***********************************************************************
# 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);








<



>








9
10
11
12
13
14
15

16
17
18
19
20
21
22
23
24
25
26

#
#***********************************************************************
# 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);
︙︙
179
180
181
182
183
184
185
186




























187

  ) 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









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


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

  ) 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