From: Tom Lane <tgl@sss.pgh.pa.us>
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
Date: Tue, 20 Jun 2023 16:59:50 -0400
Message-ID: <3621303.1687294790@sss.pgh.pa.us> (raw)
In-Reply-To: <3564217.1687286441@sss.pgh.pa.us>
References: <17985-748b66607acd432e@postgresql.org>
<3564217.1687286441@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
Attachments:
[text/x-diff] better-fix-for-bug-17985.patch (3.0K, ../3621303.1687294790@sss.pgh.pa.us/2-better-fix-for-bug-17985.patch)
download | inline diff:diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.cindex 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/expected/join_hash.outindex 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 parameters+-- (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 = 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 = 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 = (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 = 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/join_hash.sqlindex 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 parameters+-- (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 = 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 = 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 = i4.f1+i8.q2 order by 1,2) ss;++rollback;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, zuming.jiang@inf.ethz.ch, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17985: Inconsistent results of SELECT comparing two CASE WHEN clause
In-Reply-To: <3621303.1687294790@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox