@Rocky Trying to run this query:

SELECT
	pid,
	business_currency,
	SUM(business_amount_usd) AS usd,
	COUNT(business_currency) AS cnt
FROM schema_bench.events_named
GROUP BY pid, business_currency
ORDER BY usd DESC

Get:

SQL Error [1064] [42000]: Cannot invoke "com.starrocks.sql.optimizer.operator.scalar.ColumnRefOperator.getType()" because "ref" is null

or

SQL Error [1064] [42000]: Unknown error

Removing ORDER BY or COUNT() makes it work. Anything I can do to fix original query?

The error you're seeing—`Cannot invoke "com.starrocks.sql.optimizer.operator.scalar.ColumnRefOperator.getType()" because "ref" is null`—is a known type of NullPointerException (NPE) in the StarRocks Cost-Based Optimizer (CBO). It typically occurs when the optimizer fails to correctly map a column alias (like

usd

) in the

ORDER BY

clause back to the actual aggregate result during a specific internal rewrite. The fact that removing

COUNT(business_currency)

fixes it suggests that the optimizer is attempting an optimization rule (likely related to "pushing down" or "rewriting" aggregates on grouping columns) that inadvertently leaves the

usd

reference in an invalid state. To fix your original query, you can use one of the following workarounds: 1. Use the full expression in ORDER BY Avoid using the alias

usd

in the

ORDER BY

clause and instead use the aggregate function directly. This usually avoids the alias resolution bug.

sql
SELECT
    pid,
    business_currency,
    SUM(business_amount_usd) AS usd,
    COUNT(business_currency) AS cnt
FROM schema_bench.events_named
GROUP BY pid, business_currency
ORDER BY SUM(business_amount_usd) DESC;

2. Use a Positional Reference You can refer to the column by its index in the

SELECT

list (where

usd

is the 3rd column).

sql
SELECT
    pid,
    business_currency,
    SUM(business_amount_usd) AS usd,
    COUNT(business_currency) AS cnt
FROM schema_bench.events_named
GROUP BY pid, business_currency
ORDER BY 3 DESC;

3. Wrap in a Subquery Wrapping the aggregation in a subquery forces the optimizer to "materialize" the alias before the sorting phase.

sql
SELECT * FROM (
    SELECT
        pid,
        business_currency,
        SUM(business_amount_usd) AS usd,
        COUNT(business_currency) AS cnt
    FROM schema_bench.events_named
    GROUP BY pid, business_currency
) t
ORDER BY usd DESC;

4. Change the COUNT expression Since

business_currency

is already in your

GROUP BY

clause,

COUNT(business_currency)

is functionally equivalent to

COUNT(_)

(unless there are specific null-handling requirements). Changing it to

COUNT(_)

often avoids the buggy rewrite rule:

sql
SELECT
    pid,
    business_currency,
    SUM(business_amount_usd) AS usd,
    COUNT(*) AS cnt
FROM schema_bench.events_named
GROUP BY pid, business_currency
ORDER BY usd DESC;

Recommendation: If you are on an older version (e.g., v2.x or early v3.x), upgrading to the latest maintenance release of v3.1.x or v3.2.x is recommended, as many of these "ref is null" optimizer bugs have been patched. If the issue persists on a recent version, you can temporarily disable the CBO for this specific query to confirm it's an optimizer issue:

SET enable_cbo = false;

(though this should only be a temporary diagnostic step, not a permanent fix). References • faq/Sql_faq.md • using_starrocks/sorted_aggregate.md • knowledge/usage/best-practice.md

@Rocky

current_version()

is

4.1.1-14b7e3f

None of the suggested queries work, I still get

unknown error

The error you are encountering—`Cannot invoke "com.starrocks.sql.optimizer.operator.scalar.ColumnRefOperator.getType()" because "ref" is null`—indicates a NullPointerException (NPE) within the StarRocks query optimizer (CBO). Specifically, a rewrite rule is attempting to access type information for a column reference that has been lost or incorrectly transformed during the optimization phase. Given that your version is 4.1.1-14b7e3f and common SQL-level workarounds (like subqueries or positional ordering) have not resolved the issue, this is likely triggered by a specific aggregate optimization rule introduced or updated in the 4.x series. To resolve the "Unknown error" and fix the query in your environment, please try disabling the following optimizer features in your session: 1. Disable Simple Aggregate Rewrite In StarRocks 4.0 and 4.1, there is a known optimization that rewrites simple aggregations to use metadata or statistics. This is often the culprit for NPEs in queries involving both

GROUP BY

and

ORDER BY

.

sql
SET enable_rewrite_simple_agg_to_meta_scan = false;

2. Disable Low Cardinality Optimization If

business_currency

or

pid

are string types, the optimizer might be attempting to use "Low Cardinality" dictionary optimization. If the dictionary mapping fails during the sort-pushdown phase, it can cause a

ColumnRef

null error.

sql
SET cbo_enable_low_cardinality_optimize = false;

3. Disable Aggregate Pushdown (If applicable) In newer versions, StarRocks tries to push down grouping and aggregation logic closer to the scan. You can test if this is the cause by disabling rule-based rewrites:

sql
SET enable_rule_based_rewrite = false;

Recommended Next Steps If the query works after setting

enable_rewrite_simple_agg_to_meta_scan = false

, it confirms a bug in that specific optimization rule. You should: 1. Check Column Types: Ensure

business_currency

and

pid

are standard types (VARCHAR/INT). If they are complex types (JSON, ARRAY), this optimization is known to have issues in v4.1. 2. Report the Issue: Since you have a specific commit hash (

14b7e3f

), please provide the output of

EXPLAIN VERBOSE <your_query>

and

SHOW CREATE TABLE schema_bench.events_named

to the StarRocks community or your support contact. The

ref is null

error is always considered a bug in the optimizer that needs to be fixed in a maintenance release. Temporary Fix Summary: Run this before your query:

sql
SET enable_rewrite_simple_agg_to_meta_scan = false;
SET cbo_enable_low_cardinality_optimize = false;

-- Original Query
SELECT
    pid,
    business_currency,
    SUM(business_amount_usd) AS usd,
    COUNT(business_currency) AS cnt
FROM schema_bench.events_named
GROUP BY pid, business_currency
ORDER BY usd DESC;

References • sql-reference/System_variable.md • faq/Sql_faq.md • knowledge/trouble_shooting/fe_deadlock_problems.md • faq/loading/Loading_faq.md • best_practices/query_tuning/query_profile_operator_metrics.md • release_notes/release-2.4.md • release_notes/release-2.5.md