ildus / clickhouse_fdw

ClickHouse FDW for PostgreSQL
Apache License 2.0
260 stars 55 forks source link

PostgreSQL EXTRACT Function based on Clickhouse table doesn't work in ORDER BY clause #98

Open mixavich opened 1 year ago

mixavich commented 1 year ago

A simple query SELECT "InsertionDate" from "AdvertisingCab" order by extract(YEAR from "InsertionDate"), extract (MONTH from "InsertionDate"); , where AdvertisingCab is a PostgreSQL view linked to a similar Clickhouse table, falls with the following error:

ERROR: clickhouse_fdw:Code: 43. DB::Exception: Illegal type DateTime('UTC') of argument of function extract: While processing extract('year', insertion_date) ASC, extract('month', insertion_date) ASC. (ILLEGAL_TYPE_OF_ARGUMENT) QUERY:SELECT "insertion_date" FROM "test".advertising_cabinet ORDER BY extract('year', "insertion_date") ASC, extract('month', "insertion_date") ASC

In other words, clickhouse_fdw translates the extract functions in the order by clause incorrectly.

At the same time the query SELECT extract(YEAR from "InsertionDate"), extract (MONTH from "InsertionDate") from "AdvertisingCab" translates into

QUERY PLAN

Foreign Scan on public.advertising_cabinet (cost=0.00..0.00 rows=0 width=64) Output: EXTRACT(year FROM advertising_cabinet."insertion_date"), EXTRACT(month FROM advertising_cabinet."insertion_date") Remote SQL: SELECT "insertion_date" FROM "test".advertising_cabinet (3 rows)

and works perfectly.