Error "ERROR: unrecognized node type: 233" generated by Query containing sub-queries to test Where clause push-down

[kashifzeeshan]

Error "ERROR: unrecognized node type: 233" generated by Query containing sub-queries to test Where clause push-down.

Error on Foreign Table

edb=# 
edb=# EXPLAIN (COSTS FALSE,VERBOSE TRUE) SELECT empno, ename, ea.deptno FROM emp_fr_tbl ea
edb-# WHERE sal = (SELECT MAX(sal) FROM emp_fr_tbl eb 
edb(#      WHERE eb.deptno = ea.deptno)
edb-# ORDER BY deptno;
ERROR:  unrecognized node type: 233
edb=# 
edb=# 

edb=# SELECT empno, ename, ea.deptno FROM emp_fr_tbl ea
edb-# WHERE sal = (SELECT MAX(sal) FROM emp_fr_tbl eb 
edb(#      WHERE eb.deptno = ea.deptno)
edb-# ORDER BY deptno;
ERROR:  unrecognized node type: 233
edb=# 
edb=# 

MySQL Data Setup

mysql> 
mysql>  create database mysql_test;
Query OK, 1 row affected (0.06 sec)

mysql> use mysql_test;
Database changed
mysql> 
mysql> 

CREATE TABLE emp (
    empno           INT primary key,
    ename           VARCHAR(10) ,
    job             CHAR(9),
    mgr             MEDIUMINT,
    hiredate        DATE,
    sal             DECIMAL(10,5),
    comm            INT,
    deptno          SMALLINT
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800.23,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975.12,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450.45,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1980-09-08',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

mysql> 
mysql> 
mysql> select * from emp;
+-------+--------+-----------+------+------------+------------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal        | comm | deptno |
+-------+--------+-----------+------+------------+------------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.23000 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00000 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00000 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.12000 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00000 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00000 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.45000 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1980-09-08 | 1500.00000 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00000 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00000 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00000 | NULL |     10 |
+-------+--------+-----------+------+------------+------------+------+--------+
14 rows in set (0.00 sec)

EPAS10 Data Setup

CREATE EXTENSION mysql_fdw;

-- Create MySQL FDW Server.

CREATE SERVER mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '127.0.0.1', port '3306');

-- Create MySQL USER MAPPING.

CREATE USER MAPPING FOR edb SERVER mysql_server
OPTIONS (username 'root', password 'Fusion123!');

-- Create Foreign Table.

CREATE FOREIGN TABLE emp_fr_tbl (
    empno           INTEGER,
    ename           VARCHAR(10),
    job             CHAR(9),
    mgr             BIGINT,
    hiredate        DATE,
    sal             DECIMAL,
    comm            INTEGER,
    deptno          SMALLINT
)
SERVER mysql_server OPTIONS (dbname 'mysql_test', table_name 'emp');