IN clause with thousands of values

[joan.ba...@gmail.com]

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]

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]

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.