Left JOIN pushdown problem?

[ugurlu]

Hello,

When default options are used LEFT JOIN returns no results. If the enable_join_pushdown is set to false, query works as expected. Tried on 5.1.1

[vaibhavdalvi93]

Thanks, @ugurlu for reporting an issue. Could you please provide us the problematic query along with table definitions to look into it further?

[ugurlu]

Thank you for the quick response

Following query returns empty set.

select t.* from testlog t
    left join testdevice d on d.mac = t."logMeta.logMac";

CREATE FOREIGN table testdevice
    (
        _id int,
        "name" varchar,
        "mac" varchar
    )
    SERVER mongo_server
    OPTIONS (database 'testdb', collection 'testdevice');

CREATE FOREIGN table testlog
    (
        _id int,
        "log" varchar,
        "logMeta.logMac" varchar
    )
    SERVER mongo_server
    OPTIONS (database 'testdb', collection 'testlog');

{
  "_id": {
    "$oid": "658040214898199d6e0173d0"
  },
  "log": "hello log",
  "logMeta": {
    "logMac": "001122334455"
  }
}

{
  "_id": {
    "$oid": "6580400c4898199d6e0173cd"
  },
  "mac": "001122334455",
  "name": "test device"
}

[vaibhavdalvi93]

Thank you, @ugurlu for detailed information. I can confirm this as a bug with join pushdown. I could reproduce this issue on mongo_fdw latest version i.e. 5.5.1. I have reported this issue internally and we will try to fix this issue in near future. On high level I suspect that Join push-down not returning a result for join condition on sub-column because MongoDB query pipeline is getting incorrectly formed.