Outer table columns in dependent sub query are pused-down as 0 or NULL

[lukesilvia]

Hello.

I have some SQL that's incorrect because of a dependent sub query.

I have a simple table.

test=# select * from users;
 id | name
----+------
  1 | aaa
  2 | bbb

But a dependent sub query gives incorrect results:

test=# select id, (select name from users u where u.id = users.id) from users;
 id | name
----+------
  1 |
  2 |

Expected results:

 id | name
----+------
  1 | aaa
  2 | bbb

When I checked MySQL query log, I found that users.id in dependent sub query was pushed-down as 0 or NULL.
The following is MySQL query log when I executed previous dependent sub query on PostgreSQL.

200805 12:01:33  1157 Query     SET sql_mode='ANSI_QUOTES'
                 1157 Query     SET sql_mode='ANSI_QUOTES'
                 1157 Query     SET sql_mode='ANSI_QUOTES'
                 1157 Prepare   SELECT `name` FROM `test`.`users` WHERE ((`id` = ?))
                 1157 Execute   SELECT `name` FROM `test`.`users` WHERE ((`id` = 0))
                 1157 Query     SET sql_mode='ANSI_QUOTES'
                 1157 Prepare   SELECT `id` FROM `test`.`users`
                 1157 Execute   SELECT `id` FROM `test`.`users`
                 1157 Execute   SELECT `name` FROM `test`.`users` WHERE ((`id` = NULL))
                 1157 Execute   SELECT `name` FROM `test`.`users` WHERE ((`id` = NULL))

DDL to reproduce

MySQL

drop database if exists test;
create databse test;
use test;
create table users ( id int, name varchar(255) );
insert into users (id, name) values (1, 'aaa'), (2, 'bbb');

PostgreSQL

drop database if exists test;
create database test;
\c test;
create extension mysql_fdw;
create server mysql_server
  foreign data wrapper mysql_fdw
  options  (host '127.0.0.1', port '3306');;
create user mapping for postgres
  server mysql_server
  option (username 'root', password '[secret]');
import foreign schema test from server mysql_server into public;

Environments

• MySQL 5.6.23
• PostgreSQL 9.5.22
• mysql_fdw REL-2_5_4

Thanks for reading my issue.

[surajkharage19]

Thank you @lukesilvia for reporting this issue. This looks very similar to issue #172. As you correctly said, it is an issue with parameterized queries (param nodes in where clause). We are aware of this and will try to address this.

[surajkharage19]

Hi @lukesilvia,

We have fixed the above-mentioned issue. We are closing the case for now. If the issue persists, you can reopen the case anytime.