[
https://issues.apache.org/jira/browse/IGNITE-24746?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sergey Korotkov updated IGNITE-24746:
-------------------------------------
Description:
Always fails in planning.
Very similar to query #5 problem (IGNITE-24741) but fails always for me.
{noformat}
[2025-03-18T17:00:52,636][WARN ][test-runner-#494%tpch.TpchQ8Test%][task]
Volcano planning times out, cancels the subsequent optimization.
[2025-03-18T17:00:52,638][ERROR][test-runner-#494%tpch.TpchQ8Test%][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], distr=single, rewindability=one-way,
correlation=uncorrelated.
Missing conversions are IgniteLogicalTableScan[convention: NONE -> IGNITE,
sort: [] -> [0], distr: any -> single], IgniteLogicalTableScan[convention: NONE
-> IGNITE, sort: [] -> [0]]
There are 2 empty subsets:
Empty subset 0: rel#4770:RelSubset#11.IGNITE.[0].any.one-way.uncorrelated, the
relevant part of the original plan is as follows
413:IgniteLogicalTableScan(table=[[PUBLIC, NATION]], requiredColumns=[{2, 3}])
Empty subset 1: rel#4775:RelSubset#11.IGNITE.[0].single.one-way.uncorrelated,
the relevant part of the original plan is as follows
413:IgniteLogicalTableScan(table=[[PUBLIC, NATION]], requiredColumns=[{2, 3}])
Root: rel#4748:RelSubset#17.IGNITE.[0
ASC-nulls-first].single.one-way.uncorrelated
Original rel:
LogicalSort(subset=[rel#461:RelSubset#18.IGNITE.[0
ASC-nulls-first].single.one-way.uncorrelated], sort0=[$0],
dir0=[ASC-nulls-first]): rowcount = 5.421865196228027E12, cumulative cost =
IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
network=Infinity], id = 459
LogicalProject(subset=[rel#458:RelSubset#17.NONE.[].any.one-way.uncorrelated],
O_YEAR=[$0], MKT_SHARE=[/($1, $2)]): rowcount = 5.421865196228027E12,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 457
LogicalAggregate(subset=[rel#456:RelSubset#16.NONE.[].any.one-way.uncorrelated],
group=[{0}], agg#0=[SUM($1)], agg#1=[SUM($2)]): rowcount =
5.421865196228027E12, cumulative cost = IgniteCost [rowCount=Infinity,
cpu=Infinity, memory=Infinity, io=Infinity, network=Infinity], id = 455
LogicalProject(subset=[rel#454:RelSubset#15.NONE.[].any.one-way.uncorrelated],
O_YEAR=[EXTRACT(FLAG(YEAR), $11)], $f1=[CASE(=($17, _UTF-8'BRAZIL'), *($7, -(1,
$8)), 0.0000:DECIMAL(31, 4))], VOLUME=[*($7, -(1, $8))]): rowcount =
1.0843730392456055E13, cumulative cost = IgniteCost [rowCount=Infinity,
cpu=Infinity, memory=Infinity, io=Infinity, network=Infinity], id = 453
LogicalJoin(subset=[rel#452:RelSubset#14.NONE.[].any.one-way.uncorrelated],
condition=[=($15, $18)], joinType=[inner]): rowcount = 1.0843730392456055E13,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 451
LogicalJoin(subset=[rel#449:RelSubset#12.NONE.[].any.one-way.uncorrelated],
condition=[=($3, $16)], joinType=[inner]): rowcount = 7.229153594970703E13,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 448
LogicalJoin(subset=[rel#446:RelSubset#10.NONE.[].any.one-way.uncorrelated],
condition=[=($13, $14)], joinType=[inner]): rowcount = 1.9277742919921875E13,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 445
LogicalJoin(subset=[rel#443:RelSubset#8.NONE.[].any.one-way.uncorrelated],
condition=[=($10, $12)], joinType=[inner]): rowcount = 5.1407314453125E12,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 442
LogicalJoin(subset=[rel#440:RelSubset#6.NONE.[].any.one-way.uncorrelated],
condition=[=($4, $9)], joinType=[inner]): rowcount = 2.28476953125E10,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 439
LogicalJoin(subset=[rel#437:RelSubset#4.NONE.[].any.one-way.uncorrelated],
condition=[AND(=($0, $5), =($2, $6))], joinType=[inner]): rowcount =
4.0618125E7, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
memory=Infinity, io=Infinity, network=Infinity], id = 436
LogicalJoin(subset=[rel#434:RelSubset#2.NONE.[].any.one-way.uncorrelated],
condition=[true], joinType=[inner]): rowcount = 30000.0, cumulative cost =
IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
network=Infinity], id = 433
IgniteLogicalTableScan(subset=[rel#431:RelSubset#0.NONE.[].any.one-way.uncorrelated],
table=[[PUBLIC, PART]], filters=[=($t1, _UTF-8'ECONOMY ANODIZED STEEL')],
requiredColumns=[{2, 6}]): rowcount = 300.0, cumulative cost = IgniteCost
[rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
network=Infinity], id = 359
IgniteLogicalTableScan(subset=[rel#432: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 = 368
IgniteLogicalTableScan(subset=[rel#435:RelSubset#3.NONE.[].any.one-way.uncorrelated],
table=[[PUBLIC, LINEITEM]], requiredColumns=[{2, 3, 4, 7, 8}]): rowcount =
60175.0, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
memory=Infinity, io=Infinity, network=Infinity], id = 377
IgniteLogicalTableScan(subset=[rel#438:RelSubset#5.NONE.[].any.one-way.uncorrelated],
table=[[PUBLIC, ORDERS]], filters=[AND(>=($t2, 1995-01-01), <=($t2,
1996-12-31))], requiredColumns=[{2, 3, 6}]): rowcount = 3750.0, cumulative cost
= IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
network=Infinity], id = 386
IgniteLogicalTableScan(subset=[rel#441:RelSubset#7.NONE.[].any.one-way.uncorrelated],
table=[[PUBLIC, CUSTOMER]], requiredColumns=[{2, 5}]): rowcount = 1500.0,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 395
IgniteLogicalTableScan(subset=[rel#444:RelSubset#9.NONE.[].any.one-way.uncorrelated],
table=[[PUBLIC, NATION]], requiredColumns=[{2, 4}]): rowcount = 25.0,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 404
IgniteLogicalTableScan(subset=[rel#447:RelSubset#11.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 = 413
IgniteLogicalTableScan(subset=[rel#450:RelSubset#13.NONE.[].any.one-way.uncorrelated],
table=[[PUBLIC, REGION]], filters=[=($t1, _UTF-8'AMERICA')],
requiredColumns=[{2, 3}]): rowcount = 1.0, cumulative cost = IgniteCost
[rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
network=Infinity], id = 422
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.TpchQ8Test.exec(TpchQ8Test.java:76)
[test-classes/:?]
at
org.apache.ignite.internal.processors.query.calcite.integration.tpch.TpchQ8Test.testQ8(TpchQ8Test.java:65)
[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:00:52,643][ERROR][test-runner-#494%tpch.TpchQ8Test%][PrepareServiceImpl]
Root: rel#4748:RelSubset#17.IGNITE.[0
ASC-nulls-first].single.one-way.uncorrelated
Original rel:
LogicalSort(subset=[rel#461:RelSubset#18.IGNITE.[0
ASC-nulls-first].single.one-way.uncorrelated], sort0=[$0],
dir0=[ASC-nulls-first]): rowcount = 5.421865196228027E12, cumulative cost =
IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
network=Infinity], id = 459
LogicalProject(subset=[rel#458:RelSubset#17.NONE.[].any.one-way.uncorrelated],
O_YEAR=[$0], MKT_SHARE=[/($1, $2)]): rowcount = 5.421865196228027E12,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 457
LogicalAggregate(subset=[rel#456:RelSubset#16.NONE.[].any.one-way.uncorrelated],
group=[{0}], agg#0=[SUM($1)], agg#1=[SUM($2)]): rowcount =
5.421865196228027E12, cumulative cost = IgniteCost [rowCount=Infinity,
cpu=Infinity, memory=Infinity, io=Infinity, network=Infinity], id = 455
LogicalProject(subset=[rel#454:RelSubset#15.NONE.[].any.one-way.uncorrelated],
O_YEAR=[EXTRACT(FLAG(YEAR), $11)], $f1=[CASE(=($17, _UTF-8'BRAZIL'), *($7, -(1,
$8)), 0.0000:DECIMAL(31, 4))], VOLUME=[*($7, -(1, $8))]): rowcount =
1.0843730392456055E13, cumulative cost = IgniteCost [rowCount=Infinity,
cpu=Infinity, memory=Infinity, io=Infinity, network=Infinity], id = 453
LogicalJoin(subset=[rel#452:RelSubset#14.NONE.[].any.one-way.uncorrelated],
condition=[=($15, $18)], joinType=[inner]): rowcount = 1.0843730392456055E13,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 451
LogicalJoin(subset=[rel#449:RelSubset#12.NONE.[].any.one-way.uncorrelated],
condition=[=($3, $16)], joinType=[inner]): rowcount = 7.229153594970703E13,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 448
LogicalJoin(subset=[rel#446:RelSubset#10.NONE.[].any.one-way.uncorrelated],
condition=[=($13, $14)], joinType=[inner]): rowcount = 1.9277742919921875E13,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 445
LogicalJoin(subset=[rel#443:RelSubset#8.NONE.[].any.one-way.uncorrelated],
condition=[=($10, $12)], joinType=[inner]): rowcount = 5.1407314453125E12,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 442
LogicalJoin(subset=[rel#440:RelSubset#6.NONE.[].any.one-way.uncorrelated],
condition=[=($4, $9)], joinType=[inner]): rowcount = 2.28476953125E10,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 439
LogicalJoin(subset=[rel#437:RelSubset#4.NONE.[].any.one-way.uncorrelated],
condition=[AND(=($0, $5), =($2, $6))], joinType=[inner]): rowcount =
4.0618125E7, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
memory=Infinity, io=Infinity, network=Infinity], id = 436
LogicalJoin(subset=[rel#434:RelSubset#2.NONE.[].any.one-way.uncorrelated],
condition=[true], joinType=[inner]): rowcount = 30000.0, cumulative cost =
IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
network=Infinity], id = 433
IgniteLogicalTableScan(subset=[rel#431:RelSubset#0.NONE.[].any.one-way.uncorrelated],
table=[[PUBLIC, PART]], filters=[=($t1, _UTF-8'ECONOMY ANODIZED STEEL')],
requiredColumns=[{2, 6}]): rowcount = 300.0, cumulative cost = IgniteCost
[rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
network=Infinity], id = 359
IgniteLogicalTableScan(subset=[rel#432: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 = 368
IgniteLogicalTableScan(subset=[rel#435:RelSubset#3.NONE.[].any.one-way.uncorrelated],
table=[[PUBLIC, LINEITEM]], requiredColumns=[{2, 3, 4, 7, 8}]): rowcount =
60175.0, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
memory=Infinity, io=Infinity, network=Infinity], id = 377
IgniteLogicalTableScan(subset=[rel#438:RelSubset#5.NONE.[].any.one-way.uncorrelated],
table=[[PUBLIC, ORDERS]], filters=[AND(>=($t2, 1995-01-01), <=($t2,
1996-12-31))], requiredColumns=[{2, 3, 6}]): rowcount = 3750.0, cumulative cost
= IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
network=Infinity], id = 386
IgniteLogicalTableScan(subset=[rel#441:RelSubset#7.NONE.[].any.one-way.uncorrelated],
table=[[PUBLIC, CUSTOMER]], requiredColumns=[{2, 5}]): rowcount = 1500.0,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 395
IgniteLogicalTableScan(subset=[rel#444:RelSubset#9.NONE.[].any.one-way.uncorrelated],
table=[[PUBLIC, NATION]], requiredColumns=[{2, 4}]): rowcount = 25.0,
cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
io=Infinity, network=Infinity], id = 404
IgniteLogicalTableScan(subset=[rel#447:RelSubset#11.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 = 413
IgniteLogicalTableScan(subset=[rel#450:RelSubset#13.NONE.[].any.one-way.uncorrelated],
table=[[PUBLIC, REGION]], filters=[=($t1, _UTF-8'AMERICA')],
requiredColumns=[{2, 3}]): rowcount = 1.0, cumulative cost = IgniteCost
[rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
network=Infinity], id = 422
[2025-03-18T17:00:52,644][WARN
][test-runner-#494%tpch.TpchQ8Test%][HeavyQueriesTracker] Long running query is
finished with error: Failed to plan query
[queryId=4716d69a-62cb-4336-92de-06f415540db7,
globalQueryId=03c8b901-a980-4adb-84ce-d8cc35181b7e_30, planningTime=15476ms,
execTime=0ms, idleTime=0ms, timeout=0ms, type=CALCITE, state=CLOSED,
schema=PUBLIC, sql='SELECT `O_YEAR`, SUM(CASE WHEN `NATION` = 'BRAZIL' THEN
`VOLUME` ELSE 0 END) / SUM(`VOLUME`) AS `MKT_SHARE`
FROM (SELECT EXTRACT(YEAR FROM `O_ORDERDATE`) AS `O_YEAR`, `L_EXTENDEDPRICE` *
(1 - `L_DISCOUNT`) AS `VOLUME`, `N2`.`N_NAME` AS `NATION`
FROM `PART`,
`SUPPLIER`,
`LINEITEM`,
`ORDERS`,
`CUSTOMER`,
`NATION` AS `N1`,
`NATION` AS `N2`,
`REGION`
WHERE `P_PARTKEY` = `L_PARTKEY` AND `S_SUPPKEY` = `L_SUPPKEY` AND `L_ORDERKEY`
= `O_ORDERKEY` AND `O_CUSTKEY` = `C_CUSTKEY` AND `C_NATIONKEY` =
`N1`.`N_NATIONKEY` AND `N1`.`N_REGIONKEY` = `R_REGIONKEY` AND `R_NAME` =
'AMERICA' AND `S_NATIONKEY` = `N2`.`N_NATIONKEY` AND `O_ORDERDATE` BETWEEN
ASYMMETRIC DATE '1995-01-01' AND DATE '1996-12-31' AND `P_TYPE` = 'ECONOMY
ANODIZED STEEL') AS `ALL_NATIONS`
GROUP BY `O_YEAR`
ORDER BY `O_YEAR`]
{noformat}
was:
{color:#172b4d}Very similar to query #5 problem (IGNITE-24741) but fails always
for me.{color}
{noformat}
*no* further _formatting_ is done here{noformat}
> Calcite. TPC-H query #8: failed to plan query
> ---------------------------------------------
>
> Key: IGNITE-24746
> URL: https://issues.apache.org/jira/browse/IGNITE-24746
> Project: Ignite
> Issue Type: Bug
> Reporter: Sergey Korotkov
> Priority: Major
> Labels: ise, tpch
>
> Always fails in planning.
> Very similar to query #5 problem (IGNITE-24741) but fails always for me.
>
> {noformat}
> [2025-03-18T17:00:52,636][WARN ][test-runner-#494%tpch.TpchQ8Test%][task]
> Volcano planning times out, cancels the subsequent optimization.
> [2025-03-18T17:00:52,638][ERROR][test-runner-#494%tpch.TpchQ8Test%][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], distr=single, rewindability=one-way,
> correlation=uncorrelated.
> Missing conversions are IgniteLogicalTableScan[convention: NONE -> IGNITE,
> sort: [] -> [0], distr: any -> single], IgniteLogicalTableScan[convention:
> NONE -> IGNITE, sort: [] -> [0]]
> There are 2 empty subsets:
> Empty subset 0: rel#4770:RelSubset#11.IGNITE.[0].any.one-way.uncorrelated,
> the relevant part of the original plan is as follows
> 413:IgniteLogicalTableScan(table=[[PUBLIC, NATION]], requiredColumns=[{2, 3}])
> Empty subset 1: rel#4775:RelSubset#11.IGNITE.[0].single.one-way.uncorrelated,
> the relevant part of the original plan is as follows
> 413:IgniteLogicalTableScan(table=[[PUBLIC, NATION]], requiredColumns=[{2, 3}])
> Root: rel#4748:RelSubset#17.IGNITE.[0
> ASC-nulls-first].single.one-way.uncorrelated
> Original rel:
> LogicalSort(subset=[rel#461:RelSubset#18.IGNITE.[0
> ASC-nulls-first].single.one-way.uncorrelated], sort0=[$0],
> dir0=[ASC-nulls-first]): rowcount = 5.421865196228027E12, cumulative cost =
> IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 459
>
> LogicalProject(subset=[rel#458:RelSubset#17.NONE.[].any.one-way.uncorrelated],
> O_YEAR=[$0], MKT_SHARE=[/($1, $2)]): rowcount = 5.421865196228027E12,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 457
>
> LogicalAggregate(subset=[rel#456:RelSubset#16.NONE.[].any.one-way.uncorrelated],
> group=[{0}], agg#0=[SUM($1)], agg#1=[SUM($2)]): rowcount =
> 5.421865196228027E12, cumulative cost = IgniteCost [rowCount=Infinity,
> cpu=Infinity, memory=Infinity, io=Infinity, network=Infinity], id = 455
>
> LogicalProject(subset=[rel#454:RelSubset#15.NONE.[].any.one-way.uncorrelated],
> O_YEAR=[EXTRACT(FLAG(YEAR), $11)], $f1=[CASE(=($17, _UTF-8'BRAZIL'), *($7,
> -(1, $8)), 0.0000:DECIMAL(31, 4))], VOLUME=[*($7, -(1, $8))]): rowcount =
> 1.0843730392456055E13, cumulative cost = IgniteCost [rowCount=Infinity,
> cpu=Infinity, memory=Infinity, io=Infinity, network=Infinity], id = 453
>
> LogicalJoin(subset=[rel#452:RelSubset#14.NONE.[].any.one-way.uncorrelated],
> condition=[=($15, $18)], joinType=[inner]): rowcount = 1.0843730392456055E13,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 451
>
> LogicalJoin(subset=[rel#449:RelSubset#12.NONE.[].any.one-way.uncorrelated],
> condition=[=($3, $16)], joinType=[inner]): rowcount = 7.229153594970703E13,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 448
>
> LogicalJoin(subset=[rel#446:RelSubset#10.NONE.[].any.one-way.uncorrelated],
> condition=[=($13, $14)], joinType=[inner]): rowcount = 1.9277742919921875E13,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 445
>
> LogicalJoin(subset=[rel#443:RelSubset#8.NONE.[].any.one-way.uncorrelated],
> condition=[=($10, $12)], joinType=[inner]): rowcount = 5.1407314453125E12,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 442
>
> LogicalJoin(subset=[rel#440:RelSubset#6.NONE.[].any.one-way.uncorrelated],
> condition=[=($4, $9)], joinType=[inner]): rowcount = 2.28476953125E10,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 439
>
> LogicalJoin(subset=[rel#437:RelSubset#4.NONE.[].any.one-way.uncorrelated],
> condition=[AND(=($0, $5), =($2, $6))], joinType=[inner]): rowcount =
> 4.0618125E7, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 436
>
> LogicalJoin(subset=[rel#434:RelSubset#2.NONE.[].any.one-way.uncorrelated],
> condition=[true], joinType=[inner]): rowcount = 30000.0, cumulative cost =
> IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 433
>
> IgniteLogicalTableScan(subset=[rel#431:RelSubset#0.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, PART]], filters=[=($t1, _UTF-8'ECONOMY ANODIZED STEEL')],
> requiredColumns=[{2, 6}]): rowcount = 300.0, cumulative cost = IgniteCost
> [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 359
>
> IgniteLogicalTableScan(subset=[rel#432: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 = 368
>
> IgniteLogicalTableScan(subset=[rel#435:RelSubset#3.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, LINEITEM]], requiredColumns=[{2, 3, 4, 7, 8}]): rowcount =
> 60175.0, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 377
>
> IgniteLogicalTableScan(subset=[rel#438:RelSubset#5.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, ORDERS]], filters=[AND(>=($t2, 1995-01-01), <=($t2,
> 1996-12-31))], requiredColumns=[{2, 3, 6}]): rowcount = 3750.0, cumulative
> cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
> io=Infinity, network=Infinity], id = 386
>
> IgniteLogicalTableScan(subset=[rel#441:RelSubset#7.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, CUSTOMER]], requiredColumns=[{2, 5}]): rowcount = 1500.0,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 395
>
> IgniteLogicalTableScan(subset=[rel#444:RelSubset#9.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, NATION]], requiredColumns=[{2, 4}]): rowcount = 25.0,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 404
>
> IgniteLogicalTableScan(subset=[rel#447:RelSubset#11.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 = 413
>
> IgniteLogicalTableScan(subset=[rel#450:RelSubset#13.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, REGION]], filters=[=($t1, _UTF-8'AMERICA')],
> requiredColumns=[{2, 3}]): rowcount = 1.0, cumulative cost = IgniteCost
> [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 422
> 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.TpchQ8Test.exec(TpchQ8Test.java:76)
> [test-classes/:?]
> at
> org.apache.ignite.internal.processors.query.calcite.integration.tpch.TpchQ8Test.testQ8(TpchQ8Test.java:65)
> [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:00:52,643][ERROR][test-runner-#494%tpch.TpchQ8Test%][PrepareServiceImpl]
> Root: rel#4748:RelSubset#17.IGNITE.[0
> ASC-nulls-first].single.one-way.uncorrelated
> Original rel:
> LogicalSort(subset=[rel#461:RelSubset#18.IGNITE.[0
> ASC-nulls-first].single.one-way.uncorrelated], sort0=[$0],
> dir0=[ASC-nulls-first]): rowcount = 5.421865196228027E12, cumulative cost =
> IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 459
>
> LogicalProject(subset=[rel#458:RelSubset#17.NONE.[].any.one-way.uncorrelated],
> O_YEAR=[$0], MKT_SHARE=[/($1, $2)]): rowcount = 5.421865196228027E12,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 457
>
> LogicalAggregate(subset=[rel#456:RelSubset#16.NONE.[].any.one-way.uncorrelated],
> group=[{0}], agg#0=[SUM($1)], agg#1=[SUM($2)]): rowcount =
> 5.421865196228027E12, cumulative cost = IgniteCost [rowCount=Infinity,
> cpu=Infinity, memory=Infinity, io=Infinity, network=Infinity], id = 455
>
> LogicalProject(subset=[rel#454:RelSubset#15.NONE.[].any.one-way.uncorrelated],
> O_YEAR=[EXTRACT(FLAG(YEAR), $11)], $f1=[CASE(=($17, _UTF-8'BRAZIL'), *($7,
> -(1, $8)), 0.0000:DECIMAL(31, 4))], VOLUME=[*($7, -(1, $8))]): rowcount =
> 1.0843730392456055E13, cumulative cost = IgniteCost [rowCount=Infinity,
> cpu=Infinity, memory=Infinity, io=Infinity, network=Infinity], id = 453
>
> LogicalJoin(subset=[rel#452:RelSubset#14.NONE.[].any.one-way.uncorrelated],
> condition=[=($15, $18)], joinType=[inner]): rowcount = 1.0843730392456055E13,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 451
>
> LogicalJoin(subset=[rel#449:RelSubset#12.NONE.[].any.one-way.uncorrelated],
> condition=[=($3, $16)], joinType=[inner]): rowcount = 7.229153594970703E13,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 448
>
> LogicalJoin(subset=[rel#446:RelSubset#10.NONE.[].any.one-way.uncorrelated],
> condition=[=($13, $14)], joinType=[inner]): rowcount = 1.9277742919921875E13,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 445
>
> LogicalJoin(subset=[rel#443:RelSubset#8.NONE.[].any.one-way.uncorrelated],
> condition=[=($10, $12)], joinType=[inner]): rowcount = 5.1407314453125E12,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 442
>
> LogicalJoin(subset=[rel#440:RelSubset#6.NONE.[].any.one-way.uncorrelated],
> condition=[=($4, $9)], joinType=[inner]): rowcount = 2.28476953125E10,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 439
>
> LogicalJoin(subset=[rel#437:RelSubset#4.NONE.[].any.one-way.uncorrelated],
> condition=[AND(=($0, $5), =($2, $6))], joinType=[inner]): rowcount =
> 4.0618125E7, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 436
>
> LogicalJoin(subset=[rel#434:RelSubset#2.NONE.[].any.one-way.uncorrelated],
> condition=[true], joinType=[inner]): rowcount = 30000.0, cumulative cost =
> IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 433
>
> IgniteLogicalTableScan(subset=[rel#431:RelSubset#0.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, PART]], filters=[=($t1, _UTF-8'ECONOMY ANODIZED STEEL')],
> requiredColumns=[{2, 6}]): rowcount = 300.0, cumulative cost = IgniteCost
> [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 359
>
> IgniteLogicalTableScan(subset=[rel#432: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 = 368
>
> IgniteLogicalTableScan(subset=[rel#435:RelSubset#3.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, LINEITEM]], requiredColumns=[{2, 3, 4, 7, 8}]): rowcount =
> 60175.0, cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 377
>
> IgniteLogicalTableScan(subset=[rel#438:RelSubset#5.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, ORDERS]], filters=[AND(>=($t2, 1995-01-01), <=($t2,
> 1996-12-31))], requiredColumns=[{2, 3, 6}]): rowcount = 3750.0, cumulative
> cost = IgniteCost [rowCount=Infinity, cpu=Infinity, memory=Infinity,
> io=Infinity, network=Infinity], id = 386
>
> IgniteLogicalTableScan(subset=[rel#441:RelSubset#7.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, CUSTOMER]], requiredColumns=[{2, 5}]): rowcount = 1500.0,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 395
>
> IgniteLogicalTableScan(subset=[rel#444:RelSubset#9.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, NATION]], requiredColumns=[{2, 4}]): rowcount = 25.0,
> cumulative cost = IgniteCost [rowCount=Infinity, cpu=Infinity,
> memory=Infinity, io=Infinity, network=Infinity], id = 404
>
> IgniteLogicalTableScan(subset=[rel#447:RelSubset#11.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 = 413
>
> IgniteLogicalTableScan(subset=[rel#450:RelSubset#13.NONE.[].any.one-way.uncorrelated],
> table=[[PUBLIC, REGION]], filters=[=($t1, _UTF-8'AMERICA')],
> requiredColumns=[{2, 3}]): rowcount = 1.0, cumulative cost = IgniteCost
> [rowCount=Infinity, cpu=Infinity, memory=Infinity, io=Infinity,
> network=Infinity], id = 422
> [2025-03-18T17:00:52,644][WARN
> ][test-runner-#494%tpch.TpchQ8Test%][HeavyQueriesTracker] Long running query
> is finished with error: Failed to plan query
> [queryId=4716d69a-62cb-4336-92de-06f415540db7,
> globalQueryId=03c8b901-a980-4adb-84ce-d8cc35181b7e_30, planningTime=15476ms,
> execTime=0ms, idleTime=0ms, timeout=0ms, type=CALCITE, state=CLOSED,
> schema=PUBLIC, sql='SELECT `O_YEAR`, SUM(CASE WHEN `NATION` = 'BRAZIL' THEN
> `VOLUME` ELSE 0 END) / SUM(`VOLUME`) AS `MKT_SHARE`
> FROM (SELECT EXTRACT(YEAR FROM `O_ORDERDATE`) AS `O_YEAR`, `L_EXTENDEDPRICE`
> * (1 - `L_DISCOUNT`) AS `VOLUME`, `N2`.`N_NAME` AS `NATION`
> FROM `PART`,
> `SUPPLIER`,
> `LINEITEM`,
> `ORDERS`,
> `CUSTOMER`,
> `NATION` AS `N1`,
> `NATION` AS `N2`,
> `REGION`
> WHERE `P_PARTKEY` = `L_PARTKEY` AND `S_SUPPKEY` = `L_SUPPKEY` AND
> `L_ORDERKEY` = `O_ORDERKEY` AND `O_CUSTKEY` = `C_CUSTKEY` AND `C_NATIONKEY` =
> `N1`.`N_NATIONKEY` AND `N1`.`N_REGIONKEY` = `R_REGIONKEY` AND `R_NAME` =
> 'AMERICA' AND `S_NATIONKEY` = `N2`.`N_NATIONKEY` AND `O_ORDERDATE` BETWEEN
> ASYMMETRIC DATE '1995-01-01' AND DATE '1996-12-31' AND `P_TYPE` = 'ECONOMY
> ANODIZED STEEL') AS `ALL_NATIONS`
> GROUP BY `O_YEAR`
> ORDER BY `O_YEAR`]
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
