search

ildus / clickhouse_fdw

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

all integers are returned with inversed sign #69

Closed beda42 closed 3 years ago

beda42 commented 3 years ago

Hi,

I have installed clickhouse_fdw using the procedure laid out here into PostgreSQL 12 running on Ubuntu 20.04 and all seems to work well. The only strange thing is that all integers are reported with a minus sign. And these are not only integers from the data itself, but also for example counts. When I do SELECT COUNT(*) FROM access_log;, I get -105277473.

Any idea what could be the source of this problem?

p.s.- I have CH version 21.4.5

ildus commented 3 years ago

Hi, could you provide some sql script which leads to it?

beda42 commented 3 years ago

I am using CollapsingMergeTree and I found out that by mistake I have set the sign value to -1 instead of 1 for all imported records. Is it possible that the CollapsingMergeTree automatically multiplies the values by the sign? Because now I have installed it on a different machine with the correct sign and it seems to work properly. Unfortunately I cannot test it on the previous machines now, so I am just trying to find an explanation.

Anyway, it seems that this is either caused by the sign column or it is specific to one machine, so this issue is probably invalid.

ildus commented 3 years ago

yes, sign fields are used automatically with aggregations. You can use explain (verbose) to see the generated query.