Select w/ SubPlan and Self-join: "failed to execute the MySQL query: " (no error description)

[smilingthax]

Postgres log output of minimal reproducible example:

2024-07-22 11:08:42.039 UTC [173] ERROR:  failed to execute the MySQL query:

2024-07-22 11:08:42.039 UTC [173] STATEMENT:  
        WITH tbl1 (id, pid) AS MATERIALIZED (
	  VALUES (1,1)
	)
	SELECT
	  (SELECT
	     t.id
	   FROM ext.test1 "t"
	   LEFT JOIN ext.test1 "s"
	          ON s.pid = t.pid
	   WHERE t.pid = tbl1.pid
	     AND t.id = tbl1.id
	  ) "val"
	FROM tbl1

Query Plan:

                                                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on tbl1  (cost=0.01..35.03 rows=1 width=4)
   Output: (SubPlan 2)
   CTE tbl1
     ->  Result  (cost=0.00..0.01 rows=1 width=8)
           Output: 1, 1
   SubPlan 2
     ->  Foreign Scan  (cost=15.00..35.00 rows=1000000 width=4)
           Output: t.id
           Relations: (test.test1 t) LEFT JOIN (test.test1 s)
           Remote server startup cost: 25
           Remote query: SELECT r1.`id` FROM (`test`.`test1` r1 LEFT JOIN `test`.`test1` r2 ON ((true) AND ((r2.`pid` = ?)))) WHERE ((r1.`pid` = ?)) AND ((r1.`id` = ?))
(11 rows)

(Originally another external table was used instead of a materialized CTE, w/ same error; instead of MATERIALIZED OFFSET 0 can be used to prevent the optimizer from removing the SubPlan).

Mariadb setup (docker image):

-- MariaDB Server 1:11.4.2+Maria~ubu2404

CREATE TABLE test1 (id int primary key auto_increment, pid int not null); 
-- table can be left empty

Postgres setup (docker image + apt install postgresql-16-mysql-fdw):

-- PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
-- postgresql-16-mysql-fdw  2.9.2-1.pgdg120+1 amd64

-- ... CREATE EXTENSION, CREATE SERVER, CREATE USER MAPPING ...
CREATE SCHEMA ext;
IMPORT FOREIGN SCHEMA "test" FROM SERVER mariadb_server INTO ext;

Mariadb general_log:

240722 11:08:42      5 Connect  [email protected] on test using SSL/TLS
                     5 Query    SET sql_mode = 'ANSI_QUOTES'
                     5 Query    SET sql_mode = 'ANSI_QUOTES'
                     5 Query    SET sql_mode = 'ANSI_QUOTES'
                     5 Prepare  SELECT r1.`id` FROM (`test`.`test1` r1 LEFT JOIN `test`.`test1` r2 ON ((true) AND ((r2.`pid` = ?)))) WHERE ((r1.`pid` = ?)) AND ((r1.`id` = ?))
                     5 Quit

(AFAICT a working query would include Close stmt between Prepare and Quit)

[surajkharage19]

Hi @smilingthax,

Thanks for reporting this issue. I can reproduce the same and will look into this.