any equality or comparison condition in WHERE returns empty result for all other columns different than _id and except not null or is null

[MihaiRaduSandu]

Hello,

I reinstalled the postgres server and mongo_fdw wrapper, and now I have an issue with one of the mongo servers that I am connected to. If I add any = or < or > conditions in WHERE for columns it returns an empty result even if the condition should return results. If I use conditions like IS NULL IS NOT NULL then it works. The only column for which it returns results is _id. If I put no condition then it will bring all the data correctly.

The strange thing is that I have two mongo servers to which I connect, and only for one of them this issue replicates. Also, on my previous postgre server it was working.

Do you know what might be causing this or how I can investigate further?

I am using a docker image :

FROM postgres:15.1

ENV PKG_CONFIG_PATH /lib/pkgconfig

RUN apt update && \ apt install -y git wget cmake pkg-config libssl-dev postgresql-server-dev-$PG_MAJOR

install mongodb federation driver

RUN cd /opt && \ git clone https://github.com/EnterpriseDB/mongo_fdw.git && \ cd mongo_fdw && \ ./autogen.sh --with-master && \ make install

Thank you, Mihai

[vaibhavdalvi93]

@MihaiRaduSandu , thanks for reporting an issue.

The reported issue is not reproducible at our end.

postgres@9157=#select mongo_fdw_version();
 mongo_fdw_version 
-------------------
             50500
(1 row)

postgres@9157=#select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres@9157=#\d t1;
                    Foreign table "public.t1"
 Column |  Type   | Collation | Nullable | Default | FDW options 
--------+---------+-----------+----------+---------+-------------
 _id    | name    |           |          |         | 
 name   | text    |           |          |         | 
 age    | integer |           |          |         | 
Server: mongo_server1
FDW options: (database 'db1', collection 't1')

postgres@9157=#select * from t1;
           _id            | name | age 
--------------------------+------+-----
 63119ec84bcc8d2332020052 | dvd  |  25
(1 row)

postgres@9157=#select _id, age, name from t1 where age > 24;
           _id            | age | name 
--------------------------+-----+------
 63119ec84bcc8d2332020052 |  25 | dvd
(1 row)

postgres@9157=#select age from t1 where age > 24;
 age 
-----
  25
(1 row)

postgres@9157=#select name, age from t1 where age > 24 and name = 'dvd';
 name | age 
------+-----
 dvd  |  25
(1 row)

To investigate reported issue further, could you please provide us below details?

1. The operating system information with version details.
2. MongoDB version.
3. If possible, small reproducible test case.
4. Foreign table definition.
5. MongoDB collection definition.
6. If possible, data inside the MongoDB collection.

Thanks, Vaibhav

[MihaiRaduSandu]

Hello, I am running a docker image with debian 11 on a centos 7. The pg version is 15.1

The mongo version on which queries are working : "4.4.15" (no authentification) The mongo version on which I have this issue : "3.4.2" (with authentification)

As mentioned above, the server that is working has no authentification, but on the server that is not working has authentification.

I actually have 2 collections that I am mapping from the mongo server, both with the same issue so I am adding the structure for one of them: -- create foreign table CREATE FOREIGN TABLE mongo.request_body ( _id NAME, request_snapshot_id INTEGER, request_version INTEGER, broadcast_version INTEGER, created NUMERIC, document_state VARCHAR, "message_type" VARCHAR, "body" JSON, "source" VARCHAR, "destination" VARCHAR, document_type VARCHAR, "BELNR" VARCHAR, "GJAHR" VARCHAR, "filename" VARCHAR ) SERVER mongo OPTIONS (database 'supplier-order', collection 'request_body');

_*Error in PG logs: 2023-02-08 15:18:16.837 UTC [35] LOG: statement: SELECT FROM "mongo"."request_body" WHERE request_snapshot_id < 7 LIMIT 1000 bson_appendarray(): invalid array detected. first element of array parameter is not "0".**

Searching for this error I found some posts about SCRAM-SHA-1 authentification. I also saw this: https://github.com/EnterpriseDB/mongo_fdw/issues/62

Maybe I should compile my libmongoc with --enable-ssl? My docker config looks like this: RUN cd /opt && \ git clone https://github.com/EnterpriseDB/mongo_fdw.git && \ cd mongo_fdw && \ ./autogen.sh --with-master && \ make install

Thank you, Mihai

[vaibhavdalvi93]

Thanks, @MihaiRaduSandu for detailed information. This issue is reproducible against MongoDB "3.4" (I tried on 3.4.24). This issue got resolved on MongoDB v3.6 (I checked on 3.6.23) and later versions. This is NOT related to authentication as such. This looks like something related to the aggregation pipeline formation.

The MongoDB v3.4 is end of life(EOL) and no longer supported. It's less likely that this issue will get fixed. If possible, could you please upgrade to MongoDB v3.6 or higher.

Please let me know if I can help you more.

Thanks, Vaibhav

[MihaiRaduSandu]

Hello @vaibhavdalvi93 ,

Thank you as well for your prompt answer. I will discuss this with the development team, we will probably do it but it will take some time to upgrade.

The problem is that ideally I would need it to work until then, so if you have any other suggestions to make it work I would greatly appreciate it.

The thing is we had another pg server before that was working fine with this mongo version. But unfortunately that server doesn't exist anymore and I don't know which versions were used. From what I remember, we were using postgre 14. Probably we were using REL-5_4_0 OR REL-5_3_0 for the mongo_fdw. And we most definitely used a different libmongoc version, which I don't remember, because we didn't use autogen.sh then.

So I will try some older versions to see if it will work, but if you maybe know which versions I should use it would be great.

Thank you, Mihai

[vaibhavdalvi93]

Thanks, @MihaiRaduSandu for response.

The previous used version of mongo-c-driver was 1.9.5 and mongo_fdw version REL-5_2_8. You may need to use postgres 13. The compatible json-c version should be 0.13.1-20180305.

Please try and let us know.

[MihaiRaduSandu]

Hello @vaibhavdalvi93 ,

It seems I managed to make it work just by changing the mongo_fdw version to 5_4_0 (the previous version), still using pg15. So probably the change that generated this issue is in the last release.

My dockerfile:

FROM postgres:15

ENV PKG_CONFIG_PATH /lib/pkgconfig

RUN apt update && \ apt install -y git wget cmake pkg-config libssl-dev postgresql-server-dev-$PG_MAJOR

install mongodb federation driver

RUN cd /opt && \ git clone https://github.com/EnterpriseDB/mongo_fdw.git --branch REL-5_4_0 && \ cd mongo_fdw && \ ./autogen.sh --with-master && \ make install

Thank you for your help, Mihai

[vaibhavdalvi93]

Good to know that you are able to make it work.

In the last release, to support more cases in WHERE clause pushdown, we have used $expr operator which allows the use of aggregation expressions. Also, to support aggregate pushdown, WHERE clause query needs to be implemented using aggregation pipeline. The root cause of the issue is the use of $expr operator which is supported from MongoDB v3.6(https://www.mongodb.com/docs/v4.2/reference/operator/query/expr/). Hence, an issue is present in previous versions of MongoDB(i.e. Which are EOL). To support modern/latest features, needed to use latest operators.