ClickHouse Bug: PostgreSQL Correlated Subqueries Fail

Hey guys! Today, we're diving deep into a tricky bug that some of you might have bumped into when using ClickHouse with PostgreSQL engine tables in correlated subqueries. It's one of those issues that can leave you scratching your head, wondering why your queries are suddenly throwing a fit. We're talking about a specific error: "Cannot clone ReadFromPreparedSource plan step." This bug pops up under a couple of distinct scenarios, and understanding them is key to either avoiding the issue or figuring out a workaround. So, let's break it down and get you back on track!

Understanding the "Cannot clone ReadFromPreparedSource plan step" Error

So, what's the deal with this "Cannot clone ReadFromPreparedSource plan step" error? In essence, this error message signals that ClickHouse is having trouble duplicating a specific part of its query execution plan. Think of the query execution plan as a recipe for how ClickHouse is going to fetch and process your data. When it encounters a correlated subquery involving PostgreSQL engine tables, it needs to set up a way to repeatedly execute that subquery for each row from the outer query. The "cloning" part refers to ClickHouse's attempt to create multiple instances of this subquery execution plan. When this cloning process fails, you get this error. It often points to an underlying issue in how ClickHouse handles the interaction with external data sources like PostgreSQL, especially when those sources are accessed repeatedly within a single query. It's like trying to make multiple copies of a delicate blueprint, and one of the copies just won't come out right. This can be particularly frustrating because, from a user's perspective, the query might look perfectly fine, and it works flawlessly with other table types or in simpler scenarios. The complexity arises from the combination of correlated subqueries, which require dynamic execution, and the external nature of PostgreSQL engine tables. This means ClickHouse isn't just reading from its own storage; it's actively communicating with another database system, and that communication needs to be managed very carefully, especially when done multiple times within a single query execution. The failure to clone the plan step suggests a breakdown in this management, possibly due to resource limitations, state-sharing issues, or simply a logical flaw in how the plan is designed to be replicated for correlated subqueries against external tables. Understanding this error is the first step towards diagnosing and fixing the problem, or at least finding a way to structure your queries differently to sidestep it. We'll be looking at the specific scenarios where this happens next, which should give you a clearer picture.

Scenario 1: Multiple References to the Same PostgreSQL Table

Alright guys, let's talk about the first major culprit for this ClickHouse bug: when your correlated subquery references the same PostgreSQL engine table multiple times. Imagine you've got a query that needs to check a row against a PostgreSQL table, but it has to do it based on two different conditions, both pointing to that same PostgreSQL table. This is where things can go sideways. You might be using EXISTS clauses or IN operators, and within those, you're hitting the same pg_engine_table more than once. ClickHouse, when it tries to optimize and execute this, runs into a wall.

Here's a classic example of what this looks like:

SELECT 
 * 
FROM 
 ck_table a
WHERE 
 EXISTS (SELECT 1 FROM pg_engine_table b WHERE toIPv6(b.ip_addr) = a.sip)
OR
 EXISTS (SELECT 1 FROM pg_engine_table c WHERE toIPv6(c.ip_addr) = a.dip)

In this snippet, ck_table is your main ClickHouse table, and pg_engine_table is the PostgreSQL table we're querying from. Notice how pg_engine_table is referenced twice, once with alias b and once with alias c. Both a.sip and a.dip are being compared against toIPv6(b.ip_addr) and toIPv6(c.ip_addr) respectively. The intention here is to check if either the sip or dip from ck_table exists in the ip_addr column of pg_engine_table. Both pg_engine_table and ck_table are assumed to contain data. When ClickHouse tries to generate the execution plan for this, it attempts to create separate plan steps for each EXISTS subquery. However, because both subqueries target the exact same PostgreSQL table, ClickHouse struggles to properly clone the execution plan for pg_engine_table. It's like asking the engine to maintain two independent, identical copies of a connection and query setup for the same external resource simultaneously, and it just can't handle the duplication gracefully. The result? Boom! You get that dreaded "Cannot clone ReadFromPreparedSource plan step" error. This issue highlights a limitation in how ClickHouse manages state and resources when dealing with repeated access to external data sources within correlated subqueries. The fix or workaround often involves restructuring the query to avoid referencing the same external table multiple times directly within correlated subqueries, perhaps by using joins or pre-aggregating data from the PostgreSQL table into ClickHouse first.

Scenario 2: Correlated Subqueries with Empty ClickHouse Tables

Now, let's switch gears to the second scenario where this pesky bug likes to show up: using correlated subqueries when the target ClickHouse table in the subquery is empty. This one might seem a bit counter-intuitive, right? You'd think an empty table would be easier to handle. But nope, in this specific context, it can also trigger the "Cannot clone ReadFromPreparedSource plan step" error. This happens when your outer query has data, but one or more of the correlated subqueries you're using to filter or augment that data are based on a ClickHouse table that currently has no rows. It's the combination of having data in the outer table and an empty table in the subquery that causes the problem.

Consider this example:

SELECT 
 * 
FROM 
 ck_table1 a
WHERE 
 EXISTS (SELECT 1 FROM ck_table2 b WHERE toIPv6(b.ipseg) = a.sip)
OR
 EXISTS (SELECT 1 FROM ck_table2 c WHERE toIPv6(c.ipseg) = a.dip)

Here, ck_table1 has data, but ck_table2 is empty. The query checks if a.sip or a.dip from ck_table1 has a corresponding entry in ck_table2. Because ck_table2 is empty, the EXISTS subqueries will always return false, which is logically correct. However, ClickHouse's internal process for preparing the execution plan for the ck_table2 subquery fails. It seems that ClickHouse expects to be able to