/ Check-in [86297431]
Login

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

Overview
Comment:Continue to back away from the LEFT JOIN optimization of check-in [41c27bc0ff1d3135] by disallowing query flattening if the outer query is DISTINCT. Without this fix, if an index scan is run on the table within the view on the right-hand side of the LEFT JOIN, stale result registers might be accessed yielding incorrect results, and/or an OP_IfNullRow opcode might be invoked on the un-opened table, resulting in a NULL-pointer dereference. This problem was found by the Yongheng and Rui fuzzer.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 862974312edf00e9d1068115d1a39b7235b7db68b6d86b81d38a12f025a4748e
User & Date: drh 2019-12-18 20:51:58
Context
2019-12-18
21:22
Change an unreachable testcase() into an assert() (check-in: 062e00c4 user: drh tags: trunk)
20:51
Continue to back away from the LEFT JOIN optimization of check-in [41c27bc0ff1d3135] by disallowing query flattening if the outer query is DISTINCT. Without this fix, if an index scan is run on the table within the view on the right-hand side of the LEFT JOIN, stale result registers might be accessed yielding incorrect results, and/or an OP_IfNullRow opcode might be invoked on the un-opened table, resulting in a NULL-pointer dereference. This problem was found by the Yongheng and Rui fuzzer. (check-in: 86297431 user: drh tags: trunk)
13:42
Add the --vdbe-debug option to fuzzcheck. (check-in: 289158aa user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

3596
3597
3598
3599
3600
3601
3602

3603
3604
3605
3606
3607
3608
3609
**             from 2015-02-09.)
**
**   (3)  If the subquery is the right operand of a LEFT JOIN then
**        (3a) the subquery may not be a join and
**        (3b) the FROM clause of the subquery may not contain a virtual
**             table and
**        (3c) the outer query may not be an aggregate.

**
**   (4)  The subquery can not be DISTINCT.
**
**  (**)  At one point restrictions (4) and (5) defined a subset of DISTINCT
**        sub-queries that were excluded from this optimization. Restriction 
**        (4) has since been expanded to exclude all DISTINCT subqueries.
**







>







3596
3597
3598
3599
3600
3601
3602
3603
3604
3605
3606
3607
3608
3609
3610
**             from 2015-02-09.)
**
**   (3)  If the subquery is the right operand of a LEFT JOIN then
**        (3a) the subquery may not be a join and
**        (3b) the FROM clause of the subquery may not contain a virtual
**             table and
**        (3c) the outer query may not be an aggregate.
**        (3d) the outer query may not be DISTINCT.
**
**   (4)  The subquery can not be DISTINCT.
**
**  (**)  At one point restrictions (4) and (5) defined a subset of DISTINCT
**        sub-queries that were excluded from this optimization. Restriction 
**        (4) has since been expanded to exclude all DISTINCT subqueries.
**
3792
3793
3794
3795
3796
3797
3798


3799
3800


3801
3802
3803
3804
3805
3806
3807
  ** aggregates are processed - there is no mechanism to determine if
  ** the LEFT JOIN table should be all-NULL.
  **
  ** See also tickets #306, #350, and #3300.
  */
  if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
    isLeftJoin = 1;


    if( pSubSrc->nSrc>1 || isAgg || IsVirtual(pSubSrc->a[0].pTab) ){
      /*  (3a)             (3c)     (3b) */


      return 0;
    }
  }
#ifdef SQLITE_EXTRA_IFNULLROW
  else if( iFrom>0 && !isAgg ){
    /* Setting isLeftJoin to -1 causes OP_IfNullRow opcodes to be generated for
    ** every reference to any result column from subquery in a join, even







>
>
|
<
>
>







3793
3794
3795
3796
3797
3798
3799
3800
3801
3802

3803
3804
3805
3806
3807
3808
3809
3810
3811
  ** aggregates are processed - there is no mechanism to determine if
  ** the LEFT JOIN table should be all-NULL.
  **
  ** See also tickets #306, #350, and #3300.
  */
  if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
    isLeftJoin = 1;
    if( pSubSrc->nSrc>1                   /* (3a) */
     || isAgg                             /* (3b) */
     || IsVirtual(pSubSrc->a[0].pTab)     /* (3c) */

     || (p->selFlags & SF_Distinct)!=0    /* (3d) */
    ){
      return 0;
    }
  }
#ifdef SQLITE_EXTRA_IFNULLROW
  else if( iFrom>0 && !isAgg ){
    /* Setting isLeftJoin to -1 causes OP_IfNullRow opcodes to be generated for
    ** every reference to any result column from subquery in a join, even

Changes to test/join.test.

970
971
972
973
974
975
976
977













978
  SELECT 14, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
  CREATE INDEX i0 ON t0(aa) WHERE aa ISNULL;
  SELECT 21, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
  SELECT 22, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
  SELECT 23, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
  SELECT 24, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
} {13 1 {} 14 1 {} 23 1 {} 24 1 {}}














finish_test








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

970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
  SELECT 14, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
  CREATE INDEX i0 ON t0(aa) WHERE aa ISNULL;
  SELECT 21, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
  SELECT 22, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
  SELECT 23, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
  SELECT 24, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
} {13 1 {} 14 1 {} 23 1 {} 24 1 {}}

# 2019-12-18 problem with a LEFT JOIN where the RHS is a view.
# Detected by Yongheng and Rui.
# Follows from the optimization attempt of check-in 41c27bc0ff1d3135
# on 2017-04-18
#
reset_db
do_execsql_test join-22.10 {
  CREATE TABLE t0(a, b);
  CREATE INDEX t0a ON t0(a);
  INSERT INTO t0 VALUES(10,10),(10,11),(10,12);
  SELECT DISTINCT c FROM t0 LEFT JOIN (SELECT a+1 AS c FROM t0) ORDER BY c ;
} {11}

finish_test