[YSQL] Crash due to multiple-table joining

[bajinsheng]

Jira Link: DB-16502

Description

Please see this test case:

create table t0 (c0 numeric, c1 text, c2 int4);
create table t1 (c3 numeric, c4 int4, c5 timestamp, c6 int4, c7 text);
create table t2 (c11 int4, c14 text, c15 numeric);
create index i0 on t2 (c11, c15, c14);
select * from t1 a
where
  exists (
    select * from
      t2 full
      join t0 on true
      join t1 on c2 = c3
      cross join t0 b
      join t0 c on 0 = 0
      join t2 d on c5 > c5
    where
      d.c14 = (select b.c1 where a.c7 ^@ c.c1)
  ) 

The server crashes: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request..

Version:

yugabyte=# select version();
                                                                                         version                                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2-YB-2.25.1.0-b0 on x86_64-pc-linux-gnu, compiled by clang version 17.0.6 (https://github.com/yugabyte/llvm-project.git 9b881774e40024e901fc6f3d313607b071c08631), 64-bit
(1 row)

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.

[ddorian]

Hi @bajinsheng

I couldn't reproduce this on 25.1.0.0. Can you get a stack trace from the core dump https://support.yugabyte.com/hc/en-us/articles/34358075684877-Collecting-Core-Dump-of-PostgreSQL-Backend-Process-Using-gcore ?

[bajinsheng]

Sorry, can you try the version 72c0408?

And it requires two additional set:

set yb_enable_optimizer_statistics=on;
set yb_enable_base_scans_cost_model=on;

[timothy-e]

Thanks for sharing those flags, I can reproduce with those enabled so there's no need to send a core dump!

[timothy-e]

The problem is related to a BNL join without a join filter.

Applying the following patch allows the query execution to complete:

diff --git src/postgres/src/backend/optimizer/plan/createplan.c src/postgres/src/backend/optimizer/plan/createplan.c
index b340f36c661..41033e0e0b4 100644
--- src/postgres/src/backend/optimizer/plan/createplan.c
+++ src/postgres/src/backend/optimizer/plan/createplan.c
@@ -5934,11 +5934,11 @@ create_nestloop_plan(PlannerInfo *root,
 		 */
 		ListCell   *l;
 
-		yb_hashClauseInfos =
-			palloc0(joinrestrictclauses->length * sizeof(YbBNLHashClauseInfo));
-
 		/* YB: This length is later adjusted in setrefs.c. */
-		yb_num_hashClauseInfos = joinrestrictclauses->length;
+		yb_num_hashClauseInfos = joinrestrictclauses ? joinrestrictclauses->length : 0;
+
+		yb_hashClauseInfos =
+			palloc0(yb_num_hashClauseInfos * sizeof(YbBNLHashClauseInfo));
 
 		Relids		batched_outerrelids = bms_difference(outerrelids,
 														 yb_get_unbatched_relids(best_path));
diff --git src/postgres/src/backend/optimizer/plan/setrefs.c src/postgres/src/backend/optimizer/plan/setrefs.c
index 9584f1b77a8..e53d6e3cb8f 100644
--- src/postgres/src/backend/optimizer/plan/setrefs.c
+++ src/postgres/src/backend/optimizer/plan/setrefs.c
@@ -2384,7 +2384,7 @@ set_join_references(PlannerInfo *root, Join *join, int rtoffset)
 
 		ListCell   *l;
 
-		if (IsA(join, YbBatchedNestLoop))
+		if (IsA(join, YbBatchedNestLoop) && join->joinqual)
 		{
 			YbBatchedNestLoop *batchednl = (YbBatchedNestLoop *) join;

and shows the plan:

                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 YB Batched Nested Loop Semi Join (actual rows=0 loops=1)
   Join Filter: ((SubPlan 1) = d.c14)
   ->  Seq Scan on t1 a (actual rows=0 loops=1)
   ->  YB Batched Nested Loop Join (never executed)
         ->  Seq Scan on t0 c (never executed)
         ->  YB Batched Nested Loop Join (never executed)
               ->  Seq Scan on t0 b (never executed)
               ->  Nested Loop (never executed)
                     ->  Nested Loop Left Join (never executed)
                           ->  Nested Loop (never executed)
                                 Join Filter: ((t0.c2)::numeric = t1.c3)
                                 ->  Seq Scan on t0 (never executed)
                                 ->  Seq Scan on t1 (never executed)
                                       Storage Filter: (c5 > c5)
                           ->  Index Only Scan using i0 on t2 (never executed)
                                 Heap Fetches: 0
                     ->  Index Only Scan using i0 on t2 d (never executed)
                           Index Cond: (c14 = ANY (ARRAY[(SubPlan 1), (SubPlan 1), (SubPlan 1), ..., (SubPlan 1)]))
                           Heap Fetches: 0
                           SubPlan 1
                             ->  Result *RESULT* (never executed)
                                   One-Time Filter: (a.c7 ^@ c.c1)
(22 rows)

Searching for the regex YB Batched.*\n\s+[A-IK-Z] in our code base shows that every tested BNL plan is followed by a Join Filter.

[mtakahar]

The plan above doesn't look correct:

1. Index Cond: (c14 = ANY (ARRAY[(SubPlan 1), (SubPlan 1), (SubPlan 1), ..., (SubPlan 1)])): All the "SubPlan 1" are the same in each iteration.
2. There's no batched expression corresponding to the inner BNL.

It seems to be failing to recognize missing "batchable" join predicate.

[mtakahar]

This issue making noises in RQG runs.