Different results for same postgres and clickhouse queries

[josefinaestevez]

I've noticed that some queries shows different results in postgres and clickhouse when, if I'm not wrong, they should be the same.

A simple example:

Postgres

SELECT COUNT(DISTINCT relation_id) FROM clickhouse_table WHERE relation_id IN (SELECT relation_id FROM clickhouse_table WHERE date_from > '2019-02-01');
 count 
-------
   116
(1 row)

Clickhouse

SELECT COUNT(DISTINCT relation_id) FROM table WHERE relation_id IN (SELECT relation_id FROM table WHERE date_from > '2019-02-01');
┌─uniqExact(report_id)─┐
│                  677 │
└──────────────────────┘

Same inconsistency if I remove the WHERE clause.
Do you have any idea what could be happening?
Thanks a lot!

[ibrarahmad]

Let me take a look at that

…

On Mon, Jul 15, 2019 at 5:35 PM Josefina Estevez ***@***.***> wrote: Reopened #21 <#21>. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#21?email_source=notifications&email_token=AA3NF4S4SROTQSINZBHNPG3P7RVJPA5CNFSM4IBQNFMKYY3PNVWWK3TUL52HS4DFWZEXG43VMVCXMZLOORHG65DJMZUWGYLUNFXW5KTDN5WW2ZLOORPWSZGOSP5ZM2A#event-2482738792>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3NF4RRYJTZX3AJHDN64XDP7RVJPANCNFSM4IBQNFMA> .

-- Ibrar Ahmed

[josefinaestevez]

Thanks for taking a look! Another example:

Postgres

SELECT DISTINCT(country_id), COUNT(1) as cant FROM clickhouse_table GROUP BY country_id ORDER BY cant;
 country_id |  cant   
------------+---------
          7 | 1742709
          6 | 1755293
(2 rows)

Clickhouse

SELECT DISTINCT(country_id), COUNT(1) as cant FROM table GROUP BY country_id ORDER BY cant;
┌─country_id─┬────cant─┐
│         26 │   70000 │
│          6 │ 5933100 │
│          7 │ 5933100 │
└────────────┴─────────┘

[ibrarahmad]

The tuples do not store in an ordered way in the database. When we are applying the "order by clause" in PostgreSQL so it is possible to have a different result. Please restrict your query with WHERE clause.

…

On Wed, Jul 17, 2019 at 4:34 PM Josefina Estevez ***@***.***> wrote: Thanks for taking a look! Another example: Postgres SELECT DISTINCT(country_id), COUNT(1) as cant FROM clickhouse_table GROUP BY country_id ORDER BY cant; country_id | cant ------------+--------- 7 | 1742709 6 | 1755293 (2 rows) Clickhouse SELECT DISTINCT(country_id), COUNT(1) as cant FROM table GROUP BY country_id ORDER BY cant; ┌─country_id─┬────cant─┐ │ 26 │ 70000 │ │ 6 │ 5933100 │ │ 7 │ 5933100 │ └────────────┴─────────┘ — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#21?email_source=notifications&email_token=AA3NF4U5ENW7H47GFLZL2C3P737V7A5CNFSM4IBQNFMKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD2D42BY#issuecomment-512216327>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3NF4X55U4JIN4UGQHVJQDP737V7ANCNFSM4IBQNFMA> .

-- Ibrar Ahmed

[Kale-Grabovski]

Hello. I've got the same issue seems. As mentioned, the results are different at pg and clickhouse.

CREATE FOREIGN TABLE dwh_loads(
  country_id int,
  ip text,
  created_at_date date
) SERVER clickhouse_svr;

pg=# select count(*), created_at_date
from dwh_loads 
where created_at_date > '2019-08-20' 
group by created_at_date;

  count  | created_at_date 
---------+-----------------
 2307442 | 2019-08-22
 4583210 | 2019-08-21
   30920 | 2019-08-23
   39461 | 2019-08-24
(4 rows)

pg=# select count(*), created_at_date 
from dwh_loads 
where created_at_date > '2019-08-10'
group by created_at_date;

  count  | created_at_date 
---------+-----------------
 3670016 | 2019-08-16
 1686864 | 2019-08-11
 1638122 | 2019-08-18
(3 rows)

# clickhouse
SELECT 
    count(*), 
    created_at_date
FROM dwh_loads
WHERE created_at_date > '2019-08-10'
GROUP BY created_at_date
ORDER BY created_at_date ASC

┌─count()─┬─created_at_date─┐
│ 1987751 │      2019-08-11 │
│ 2225018 │      2019-08-12 │
│ 4098923 │      2019-08-13 │
│ 7453111 │      2019-08-14 │
│ 6787449 │      2019-08-15 │
│ 6396884 │      2019-08-16 │
│ 5642157 │      2019-08-17 │
│ 5485166 │      2019-08-18 │
│ 4949855 │      2019-08-19 │
│ 4968256 │      2019-08-20 │
│ 4583210 │      2019-08-21 │
│ 4781908 │      2019-08-22 │
│ 4449216 │      2019-08-23 │
│ 4911274 │      2019-08-24 │
│ 1945331 │      2019-08-25 │
└─────────┴─────────┘

More of that, sometimes I see postgres down when trying to run one of above queries from psql:

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

and all my queries to db in about 1 minute end up with this error: pq: the database system is in recovery mode

[ibrarahmad]

On Sun, Aug 25, 2019 at 1:04 PM Kale ***@***.***> wrote: Hello. I've got the same issue seems. As mentioned, the results are different at pg and clickhouse.

I am looking at the issue, specifically the crash.

…

CREATE FOREIGN TABLE dwh_loads( country_id int, ip text, created_at_date date ) SERVER clickhouse_svr; pg=# select count(*), created_at_date from dwh_loads where created_at_date > '2019-08-20' group by created_at_date; count | created_at_date ---------+----------------- 2307442 | 2019-08-22 4583210 | 2019-08-21 30920 | 2019-08-23 39461 | 2019-08-24 (4 rows) pg=# select count(*), created_at_date from dwh_loads where created_at_date > '2019-08-10' group by created_at_date; count | created_at_date ---------+----------------- 3670016 | 2019-08-16 1686864 | 2019-08-11 1638122 | 2019-08-18 (3 rows) # clickhouse SELECT count(*), created_at_date FROM dwh_loads WHERE created_at_date > '2019-08-10' GROUP BY created_at_date ORDER BY created_at_date ASC ┌─count()─┬─created_at_date─┐ │ 1987751 │ 2019-08-11 │ │ 2225018 │ 2019-08-12 │ │ 4098923 │ 2019-08-13 │ │ 7453111 │ 2019-08-14 │ │ 6787449 │ 2019-08-15 │ │ 6396884 │ 2019-08-16 │ │ 5642157 │ 2019-08-17 │ │ 5485166 │ 2019-08-18 │ │ 4949855 │ 2019-08-19 │ │ 4968256 │ 2019-08-20 │ │ 4583210 │ 2019-08-21 │ │ 4781908 │ 2019-08-22 │ │ 4449216 │ 2019-08-23 │ │ 4911274 │ 2019-08-24 │ │ 1945331 │ 2019-08-25 │ └─────────┴─────────┘ More of that, sometimes I see postgres down when trying to run one of above queries from psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#21?email_source=notifications&email_token=AA3NF4TPVFNKWRKX3YTWCODQGI4KPA5CNFSM4IBQNFMKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD5COULQ#issuecomment-524610094>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3NF4U7CCQAE44WKKG3B3DQGI4KPANCNFSM4IBQNFMA> .

-- Ibrar Ahmed