@Rocky [Issue] INSERT INTO with CTE hangs at Prepare stage on RANDOM distribution table* Environment • StarRocks: 4.0.2 • Kubernetes-based deployment • Query client: Redash (2 worker pods) Problem INSERT INTO a RANDOM distribution table using CTE (WITH clause) hangs indefinitely at the Prepare stage (job type: INSERT STREAMING). The connection is then dropped with

State=EOF|ErrorCode=1|ScanBytes=0|ScanRows=0

before any data is read. Key Observations • The same CTE query runs as SELECT in under 10 seconds — no issue • Simple INSERT (no CTE, e.g.

INSERT INTO ... SELECT * FROM ...

) works fine • Only complex CTE-based INSERT hangs •

SHOW PROC '/current_queries'

shows the job stuck at Prepare • Two connections appear simultaneously (from two different Redash worker pod IPs), suggesting possible duplicate submission • Query execution plan (EXPLAIN) looks normal — no obvious planning issue What we've tried •

SET cbo_cte_reuse = false

→ no effect •

SET enable_pipeline_load = false

→ error: non-pipeline engine not supported since 3.2 • ALTER TABLE to adjust bucket count → RANDOM tables use auto-scaling, ALTER not applicable • CTAS workaround (create temp HASH table → INSERT into RANDOM table) → also hangs •

ADMIN SET FRONTEND CONFIG("enable_statistic_collect_on_first_load"="false")

→ not relevant (UPDATE-related) • Reducing Redash

WORKERS_COUNT

→ did not resolve duplicate connection issue Current Suspicion The Prepare stage for INSERT STREAMING on a RANDOM distribution table may require opening tablet writers for all tablets simultaneously. With CTE queries (larger/more complex data flow), this causes a timeout or deadlock before execution begins. Possibly a bug or limitation in 4.0.2. Has anyone seen this before or have suggestions? cc @data-infra

It looks like you've encountered a specific bottleneck where the combination of a complex CTE and a

RANDOM

distribution target table is causing the FE-BE coordination to stall during the transaction initialization. Since the tool search is currently facing a technical glitch, here is a troubleshooting guide based on StarRocks' internal architecture and common "Prepare" stage hang scenarios in versions 3.3+: 1. Analyze the "Prepare" Hang When a query hangs at the Prepare stage during an

INSERT

, it usually means the Frontend (FE) has generated the plan but is waiting for the Backend (BE) to acknowledge the opening of TabletWriters or the initialization of the transaction (via the

load_stream_stub

). Possible Causes: * Tablet Writer Exhaustion:

RANDOM

distribution tables often spread data across all available tablets. If your target table has a high number of tablets (due to automatic bucketing), a complex CTE plan might be attempting to open too many concurrent write channels. * CTE Reuse Complexity: StarRocks' CBO (Cost-Based Optimizer) often rewrites CTEs to be "reusable" (Materialized). This creates a temporary internal sink. When combined with the final

INSERT

sink, the execution graph becomes much more complex, potentially leading to a deadlock in the pipeline driver if resources are tight. 2. Suggested Workarounds & Configs A. Refine CTE Reuse You tried

cbo_cte_reuse

, but please verify the exact session variable for your version (4.0.2). Try:

sql
SET cbo_enable_cte_reuse = false;
INSERT INTO ... SELECT ...

Alternatively, try

SET enable_cost_based_rewrite = false;

to see if a simpler logical plan bypasses the hang. B. Handle the "Duplicate Connection" Redash Issue You noticed two connections from Redash. In StarRocks, multiple concurrent

INSERT

operations into the same table (especially streaming inserts) can lead to contention for the transaction lock. * Action: Ensure only one Redash worker is submitting the query at a time. If the first connection times out at the client level but remains "running" on the FE, the second submission might be getting queued or deadlocked behind the first one's cleanup. C. Increase Tablet Writer Limits (BE Config) If the issue is related to opening many tablets for the

RANDOM

distribution, check your BE configuration. Check

<http://be.INFO|be.INFO>

logs for messages like

exceeds max tablet writer count

. You may need to adjust: *

streaming_load_max_batch_size_per_ptr

*

max_runnings_transactions_per_txn_mgr

(on FE) D. Force a "Shuffle" to Reduce Writer Pressure Even though it's a

RANDOM

table, you can try to force a distribution step before the sink to see if it stabilizes the Prepare phase:

sql
INSERT INTO target_table
SELECT * FROM (SELECT /*+ SET_VAR(parallel_fragment_exec_instance_num=1) */ * FROM cte_result) t;

3. Debugging Steps To get more visibility into why it's stuck: 1. Check FE Logs: Look at

fe.log

and

fe.warn.log

during the time the query is stuck in "Prepare". Search for the query ID and look for "waiting for transaction" or "RPC timeout". 2. Check BE Logs: Check

<http://be.INFO|be.INFO>

on the target nodes. Look for

open_tablet_writer

or

load_channel

logs. 3. Profile the Plan: Since it's an

INSERT

, you can't get the profile easily while it's hung, but you can run the

EXPLAIN ANALYZE

version of the query if possible (or just

EXPLAIN

to see the number of fragments). Recommendation for Version 4.0.2: Since 4.0.x is extremely new, this might be a regression in the Pipeline Engine's handling of multi-sink plans (CTE + INSERT). If

cbo_enable_cte_reuse = false

doesn't work, I recommend trying to wrap the CTE into a temporary view or a permanent table as a temporary bridge to see if decoupling the logic helps.