agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19626: Segmentation fault planning self-join IN subquery with LATERAL UNION ALL
9+ messages / 5 participants
[nested] [flat]
* BUG #19626: Segmentation fault planning self-join IN subquery with LATERAL UNION ALL
@ 2026-08-18 03:06 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 9+ messages in thread
From: PG Bug reporting form @ 2026-08-18 03:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: syzhong16@gmail.com
The following bug has been logged on the website:
Bug reference: 19626
Logged by: Suyang Zhong
Email address: syzhong16@gmail.com
PostgreSQL version: 19beta3
Operating system: Ubuntu 22.04
Description:
Hi,
The following test case caused a segmentation fault.
```
CREATE TABLE t0(c2 INT PRIMARY KEY, c3 INT);
SELECT count(*) FROM t0
INNER JOIN LATERAL (SELECT t0.c3 UNION ALL SELECT t0.c3) AS s ON (s.c3 IS
NOT NULL)
WHERE t0.c2 IN (SELECT c2 FROM t0);
-- server closed the connection unexpectedly
```
Here's the log on the server side.
```
2026-08-18 02:50:27.936 UTC [530] STATEMENT: CREATE TABLE t0(c2 INT PRIMARY
KEY, c3 INT);
2026-08-18 02:50:32.126 UTC [1] LOG: client backend (PID 530) was
terminated by signal 11: Segmentation fault
2026-08-18 02:50:32.126 UTC [1] DETAIL: Failed process was running: SELECT
count(*) FROM t0
INNER JOIN LATERAL (SELECT t0.c3 UNION ALL SELECT t0.c3) AS s ON
(s.c3 IS NOT NULL)
WHERE t0.c2 IN (SELECT c2 FROM t0);
2026-08-18 02:50:32.126 UTC [1] LOG: terminating any other active server
processes
2026-08-18 02:50:32.128 UTC [1] LOG: all server processes terminated;
reinitializing
2026-08-18 02:50:32.143 UTC [534] LOG: database system was interrupted;
last known up at 2026-08-18 01:27:03 UTC
```
Reproduced on 20devel and 19beta3.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19626: Segmentation fault planning self-join IN subquery with LATERAL UNION ALL
@ 2026-08-18 13:29 Andrey Rachitskiy <pl0h0yp1@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 9+ messages in thread
From: Andrey Rachitskiy @ 2026-08-18 13:29 UTC (permalink / raw)
To: syzhong16@gmail.com; pgsql-bugs@lists.postgresql.org
вт, 18 авг. 2026 г. в 17:16, PG Bug reporting form <noreply@postgresql.org>:
> The following bug has been logged on the website:
>
> Bug reference: 19626
> Logged by: Suyang Zhong
> Email address: syzhong16@gmail.com
> PostgreSQL version: 19beta3
> Operating system: Ubuntu 22.04
> Description:
>
> Hi,
>
> The following test case caused a segmentation fault.
>
> ```
> CREATE TABLE t0(c2 INT PRIMARY KEY, c3 INT);
>
> SELECT count(*) FROM t0
> INNER JOIN LATERAL (SELECT t0.c3 UNION ALL SELECT t0.c3) AS s ON (s.c3 IS
> NOT NULL)
> WHERE t0.c2 IN (SELECT c2 FROM t0);
> -- server closed the connection unexpectedly
> ```
>
> Hi, Suyang!
Thanks for the report.
SET enable_self_join_elimination = off avoids the crash.
So does replacing the IS NOT NULL with ON true, using UNION rather than
UNION ALL, or omitting a unique index on c2. A UNIQUE constraint is enough.
It does not have to be a primary key.
Backtrace:
```
#0 var_is_nonnullable (root=..., var=..., source=...) at clauses.c:4726
rte = 0x0
#1 expr_is_nonnullable () at clauses.c:4823
#2 eval_const_expressions_mutator () at clauses.c:3942
#3 eval_const_expressions () at clauses.c:2537
#4 apply_child_basequals () at inherit.c:866
#5 build_simple_rel () at relnode.c:425
#6 expand_appendrel_subquery () at inherit.c:818
#7 expand_inherited_rtentry () at inherit.c:102
#8 add_other_rels_to_query () at initsplan.c:235
#9 query_planner () at planmain.c:285
```
The LATERAL UNION ALL is flattened to an appendrel. The leaf expressions
t0.c3 are stored in AppendRelInfo.translated_vars. The IN subquery on the
primary key is reduced to an inner join and then removed by self-join
elimination. SJE rewrites Vars in the Query tree, PlaceHolderVars,
RestrictInfos and EquivalenceClasses. It does not touch
root->append_rel_list. After that it NULLs the removed rel's slots in
simple_rel_array and simple_rte_array.
add_other_rels_to_query runs later. apply_child_basequals() substitutes the
stale translated_vars into s.c3 IS NOT NULL. eval_const_expressions() then
asks var_is_nonnullable() about a Var whose varno has no RTE.
planner_rt_fetch() returns NULL because simple_rte_array is already built
and that slot is empty. The subsequent rte->rtekind dereference crashes.
The attached patch runs ChangeVarNodesExtended() over append_rel_list in
remove_self_join_rel(), same as for the Query tree.
--
Regards,
Rachitskiy Andrey
Attachments:
[text/x-patch] 0001-Fix-SIGSEGV-in-self-join-elimination-with-LATERAL-UNION-ALL.patch (2.8K, ../../CAB8bMitstgj-VxwVPKYjdvXt+Q9oLyjXZ6BHOnwycCEEbAA8OQ@mail.gmail.com/3-0001-Fix-SIGSEGV-in-self-join-elimination-with-LATERAL-UNION-ALL.patch)
download | inline diff:
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Subject: [PATCH] Fix SIGSEGV in self-join elimination with LATERAL UNION ALL
SJE rewrites Vars in the Query tree but not in
AppendRelInfo.translated_vars. A LATERAL UNION ALL can store Vars of
the removed relation there. After the RTE slot is cleared,
apply_child_basequals() const-simplifies IS NOT NULL on that Var and
crashes in var_is_nonnullable().
Update translated_vars when substituting the removed relid.
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 87a5d9d58b8..4afd969d1dd 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -2171,6 +2171,10 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark *kmark, PlanRowMark *rmark,
ChangeVarNodesExtended((Node *) root->parse, toRemove->relid, toKeep->relid,
0, replace_relid_callback);
+ /* Replace varno in translated_vars, else they keep the removed relid */
+ ChangeVarNodesExtended((Node *) root->append_rel_list, toRemove->relid,
+ toKeep->relid, 0, replace_relid_callback);
+
/* Replace links in the planner info */
remove_rel_from_query(root, toRemove->relid, toKeep->relid, NULL, NULL);
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 05f359d3aa7..332133fcbca 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -8287,6 +8287,24 @@ SELECT a1.a FROM sj a1,sj a2 WHERE (a1.a=a2.a) FOR UPDATE;
Filter: (a IS NOT NULL)
(3 rows)
+-- Check that SJE rewrites Vars in a flattened LATERAL UNION ALL
+explain (costs off)
+select count(*) from sj p
+inner join lateral (select p.b union all select p.b) s(x) on (s.x is not null)
+where p.a in (select a from sj);
+ QUERY PLAN
+---------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Seq Scan on sj
+ Filter: (a IS NOT NULL)
+ -> Append
+ -> Result
+ One-Time Filter: (sj.b IS NOT NULL)
+ -> Result
+ One-Time Filter: (sj.b IS NOT NULL)
+(9 rows)
+
reset enable_hashjoin;
reset enable_mergejoin;
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 450bd5bbf2c..3df7ce58725 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3260,6 +3260,12 @@ ON sj_t1.id = _t2t3t4.id;
EXPLAIN (COSTS OFF)
SELECT a1.a FROM sj a1,sj a2 WHERE (a1.a=a2.a) FOR UPDATE;
+-- Check that SJE rewrites Vars in a flattened LATERAL UNION ALL
+explain (costs off)
+select count(*) from sj p
+inner join lateral (select p.b union all select p.b) s(x) on (s.x is not null)
+where p.a in (select a from sj);
+
reset enable_hashjoin;
reset enable_mergejoin;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19626: Segmentation fault planning self-join IN subquery with LATERAL UNION ALL
@ 2026-08-19 02:28 Tender Wang <tndrwang@gmail.com>
parent: Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tender Wang @ 2026-08-19 02:28 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: syzhong16@gmail.com, pgsql-bugs@lists.postgresql.org, Alexander Korotkov <aekorotkov@gmail.com>
Andrey Rachitskiy <pl0h0yp1@gmail.com> 于2026年8月18日周二 21:29写道：
>
>
> вт, 18 авг. 2026 г. в 17:16, PG Bug reporting form <noreply@postgresql.org>:
>>
>> CREATE TABLE t0(c2 INT PRIMARY KEY, c3 INT);
>>
>> SELECT count(*) FROM t0
>> INNER JOIN LATERAL (SELECT t0.c3 UNION ALL SELECT t0.c3) AS s ON (s.c3 IS
>> NOT NULL)
>> WHERE t0.c2 IN (SELECT c2 FROM t0);
> The LATERAL UNION ALL is flattened to an appendrel. The leaf expressions t0.c3 are stored in AppendRelInfo.translated_vars. The IN subquery on the primary key is reduced to an inner join and then removed by self-join elimination. SJE rewrites Vars in the Query tree, PlaceHolderVars, RestrictInfos and EquivalenceClasses. It does not touch root->append_rel_list. After that it NULLs the removed rel's slots in simple_rel_array and simple_rte_array.
>
> add_other_rels_to_query runs later. apply_child_basequals() substitutes the stale translated_vars into s.c3 IS NOT NULL. eval_const_expressions() then asks var_is_nonnullable() about a Var whose varno has no RTE. planner_rt_fetch() returns NULL because simple_rte_array is already built and that slot is empty. The subsequent rte->rtekind dereference crashes.
>
In apply_child_basequals(), it calls adjust_appendrel_attrs() to
adjust childqual's varno to childRTIndex according to appinfo; the
current info is below:
(gdb) pgprint rinfo->clause <=== parent qual
NullTest [nulltesttype=IS_NOT_NULL argisrow=false location=103]
[arg] Var [varno=2 varattno=1 vartype=23
varreturningtype=VAR_RETURNING_DEFAULT varnosyn=2 varattnosyn=1]
(gdb) pgprint appinfo
AppendRelInfo [parent_relid=2 child_relid=7 parent_reltype=0
child_reltype=0 num_child_cols=1 parent_colnos=0x5fcd7f621d58
parent_reloid=0]
[translated_vars]
Var [varno=1 varattno=2 vartype=23
varreturningtype=VAR_RETURNING_DEFAULT varnosyn=1 varattnosyn=2]
Then the childqual was adjusted to:
(gdb) pgprint childqual
NullTest [nulltesttype=IS_NOT_NULL argisrow=false location=103]
[arg] Var [varno=1 varattno=2 vartype=23
varreturningtype=VAR_RETURNING_DEFAULT varnosyn=1 varattnosyn=2]
"varno=1" is the reference to the first rtable in parse->rtable. But
that rtable was set to NULL after SJE. So the crash occurs in
var_is_nonnullable().
> The attached patch runs ChangeVarNodesExtended() over append_rel_list in remove_self_join_rel(), same as for the Query tree.
The patch looks good to me.
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19626: Segmentation fault planning self-join IN subquery with LATERAL UNION ALL
@ 2026-08-19 07:02 Andrey Rachitskiy <pl0h0yp1@gmail.com>
parent: Tender Wang <tndrwang@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Andrey Rachitskiy @ 2026-08-19 07:02 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: syzhong16@gmail.com, pgsql-bugs@lists.postgresql.org, Alexander Korotkov <aekorotkov@gmail.com>
ср, 19 авг. 2026 г. в 07:28, Tender Wang <tndrwang@gmail.com>:
>
> The patch looks good to me.
>
> Hi Tender,
Thanks for the review.
--
Regards,
Rachitskiy Andrey
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19626: Segmentation fault planning self-join IN subquery with LATERAL UNION ALL
@ 2026-08-25 19:23 Nathan Bossart <nathandbossart@gmail.com>
parent: Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Nathan Bossart @ 2026-08-25 19:23 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: Tender Wang <tndrwang@gmail.com>; syzhong16@gmail.com, pgsql-bugs@lists.postgresql.org, Alexander Korotkov <aekorotkov@gmail.com>
Does this one deserve a mention on the open items wiki [0]?
[0] https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items
--
nathan
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19626: Segmentation fault planning self-join IN subquery with LATERAL UNION ALL
@ 2026-08-25 20:02 Andrey Rachitskiy <pl0h0yp1@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Andrey Rachitskiy @ 2026-08-25 20:02 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Tender Wang <tndrwang@gmail.com>; syzhong16@gmail.com, pgsql-bugs@lists.postgresql.org, Alexander Korotkov <aekorotkov@gmail.com>
ср, 26 авг. 2026 г. в 00:23, Nathan Bossart <nathandbossart@gmail.com>:
> Does this one deserve a mention on the open items wiki [0]?
>
> [0] https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items
>
>
> Dear Nathan,
I think we can add that.
--
Regards,
Rachitskiy Andrey
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19626: Segmentation fault planning self-join IN subquery with LATERAL UNION ALL
@ 2026-09-04 03:11 Tender Wang <tndrwang@gmail.com>
parent: Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tender Wang @ 2026-09-04 03:11 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: Nathan Bossart <nathandbossart@gmail.com>; syzhong16@gmail.com, pgsql-bugs@lists.postgresql.org, Alexander Korotkov <aekorotkov@gmail.com>
Hi all,
Andrey Rachitskiy <pl0h0yp1@gmail.com> 于2026年8月26日周三 04:02写道：
>
>
>
> ср, 26 авг. 2026 г. в 00:23, Nathan Bossart <nathandbossart@gmail.com>:
>>
>> Does this one deserve a mention on the open items wiki [0]?
>>
>> [0] https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items
>>
>>
> Dear Nathan,
>
> I think we can add that.
>
With 2ebf25e7d70a, this crash is gone. I think we can remove this item
from pg19_open_items.
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19626: Segmentation fault planning self-join IN subquery with LATERAL UNION ALL
@ 2026-09-04 06:38 Alexander Korotkov <aekorotkov@gmail.com>
parent: Tender Wang <tndrwang@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Alexander Korotkov @ 2026-09-04 06:38 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: Andrey Rachitskiy <pl0h0yp1@gmail.com>; Nathan Bossart <nathandbossart@gmail.com>; syzhong16@gmail.com; pgsql-bugs@lists.postgresql.org
Hi, Tender,
HI, Andrey,
On Fri, Sep 4, 2026 at 6:11 AM Tender Wang <tndrwang@gmail.com> wrote:
> Andrey Rachitskiy <pl0h0yp1@gmail.com> 于2026年8月26日周三 04:02写道：
> >
> >
> >
> > ср, 26 авг. 2026 г. в 00:23, Nathan Bossart <nathandbossart@gmail.com>:
> >>
> >> Does this one deserve a mention on the open items wiki [0]?
> >>
> >> [0] https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items
> >>
> >>
> > Dear Nathan,
> >
> > I think we can add that.
> >
>
> With 2ebf25e7d70a, this crash is gone. I think we can remove this item
> from pg19_open_items.
I was going to look at this. Andrey, could you, please, recheck if
issue is gone and your patch no longer needed?
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19626: Segmentation fault planning self-join IN subquery with LATERAL UNION ALL
@ 2026-09-04 08:13 Andrey Rachitskiy <pl0h0yp1@gmail.com>
parent: Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Andrey Rachitskiy @ 2026-09-04 08:13 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Tender Wang <tndrwang@gmail.com>; Nathan Bossart <nathandbossart@gmail.com>; syzhong16@gmail.com; pgsql-bugs@lists.postgresql.org
пт, 4 сент. 2026 г. в 11:38, Alexander Korotkov <aekorotkov@gmail.com>:
>
> I was going to look at this. Andrey, could you, please, recheck if
> issue is gone and your patch no longer needed?
>
>
> Hi, Alexander!
I've re-tested this, and the bug is no longer reproducible.
Tom has already fixed it.
Please take a look at this thread: [0] — it's still relevant.
[0]
https://www.postgresql.org/message-id/CAB8bMivfsjkq_kG3VehogS8-PNMMxuVdprpXKMPxoEyh6WS-Rw@mail.gmail...
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-09-04 08:13 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-18 03:06 BUG #19626: Segmentation fault planning self-join IN subquery with LATERAL UNION ALL PG Bug reporting form <noreply@postgresql.org>
2026-08-18 13:29 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-19 02:28 ` Tender Wang <tndrwang@gmail.com>
2026-08-19 07:02 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-25 19:23 ` Nathan Bossart <nathandbossart@gmail.com>
2026-08-25 20:02 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-04 03:11 ` Tender Wang <tndrwang@gmail.com>
2026-09-04 06:38 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-09-04 08:13 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox