Postgresql connection results inconsistent

[gabrielmcg44]

Describe the bug

I am migrating to use clickhouse-connect but I am facing a really weird problem when querying postgres tables

The whole test involves creating a testcontainer and a named collection. The test was working well with another clickhouse client.

Basically, depending on the filter, the queries do not return anything.

For example, if i run

        result = await self.async_client.query(
            query=statement, settings={"session_id": "random_session_id"}
        )
        print(result.column_names)

With 4 different statements, I get:

• select * from postgresql(my_named_collection, table='my_table')
  where category = 'category_1'
  Several results

• select * from postgresql(my_named_collection, table='my_table')
  where category = 'category_2'
  Several results

• select * from postgresql(my_named_collection, table='my_table')
  where category != 'category_1'
  Empty list

• select * from postgresql(my_named_collection, table='my_table')
  Empty list

[genzgd]

What version are you using? Is that behavior consistent? Can you turn off compression? This seems similar to #401, but I've not been able to reproduce it.

[genzgd]

Also it would be really helpful if you could get data from the system.query_log table that match the queries that don't return data.

[gabrielmcg44]

I added this "select * from system.query_log" after two contradictory queries (running in sequence)

And the two of them returned the exactly the same result (tested in a text comparator)

[('QueryStart', datetime.date(2024, 10, 3), datetime.datetime(2024, 10, 3, 23, 36, 12), datetime.datetime(2024, 10, 3, 23, 36, 12, 676841), datetime.datetime(2024, 10, 3, 23, 36, 12), datetime.datetime(2024, 10, 3, 23, 36, 12, 676841), 0, 0, 0, 0, 0, 0, 0, 0, 'default', 'CREATE DATABASE IF NOT EXISTS my_database', '', 14222981827203494986, 'Create', ['my_database'], [], [], [], [], [], 0, '', '', 1, 'my_database', 'cb2526c6-ae8a-416d-a032-b5757bb8a72d', IPv4Address('127.0.0.1'), 48430, 'my_database', 'cb2526c6-ae8a-416d-a032-b5757bb8a72d', IPv4Address('127.0.0.1'), 48430, datetime.datetime(2024, 10, 3, 23, 36, 12), datetime.datetime(2024, 10, 3, 23, 36, 12, 676841), 1, 0, '', 'afaa0fa22e1e', 'ClickHouse client', 54465, 23, 8, 8, 0, '', '', '', '', 0, 54477, '', [], {}, {}, [], [], [], [], [], [], [], [], [], [], (0, 0, UUID('00000000-0000-0000-0000-000000000000')), 'Unknown', {}), ('QueryFinish', datetime.date(2024, 10, 3), datetime.datetime(2024, 10, 3, 23, 36, 12), datetime.datetime(2024, 10, 3, 23, 36, 12, 679330), datetime.datetime(2024, 10, 3, 23, 36, 12), datetime.datetime(2024, 10, 3, 23, 36, 12, 676841), 2, 0, 0, 0, 0, 0, 0, 0, 'default', 'CREATE DATABASE IF NOT EXISTS my_database', '', 14222981827203494986, 'Create', ['my_database'], [], [], [], [], [], 0, '', '', 1, 'my_database', 'cb2526c6-ae8a-416d-a032-b5757bb8a72d', IPv4Address('127.0.0.1'), 48430, 'my_database', 'cb2526c6-ae8a-416d-a032-b5757bb8a72d', IPv4Address('127.0.0.1'), 48430, datetime.datetime(2024, 10, 3, 23, 36, 12), datetime.datetime(2024, 10, 3, 23, 36, 12, 676841), 1, 0, '', 'afaa0fa22e1e', 'ClickHouse client', 54465, 23, 8, 8, 0, '', '', '', '', 0, 54477, '', [56], {'Query': 1, 'QueriesWithSubqueries': 1, 'FileOpen': 1, 'WriteBufferFromFileDescriptorWrite': 1, 'WriteBufferFromFileDescriptorWriteBytes': 78, 'FileSync': 1, 'FileSyncElapsedMicroseconds': 1530, 'IOBufferAllocs': 1, 'IOBufferAllocBytes': 141, 'DiskWriteElapsedMicroseconds': 8, 'ContextLock': 15, 'RealTimeMicroseconds': 2591, 'UserTimeMicroseconds': 966, 'SoftPageFaults': 17, 'OSCPUVirtualTimeMicroseconds': 966, 'OSWriteBytes': 4096, 'OSReadChars': 434, 'OSWriteChars': 1138, 'LogTrace': 3, 'LogDebug': 1, 'LogInfo': 3}, {}, [], [], ['Atomic'], [], [], [], [], [], [], [], (0, 0, UUID('00000000-0000-0000-0000-000000000000')), 'None', {})]

[gabrielmcg44]

Let me know if I can provide some more info

[gabrielmcg44]

Yes, the behavior is consistent. Tried the same test several times

[gabrielmcg44]

Tried it passing compress=False to where I create the client, and it worked!

But why? Should I keep it uncompressed? Wont I lose performance?

[genzgd]

What version of clickhouse-connect are you using? There were changes to lz4 compression that might have a bug. In the meantime you can try 'zstd' compression instead

[genzgd]

I have tried another fix to the lz4 compression in version 0.8.3. Unfortunately I still haven't been able to reproduce this locally.

[gabrielmcg44]

just tried but zstd also fail

[genzgd]

Have you tried the latest 0.8.2 version?

…

On Fri, Oct 4, 2024, 7:30 PM gabrielmcg44 ***@***.***> wrote: just tried but zstd also fail — Reply to this email directly, view it on GitHub <#403 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEYZWUJTKB37RBQ7KFKOTTZZ46KTAVCNFSM6AAAAABPKGPSHKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGOJUHA2TAMZQG4> . You are receiving this because you modified the open/close state.Message ID: ***@***.***>

[gabrielmcg44]

tested, 0.8.2 with zstd works, thanks

[genzgd]

If you have a chance, please test with lz4 as well (that's the default).