Postgres Volatile functions getting pushed down

[robins]

Issue: Postgres Volatile functions are getting pushed down to MySQL which obviously fail.

Expected: Ideally I'd expect Postgres Functions to not get pushed down (for EXPLAIN or for Query execution) to MySQL.

Notes:

• Stable / Immutable functions work correctly
• EXPLAIN fails too
• Error message clearly shows the function schema name changing, which seems to be part of the issue.
• Tested on MySQL_FDW (master) and Postgres v13.4.

Sample repro:

mysql_fdw_sample=# explain SELECT ref_0.STATE
mysql_fdw_sample-# FROM PUBLIC.offices AS ref_0
mysql_fdw_sample-# WHERE pg_catalog.timeofday() IS NOT NULL limit 1;
ERROR:  failed to prepare the MySQL query:
FUNCTION classicmodels.timeofday does not exist
mysql_fdw_sample=#

Detailed Repro:

mysql_fdw_sample-# FROM PUBLIC.offices AS ref_0
mysql_fdw_sample-# WHERE pg_catalog.pi() IS NOT NULL limit 1;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Limit  (cost=25.00..26.00 rows=1 width=118)
   ->  Foreign Scan on offices ref_0  (cost=25.00..1025.00 rows=1000 width=118)
         Remote server startup cost: 25
(3 rows)

mysql_fdw_sample=# explain SELECT ref_0.STATE
mysql_fdw_sample-# FROM PUBLIC.offices AS ref_0
mysql_fdw_sample-# WHERE pg_catalog.version() IS NOT NULL limit 1;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Limit  (cost=25.00..26.00 rows=1 width=118)
   ->  Result  (cost=25.00..1025.00 rows=1000 width=118)
         One-Time Filter: (version() IS NOT NULL)
         ->  Foreign Scan on offices ref_0  (cost=25.00..1025.00 rows=1000 width=118)
               Remote server startup cost: 25
(5 rows)

mysql_fdw_sample=# explain SELECT ref_0.STATE
mysql_fdw_sample-# FROM PUBLIC.offices AS ref_0
mysql_fdw_sample-# WHERE pg_catalog.timeofday() IS NOT NULL limit 1;
ERROR:  failed to prepare the MySQL query:
FUNCTION classicmodels.timeofday does not exist

mysql_fdw_sample=# SELECT ref_0.STATE
FROM PUBLIC.offices AS ref_0
WHERE pg_catalog.timeofday() IS NOT NULL limit 1;
ERROR:  failed to prepare the MySQL query:
FUNCTION classicmodels.timeofday does not exist

mysql_fdw_sample=# select proname, provolatile from pg_proc where proname in ('pi', 'timeofday', 'version') order by 2;                              proname  | provolatile
-----------+-------------
 pi        | i
 version   | s
 timeofday | v
(3 rows)

mysql_fdw_sample=# \det offices
     List of foreign tables
 Schema |  Table  |    Server
--------+---------+--------------
 public | offices | mysql_server
(1 row)

mysql_fdw_sample=# select version();
                                               version
-----------------------------------------------------------------------------------------------------
 PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)

mysql_fdw_sample=# select mysql_fdw_version();
 mysql_fdw_version
-------------------
             20600
(1 row)

[surajkharage19]

Thank you for reporting this issue. I am able to reproduce this issue. We will try to fix this in the near future.

[surajkharage19]

Hi @robins,

We have fixed this issue under commit - dc34c2d1a5dac0d4a5989a9240cdc0639975420c. Please check if that resolves the issue at your end and share the feedback. Thanks once again for reporting this.

[robins]

Thanks @surajkharage19 . Looks good to me.

$ git log --oneline | head -1
dc34c2d Do not push expressions having mutable functions to the remote server.

mysql_fdw_sample=# explain SELECT ref_0.STATE
mysql_fdw_sample-# FROM PUBLIC.offices AS ref_0
mysql_fdw_sample-# WHERE pg_catalog.timeofday() IS NOT NULL limit 1;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Limit  (cost=25.00..26.00 rows=1 width=118)
   ->  Foreign Scan on offices ref_0  (cost=25.00..1025.00 rows=1000 width=118)
         Filter: (timeofday() IS NOT NULL)
         Remote server startup cost: 25
(4 rows)

mysql_fdw_sample=# SELECT ref_0.STATE
FROM PUBLIC.offices AS ref_0
WHERE pg_catalog.timeofday() IS NOT NULL limit 1;
 state
-------
 CA
(1 row)