IN clause with thousands of values

23 views
Skip to first unread message

joan.ba...@gmail.com

unread,
Mar 15, 2022, 6:28:15 AMMar 15
to ClickHouse
Hello,

I occassionally can have a WHERE with an IN clause with thousands of values. With such queries many times CH simply returns a "No data found."

To solve this issue the doc says I should use temporary tables. But I don't see how I must create this table, how I should insert the list of values of the IN clause and how to query it.

I understand I should create the temporary table with just one field called "ID" (for example), then insert all the IN clause identifiers into this table and then perform a subselect against it.

I'm using clickhouse-go driver. Is there any sample I could check, or can you help me how to do this?

Thanks,

Joan.

joan.ba...@gmail.com

unread,
Mar 15, 2022, 6:44:41 AMMar 15
to ClickHouse
Hi,

Ok, there is a limitation on the query size:
An error has occurred executing the 'query' request: code: 62, message: Syntax error: failed at position 262136 ('COUNTRY_ID'): COUNTRY_ID. Max query size exceeded: 'COUNTRY_ID'

In addition to the previous question, is there a recommended value not to exceed when using a lot of values on an IN clause?

Thanks,

Joan.

joan.ba...@gmail.com

unread,
Mar 15, 2022, 7:07:03 AMMar 15
to ClickHouse
Sorry ... again ...
The issue is sometimes an error code 62 is returned, but sometimes CH just returns "No data found", so I'm unable to find out if the query was executed correctly or not.

Is there any way to "validate" the query? Perform a kind of test to verify the query is right BEFORE executing it. In this case if the query is right I can execute it directly, if not I can use the temporary table.

Thanks,

Joan.

Reply all
Reply to author
Forward
0 new messages