error in partition join query

[jd-zhang]

Issue migrated from trac ticket # 66

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

2021-04-28 12:29:34: @jd-zhang created the issue

---

sql code:

DROP TABLE if exists prt1; DROP TABLE if exists prt2; CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a); CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250); CREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (500) TO (600); CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500); CREATE TABLE prt2 (a int, b int, c varchar) PARTITION BY RANGE(b); CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (0) TO (250); CREATE TABLE prt2_p2 PARTITION OF prt2 FOR VALUES FROM (250) TO (500); CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (500) TO (600); SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a # t2.b WHERE t1.b0 ORDER BY t1.a, t2.b;

error output: ERROR: MySQL storage node (1, 1) returned error: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the rig, a from tdb1_$$_public.prt1' at line 1. ERROR: MySQL storage node (2, 2) returned error: 1399, XAER_RMFAIL: The command cannot be executed when global transaction is in the NON-EXISTING state.

The error does no happen if no data is populated.

A similar query sql is:

SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text # t2::text AND t1.at2.b ORDER BY t1.a;

[jd-zhang]

2021-04-28 12:30:27: @jd-zhang commented

---

A similar crash case for partition join:

DROP TABLE if exists prt1; DROP TABLE if exists prt2; CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a); CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250); CREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (500) TO (600); CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500); INSERT INTO prt1 SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(0, 599) i WHERE i % 2 = 0; CREATE TABLE prt2 (a int, b int, c varchar) PARTITION BY RANGE(b); CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (0) TO (250); CREATE TABLE prt2_p2 PARTITION OF prt2 FOR VALUES FROM (250) TO (500); CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (500) TO (600); INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(0, 599) i WHERE i % 3 = 0; SELECT * FROM prt1 t1 LEFT JOIN LATERAL (SELECT t2.a AS t2a, t3.a AS t3a, least(t1.a,t2.a,t3.b) FROM prt1 t2 JOIN prt2 t3 ON (t2.a # t3.b)) ss ON t1.ass.t2a WHERE t1.b = 0 ORDER BY t1.a; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

[jd-zhang]

2021-04-28 15:13:31: @jd-zhang edited the issue description

[jd-zhang]

2021-05-13 19:02:55: @david-zhao

[jd-zhang]

2021-05-13 19:02:55: @david-zhao changed severity from minor to normal

[jd-zhang]

2021-05-13 19:02:55: @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-28 01:19:03: @jd-zhang commented

---

The following query still can not run:

SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text # t2::text AND t1.at2.b ORDER BY t1.a;

This is because we have limited support for whole-row field currently, so change it to kunlun-1.0

[jd-zhang]

2021-07-28 01:19:03: @jd-zhang

[jd-zhang]

2021-12-20 15:16:32: @vito4172 commented

---

It has been confirmed that the above problems do not exist

SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text # t2::text AND t1.at2.b ORDER BY t1.a;

transfer to #324

[jd-zhang]

2021-12-20 15:16:32: @vito4172 set resolution to fixed

[jd-zhang]

2021-12-20 15:16:32: @vito4172 changed status from accepted to closed