Lateral expression crash in table join queries

[jd-zhang]

Issue migrated from trac ticket # 43

component: computing nodes | priority: major | resolution: fixed

2021-04-15 16:54:38: @jd-zhang created the issue

---

sql code:

CREATE TABLE INT4_TBL(f1 int4); CREATE TABLE INT8_TBL(q1 int8, q2 int8); CREATE TABLE TEXT_TBL (f1 text);

explain (verbose, costs off) select from text_tbl t1 left join int8_tbl i8 on i8.q2 = 123, lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1, lateral (select ss1. from text_tbl t3 limit 1) as ss2 where t1.f1 = ss2.f1; psql: server closed the connection unexpectedly < This probably means the server terminated abnormally < before or while processing the request.

-- AND no explain--

select from text_tbl t1 left join int8_tbl i8 on i8.q2 = 123, lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1, lateral (select ss1. from text_tbl t3 limit 1) as ss2 where t1.f1 = ss2.f1; psql: server closed the connection unexpectedly < This probably means the server terminated abnormally < before or while processing the request.

-- AND --

select from text_tbl t1 left join int8_tbl i8 on i8.q2 = 123, lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1, lateral (select ss1. from text_tbl t3 limit 1) as ss2 where t1.f1 = ss2.f1; psql: server closed the connection unexpectedly < This probably means the server terminated abnormally < before or while processing the request.

-- AND --

select ss2. from int4_tbl i41 left join int8_tbl i8 join (select i42.f1 as c1, i43.f1 as c2, 42 as c3 from int4_tbl i42, int4_tbl i43) ss1 on i8.q1 = ss1.c2 on i41.f1 = ss1.c1, lateral (select i41., i8., ss1. from text_tbl limit 1) ss2 where ss1.c2 = 0;

--- others are not listed here ---

[jd-zhang]

2021-04-26 12:06:53: @jd-zhang

[jd-zhang]

2021-04-26 12:06:53: @jd-zhang commented

---

Another implicit lateral sql code:

tdb1=# CREATE TABLE rngfunc2(rngfuncid int, f2 int); CREATE TABLE tdb1=# INSERT INTO rngfunc2 VALUES(1, 11); INSERT 0 1 tdb1=# INSERT INTO rngfunc2 VALUES(2, 22); INSERT 0 1 tdb1=# INSERT INTO rngfunc2 VALUES(1, 111); INSERT 0 1 tdb1=# CREATE FUNCTION rngfunct(int) returns setof rngfunc2 as 'SELECT FROM rngfunc2 WHERE rngfuncid = $1 ORDER BY f2;' LANGUAGE SQL; CREATE FUNCTION tdb1=# select from rngfunc2, rngfunct(rngfunc2.rngfuncid) z where rngfunc2.f2 = z.f2; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. psql (12.5 (Ubuntu 12.5-0ubuntu0.20.04.1), server 11.5)

similar commands are: select * from rngfunc2, rngfunct(rngfunc2.rngfuncid) with ordinality as z(rngfuncid,f2,ord) where rngfunc2.f2 = z.f2;

select * from rngfunc2 where f2 in (select f2 from rngfunct(rngfunc2.rngfuncid) z where z.rngfuncid = rngfunc2.rngfuncid) ORDER BY 1,2;

select * from rngfunc2 where f2 in (select f2 from rngfunct(1) z where z.rngfuncid = rngfunc2.rngfuncid) ORDER BY 1,2;

select * from rngfunc2 where f2 in (select f2 from rngfunct(rngfunc2.rngfuncid) z where z.rngfuncid = 1) ORDER BY 1,2;

[jd-zhang]

2021-04-28 15:23:05: @jd-zhang commented

---

similar case:

DROP TABLE if exists prt1_l; CREATE TABLE prt1_l (a int, b int, c varchar) PARTITION BY RANGE(a); CREATE TABLE prt1_l_p1 PARTITION OF prt1_l FOR VALUES FROM (0) TO (250); CREATE TABLE prt1_l_p2 PARTITION OF prt1_l FOR VALUES FROM (250) TO (500) PARTITION BY LIST (c); CREATE TABLE prt1_l_p2_p1 PARTITION OF prt1_l_p2 FOR VALUES IN ('0000', '0001'); CREATE TABLE prt1_l_p2_p2 PARTITION OF prt1_l_p2 FOR VALUES IN ('0002', '0003'); CREATE TABLE prt1_l_p3 PARTITION OF prt1_l FOR VALUES FROM (500) TO (600) PARTITION BY RANGE (b); CREATE TABLE prt1_l_p3_p1 PARTITION OF prt1_l_p3 FOR VALUES FROM (0) TO (13); CREATE TABLE prt1_l_p3_p2 PARTITION OF prt1_l_p3 FOR VALUES FROM (13) TO (25); INSERT INTO prt1_l SELECT i, i % 25, to_char(i % 4, 'FM0000') FROM generate_series(0, 599, 2) i; DROP TABLE if exists prt2_l; CREATE TABLE prt2_l (a int, b int, c varchar) PARTITION BY RANGE(b); CREATE TABLE prt2_l_p1 PARTITION OF prt2_l FOR VALUES FROM (0) TO (250); CREATE TABLE prt2_l_p2 PARTITION OF prt2_l FOR VALUES FROM (250) TO (500) PARTITION BY LIST (c); CREATE TABLE prt2_l_p2_p1 PARTITION OF prt2_l_p2 FOR VALUES IN ('0000', '0001'); CREATE TABLE prt2_l_p2_p2 PARTITION OF prt2_l_p2 FOR VALUES IN ('0002', '0003'); CREATE TABLE prt2_l_p3 PARTITION OF prt2_l FOR VALUES FROM (500) TO (600) PARTITION BY RANGE (a); CREATE TABLE prt2_l_p3_p1 PARTITION OF prt2_l_p3 FOR VALUES FROM (0) TO (13); CREATE TABLE prt2_l_p3_p2 PARTITION OF prt2_l_p3 FOR VALUES FROM (13) TO (25); INSERT INTO prt2_l SELECT i % 25, i, to_char(i % 4, 'FM0000') FROM generate_series(0, 599, 3) i; SELECT * FROM prt1_l t1 LEFT JOIN LATERAL (SELECT t2.a AS t2a, t2.c AS t2c, t2.b AS t2b, t3.b AS t3b, least(t1.a,t2.a,t3.b) FROM prt1_l t2 JOIN prt2_l t3 ON (t2.a # t3.b AND t2.ct3.c)) ss ON t1.a # ss.t2a AND t1.css.t2c WHERE t1.b = 0 ORDER BY t1.a;

This will crash: The interesting thing is that, if we add 'SET enable_partitionwise_join to true;' to the beginning, the last sql statement does not crash, but errors, like following: ERROR: invalid input syntax for integer: "▒▒▒"

[jd-zhang]

2021-05-12 12:39:04: @david-zhao

[jd-zhang]

2021-05-12 12:39:04: @david-zhao changed status from assigned to accepted

[jd-zhang]

2021-05-25 10:21:55: @david-zhao commented

---

Milestone renamed

[jd-zhang]

2021-05-25 10:21:55: @david-zhao

[jd-zhang]

2021-07-19 15:20:47: @charleszettadb commented

---

VERIFIED FIXED

[jd-zhang]

2021-07-19 15:20:47: @charleszettadb set resolution to fixed

[jd-zhang]

2021-07-19 15:20:47: @charleszettadb changed status from accepted to closed

[jd-zhang]

2021-10-18 16:44:27: @david-zhao commented

---

this bug need reappears after agg push down.

[jd-zhang]

2021-10-18 16:44:27: @david-zhao

[jd-zhang]

2021-10-18 16:44:27: @david-zhao removed resolution (was fixed)

[jd-zhang]

2021-10-18 16:44:27: @david-zhao changed status from closed to reopened

[jd-zhang]

2021-12-20 15:26:34: @vito4172 commented

---

It has been confirmed that the above problems do not exist

[jd-zhang]

2021-12-20 15:26:34: @vito4172 set resolution to fixed

[jd-zhang]

2021-12-20 15:26:34: @vito4172 changed status from reopened to closed