Baike.dev
Log in
Back to tool/Back to issues
#28482·presto

CPU 100% / query never finishes: UnaliasSymbolReferences dedup creates mapping cycle from equal constants

Author: sydt2014Created Sep 14, 2026Updated Sep 14, 2026
Labelsbug

Summary

UnaliasSymbolReferences optimizer enters an infinite loop (or OOM) when processing a query that causes the symbol mapping to contain a cycle. The root cause is the deduplication logic in canonicalize(Assignments), which merges different symbols that fold to equal ConstantExpression values. Because ConstantExpression.equals()/hashCode() is value-based, distinct columns filtered to the same literal via IN('...') become "equal" and get mapped to each other. Across multiple ProjectNodes the mappings form a 2-node cycle (A→B, B→A), causing the while loop in canonicalize(VariableReferenceExpression) to spin forever.

Affected Version

Reproduced on presto-0.296.

Steps to Reproduce

1. Create the table

sql
CREATE TABLE ads.ads_bi_tob_exp_app_mall_pool_1d_d_s0(
  `exp_salt` string,
  `exp_layer_id` string,
  `exp_id` string,
  `exp_layer_name` string,
  `exp_name` string,
  `mall_pool_id` string,
  `mall_tag` string,
  `mall_tag_value` string,
  `is_mall_online_ad_goods_cnt_above_99pct` string,
  `is_mall_ad_bill_spend_above_99pct` string,
  `buk_cnt` double,
  `ad_bill_spend_1d_buk` double,
  `mall_pool_type` string,
  `est_use_amt_buck` bigint,
  `abflag` bigint,
  `est_use_amt` double
)
PARTITIONED BY (`pt` string)
STORED AS ORC;

Only the columns referenced by the reproducer SQL are included above. The original table has 81 columns; the extra columns are irrelevant to the bug.

2. Run the reproducer query

It must be this SQL statement. Reproducing this bug requires extremely strict conditions.

sql
SELECT `col_1`,`col_2`,`col_3`,`col_4`,`col_5`,`col_6`,`col_7`,`col_8`,`col_9`,`col_10`,`col_11`,`col_12`,`col_13`
FROM (
  WITH tmp_exp AS
  (
    SELECT
        pt,
        exp_layer_name,
        exp_name,
        abflag,
        is_mall_online_ad_goods_cnt_above_99pct,
        is_mall_ad_bill_spend_above_99pct,
        mall_tag,
        mall_tag_value,
        mall_pool_id,
        mall_pool_type,
        sum(buk_cnt) AS buk_cnt,
        sum(ad_bill_spend_1d_buk*buk_cnt)/sum(buk_cnt) AS ad_bill_spend_1d_buk,
        nvl(sum(nvl(est_use_amt,est_use_amt_buck)*buk_cnt)/sum(buk_cnt),0.0) AS est_use_amt
    FROM ads.ads_bi_tob_exp_app_mall_pool_1d_d_s0
    WHERE pt BETWEEN '2026-09-07' AND '2026-09-09'
      AND exp_layer_name IN ('goods_subsidy_popup_full')
      AND is_mall_online_ad_goods_cnt_above_99pct IN ('ALL')
      AND is_mall_ad_bill_spend_above_99pct IN ('ALL')
      AND mall_pool_id IN ('200056')
      AND mall_pool_type IN ('cumulative_pool')
      AND mall_tag IN ('ALL')
      AND mall_tag_value IN ('ALL')
      AND exp_name IN ('GoodsSubsidyPopupFull1')
    GROUP BY
      pt, exp_layer_name, exp_name, abflag,
      is_mall_online_ad_goods_cnt_above_99pct,
      is_mall_ad_bill_spend_above_99pct,
      mall_tag, mall_tag_value, mall_pool_id, mall_pool_type
  ),
  tmp_base AS
  (
    SELECT
        pt,
        exp_layer_name,
        exp_name,
        abflag,
        is_mall_online_ad_goods_cnt_above_99pct,
        is_mall_ad_bill_spend_above_99pct,
        mall_tag,
        mall_tag_value,
        mall_pool_id,
        mall_pool_type,
        sum(buk_cnt) AS buk_cnt,
        sum(ad_bill_spend_1d_buk*buk_cnt)/sum(buk_cnt) AS ad_bill_spend_1d_buk,
        nvl(sum(nvl(est_use_amt,est_use_amt_buck)*buk_cnt)/sum(buk_cnt),0.0) AS est_use_amt
    FROM ads.ads_bi_tob_exp_app_mall_pool_1d_d_s0
    WHERE pt BETWEEN '2026-09-07' AND '2026-09-09'
      AND exp_layer_name IN ('goods_subsidy_popup_full')
      AND is_mall_online_ad_goods_cnt_above_99pct IN ('ALL')
      AND is_mall_ad_bill_spend_above_99pct IN ('ALL')
      AND mall_pool_id IN ('200056')
      AND mall_pool_type IN ('cumulative_pool')
      AND mall_tag IN ('ALL')
      AND mall_tag_value IN ('ALL')
      AND exp_name IN ('goods_subsidy_popup_full_base')
    GROUP BY
      pt, exp_layer_name, exp_name, abflag,
      is_mall_online_ad_goods_cnt_above_99pct,
      is_mall_ad_bill_spend_above_99pct,
      mall_tag, mall_tag_value, mall_pool_id, mall_pool_type
  )
  SELECT
      nvl(exp.pt,'PLACEHOLDER') AS `col_1`,
      exp.exp_layer_name AS `col_2`,
      exp.exp_name AS `col_3`,
      base.exp_name AS `col_4`,
      exp.buk_cnt AS `col_5`,
      base.buk_cnt AS `col_6`,
      exp.is_mall_ad_bill_spend_above_99pct AS `col_7`,
      exp.is_mall_online_ad_goods_cnt_above_99pct AS `col_8`,
      exp.mall_pool_type AS `col_9`,
      exp.mall_pool_id AS `col_10`,
      exp.mall_tag AS `col_11`,
      exp.mall_tag_value AS `col_12`,
      (sum(exp.real_ad_spend)-sum(base.real_ad_spend))
        /(sum(nvl(exp.est_use_amt,0.0))-sum(nvl(base.est_use_amt,0.0))) AS `col_13`
  FROM (
      SELECT
          *,
          nvl(ad_bill_spend_1d_buk,0.0)-nvl(est_use_amt,0.0) real_ad_spend
      FROM tmp_exp
  ) exp
  LEFT JOIN (
      SELECT
          *,
          nvl(ad_bill_spend_1d_buk,0.0)-nvl(est_use_amt,0.0) real_ad_spend
      FROM tmp_base
  ) base
  ON exp.pt = base.pt
     AND exp.exp_layer_name = base.exp_layer_name
     AND exp.is_mall_online_ad_goods_cnt_above_99pct = base.is_mall_online_ad_goods_cnt_above_99pct
     AND exp.is_mall_ad_bill_spend_above_99pct = base.is_mall_ad_bill_spend_above_99pct
     AND exp.mall_pool_id = base.mall_pool_id
     AND exp.mall_pool_type = base.mall_pool_type
     AND exp.mall_tag = base.mall_Tag
     AND exp.mall_tag_value = base.mall_tag_value
     AND ((exp.abflag=base.abflag) OR (exp.abflag IS NULL AND base.abflag IS NULL))
  GROUP BY GROUPING SETS (
      (exp.pt, exp.exp_layer_name, exp.exp_name, base.exp_name, exp.buk_cnt, base.buk_cnt,
       exp.is_mall_ad_bill_spend_above_99pct, exp.is_mall_online_ad_goods_cnt_above_99pct,
       exp.mall_pool_type, exp.mall_pool_id, exp.mall_tag, exp.mall_tag_value),
      (exp.exp_layer_name, exp.exp_name, base.exp_name, exp.buk_cnt, base.buk_cnt,
       exp.is_mall_ad_bill_spend_above_99pct, exp.is_mall_online_ad_goods_cnt_above_99pct,
       exp.mall_pool_type, exp.mall_pool_id, exp.mall_tag, exp.mall_tag_value)
  )
  ORDER BY exp.exp_layer_name, exp.exp_name, base.exp_name
) t LIMIT 50000;

3. Observed behavior

The query never completes. CPU spikes to 100% on a single core and the query times out (or the coordinator OOMs, depending on heap size).

Root Cause

The dedup logic in canonicalize(Assignments)

UnaliasSymbolReferences.canonicalize(Assignments) has a dedup optimization: within a single ProjectNode, if two different keys map to the same deterministic expression, they are merged into one symbol:

java
// UnaliasSymbolReferences.java, canonicalize(Assignments)
else if (!isNull(expression) && determinismEvaluator.isDeterministic(expression)) {
    VariableReferenceExpression computedVariable = computedExpressions.get(expression);
    if (computedVariable == null) {
        computedExpressions.put(expression, entry.getKey());
    } else {
        map(entry.getKey(), computedVariable);  // merge: key -> computedVariable
    }
}

computedExpressions is a HashMap<RowExpression, VariableReferenceExpression>. The problem is that ConstantExpression.equals()/hashCode() is value-based:

java
// ConstantExpression.java
public int hashCode() {
    return Objects.hash(value, type);
}
public boolean equals(Object obj) {
    ...
    return Objects.equals(this.value, other.value)
        && Objects.equals(this.type, other.type);
}

For VARCHAR type, value is a Slice (byte buffer), and Slice.equals compares byte content. So two ConstantExpression('ALL', VARCHAR) from different columns are equals == true with the same hashCode, even though they represent semantically distinct grouping dimensions.

How the cycle forms

Four columns (is_mall_online_ad_goods_cnt_above_99pct, is_mall_ad_bill_spend_above_99pct, mall_tag, mall_tag_value) are filtered to the same single value 'ALL' via IN('ALL'). After constant folding, they all become ConstantExpression('ALL', VARCHAR).

By adding per-ProjectNode debug logging, the cycle formation was traced:

ProjectNode #3 (columns appear in order A, B, mall_tag, mall_tag_value):

key='is_mall_online_ad_goods_cnt_above_99pct'  expr=ConstantExpression('ALL')  ← first seen, record
key='is_mall_ad_bill_spend_above_99pct'        expr=ConstantExpression('ALL')  ← equals hit!
  → map('is_mall_ad_bill_spend_above_99pct' → 'is_mall_online_ad_goods_cnt_above_99pct')   i.e. B→A
key='mall_tag'                                  expr=ConstantExpression('ALL')  ← equals hit!
  → map('mall_tag' → 'is_mall_online_ad_goods_cnt_above_99pct')
key='mall_tag_value'                            expr=ConstantExpression('ALL')  ← equals hit!
  → map('mall_tag_value' → 'is_mall_online_ad_goods_cnt_above_99pct')

ProjectNode #9 (same constants, but in order B, A):

key='is_mall_ad_bill_spend_above_99pct'        expr=ConstantExpression('ALL')  ← first seen, record
key='is_mall_online_ad_goods_cnt_above_99pct'  expr=ConstantExpression('ALL')  ← equals hit!
  → map('is_mall_online_ad_goods_cnt_above_99pct' → 'is_mall_ad_bill_spend_above_99pct')  i.e. A→B  ← CYCLE!

ProjectNode #3 establishes B→A, ProjectNode #9 establishes A→B — a 2-node cycle.

The while loop in canonicalize(VariableReferenceExpression) then spins forever:

java
private VariableReferenceExpression canonicalize(VariableReferenceExpression variable)
{
    String canonical = variable.getName();
    while (mapping.containsKey(canonical)) {  // ← A↔B cycle, infinite loop
        canonical = mapping.get(canonical);
    }
    ...
}

Why this is a bug (not just a missing cycle guard)

The dedup optimization assumes "identical deterministic expressions can be merged into one symbol." This assumption is wrong for GROUPING SETS — the grouping dimension columns are semantically distinct even if their values happen to be identical. Merging them breaks the grouping semantics.

Why the reproducer cannot be trivially small

The bug does not depend on SQL topology alone — it depends on plan depth. With a small SQL, the constant is folded once at the CTE entry and reused as a single symbol across all downstream ProjectNodes, so dedup never triggers. Only when the plan is deep enough (CTE + derived column layer + join + grouping sets + a complex aggregate expression that references both a derived column and a raw column) does the optimizer fold the constant independently in multiple ProjectNodes, producing multiple ConstantExpression('ALL') instances that are equals but come from different columns.

Proposed Fix

Two-layer guard in UnaliasSymbolReferences.java:

1. Cycle detection in map() (primary fix)

Before establishing variable → canonical, check whether canonical already (transitively) maps back to variable. If so, skip the mapping to keep both symbols independent:

java
private void map(VariableReferenceExpression variable, VariableReferenceExpression canonical)
{
    Preconditions.checkArgument(!variable.equals(canonical), "Can't map variable to itself: %s", variable);
    String variableName = variable.getName();
    String canonicalName = canonical.getName();
    if (wouldCreateCycle(variableName, canonicalName)) {
        return;
    }
    mapping.put(variableName, canonicalName);
}

private boolean wouldCreateCycle(String from, String to)
{
    String current = to;
    while (mapping.containsKey(current)) {
        current = mapping.get(current);
        if (current.equals(from)) {
            return true;
        }
    }
    return false;
}

Skipping the mapping is safe: a cycle means the two symbols are being merged into each other via different paths. Keeping them independent does not lose any information — the dedup optimization is an optimization, not a correctness requirement.

2. Hop limit in canonicalize(VariableReferenceExpression) (defensive guard)

java
private VariableReferenceExpression canonicalize(VariableReferenceExpression variable)
{
    String canonical = variable.getName();
    int hops = 0;
    while (mapping.containsKey(canonical)) {
        canonical = mapping.get(canonical);
        if (++hops > mapping.size()) {
            break;  // cycle detected, avoid infinite loop
        }
    }
    return new VariableReferenceExpression(variable.getSourceLocation(), canonical,
        types.get(new SymbolReference(getNodeLocation(variable.getSourceLocation()), canonical)));
}

Verification

Scenario Before fix After fix
Reproducer SQL (108 lines) Timeout / OOM Success (~1s)
Original production SQL (385 lines) Timeout (130s) Success (2.7s)
TestUnaliasSymbolReferences unit tests — 6/6 passed

Source: prestodb/presto

View original on GitHubView discussion on GitHub