From 11d63ebf2c02f83c777448522deea6cd98487500 Mon Sep 17 00:00:00 2001 From: drh <> Date: Wed, 4 May 2022 14:45:19 +0000 Subject: [PATCH] Fix ON-clause constraint usage for FULL and RIGHT JOINs. FossilOrigin-Name: 544268a723cafdf0460221f5f018e752836436bad0fac90852cec70e63d9dcde --- manifest | 16 +++++++------- manifest.uuid | 2 +- src/where.c | 2 +- test/join8.test | 57 +++++++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 67 insertions(+), 10 deletions(-) diff --git a/manifest b/manifest index 2c27eceb6c..65831b7807 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Fix\sCLI\suse\sof\swrong\sallocator\sfor\sfree(). -D 2022-05-04T03:45:32.728 +C Fix\sON-clause\sconstraint\susage\sfor\sFULL\sand\sRIGHT\sJOINs. +D 2022-05-04T14:45:19.529 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -640,7 +640,7 @@ F src/vxworks.h d2988f4e5a61a4dfe82c6524dd3d6e4f2ce3cdb9 F src/wal.c b9df133a705093da8977da5eb202eaadb844839f1c7297c08d33471f5491843d F src/wal.h c3aa7825bfa2fe0d85bef2db94655f99870a285778baa36307c0a16da32b226a F src/walker.c f890a3298418d7cba3b69b8803594fdc484ea241206a8dfa99db6dd36f8cbb3b -F src/where.c b0f0c4b38d80530ff5e88fb8978e67555cb6392c8cedfd9f80d4d4d8e4106336 +F src/where.c 70eae5fd61a0dd2c613e1fbf84baa5f5cf95f974714538b27b3d45f0a00a16c8 F src/whereInt.h 4db5a877a9d1f38b5c928c1c84297c07f30b9a3bc1f5f66214cf1a8ef90a0556 F src/wherecode.c cd9da760d391a51013f9ab8bc8ca7ad67f3b96e4857efe0d1dd85f538b3494a6 F src/whereexpr.c 174d4ad5be165c610c907abb779ef4a97974d22b84e1ce7898d2d9f6947249e5 @@ -1148,7 +1148,7 @@ F test/join4.test 1a352e4e267114444c29266ce79e941af5885916 F test/join5.test d22b6cba8fb59ab3f1c82701434c360705eb12d4ce200c449f37b018fc47681a F test/join6.test f809c025fa253f9e150c0e9afd4cef8813257bceeb6f46e04041228c9403cc2c F test/join7.test 8e72de4b45e5e930d18c305c7efe86015fb2552731e4e03ea226353036b0dab0 -F test/join8.test 9a4b2f53d5358abb29e6356e357c0cd40f4d8f282589300dcb76728dce050b51 +F test/join8.test 68f5ec206cd88610c19ab8edb4789a174a55cdb1732619a95db8fd33dbb13783 F test/join9.test 9056ddd3b0c0f4f9d658f4521038d9a37dc23ead8ca9a505d0b0db2b6a471e05 F test/joinA.test 7eab225dc1c1ab258a5e62513a4ed7cabbd3db971d59d5d92f4fb6fa14c12f6a F test/joinB.test 1b2ba3fc8568b49411787fccbf540570c148e9b6a53a30f80691cb6268098ded @@ -1951,8 +1951,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 8cee5388232ade91ea627bb6857eb8937d7530b5ea519e0ae6da362c37c7ed35 -R 3c89c63aaa0653a254417d115718d7c3 -U larrybr -Z 111ff23023abfd8d38c72ec3de24a2fe +P 9fb20a21feb8f6979812f45691e06aa3e297d7370cf0d5820523e817a4e97863 +R d74d2aa039568ce071dd683a6739d947 +U drh +Z 8ab0a178246eeb862fc914300baceadc # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index 0ae4c52f89..d213c503ba 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -9fb20a21feb8f6979812f45691e06aa3e297d7370cf0d5820523e817a4e97863 \ No newline at end of file +544268a723cafdf0460221f5f018e752836436bad0fac90852cec70e63d9dcde \ No newline at end of file diff --git a/src/where.c b/src/where.c index c6fa56cdbd..5902902458 100644 --- a/src/where.c +++ b/src/where.c @@ -2836,7 +2836,7 @@ static int whereLoopAddBtreeIndex( ** RIGHT JOIN. Only constraints in the ** ON clause are allowed. See tag-20191211-002 for the vtab equivalent. */ if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ))!=0 - && !ExprHasProperty(pTerm->pExpr, EP_FromJoin) + && !ExprHasProperty(pTerm->pExpr, EP_FromJoin|EP_InnerJoin) ){ continue; } diff --git a/test/join8.test b/test/join8.test index a33622d9bb..3a3b06a1c4 100644 --- a/test/join8.test +++ b/test/join8.test @@ -180,4 +180,61 @@ do_execsql_test join8-6020 { WHERE (a,b) IN (SELECT rowid, b FROM t1); } {17 1 2} +# Bloom filter usage by RIGHT and FULL JOIN +# +reset_db +do_execsql_test join8-7000 { +CREATE TABLE t1(a INT, b INT, c INT, d INT); + WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<10) + INSERT INTO t1(a,b,c,d) SELECT x, x+100, x+200, x+300 FROM c; + CREATE TABLE t2(b INT, x INT); + INSERT INTO t2(b,x) SELECT b, a FROM t1 WHERE a%2=0; + CREATE INDEX t2b ON t2(b); + CREATE TABLE t3(c INT, y INT); + INSERT INTO t3(c,y) SELECT c, a FROM t1 WHERE a%3=0; + CREATE INDEX t3c ON t3(c); + CREATE TABLE t4(d INT, z INT); + INSERT INTO t4(d,z) SELECT d, a FROM t1 WHERE a%5=0; + CREATE INDEX t4d ON t4(d); + INSERT INTO t1(a,b,c,d) VALUES + (96,NULL,296,396), + (97,197,NULL,397), + (98,198,298,NULL), + (99,NULL,NULL,NULL); + ANALYZE sqlite_schema; + INSERT INTO sqlite_stat1 VALUES('t4','t4d','20 1'); + INSERT INTO sqlite_stat1 VALUES('t3','t3c','32 1'); + INSERT INTO sqlite_stat1 VALUES('t2','t2b','48 1'); + INSERT INTO sqlite_stat1 VALUES('t1',NULL,'100'); + ANALYZE sqlite_schema; +} {} +db null - +do_execsql_test join8-7010 { + WITH t0 AS MATERIALIZED ( + SELECT t1.*, t2.*, t3.* + FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0 + RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0 + ) + SELECT * FROM t0 FULL JOIN t4 ON t0.a=t4.d AND t4.z>0 + ORDER BY coalesce(t0.a, t0.y+200, t4.d); +} { + 6 106 206 306 106 6 206 6 - - + - - - - - - 200 0 - - + - - - - - - 203 3 - - + - - - - - - 209 9 - - + - - - - - - - - 300 0 + - - - - - - - - 305 5 + - - - - - - - - 310 10 +} +do_execsql_test join8-7020 { + EXPLAIN QUERY PLAN + WITH t0 AS MATERIALIZED ( + SELECT t1.*, t2.*, t3.* + FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0 + RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0 + ) + SELECT * FROM t0 FULL JOIN t4 ON t0.a=t4.d AND t4.z>0 + ORDER BY coalesce(t0.a, t0.y+200, t4.d); +} {/.*BLOOM FILTER ON t2.*BLOOM FILTER ON t3.*BLOOM FILTER ON t4.*/} + finish_test -- 2.47.2