Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qBiSE-0004oe-52 for pgsql-bugs@arkaria.postgresql.org; Tue, 20 Jun 2023 20:59:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qBiSC-0004qW-QT for pgsql-bugs@arkaria.postgresql.org; Tue, 20 Jun 2023 20:59:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qBiSC-0004qN-IS for pgsql-bugs@lists.postgresql.org; Tue, 20 Jun 2023 20:59:56 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qBiS9-003J59-Sn for pgsql-bugs@lists.postgresql.org; Tue, 20 Jun 2023 20:59:55 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 35KKxo913621304; Tue, 20 Jun 2023 16:59:51 -0400 From: Tom Lane To: zuming.jiang@inf.ethz.ch cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #17985: Inconsistent results of SELECT comparing two CASE WHEN clause In-reply-to: <3564217.1687286441@sss.pgh.pa.us> References: <17985-748b66607acd432e@postgresql.org> <3564217.1687286441@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Tue, 20 Jun 2023 14:40:41 -0400" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <3618298.1687293682.0@sss.pgh.pa.us> Date: Tue, 20 Jun 2023 16:59:50 -0400 Message-ID: <3621303.1687294790@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3618298.1687293682.1@sss.pgh.pa.us> I wrote: > One way to fix it is as attached. I wonder though if this isn't > telling us that there's a bug in the planner's assignment of > allParams bits for Hash nodes. The dangerous Param is present > in the Hash node's hashkeys field, so why isn't the existing > chgParam test adequate? Yeah, so looking at that, the planner is just ignoring Hash.hashkeys, apparently figuring that incorporating Param IDs from HashJoin.hashclauses into the allParams for the parent HashJoin is sufficient. But as we see here, it isn't. The attached seems like a better fix. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="better-fix-for-bug-17985.patch"; charset="us-ascii" Content-ID: <3618298.1687293682.2@sss.pgh.pa.us> Content-Description: better-fix-for-bug-17985.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimize= r/plan/subselect.c index 5f12b2ef9b..da2d8abe01 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -2648,6 +2648,11 @@ finalize_plan(PlannerInfo *root, Plan *plan, &context); break; = + case T_Hash: + finalize_primnode((Node *) ((Hash *) plan)->hashkeys, + &context); + break; + case T_Limit: finalize_primnode(((Limit *) plan)->limitOffset, &context); @@ -2748,7 +2753,6 @@ finalize_plan(PlannerInfo *root, Plan *plan, break; = case T_ProjectSet: - case T_Hash: case T_Material: case T_Sort: case T_IncrementalSort: diff --git a/src/test/regress/expected/join_hash.out b/src/test/regress/ex= pected/join_hash.out index 4faf010f8c..262fa71ed8 100644 --- a/src/test/regress/expected/join_hash.out +++ b/src/test/regress/expected/join_hash.out @@ -1128,3 +1128,39 @@ WHERE (1 row) = ROLLBACK; +-- Verify that we behave sanely when the inner hash keys contain paramete= rs +-- (that is, outer or lateral references). This situation has to defeat +-- re-use of the inner hash table across rescans. +begin; +set local enable_hashjoin =3D on; +explain (costs off) +select i8.q2, ss.* from +int8_tbl i8, +lateral (select t1.fivethous, i4.f1 from tenk1 t1 join int4_tbl i4 + on t1.fivethous =3D i4.f1+i8.q2 order by 1,2) ss; + QUERY PLAN = +----------------------------------------------------------- + Nested Loop + -> Seq Scan on int8_tbl i8 + -> Sort + Sort Key: t1.fivethous, i4.f1 + -> Hash Join + Hash Cond: (t1.fivethous =3D (i4.f1 + i8.q2)) + -> Seq Scan on tenk1 t1 + -> Hash + -> Seq Scan on int4_tbl i4 +(9 rows) + +select i8.q2, ss.* from +int8_tbl i8, +lateral (select t1.fivethous, i4.f1 from tenk1 t1 join int4_tbl i4 + on t1.fivethous =3D i4.f1+i8.q2 order by 1,2) ss; + q2 | fivethous | f1 = +-----+-----------+---- + 456 | 456 | 0 + 456 | 456 | 0 + 123 | 123 | 0 + 123 | 123 | 0 +(4 rows) + +rollback; diff --git a/src/test/regress/sql/join_hash.sql b/src/test/regress/sql/joi= n_hash.sql index e73f645e9e..6b0688ab0a 100644 --- a/src/test/regress/sql/join_hash.sql +++ b/src/test/regress/sql/join_hash.sql @@ -604,3 +604,22 @@ WHERE AND hjtest_1.a <> hjtest_2.b; = ROLLBACK; + +-- Verify that we behave sanely when the inner hash keys contain paramete= rs +-- (that is, outer or lateral references). This situation has to defeat +-- re-use of the inner hash table across rescans. +begin; +set local enable_hashjoin =3D on; + +explain (costs off) +select i8.q2, ss.* from +int8_tbl i8, +lateral (select t1.fivethous, i4.f1 from tenk1 t1 join int4_tbl i4 + on t1.fivethous =3D i4.f1+i8.q2 order by 1,2) ss; + +select i8.q2, ss.* from +int8_tbl i8, +lateral (select t1.fivethous, i4.f1 from tenk1 t1 join int4_tbl i4 + on t1.fivethous =3D i4.f1+i8.q2 order by 1,2) ss; + +rollback; ------- =_aaaaaaaaaa0--