[
https://issues.apache.org/jira/browse/IGNITE-24752?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sergey Korotkov updated IGNITE-24752:
-------------------------------------
Attachment: TpchQ9Test.java
> Calcite. TPC-H query #9: failed to plan query
> ---------------------------------------------
>
> Key: IGNITE-24752
> URL: https://issues.apache.org/jira/browse/IGNITE-24752
> Project: Ignite
> Issue Type: Bug
> Reporter: Sergey Korotkov
> Priority: Major
> Labels: ise, tpch
> Attachments: TpchQ9Test.java
>
>
> Always fails in planning the similar way as #5 (IGNITE-24741) and #8
> (IGNITE-24746):
> Unexpected error at query optimizer.
> org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not
> enough rules to produce a node with desired properties: convention=IGNITE,
> sort=[0 ASC-nulls-first, 1 DESC-nulls-last], distr=single,
> rewindability=one-way, correlation=uncorrelated.
>
> {noformat}
> [2025-03-18T17:08:08,205][WARN ][test-runner-#494%tpch.TpchQ9Test%][task]
> Volcano planning times out, cancels the subsequent optimization.
> [2025-03-18T17:08:08,208][ERROR][test-runner-#494%tpch.TpchQ9Test%][PrepareServiceImpl]
> Unexpected error at query optimizer.
> org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not
> enough rules to produce a node with desired properties: convention=IGNITE,
> sort=[0 ASC-nulls-first, 1 DESC-nulls-last], distr=single,
> rewindability=one-way, correlation=uncorrelated.
> Missing conversions are IgniteLogicalTableScan[convention: NONE -> IGNITE,
> sort: [] -> [0]], IgniteLogicalTableScan[convention: NONE -> IGNITE, sort: []
> -> [0], distr: any -> single]
> There are 2 empty subsets:
> Empty subset 0: rel#1430:RelSubset#7.IGNITE.[0].single.one-way.uncorrelated,
> the relevant part of the original plan is as follows
> 309:IgniteLogicalTableScan(table=[[PUBLIC, ORDERS]], requiredColumns=[{2, 6}])
> Empty subset 1: rel#1425:RelSubset#7.IGNITE.[0].any.one-way.uncorrelated, the
> relevant part of the original plan is as follows
> 309:IgniteLogicalTableScan(table=[[PUBLIC, ORDERS]], requiredColumns=[{2, 6}])
> Root: rel#1401:RelSubset#12.IGNITE.[0 ASC-nulls-first, 1
> DESC-nulls-last].single.one-way.uncorrelated
> Original rel:
> LogicalSort(subset=[rel#345:RelSubset#13.IGNITE.[0 ASC-nulls-first, 1
> DESC-nulls-last].single.one-way.uncorrelated], sort0=[$0], sort1=[$1],
> dir0=[ASC-nulls-first], dir1=[DESC-nulls-last]): rowcount =
> 7.71109716796875E13, cumulative cost = IgniteCost [rowCount=Infinity,
> cpu=Infinity, memory=Infinity, io=Infinity, network=Infinity], id = 343
>
> LogicalAggregate(subset=[rel#342:RelSubset#12.NONE.[].any.one-way.uncorrelated],
> group=[{0, 1}], SUM_PROFIT=[SUM($2)]): rowcount = 7.71109716796875E13,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 341
>
> LogicalProject(subset=[rel#340:RelSubset#11.NONE.[].any.one-way.uncorrelated],
> NATION=[$16], O_YEAR=[EXTRACT(FLAG(YEAR), $14)], AMOUNT=[-(*($8, -(1, $9)),
> *($12, $7))]): rowcount = 1.0281462890625E14, cumulative cost = IgniteCost
> [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 339
>
> LogicalJoin(subset=[rel#338:RelSubset#10.NONE.[].any.one-way.uncorrelated],
> condition=[=($3, $15)], joinType=[inner]): rowcount = 1.0281462890625E14,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 337
>
> LogicalJoin(subset=[rel#335:RelSubset#8.NONE.[].any.one-way.uncorrelated],
> condition=[=($13, $4)], joinType=[inner]): rowcount = 2.7417234375E13,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 334
>
> LogicalJoin(subset=[rel#332:RelSubset#6.NONE.[].any.one-way.uncorrelated],
> condition=[AND(=($11, $6), =($10, $5))], joinType=[inner]): rowcount =
> 1.21854375E10, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 331
>
> LogicalJoin(subset=[rel#329:RelSubset#4.NONE.[].any.one-way.uncorrelated],
> condition=[AND(=($2, $6), =($0, $5))], joinType=[inner]): rowcount =
> 6.7696875E7, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 328
>
> LogicalJoin(subset=[rel#326:RelSubset#2.NONE.[].any.one-way.uncorrelated],
> condition=[true], joinType=[inner]): rowcount = 50000.0, cumulative cost =
> IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 325
>
> IgniteLogicalTableScan(subset=[rel#323:RelSubset#0.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, PART]], filters=[LIKE($t1, _UTF-8'%green%')],
> requiredColumns=[{2, 3}]): rowcount = 500.0, cumulative cost = IgniteCost
> [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 281
>
> IgniteLogicalTableScan(subset=[rel#324:RelSubset#1.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, SUPPLIER]], requiredColumns=[{2, 5}]): rowcount = 100.0,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 288
>
> IgniteLogicalTableScan(subset=[rel#327:RelSubset#3.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, LINEITEM]], requiredColumns=[{2, 3, 4, 6, 7, 8}]): rowcount
> = 60175.0, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 295
>
> IgniteLogicalTableScan(subset=[rel#330:RelSubset#5.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, PARTSUPP]], requiredColumns=[{2, 3, 5}]): rowcount = 8000.0,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 302
>
> IgniteLogicalTableScan(subset=[rel#333:RelSubset#7.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, ORDERS]], requiredColumns=[{2, 6}]): rowcount = 15000.0,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 309
>
> IgniteLogicalTableScan(subset=[rel#336:RelSubset#9.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, NATION]], requiredColumns=[{2, 3}]): rowcount = 25.0,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 316
> at
> org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:718)
> ~[calcite-core-1.37.0.jar:1.37.0]
> at
> org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:391)
> ~[calcite-core-1.37.0.jar:1.37.0]
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:534)
> ~[calcite-core-1.37.0.jar:1.37.0]
> at
> org.apache.calcite.tools.Programs$RuleSetProgram.run(Programs.java:328)
> ~[calcite-core-1.37.0.jar:1.37.0]
> at
> org.apache.ignite.internal.processors.query.calcite.prepare.IgnitePlanner.transform(IgnitePlanner.java:381)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.calcite.prepare.PlannerHelper.optimize(PlannerHelper.java:107)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.calcite.prepare.PrepareServiceImpl.prepareQuery(PrepareServiceImpl.java:168)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.calcite.prepare.PrepareServiceImpl.prepareSingle(PrepareServiceImpl.java:94)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.calcite.CalciteQueryProcessor.lambda$parseAndProcessQuery$5(CalciteQueryProcessor.java:553)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.calcite.prepare.QueryPlanCacheImpl.lambda$queryPlan$0(QueryPlanCacheImpl.java:70)
> ~[classes/:?]
> at
> java.base/java.util.concurrent.ConcurrentMap.computeIfAbsent(ConcurrentMap.java:330)
> ~[?:?]
> at
> org.apache.ignite.internal.processors.query.calcite.prepare.QueryPlanCacheImpl.queryPlan(QueryPlanCacheImpl.java:70)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.calcite.CalciteQueryProcessor.lambda$parseAndProcessQuery$6(CalciteQueryProcessor.java:550)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.calcite.CalciteQueryProcessor.processQuery(CalciteQueryProcessor.java:701)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.calcite.CalciteQueryProcessor.parseAndProcessQuery(CalciteQueryProcessor.java:547)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.calcite.CalciteQueryProcessor.query(CalciteQueryProcessor.java:420)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor$2.applyx(GridQueryProcessor.java:3119)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor$2.applyx(GridQueryProcessor.java:3075)
> ~[classes/:?]
> at
> org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:3861)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor.lambda$querySqlFields$3(GridQueryProcessor.java:3152)
> ~[classes/:?]
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuerySafe(GridQueryProcessor.java:3289)
> [classes/:?]
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:3071)
> [classes/:?]
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2995)
> [classes/:?]
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2968)
> [classes/:?]
> at
> org.apache.ignite.internal.processors.query.calcite.integration.tpch.TpchQ9Test.exec(TpchQ9Test.java:73)
> [test-classes/:?]
> at
> org.apache.ignite.internal.processors.query.calcite.integration.tpch.TpchQ9Test.testQ9(TpchQ9Test.java:62)
> [test-classes/:?]
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
> Method) ~[?:?]
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> ~[?:?]
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> ~[?:?]
> at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
> at
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
> [junit-4.12.jar:4.12]
> at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
> [junit-4.12.jar:4.12]
> at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
> [junit-4.12.jar:4.12]
> at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
> [junit-4.12.jar:4.12]
> at
> org.apache.ignite.testframework.junits.GridAbstractTest$6.run(GridAbstractTest.java:2508)
> [test-classes/:?]
> at java.base/java.lang.Thread.run(Thread.java:829) [?:?]
> [2025-03-18T17:08:08,212][ERROR][test-runner-#494%tpch.TpchQ9Test%][PrepareServiceImpl]
> Root: rel#1401:RelSubset#12.IGNITE.[0 ASC-nulls-first, 1
> DESC-nulls-last].single.one-way.uncorrelated
> Original rel:
> LogicalSort(subset=[rel#345:RelSubset#13.IGNITE.[0 ASC-nulls-first, 1
> DESC-nulls-last].single.one-way.uncorrelated], sort0=[$0], sort1=[$1],
> dir0=[ASC-nulls-first], dir1=[DESC-nulls-last]): rowcount =
> 7.71109716796875E13, cumulative cost = IgniteCost [rowCount=Infinity,
> cpu=Infinity, memory=Infinity, io=Infinity, network=Infinity], id = 343
>
> LogicalAggregate(subset=[rel#342:RelSubset#12.NONE.[].any.one-way.uncorrelated],
> group=[{0, 1}], SUM_PROFIT=[SUM($2)]): rowcount = 7.71109716796875E13,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 341
>
> LogicalProject(subset=[rel#340:RelSubset#11.NONE.[].any.one-way.uncorrelated],
> NATION=[$16], O_YEAR=[EXTRACT(FLAG(YEAR), $14)], AMOUNT=[-(*($8, -(1, $9)),
> *($12, $7))]): rowcount = 1.0281462890625E14, cumulative cost = IgniteCost
> [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 339
>
> LogicalJoin(subset=[rel#338:RelSubset#10.NONE.[].any.one-way.uncorrelated],
> condition=[=($3, $15)], joinType=[inner]): rowcount = 1.0281462890625E14,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 337
>
> LogicalJoin(subset=[rel#335:RelSubset#8.NONE.[].any.one-way.uncorrelated],
> condition=[=($13, $4)], joinType=[inner]): rowcount = 2.7417234375E13,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 334
>
> LogicalJoin(subset=[rel#332:RelSubset#6.NONE.[].any.one-way.uncorrelated],
> condition=[AND(=($11, $6), =($10, $5))], joinType=[inner]): rowcount =
> 1.21854375E10, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 331
>
> LogicalJoin(subset=[rel#329:RelSubset#4.NONE.[].any.one-way.uncorrelated],
> condition=[AND(=($2, $6), =($0, $5))], joinType=[inner]): rowcount =
> 6.7696875E7, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 328
>
> LogicalJoin(subset=[rel#326:RelSubset#2.NONE.[].any.one-way.uncorrelated],
> condition=[true], joinType=[inner]): rowcount = 50000.0, cumulative cost =
> IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 325
>
> IgniteLogicalTableScan(subset=[rel#323:RelSubset#0.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, PART]], filters=[LIKE($t1, _UTF-8'%green%')],
> requiredColumns=[{2, 3}]): rowcount = 500.0, cumulative cost = IgniteCost
> [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 281
>
> IgniteLogicalTableScan(subset=[rel#324:RelSubset#1.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, SUPPLIER]], requiredColumns=[{2, 5}]): rowcount = 100.0,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 288
>
> IgniteLogicalTableScan(subset=[rel#327:RelSubset#3.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, LINEITEM]], requiredColumns=[{2, 3, 4, 6, 7, 8}]): rowcount
> = 60175.0, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 295
>
> IgniteLogicalTableScan(subset=[rel#330:RelSubset#5.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, PARTSUPP]], requiredColumns=[{2, 3, 5}]): rowcount = 8000.0,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 302
>
> IgniteLogicalTableScan(subset=[rel#333:RelSubset#7.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, ORDERS]], requiredColumns=[{2, 6}]): rowcount = 15000.0,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 309
>
> IgniteLogicalTableScan(subset=[rel#336:RelSubset#9.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, NATION]], requiredColumns=[{2, 3}]): rowcount = 25.0,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 316
> [2025-03-18T17:08:08,213][WARN
> ][test-runner-#494%tpch.TpchQ9Test%][HeavyQueriesTracker] Long running query
> is finished with error: Failed to plan query
> [queryId=89ae02a6-60da-4925-b3d2-afa7b0cfbcf0,
> globalQueryId=2c3da017-e417-480e-912d-c2aad54ee25d_30, planningTime=40013ms,
> execTime=0ms, idleTime=0ms, timeout=0ms, type=CALCITE, state=CLOSED,
> schema=PUBLIC, sql='SELECT `NATION`, `O_YEAR`, SUM(`AMOUNT`) AS `SUM_PROFIT`
> FROM (SELECT `N_NAME` AS `NATION`, EXTRACT(YEAR FROM `O_ORDERDATE`) AS
> `O_YEAR`, `L_EXTENDEDPRICE` * (1 - `L_DISCOUNT`) - `PS_SUPPLYCOST` *
> `L_QUANTITY` AS `AMOUNT`
> FROM `PART`,
> `SUPPLIER`,
> `LINEITEM`,
> `PARTSUPP`,
> `ORDERS`,
> `NATION`
> WHERE `S_SUPPKEY` = `L_SUPPKEY` AND `PS_SUPPKEY` = `L_SUPPKEY` AND
> `PS_PARTKEY` = `L_PARTKEY` AND `P_PARTKEY` = `L_PARTKEY` AND `O_ORDERKEY` =
> `L_ORDERKEY` AND `S_NATIONKEY` = `N_NATIONKEY` AND `P_NAME` LIKE '%green%')
> AS `PROFIT`
> GROUP BY `NATION`, `O_YEAR`
> ORDER BY `NATION`, `O_YEAR` DESC]
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
