[CALCITE-4739] json_object pushdown JDBC Postgresql

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.27.0
• Component/s: jdbc-adapter
• Labels: Bug pull-request-available

Environment

Calcite 1.27

Postgres 12

• Assignee: Unassigned
• Reporter: Martin Jonsson
• Created: 16/Aug/21 15:58
• Updated: 14/Mar/22 13:58

Description

I'm going through the JSON functions with JDBC Postgresql. Most functions work well but this is what my postgresql sees when using json_object:

SELECT "product_id", JSON_OBJECT(KEY 'name' VALUE "name" NULL ON NULL) AS "$f1"SELECT "product_id", JSON_OBJECT(KEY 'name' VALUE "name" NULL ON NULL) AS "$f1" FROM "public"."products"

This is obviously not valid postgres sql syntax. So we have the JDBC adapter pushing down the calcite sql syntax to postgres. Note that for json_arrayagg this is not the case and there it seems to work as expected.

Neither can i find any tests for jdbc and functions json_object, json_arrayagg and json_objectagg.

I believe this is a bug. I could not find any setting that disable postgres pushdown for json functions and if there is one, it should be uniformly applied.

Actually, own integration test show that pushdown is prevented when using the clone functionality in the schema. This should probably be clarified somewhere.

I still wonder if code pushdown should not be implemented for postgres. I leave this open for now.

Many thanks 

Martin Jonsson

 

 

Issue Links

• links to: GitHub Pull Request #2733
• links to: Pull Request