search

EnterpriseDB / mongo_fdw

PostgreSQL foreign data wrapper for MongoDB
GNU Lesser General Public License v3.0
326 stars 70 forks source link

The conversion from the `varchar(255)` to `::text` produces the `null` #133

Closed dadhi closed 2 years ago

dadhi commented 4 years ago

Using the release https://github.com/EnterpriseDB/mongo_fdw/releases/tag/REL-5_2_5 and

This does not work

SELECT varchar256_field::text 
FROM my_mongo_fdw_table; -- produces `null`

But if the same field listed twice - it works

SELECT 
varchar256_field        as hack
varchar256_field::text  as text_value -- converts the actual value just fine
FROM my_mongo_fdw_table; -- produces `null`

It is not working though if you put it twice as a function argument

SELECT 
coalesce(varchar256_field, varchar256_field)::text -- does Not work
FROM my_mongo_fdw_table; -- produces `null`
mjrussell commented 4 years ago

I'm also hitting this issue in postgres 12. Interestingly, it appears to be an issue when converting from json implicitly or explicitly to any other type (jsonb,text, etc)

vaibhavdalvi93 commented 4 years ago

Thank you @dadhi and @mjrussell for reporting an issue.

We are able to reproduce the issue and this looks like a bug.

This issue comes when casting of target list has been performed by an user. After doing some initial investigation, came to know that mongo_fdw code missed to deparse a RelabelType (binary-compatible cast) node and considering this as an Var node which is wrong. So the handling of relabel type node need to be done.

We are internally tracking this and will try to address this issue.

sheim-dev commented 3 years ago

@dadhi I had observed a similar bug when using mongo fdw <= 5.2.7 on postgres >= 10.0, seems this bug is fixed with release 5.2.8 of mongo fdw. Can you confirm release 5.2.8 does indeed fix this issue for you?

dadhi commented 3 years ago

@sheim-dev I am no longer using mongo_fdw at the current project, so I won't be able to confirm. But the bug is easy reproducible, so I don't see a problem to check by the person who's using it.

vaibhavdalvi93 commented 3 years ago

@dadhi, @sheim-dev We have fixed this issue in release 5.2.8. Can you please let us know that this issue got resolved for you? If yes then will be closing this ticket.

sheim-dev commented 3 years ago

@vaibhavdalvi93 I can confirm release 5.2.8 does indeed fix the issues I had observed with queries incorrectly returning NULL on PostgreSQL versions >= 10 just like in this bug report. From my perspective the issue can be closed, I'm also confident @dadhi original problem reported is fixed by release 5.2.8. Given he cannot provide independent explicit confirmation of that anymore, I think the issue should be closed.

jeevanchalke commented 2 years ago

Closing per @sheim-dev comment above.