pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix hash join when inner hashkey expressions contain Params. 5+ messages / 1 participants [nested] [flat]
* pgsql: Fix hash join when inner hashkey expressions contain Params. @ 2023-06-20 21:48 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2023-06-20 21:48 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix hash join when inner hashkey expressions contain Params. If the inner-side expressions contain PARAM_EXEC Params, we must re-hash whenever the values of those Params change. The executor mechanism for that exists already, but we failed to invoke it because finalize_plan() neglected to search the Hash.hashkeys field for Params. This allowed a previous scan's hash table to be re-used when it should not be, leading to rows missing from the join's output. (I believe incorrectly-included join rows are impossible however, since checking the real hashclauses would reject false matches.) This bug is very ancient, dating probably to d24d75ff1 of 7.4. Sadly, this simple fix depends on the plan representational changes made by 2abd7ae9b, so it will only work back to v12. I thought about trying to make some kind of hack for v11, but I'm leery of putting code significantly different from what is used in the newer branches into a nearly-EOL branch. Seeing that the bug escaped detection for a full twenty years, problematic cases must be rare; so I don't feel too awful about leaving v11 as-is. Per bug #17985 from Zuming Jiang. Back-patch to v12. Discussion: https://postgr.es/m/17985-748b66607acd432e@postgresql.org Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/45392626c95c6345d48c1b1b7541add0102ba59f Modified Files -------------- src/backend/optimizer/plan/subselect.c | 6 +++++- src/test/regress/expected/join_hash.out | 36 +++++++++++++++++++++++++++++++++ src/test/regress/sql/join_hash.sql | 19 +++++++++++++++++ 3 files changed, 60 insertions(+), 1 deletion(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
* pgsql: Fix hash join when inner hashkey expressions contain Params. @ 2023-06-20 21:48 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2023-06-20 21:48 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix hash join when inner hashkey expressions contain Params. If the inner-side expressions contain PARAM_EXEC Params, we must re-hash whenever the values of those Params change. The executor mechanism for that exists already, but we failed to invoke it because finalize_plan() neglected to search the Hash.hashkeys field for Params. This allowed a previous scan's hash table to be re-used when it should not be, leading to rows missing from the join's output. (I believe incorrectly-included join rows are impossible however, since checking the real hashclauses would reject false matches.) This bug is very ancient, dating probably to d24d75ff1 of 7.4. Sadly, this simple fix depends on the plan representational changes made by 2abd7ae9b, so it will only work back to v12. I thought about trying to make some kind of hack for v11, but I'm leery of putting code significantly different from what is used in the newer branches into a nearly-EOL branch. Seeing that the bug escaped detection for a full twenty years, problematic cases must be rare; so I don't feel too awful about leaving v11 as-is. Per bug #17985 from Zuming Jiang. Back-patch to v12. Discussion: https://postgr.es/m/17985-748b66607acd432e@postgresql.org Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/d911dce14d766a16c4e5b98b54960ef4df857c3b Modified Files -------------- src/backend/optimizer/plan/subselect.c | 6 +++++- src/test/regress/expected/join_hash.out | 36 +++++++++++++++++++++++++++++++++ src/test/regress/sql/join_hash.sql | 19 +++++++++++++++++ 3 files changed, 60 insertions(+), 1 deletion(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
* pgsql: Fix hash join when inner hashkey expressions contain Params. @ 2023-06-20 21:48 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2023-06-20 21:48 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix hash join when inner hashkey expressions contain Params. If the inner-side expressions contain PARAM_EXEC Params, we must re-hash whenever the values of those Params change. The executor mechanism for that exists already, but we failed to invoke it because finalize_plan() neglected to search the Hash.hashkeys field for Params. This allowed a previous scan's hash table to be re-used when it should not be, leading to rows missing from the join's output. (I believe incorrectly-included join rows are impossible however, since checking the real hashclauses would reject false matches.) This bug is very ancient, dating probably to d24d75ff1 of 7.4. Sadly, this simple fix depends on the plan representational changes made by 2abd7ae9b, so it will only work back to v12. I thought about trying to make some kind of hack for v11, but I'm leery of putting code significantly different from what is used in the newer branches into a nearly-EOL branch. Seeing that the bug escaped detection for a full twenty years, problematic cases must be rare; so I don't feel too awful about leaving v11 as-is. Per bug #17985 from Zuming Jiang. Back-patch to v12. Discussion: https://postgr.es/m/17985-748b66607acd432e@postgresql.org Branch ------ REL_12_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/9529b1eb1b833686b09aff8f325626872be34297 Modified Files -------------- src/backend/optimizer/plan/subselect.c | 6 +++++- src/test/regress/expected/join_hash.out | 36 +++++++++++++++++++++++++++++++++ src/test/regress/sql/join_hash.sql | 19 +++++++++++++++++ 3 files changed, 60 insertions(+), 1 deletion(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
* pgsql: Fix hash join when inner hashkey expressions contain Params. @ 2023-06-20 21:48 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2023-06-20 21:48 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix hash join when inner hashkey expressions contain Params. If the inner-side expressions contain PARAM_EXEC Params, we must re-hash whenever the values of those Params change. The executor mechanism for that exists already, but we failed to invoke it because finalize_plan() neglected to search the Hash.hashkeys field for Params. This allowed a previous scan's hash table to be re-used when it should not be, leading to rows missing from the join's output. (I believe incorrectly-included join rows are impossible however, since checking the real hashclauses would reject false matches.) This bug is very ancient, dating probably to d24d75ff1 of 7.4. Sadly, this simple fix depends on the plan representational changes made by 2abd7ae9b, so it will only work back to v12. I thought about trying to make some kind of hack for v11, but I'm leery of putting code significantly different from what is used in the newer branches into a nearly-EOL branch. Seeing that the bug escaped detection for a full twenty years, problematic cases must be rare; so I don't feel too awful about leaving v11 as-is. Per bug #17985 from Zuming Jiang. Back-patch to v12. Discussion: https://postgr.es/m/17985-748b66607acd432e@postgresql.org Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/c2f974fffbecbbc10d302f69c9f4af80f0c9cd7b Modified Files -------------- src/backend/optimizer/plan/subselect.c | 6 +++++- src/test/regress/expected/join_hash.out | 36 +++++++++++++++++++++++++++++++++ src/test/regress/sql/join_hash.sql | 19 +++++++++++++++++ 3 files changed, 60 insertions(+), 1 deletion(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
* pgsql: Fix hash join when inner hashkey expressions contain Params. @ 2023-06-20 21:48 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2023-06-20 21:48 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix hash join when inner hashkey expressions contain Params. If the inner-side expressions contain PARAM_EXEC Params, we must re-hash whenever the values of those Params change. The executor mechanism for that exists already, but we failed to invoke it because finalize_plan() neglected to search the Hash.hashkeys field for Params. This allowed a previous scan's hash table to be re-used when it should not be, leading to rows missing from the join's output. (I believe incorrectly-included join rows are impossible however, since checking the real hashclauses would reject false matches.) This bug is very ancient, dating probably to d24d75ff1 of 7.4. Sadly, this simple fix depends on the plan representational changes made by 2abd7ae9b, so it will only work back to v12. I thought about trying to make some kind of hack for v11, but I'm leery of putting code significantly different from what is used in the newer branches into a nearly-EOL branch. Seeing that the bug escaped detection for a full twenty years, problematic cases must be rare; so I don't feel too awful about leaving v11 as-is. Per bug #17985 from Zuming Jiang. Back-patch to v12. Discussion: https://postgr.es/m/17985-748b66607acd432e@postgresql.org Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/2f97105e93c286a1377ddc0e2c9c7a5af4a2eee3 Modified Files -------------- src/backend/optimizer/plan/subselect.c | 6 +++++- src/test/regress/expected/join_hash.out | 36 +++++++++++++++++++++++++++++++++ src/test/regress/sql/join_hash.sql | 19 +++++++++++++++++ 3 files changed, 60 insertions(+), 1 deletion(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-06-20 21:48 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-06-20 21:48 pgsql: Fix hash join when inner hashkey expressions contain Params. Tom Lane <tgl@sss.pgh.pa.us> 2023-06-20 21:48 pgsql: Fix hash join when inner hashkey expressions contain Params. Tom Lane <tgl@sss.pgh.pa.us> 2023-06-20 21:48 pgsql: Fix hash join when inner hashkey expressions contain Params. Tom Lane <tgl@sss.pgh.pa.us> 2023-06-20 21:48 pgsql: Fix hash join when inner hashkey expressions contain Params. Tom Lane <tgl@sss.pgh.pa.us> 2023-06-20 21:48 pgsql: Fix hash join when inner hashkey expressions contain Params. Tom Lane <tgl@sss.pgh.pa.us>
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