@Rocky I get the following error message when union all multiple queries, I cannot see anything wrong, do you have any advice?

SQL Error [1064] [42000]: Incorrect logical plan found in operator: LOGICAL_UNION child size 3. Invalid reason: input cols type not equal with output cols type

This error message,

Incorrect logical plan found in operator: LOGICAL_UNION... input cols type not equal with output cols type

, is a validation error from the StarRocks Cost-Based Optimizer (CBO). It indicates that the optimizer has detected a mismatch between the data types of the columns in one of your

UNION ALL

branches and the resolved output types for the entire set. The mention of

child size 3

is a helpful clue: it specifically points to the 3rd query in your

UNION ALL

chain. Troubleshooting Steps 1. Check for Subtle Type Mismatches in the 3rd Query StarRocks requires all branches of a

UNION ALL

to have the same number of columns with compatible types. Even if they look similar, small differences can trigger this error: * Decimal Precision/Scale: Check if one branch is

DECIMAL(10,2)

while another is

DECIMAL(12,2)

or

DECIMAL(10,3)

. * String Types: Check for mixing

CHAR

and

VARCHAR

. * Integer Sizes: Mixing

TINYINT

,

INT

, and

BIGINT

usually works via implicit casting, but explicit casting is safer. * Complex Types: Ensure

ARRAY

,

JSON

,

BITMAP

, or

HLL

columns are identical across all branches. 2. Use Explicit Casting The most effective way to resolve this is to wrap your columns in an explicit

CAST

to ensure every branch returns the exact same type. Example:

sql
SELECT CAST(id AS BIGINT), CAST(price AS DECIMAL(10,2)) FROM table1
UNION ALL
SELECT CAST(id AS BIGINT), CAST(price AS DECIMAL(10,2)) FROM table2
UNION ALL
-- Focus on this 3rd branch
SELECT CAST(id AS BIGINT), CAST(price AS DECIMAL(10,2)) FROM table3;

3. Handle NULL Literals If you use

NULL

as a placeholder in any branch, StarRocks might infer its type incorrectly. Always cast

NULL

literals:

sql
-- Instead of:
SELECT NULL as my_col ...
-- Use:
SELECT CAST(NULL AS INT) as my_col ...

4. Identify the Offending Column If your query has many columns, you can find the mismatch by: 1. Running

EXPLAIN <your_query>;

or

EXPLAIN LOGICAL <your_query>;

. The output might provide more details on which column position (index) has the type mismatch. 2. Testing the

UNION ALL

with only two branches at a time (1+2, then 1+3) to confirm the 3rd one is indeed the problem. 5. Workaround (Optimizer Bug) If you are certain the types are correct, this might be a rare edge case in the CBO. You can try to bypass the specific union optimization by setting this session variable before running your query:

sql
SET enable_cbo_union_all_optimization = false;

(Note: If this fixes it, it likely indicates a bug in the planner for your specific StarRocks version.) Recommended Advice: Start by explicitly casting the columns in your 3rd sub-query to match the types of the 1st sub-query. This resolves 95% of these logical plan errors. References • faq/Sql_faq.md • best_practices/query_tuning/query_planning.md • using_starrocks/accelerate-cardinality-preserving-joins.md

@Rocky, All columns in all 4 of the queries in the union have cast statements added programmatically, hence the question. If I skip the 3rd query, it does run. But, I can run the union with the last 2 queries union together just fine. I could not see the setting for

enable_cbo_union_all_optimization

to try that. I'm on version

4.0.5-000efef

Hello! I'm Rocky, the StarRocks chatbot. It sounds like you're encountering a complex planning or execution issue with your 4-way

UNION

query on version 4.0.5. Based on your description—where skipping the 3rd query allows it to run, yet unioning the last two also works—you might be hitting a resource limit or a specific optimizer edge case when all four segments are combined. Regarding your question about

enable_cbo_union_all_optimization

, this is not a standard session variable in the public StarRocks documentation (including the v4.0 release). It's possible you may be looking for one of the following optimizations that are specifically designed for

UNION ALL

performance and resource management in recent versions: 1. Phased Scheduler (Recommended) Introduced in v3.3 and optimized in v4.0, the Phased Scheduler is designed to reduce memory usage significantly for queries with a large number of

UNION ALL

segments. It schedules fragments according to their dependencies rather than all at once. * Variable:

enable_phased_scheduler

* Command:

SET enable_phased_scheduler = true;

* Context: This is particularly helpful when you have many scans or joins within your union segments that might otherwise compete for memory. 2. Colocate Set Optimization In v3.5 and v4.0, there is a variable that controls how the optimizer handles "colocate sets" for set operations like

UNION

. * Variable:

disable_colocate_set

* Default:

false

* Note: If your query is getting stuck or failing due to distribution planning, trying

SET disable_colocate_set = true;

(temporarily) might help identify if the colocate optimization is causing the issue. 3. Compatible Data Types and CASTs Even with programmatic

CAST

statements, ensure that the resulting data types match exactly across all branches of the

UNION

. * Decimal Scale/Precision: For

DECIMAL

types, even if both are

DECIMAL(10,2)

, ensure no branch is accidentally casting to a different scale/precision which could force the optimizer to insert additional implicit casts or complex re-projections. * Nullable Columns: Check if the 3rd query handles

NULL

values differently in any of its columns compared to the others. Troubleshooting Steps: * Check the Error: If the query "doesn't run," does it return a specific error (e.g.,

Timeout

,

OOM

, or

Internal Error

)? * Explain Plan: Compare the output of

EXPLAIN

for the working 3-way union versus the failing 4-way union. Look for any "Cross Join" or "Broadcast Join" that might be appearing only when the 3rd query is included. * Union vs. Union All: Ensure you are using

UNION ALL

instead of

UNION

(which implies

DISTINCT

).

UNION DISTINCT

requires a global deduplication step that is much more resource-intensive. If you can provide the specific error message or the

EXPLAIN

plan, I can give you more targeted advice! References • faq/Sql_faq.md • sql-reference/System_variable.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • knowledge/usage/best-practice.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md