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: |
862974312edf00e9d1068115d1a39b72 |
| 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
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 |
** 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;
| > > | < > > | 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
|