/ Check-in [919d36e6]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:When flattening a query, make sure iTable attribute of TK_IF_NULL_ROW operators (that result from a prior flattening of a LEFT JOIN) are updated correctly. Fix for ticket [cad1ab4cb7b0fc344].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.19
Files: files | file ages | folders
SHA3-256: 919d36e6ea112c8c94f01e02611c48e78c441657e6898a466cfc39f1e0001403
User & Date: drh 2017-05-23 01:29:35
Context
2017-05-23
01:30
Remove a surplus semicolon, which was harmless on gcc and clang but caused compile-time errors on msvc. This only comes up using the encryption extension. check-in: 4510d55d user: drh tags: branch-3.19
01:29
When flattening a query, make sure iTable attribute of TK_IF_NULL_ROW operators (that result from a prior flattening of a LEFT JOIN) are updated correctly. Fix for ticket [cad1ab4cb7b0fc344]. check-in: 919d36e6 user: drh tags: branch-3.19
01:21
When flattening a query, make sure iTable attribute of TK_IF_NULL_ROW operators (that result from a prior flattening of a LEFT JOIN) are updated correctly. Fix for ticket [cad1ab4cb7b0fc344]. check-in: 92c17850 user: drh tags: trunk
2017-05-22
13:58
Version 3.19.0 check-in: 28a94eb2 user: drh tags: release, branch-3.19, version-3.19.0
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

3209
3210
3211
3212
3213
3214
3215


3216
3217
3218
3219
3220
3221
3222
          pNew->iRightJoinTable = pExpr->iRightJoinTable;
          pNew->flags |= EP_FromJoin;
        }
        sqlite3ExprDelete(db, pExpr);
        pExpr = pNew;
      }
    }


  }else{
    pExpr->pLeft = substExpr(pSubst, pExpr->pLeft);
    pExpr->pRight = substExpr(pSubst, pExpr->pRight);
    if( ExprHasProperty(pExpr, EP_xIsSelect) ){
      substSelect(pSubst, pExpr->x.pSelect, 1);
    }else{
      substExprList(pSubst, pExpr->x.pList);







>
>







3209
3210
3211
3212
3213
3214
3215
3216
3217
3218
3219
3220
3221
3222
3223
3224
          pNew->iRightJoinTable = pExpr->iRightJoinTable;
          pNew->flags |= EP_FromJoin;
        }
        sqlite3ExprDelete(db, pExpr);
        pExpr = pNew;
      }
    }
  }else if( pExpr->op==TK_IF_NULL_ROW && pExpr->iTable==pSubst->iTable ){
    pExpr->iTable = pSubst->iNewTable;
  }else{
    pExpr->pLeft = substExpr(pSubst, pExpr->pLeft);
    pExpr->pRight = substExpr(pSubst, pExpr->pRight);
    if( ExprHasProperty(pExpr, EP_xIsSelect) ){
      substSelect(pSubst, pExpr->x.pSelect, 1);
    }else{
      substExprList(pSubst, pExpr->x.pList);

Changes to src/vdbe.c.

2437
2438
2439
2440
2441
2442
2443

2444
2445
2446
2447
2448
2449
2450
** Check the cursor P1 to see if it is currently pointing at a NULL row.
** If it is, then set register P3 to NULL and jump immediately to P2.
** If P1 is not on a NULL row, then fall through without making any
** changes.
*/
case OP_IfNullRow: {         /* jump */
  assert( pOp->p1>=0 && pOp->p1<p->nCursor );

  if( p->apCsr[pOp->p1]->nullRow ){
    sqlite3VdbeMemSetNull(aMem + pOp->p3);
    goto jump_to_p2;
  }
  break;
}








>







2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
** Check the cursor P1 to see if it is currently pointing at a NULL row.
** If it is, then set register P3 to NULL and jump immediately to P2.
** If P1 is not on a NULL row, then fall through without making any
** changes.
*/
case OP_IfNullRow: {         /* jump */
  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  assert( p->apCsr[pOp->p1]!=0 );
  if( p->apCsr[pOp->p1]->nullRow ){
    sqlite3VdbeMemSetNull(aMem + pOp->p3);
    goto jump_to_p2;
  }
  break;
}

Changes to test/join.test.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
708
709
710
711
712
713
714



715












716
# 2002 May 24
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for joins, including outer joins.
#
# $Id: join.test,v 1.27 2009/07/01 16:12:08 danielk1977 Exp $

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

do_test join-1.1 {
  execsql {
    CREATE TABLE t1(a,b,c);
................................................................................
# In the following, the order of [cc] and [bb] must not be exchanged, even
# though this would be helpful if the query used an inner join.
do_execsql_test join-13.2 {
  CREATE INDEX ccc ON cc(c);
  SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
} {45 {} 45 45 {} 45}

















finish_test
|













<







 







>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>

1
2
3
4
5
6
7
8
9
10
11
12
13
14

15
16
17
18
19
20
21
...
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
# 2002-05-24
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for joins, including outer joins.
#


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

do_test join-1.1 {
  execsql {
    CREATE TABLE t1(a,b,c);
................................................................................
# In the following, the order of [cc] and [bb] must not be exchanged, even
# though this would be helpful if the query used an inner join.
do_execsql_test join-13.2 {
  CREATE INDEX ccc ON cc(c);
  SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
} {45 {} 45 45 {} 45}

# Verify that that iTable attributes the TK_IF_NULL_ROW operators in the
# expression tree are correctly updated by the query flattener.  This was
# a bug discovered on 2017-05-22 by Mark Brand.
#
do_execsql_test join-14.1 {
  SELECT *
    FROM (SELECT 1 a) AS x 
         LEFT JOIN (SELECT 1, * FROM (SELECT * FROM (SELECT 1)));
} {1 1 1}
do_execsql_test join-14.2 {
  SELECT *
  FROM (SELECT 1 a) AS x
    LEFT JOIN (SELECT 1, * FROM (SELECT * FROM (SELECT * FROM (SELECT 1)))) AS y
    JOIN (SELECT * FROM (SELECT 9)) AS z;
} {1 1 1 9}

finish_test