[CALCITE-6513] FilterProjectTransposeRule may cause OOM when Project expressions are complex

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.38.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Krisztian Kasa
• Reporter: Krisztian Kasa
• Created: 02/Aug/24 09:58
• Updated: 17/Oct/24 22:41
• Resolved: 17/Oct/24 22:41

Description

CALCITE-3774 addresses preventing merging projects when the resulting expressions in the merged project are too complex and lead to slow compilation or out of memory.

However, when there is a Filter on top of the Projects with a predicate referencing the complex expressions FilterProjectTransposeRule tries to push down the Filter below the bottom Project merging the expressions and causing OOM.

The issue was initially reproduced using Hive with the Hive version of FilterProjectTransposeRule. See: HIVE-28264

Calcite is also affected: https://github.com/kasakrisz/calcite/commit/b35a02f368624a9c4768f348cd072a95ed6de3e1

Let's see the following query

SELECT x1 from
    (SELECT 'L1' || x0  || x0 || x0 || x0 as x1 from
        (SELECT 'L0' || ENAME || ENAME || ENAME || ENAME as x0 from emp) t1) t2
WHERE x1 = 'Something'

Let's set the bloat property of RelBuilder.Config to 3.
The initial plan of the query is:

LogicalProject(X1=[$0])
  LogicalFilter(condition=[=($0, 'Something')])
    LogicalProject(X1=[||(||(||(||('L1', $0), $0), $0), $0)])
      LogicalProject(X0=[||(||(||(||('L0', $1), $1), $1), $1)])
        LogicalTableScan(table=[[CATALOG, SALES, EMP]])

The expressions in the Project operators are mergeable, but the resulting expression's complexity exceeds the limit of 3 in our example.
However, while applying FilterProjectTransposeRule the expressions in the Project operators are merged because the expression in the upper Project references the expression in the lower Project and the predicate in the Filter operator also references it. The limit is not applied this case, so we end up with a plan

LogicalProject(X1=[$0])
  LogicalProject(X1=[||(||(||(||('L1', $0), $0), $0), $0)])
    LogicalProject(X0=[||(||(||(||('L0', $1), $1), $1), $1)])
      LogicalFilter(condition=[=(||(||(||(||('L1', ||(||(||(||('L0', $1), $1), $1), $1)), ||(||(||(||('L0', $1), $1), $1), $1)), ||(||(||(||('L0', $1), $1), $1), $1)), ||(||(||(||('L0', $1), $1), $1), $1)), 'Something')])
        LogicalTableScan(table=[[CATALOG, SALES, EMP]])

Issue Links

• is related to: HIVE-28264 OOM/slow compilation when query contains SELECT clauses with nested expressions
• is related to: CALCITE-3774 Add option to RelBuilder to prevent it from merging projects
• links to: GitHub Pull Request #3900