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.