Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | The same restrictions on the use of WHERE clause terms to drive indexes in the presence of RIGHT JOINs also apply to the use of WHERE clause terms to manufacture automatic indexes. This fixes a problem identified by forum post 51e6959f61. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
342c501f532523347e6c339351e02043 |
| User & Date: | drh 2022-06-10 16:41:54 |
Context
|
2022-06-13
| ||
| 12:42 | Do not remove the EP_CanBeNull flag from expressions during a LEFT JOIN strength reduction if the query also contains a RIGHT JOIN. Fix for the problem identified by [forum/forumpost/b40696f50145d21c|forum post b40696f50145d21c]. (check-in: b1be2259 user: drh tags: trunk) | |
|
2022-06-10
| ||
| 16:41 | The same restrictions on the use of WHERE clause terms to drive indexes in the presence of RIGHT JOINs also apply to the use of WHERE clause terms to manufacture automatic indexes. This fixes a problem identified by forum post 51e6959f61. (check-in: 342c501f user: drh tags: trunk) | |
| 15:43 | fiddle: minor style tweaks, including using swapped colors for the input/output fields to help (hopefully) reduce the "which field is which?" dissonance. (check-in: e25dad86 user: stephan tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
753 754 755 756 757 758 759 |
const WhereTerm *pTerm, /* WHERE clause term to check */
const SrcItem *pSrc, /* Table we are trying to access */
const Bitmask notReady /* Tables in outer loops of the join */
){
char aff;
if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0;
| > | > > | | > > | < < < | > | 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 |
const WhereTerm *pTerm, /* WHERE clause term to check */
const SrcItem *pSrc, /* Table we are trying to access */
const Bitmask notReady /* Tables in outer loops of the join */
){
char aff;
if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0;
assert( (pSrc->fg.jointype & JT_RIGHT)==0 );
if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))!=0 ){
testcase( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))==JT_LEFT );
testcase( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))==JT_LTORJ );
testcase( ExprHasProperty(pTerm->pExpr, EP_OuterON) )
testcase( ExprHasProperty(pTerm->pExpr, EP_InnerON) );
if( !ExprHasProperty(pTerm->pExpr, EP_OuterON|EP_InnerON)
|| pTerm->pExpr->w.iJoin != pSrc->iCursor
){
return 0; /* See tag-20191211-001 */
}
}
if( (pTerm->prereqRight & notReady)!=0 ) return 0;
assert( (pTerm->eOperator & (WO_OR|WO_AND))==0 );
if( pTerm->u.x.leftColumn<0 ) return 0;
aff = pSrc->pTab->aCol[pTerm->u.x.leftColumn].affinity;
if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
testcase( pTerm->pExpr->op==TK_IS );
|
| ︙ | ︙ | |||
2844 2845 2846 2847 2848 2849 2850 2851 2852 2853 2854 2855 2856 2857 |
** RIGHT JOIN. Only constraints in the ON clause are allowed.
** See tag-20191211-002 for the vtab equivalent.
**
** 2022-06-06: See https://sqlite.org/forum/forumpost/206d99a16dd9212f
** for an example of a WHERE clause constraints that may not be used on
** the right table of a RIGHT JOIN because the constraint implies a
** not-NULL condition on the left table of the RIGHT JOIN.
*/
if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))!=0 ){
testcase( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))==JT_LEFT );
testcase( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))==JT_RIGHT );
testcase( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))==JT_LTORJ );
testcase( ExprHasProperty(pTerm->pExpr, EP_OuterON) )
testcase( ExprHasProperty(pTerm->pExpr, EP_InnerON) );
| > > > | 2847 2848 2849 2850 2851 2852 2853 2854 2855 2856 2857 2858 2859 2860 2861 2862 2863 |
** RIGHT JOIN. Only constraints in the ON clause are allowed.
** See tag-20191211-002 for the vtab equivalent.
**
** 2022-06-06: See https://sqlite.org/forum/forumpost/206d99a16dd9212f
** for an example of a WHERE clause constraints that may not be used on
** the right table of a RIGHT JOIN because the constraint implies a
** not-NULL condition on the left table of the RIGHT JOIN.
**
** 2022-06-10: The same condition applies to termCanDriveIndex() above.
** https://sqlite.org/forum/forumpost/51e6959f61
*/
if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))!=0 ){
testcase( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))==JT_LEFT );
testcase( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))==JT_RIGHT );
testcase( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))==JT_LTORJ );
testcase( ExprHasProperty(pTerm->pExpr, EP_OuterON) )
testcase( ExprHasProperty(pTerm->pExpr, EP_InnerON) );
|
| ︙ | ︙ |
Changes to test/join8.test.
| ︙ | ︙ | |||
670 671 672 673 674 675 676 677 678 679 |
}
do_execsql_test join8-21010 {
SELECT (b IS TRUE) FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE;
} {0}
do_execsql_test join8-22020 {
SELECT * FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE WHERE (b IS TRUE);
} {}
finish_test
| > > > > > > > > > > > > > > > > > > > > | 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 |
}
do_execsql_test join8-21010 {
SELECT (b IS TRUE) FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE;
} {0}
do_execsql_test join8-22020 {
SELECT * FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE WHERE (b IS TRUE);
} {}
# 2022-06-10
# https://sqlite.org/forum/forumpost/51e6959f61
#
# Restrictions on the usage of WHERE clause constraints by joins that are
# involved with a RIGHT JOIN must also be applied to automatic indexes.
#
reset_db
do_execsql_test join8-22000 {
CREATE TABLE t1(a INT);
CREATE TABLE t2(b INT);
CREATE TABLE t3(c TEXT); INSERT INTO t3 VALUES('x');
CREATE TABLE t4(d TEXT); INSERT INTO t4 VALUES('y');
SELECT 99
FROM t1
LEFT JOIN t2 ON true
RIGHT JOIN t3 ON true
RIGHT JOIN t4 ON true
WHERE a=b;
} {}
finish_test
|