[
https://issues.apache.org/jira/browse/CALCITE-7324?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ian Bertolacci updated CALCITE-7324:
------------------------------------
Description:
Example SQL (using TCP-H schema):
{code:Sql}
select
(select count(*) from PART where p_partkey = PARTSUPP.ps_partkey),
(select count(*) from SUPPLIER where s_suppkey = case when s_acctbal > 0
then PARTSUPP.ps_partkey + 1 else 1234 end)
from PARTSUPP
{code}
Exception:
{code}
Exception in thread "main" java.lang.NullPointerException
at java.base/java.util.Objects.requireNonNull(Objects.java:209)
at
org.apache.calcite.sql2rel.RelDecorrelator.createValueGenerator(RelDecorrelator.java:1272)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateInputWithValueGenerator(RelDecorrelator.java:1466)
at
org.apache.calcite.sql2rel.RelDecorrelator.maybeAddValueGenerator(RelDecorrelator.java:1385)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1595)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1560)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:614)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:596)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1640)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1615)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1192)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1175)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1192)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1175)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1192)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1175)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:342)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:261)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
at
com.github.zabetak.calcite.tutorial.RecursiveQueryIssueDemo.main(RecursiveQueryIssueDemo.java:170)
{code}
I demonstrated this in several calcite versions using the below (which I built
in the LuceneQueryProcessor project):
{code:java}
public static void main(String[] args) throws Exception {
String sqlQuery =
"SELECT (SELECT count(*) from PART where p_partkey = PARTSUPP.ps_partkey), " +
"(select count(*) from SUPPLIER where s_suppkey = case when s_acctbal > 0
then PARTSUPP.ps_partkey + 1 else 1234 end) " +
"FROM PARTSUPP";
System.out.println("[Input query]");
System.out.println(sqlQuery);
// Create the schema and table data types
CalciteSchema schema = CalciteSchema.createRootSchema(true);
RelDataTypeFactory typeFactory = new JavaTypeFactoryImpl();
for (TpchTable table : TpchTable.values()) {
RelDataTypeFactory.Builder builder = new
RelDataTypeFactory.Builder(typeFactory);
for (TpchTable.Column column : table.columns) {
RelDataType type = typeFactory.createJavaType(column.type);
builder.add(column.name, type.getSqlTypeName()).nullable(true);
}
String indexPath = DatasetIndexer.INDEX_LOCATION + "/tpch/" + table.name();
LuceneTable lt = new LuceneTable(indexPath, builder.build());
schema.add(table.name(), new LuceneTable(indexPath, builder.build()));
}
// Configure and instantiate validator
Properties props = new Properties();
props.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
"false");
CalciteConnectionConfig config = new CalciteConnectionConfigImpl(props);
CalciteCatalogReader catalogReader = new CalciteCatalogReader(schema,
Collections.singletonList("bs"),
typeFactory, config);
SqlValidator validator =
SqlValidatorUtil.newValidator(SqlStdOperatorTable.instance(),
catalogReader, typeFactory,
SqlValidator.Config.DEFAULT);
// Configure and instantiate the converter of the AST to Logical plan (requires
opt cluster)
RelOptCluster cluster = newCluster(typeFactory);
SqlToRelConverter relConverter = new SqlToRelConverter(
NOOP_EXPANDER,
validator,
catalogReader,
cluster,
StandardConvertletTable.INSTANCE,
SqlToRelConverter.config().withDecorrelationEnabled(false));
// Create an SQL parser
SqlParser parser = SqlParser.create(sqlQuery);
// Parse the query into an AST
SqlNode sqlNode = parser.parseQuery();
System.out.println("[Parsed query]");
System.out.println(sqlNode.toString());
// Validate the initial AST
SqlNode validNode = validator.validate(sqlNode);
// Convert the valid AST into a logical plan
RelNode logPlan = relConverter.convertQuery(validNode, false, true).rel;
// Display the logical plan
System.out.println(
RelOptUtil.dumpPlan("[Logical plan]", logPlan, SqlExplainFormat.TEXT,
SqlExplainLevel.NON_COST_ATTRIBUTES));
Program decomp =
Programs.hep(
ImmutableList.of(
CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
CoreRules.JOIN_SUB_QUERY_TO_CORRELATE
),
false,
DefaultRelMetadataProvider.INSTANCE
);
RelNode afterToCorrelate = decomp.run(cluster.getPlanner(), logPlan,
logPlan.getTraitSet(), ImmutableList.of(), ImmutableList.of());
System.out.println(
RelOptUtil.dumpPlan("[Post to Correlate plan]", afterToCorrelate,
SqlExplainFormat.TEXT,
SqlExplainLevel.NON_COST_ATTRIBUTES));
RelNode decorrelated = RelDecorrelator.decorrelateQuery(
afterToCorrelate,
RelFactories.LOGICAL_BUILDER.create(cluster, catalogReader));
System.out.println(
RelOptUtil.dumpPlan("[Decorrelated plan]", decorrelated,
SqlExplainFormat.TEXT,
SqlExplainLevel.NON_COST_ATTRIBUTES));
{code}
Plan from sql->rel conversion:
{code}
[Logical plan]
LogicalProject(variablesSet=[[$cor0]], EXPR$0=[$SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
LogicalFilter(condition=[=($0, $cor0.ps_partkey)])
LogicalTableScan(table=[[PART]])
})], EXPR$1=[$SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
LogicalFilter(condition=[=($0, CASE(>($5, CAST(0):DOUBLE NOT NULL),
+($cor0.ps_partkey, 1), 1234))])
LogicalTableScan(table=[[SUPPLIER]])
})]), id = 17
LogicalTableScan(table=[[PARTSUPP]]), id = 1
{code}
Plan after applying the sub_query_to_correlate rules:
{code}
[Post to Correlate plan]
LogicalProject(EXPR$0=[$5], EXPR$1=[$6]), id = 41
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}]), id = 39
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}]), id = 28
LogicalTableScan(table=[[PARTSUPP]]), id = 1
LogicalAggregate(group=[{}], EXPR$0=[COUNT()]), id = 26
LogicalFilter(condition=[=($0, $cor0.ps_partkey)]), id = 24
LogicalTableScan(table=[[PART]]), id = 3
LogicalAggregate(group=[{}], EXPR$0=[COUNT()]), id = 37
LogicalFilter(condition=[=($0, CASE(>($5, CAST(0):DOUBLE NOT NULL),
+($cor0.ps_partkey, 1), 1234))]), id = 35
LogicalTableScan(table=[[SUPPLIER]]), id = 8
{code}
Whats very strange is that if I use a different CorrelateFactory that produces
Something other than LogicalCorrelate in the RelBuilder used in the
decorrelation, no exception is produced, and decorrelation is performed (though
I have not checked the correctness:
{code:java}
RelNode decorrelated = RelDecorrelator.decorrelateQuery(
afterToCorrelate,
// Special builder with CorrelateFactory override
RelBuilder.proto(
Contexts.of(
new CorrelateFactory() {
// MyCorrelate which is a mirror of LogicalCorrelate
class MyCorrelate extends Correlate{
public MyCorrelate(
RelOptCluster cluster,
RelTraitSet traitSet,
List<RelHint> hints,
RelNode left,
RelNode right,
CorrelationId correlationId,
ImmutableBitSet requiredColumns,
JoinRelType joinType) {
super(
cluster,
traitSet,
hints,
left,
right,
correlationId,
requiredColumns,
joinType);
}
@Override public MyCorrelate copy(
RelTraitSet traitSet,
RelNode left, RelNode right, CorrelationId correlationId,
ImmutableBitSet requiredColumns, JoinRelType joinType
) {
return new MyCorrelate(getCluster(), traitSet, hints, left,
right, correlationId, requiredColumns, joinType);
}
@Override public RelNode accept(RelShuttle shuttle) {
return shuttle.visit(this);
}
}
public RelNode createCorrelate(RelNode left, RelNode right,
List<RelHint> hints,
CorrelationId correlationId, ImmutableBitSet requiredColumns,
JoinRelType joinType) {
// Mirror LogicalCorrelate.create
final RelTraitSet traitSet = cluster.traitSetOf(Convention.NONE);
return new MyCorrelate(cluster, traitSet, hints, left, right,
correlationId,requiredColumns, joinType);
}
}
)
).create(cluster, catalogReader)
);
{code}
Producing this plan:
{code}
[Decorrelated plan]
LogicalProject(EXPR$0=[$5], EXPR$1=[CASE(IS NULL($7), 0:BIGINT, $7)]), id = 182
LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $6)], joinType=[left]), id =
180
LogicalProject(ps_partkey=[$0], ps_suppkey=[$1], ps_availqty=[$2],
ps_supplycost=[$3], ps_comment=[$4], EXPR$0=[CASE(IS NULL($6), 0:BIGINT, $6)]),
id = 139
LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $5)], joinType=[left]),
id = 137
LogicalTableScan(table=[[PARTSUPP]]), id = 1
LogicalProject(p_partkey=[$0], EXPR$0=[CASE(IS NOT NULL($2), $2, 0)]),
id = 135
LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left]), id = 133
LogicalAggregate(group=[{0}]), id = 124
LogicalTableScan(table=[[PARTSUPP]]), id = 1
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()]), id = 131
LogicalProject(p_partkey=[$0]), id = 129
LogicalFilter(condition=[IS NOT NULL($0)]), id = 127
LogicalTableScan(table=[[PART]]), id = 3
LogicalProject(ps_partkey=[$0], EXPR$0=[CASE(IS NOT NULL($2), $2, 0)]), id
= 178
LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)], joinType=[left]),
id = 176
LogicalAggregate(group=[{0}]), id = 160
LogicalProject(ps_partkey=[$0]), id = 158
LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $5)],
joinType=[left]), id = 156
LogicalTableScan(table=[[PARTSUPP]]), id = 1
LogicalProject(p_partkey=[$0], EXPR$0=[CASE(IS NOT NULL($2), $2,
0)]), id = 154
LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left]), id = 152
LogicalAggregate(group=[{0}]), id = 143
LogicalTableScan(table=[[PARTSUPP]]), id = 1
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()]), id = 150
LogicalProject(p_partkey=[$0]), id = 148
LogicalFilter(condition=[IS NOT NULL($0)]), id = 146
LogicalTableScan(table=[[PART]]), id = 3
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()]), id = 174
LogicalProject(ps_partkey=[$7]), id = 172
LogicalJoin(condition=[=($0, CASE(>($5, 0.0E0), +($7, 1), 1234))],
joinType=[inner]), id = 184
LogicalTableScan(table=[[SUPPLIER]]), id = 8
LogicalAggregate(group=[{0}]), id = 166
LogicalProject(ps_partkey=[$0]), id = 164
LogicalTableScan(table=[[PARTSUPP]]), id = 1
{code}
was:
Example SQL (using TCP-H schema):
{code:Sql}
select
(select count(*) from PART where p_partkey = PARTSUPP.ps_partkey),
(select count(*) from SUPPLIER where s_suppkey = case when s_acctbal > 0
then PARTSUPP.ps_partkey + 1 else 1234 end)
from PARTSUPP
{code}
Exception:
{code}
Exception in thread "main" java.lang.NullPointerException
at java.base/java.util.Objects.requireNonNull(Objects.java:209)
at
org.apache.calcite.sql2rel.RelDecorrelator.createValueGenerator(RelDecorrelator.java:1272)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateInputWithValueGenerator(RelDecorrelator.java:1466)
at
org.apache.calcite.sql2rel.RelDecorrelator.maybeAddValueGenerator(RelDecorrelator.java:1385)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1595)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1560)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:614)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:596)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1640)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1615)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1192)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1175)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1192)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1175)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1192)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1175)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:342)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:261)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
at
com.github.zabetak.calcite.tutorial.RecursiveQueryIssueDemo.main(RecursiveQueryIssueDemo.java:170)
{code}
I demonstrated this in several calcite versions using the below (which I built
in the LuceneQueryProcessor project):
{code:java}
public static void main(String[] args) throws Exception {
String sqlQuery =
"SELECT (SELECT count(*) from PART where p_partkey = PARTSUPP.ps_partkey), " +
"(select count(*) from SUPPLIER where s_suppkey = case when s_acctbal > 0
then PARTSUPP.ps_partkey + 1 else 1234 end) " +
"FROM PARTSUPP";
System.out.println("[Input query]");
System.out.println(sqlQuery);
// Create the schema and table data types
CalciteSchema schema = CalciteSchema.createRootSchema(true);
RelDataTypeFactory typeFactory = new JavaTypeFactoryImpl();
for (TpchTable table : TpchTable.values()) {
RelDataTypeFactory.Builder builder = new
RelDataTypeFactory.Builder(typeFactory);
for (TpchTable.Column column : table.columns) {
RelDataType type = typeFactory.createJavaType(column.type);
builder.add(column.name, type.getSqlTypeName()).nullable(true);
}
String indexPath = DatasetIndexer.INDEX_LOCATION + "/tpch/" + table.name();
LuceneTable lt = new LuceneTable(indexPath, builder.build());
schema.add(table.name(), new LuceneTable(indexPath, builder.build()));
}
// Configure and instantiate validator
Properties props = new Properties();
props.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
"false");
CalciteConnectionConfig config = new CalciteConnectionConfigImpl(props);
CalciteCatalogReader catalogReader = new CalciteCatalogReader(schema,
Collections.singletonList("bs"),
typeFactory, config);
SqlValidator validator =
SqlValidatorUtil.newValidator(SqlStdOperatorTable.instance(),
catalogReader, typeFactory,
SqlValidator.Config.DEFAULT);
// Configure and instantiate the converter of the AST to Logical plan (requires
opt cluster)
RelOptCluster cluster = newCluster(typeFactory);
SqlToRelConverter relConverter = new SqlToRelConverter(
NOOP_EXPANDER,
validator,
catalogReader,
cluster,
StandardConvertletTable.INSTANCE,
SqlToRelConverter.config().withDecorrelationEnabled(false));
// Create an SQL parser
SqlParser parser = SqlParser.create(sqlQuery);
// Parse the query into an AST
SqlNode sqlNode = parser.parseQuery();
System.out.println("[Parsed query]");
System.out.println(sqlNode.toString());
// Validate the initial AST
SqlNode validNode = validator.validate(sqlNode);
// Convert the valid AST into a logical plan
RelNode logPlan = relConverter.convertQuery(validNode, false, true).rel;
// Display the logical plan
System.out.println(
RelOptUtil.dumpPlan("[Logical plan]", logPlan, SqlExplainFormat.TEXT,
SqlExplainLevel.NON_COST_ATTRIBUTES));
Program decomp =
Programs.hep(
ImmutableList.of(
CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
CoreRules.JOIN_SUB_QUERY_TO_CORRELATE
),
false,
DefaultRelMetadataProvider.INSTANCE
);
RelNode afterToCorrelate = decomp.run(cluster.getPlanner(), logPlan,
logPlan.getTraitSet(), ImmutableList.of(), ImmutableList.of());
System.out.println(
RelOptUtil.dumpPlan("[Post to Correlate plan]", afterToCorrelate,
SqlExplainFormat.TEXT,
SqlExplainLevel.NON_COST_ATTRIBUTES));
RelNode decorrelated = RelDecorrelator.decorrelateQuery(
afterToCorrelate,
RelFactories.LOGICAL_BUILDER.create(cluster, catalogReader));
System.out.println(
RelOptUtil.dumpPlan("[Decorrelated plan]", decorrelated,
SqlExplainFormat.TEXT,
SqlExplainLevel.NON_COST_ATTRIBUTES));
{code}
Plan from sql->rel conversion:
{code}
[Logical plan]
LogicalProject(variablesSet=[[$cor0]], EXPR$0=[$SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
LogicalFilter(condition=[=($0, $cor0.ps_partkey)])
LogicalTableScan(table=[[PART]])
})], EXPR$1=[$SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
LogicalFilter(condition=[=($0, CASE(>($5, CAST(0):DOUBLE NOT NULL),
+($cor0.ps_partkey, 1), 1234))])
LogicalTableScan(table=[[SUPPLIER]])
})]), id = 17
LogicalTableScan(table=[[PARTSUPP]]), id = 1
{code}
Plan after applying the sub_query_to_correlate rules:
{code}
[Post to Correlate plan]
LogicalProject(EXPR$0=[$5], EXPR$1=[$6]), id = 41
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}]), id = 39
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}]), id = 28
LogicalTableScan(table=[[PARTSUPP]]), id = 1
LogicalAggregate(group=[{}], EXPR$0=[COUNT()]), id = 26
LogicalFilter(condition=[=($0, $cor0.ps_partkey)]), id = 24
LogicalTableScan(table=[[PART]]), id = 3
LogicalAggregate(group=[{}], EXPR$0=[COUNT()]), id = 37
LogicalFilter(condition=[=($0, CASE(>($5, CAST(0):DOUBLE NOT NULL),
+($cor0.ps_partkey, 1), 1234))]), id = 35
LogicalTableScan(table=[[SUPPLIER]]), id = 8
{code}
Whats very strange is that if I use a different CorrelateFactory in the
RelBuilder used in the decorrelation, no exception is produced, and
decorrelation is performed (though I have not checked the correctness:
{code:java}
RelNode decorrelated = RelDecorrelator.decorrelateQuery(
afterToCorrelate,
// Special builder with CorrelateFactory override
RelBuilder.proto(
Contexts.of(
new CorrelateFactory() {
// MyCorrelate which is a mirror of LogicalCorrelate
class MyCorrelate extends Correlate{
public MyCorrelate(
RelOptCluster cluster,
RelTraitSet traitSet,
List<RelHint> hints,
RelNode left,
RelNode right,
CorrelationId correlationId,
ImmutableBitSet requiredColumns,
JoinRelType joinType) {
super(
cluster,
traitSet,
hints,
left,
right,
correlationId,
requiredColumns,
joinType);
}
@Override public MyCorrelate copy(
RelTraitSet traitSet,
RelNode left, RelNode right, CorrelationId correlationId,
ImmutableBitSet requiredColumns, JoinRelType joinType
) {
return new MyCorrelate(getCluster(), traitSet, hints, left,
right, correlationId, requiredColumns, joinType);
}
@Override public RelNode accept(RelShuttle shuttle) {
return shuttle.visit(this);
}
}
public RelNode createCorrelate(RelNode left, RelNode right,
List<RelHint> hints,
CorrelationId correlationId, ImmutableBitSet requiredColumns,
JoinRelType joinType) {
// Mirror LogicalCorrelate.create
final RelTraitSet traitSet = cluster.traitSetOf(Convention.NONE);
return new MyCorrelate(cluster, traitSet, hints, left, right,
correlationId,requiredColumns, joinType);
}
}
)
).create(cluster, catalogReader)
);
{code}
Producing this plan:
{code}
[Decorrelated plan]
LogicalProject(EXPR$0=[$5], EXPR$1=[CASE(IS NULL($7), 0:BIGINT, $7)]), id = 182
LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $6)], joinType=[left]), id =
180
LogicalProject(ps_partkey=[$0], ps_suppkey=[$1], ps_availqty=[$2],
ps_supplycost=[$3], ps_comment=[$4], EXPR$0=[CASE(IS NULL($6), 0:BIGINT, $6)]),
id = 139
LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $5)], joinType=[left]),
id = 137
LogicalTableScan(table=[[PARTSUPP]]), id = 1
LogicalProject(p_partkey=[$0], EXPR$0=[CASE(IS NOT NULL($2), $2, 0)]),
id = 135
LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left]), id = 133
LogicalAggregate(group=[{0}]), id = 124
LogicalTableScan(table=[[PARTSUPP]]), id = 1
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()]), id = 131
LogicalProject(p_partkey=[$0]), id = 129
LogicalFilter(condition=[IS NOT NULL($0)]), id = 127
LogicalTableScan(table=[[PART]]), id = 3
LogicalProject(ps_partkey=[$0], EXPR$0=[CASE(IS NOT NULL($2), $2, 0)]), id
= 178
LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)], joinType=[left]),
id = 176
LogicalAggregate(group=[{0}]), id = 160
LogicalProject(ps_partkey=[$0]), id = 158
LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $5)],
joinType=[left]), id = 156
LogicalTableScan(table=[[PARTSUPP]]), id = 1
LogicalProject(p_partkey=[$0], EXPR$0=[CASE(IS NOT NULL($2), $2,
0)]), id = 154
LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left]), id = 152
LogicalAggregate(group=[{0}]), id = 143
LogicalTableScan(table=[[PARTSUPP]]), id = 1
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()]), id = 150
LogicalProject(p_partkey=[$0]), id = 148
LogicalFilter(condition=[IS NOT NULL($0)]), id = 146
LogicalTableScan(table=[[PART]]), id = 3
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()]), id = 174
LogicalProject(ps_partkey=[$7]), id = 172
LogicalJoin(condition=[=($0, CASE(>($5, 0.0E0), +($7, 1), 1234))],
joinType=[inner]), id = 184
LogicalTableScan(table=[[SUPPLIER]]), id = 8
LogicalAggregate(group=[{0}]), id = 166
LogicalProject(ps_partkey=[$0]), id = 164
LogicalTableScan(table=[[PARTSUPP]]), id = 1
{code}
> NullPointerException from RelDecorrelator with multiple subqueries where one
> has a complex correlated condition
> ---------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7324
> URL: https://issues.apache.org/jira/browse/CALCITE-7324
> Project: Calcite
> Issue Type: Bug
> Reporter: Ian Bertolacci
> Priority: Major
>
> Example SQL (using TCP-H schema):
> {code:Sql}
> select
> (select count(*) from PART where p_partkey = PARTSUPP.ps_partkey),
> (select count(*) from SUPPLIER where s_suppkey = case when s_acctbal > 0
> then PARTSUPP.ps_partkey + 1 else 1234 end)
> from PARTSUPP
> {code}
> Exception:
> {code}
> Exception in thread "main" java.lang.NullPointerException
> at java.base/java.util.Objects.requireNonNull(Objects.java:209)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.createValueGenerator(RelDecorrelator.java:1272)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateInputWithValueGenerator(RelDecorrelator.java:1466)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.maybeAddValueGenerator(RelDecorrelator.java:1385)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1595)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1560)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:569)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:614)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:596)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:569)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1640)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1615)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:569)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1192)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1175)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:569)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1192)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1175)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:569)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1192)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1175)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:569)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:1013)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:342)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:261)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
> at
> com.github.zabetak.calcite.tutorial.RecursiveQueryIssueDemo.main(RecursiveQueryIssueDemo.java:170)
> {code}
> I demonstrated this in several calcite versions using the below (which I
> built in the LuceneQueryProcessor project):
> {code:java}
> public static void main(String[] args) throws Exception {
> String sqlQuery =
> "SELECT (SELECT count(*) from PART where p_partkey = PARTSUPP.ps_partkey),
> " +
> "(select count(*) from SUPPLIER where s_suppkey = case when s_acctbal > 0
> then PARTSUPP.ps_partkey + 1 else 1234 end) " +
> "FROM PARTSUPP";
> System.out.println("[Input query]");
> System.out.println(sqlQuery);
> // Create the schema and table data types
> CalciteSchema schema = CalciteSchema.createRootSchema(true);
> RelDataTypeFactory typeFactory = new JavaTypeFactoryImpl();
> for (TpchTable table : TpchTable.values()) {
> RelDataTypeFactory.Builder builder = new
> RelDataTypeFactory.Builder(typeFactory);
> for (TpchTable.Column column : table.columns) {
> RelDataType type = typeFactory.createJavaType(column.type);
> builder.add(column.name, type.getSqlTypeName()).nullable(true);
> }
> String indexPath = DatasetIndexer.INDEX_LOCATION + "/tpch/" + table.name();
> LuceneTable lt = new LuceneTable(indexPath, builder.build());
> schema.add(table.name(), new LuceneTable(indexPath, builder.build()));
> }
> // Configure and instantiate validator
> Properties props = new Properties();
> props.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> "false");
> CalciteConnectionConfig config = new CalciteConnectionConfigImpl(props);
> CalciteCatalogReader catalogReader = new CalciteCatalogReader(schema,
> Collections.singletonList("bs"),
> typeFactory, config);
> SqlValidator validator =
> SqlValidatorUtil.newValidator(SqlStdOperatorTable.instance(),
> catalogReader, typeFactory,
> SqlValidator.Config.DEFAULT);
> // Configure and instantiate the converter of the AST to Logical plan
> (requires opt cluster)
> RelOptCluster cluster = newCluster(typeFactory);
> SqlToRelConverter relConverter = new SqlToRelConverter(
> NOOP_EXPANDER,
> validator,
> catalogReader,
> cluster,
> StandardConvertletTable.INSTANCE,
> SqlToRelConverter.config().withDecorrelationEnabled(false));
> // Create an SQL parser
> SqlParser parser = SqlParser.create(sqlQuery);
> // Parse the query into an AST
> SqlNode sqlNode = parser.parseQuery();
> System.out.println("[Parsed query]");
> System.out.println(sqlNode.toString());
> // Validate the initial AST
> SqlNode validNode = validator.validate(sqlNode);
> // Convert the valid AST into a logical plan
> RelNode logPlan = relConverter.convertQuery(validNode, false, true).rel;
> // Display the logical plan
> System.out.println(
> RelOptUtil.dumpPlan("[Logical plan]", logPlan, SqlExplainFormat.TEXT,
> SqlExplainLevel.NON_COST_ATTRIBUTES));
> Program decomp =
> Programs.hep(
> ImmutableList.of(
> CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
> CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
> CoreRules.JOIN_SUB_QUERY_TO_CORRELATE
> ),
> false,
> DefaultRelMetadataProvider.INSTANCE
> );
> RelNode afterToCorrelate = decomp.run(cluster.getPlanner(), logPlan,
> logPlan.getTraitSet(), ImmutableList.of(), ImmutableList.of());
> System.out.println(
> RelOptUtil.dumpPlan("[Post to Correlate plan]", afterToCorrelate,
> SqlExplainFormat.TEXT,
> SqlExplainLevel.NON_COST_ATTRIBUTES));
> RelNode decorrelated = RelDecorrelator.decorrelateQuery(
> afterToCorrelate,
> RelFactories.LOGICAL_BUILDER.create(cluster, catalogReader));
> System.out.println(
> RelOptUtil.dumpPlan("[Decorrelated plan]", decorrelated,
> SqlExplainFormat.TEXT,
> SqlExplainLevel.NON_COST_ATTRIBUTES));
> {code}
> Plan from sql->rel conversion:
> {code}
> [Logical plan]
> LogicalProject(variablesSet=[[$cor0]], EXPR$0=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
> LogicalFilter(condition=[=($0, $cor0.ps_partkey)])
> LogicalTableScan(table=[[PART]])
> })], EXPR$1=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
> LogicalFilter(condition=[=($0, CASE(>($5, CAST(0):DOUBLE NOT NULL),
> +($cor0.ps_partkey, 1), 1234))])
> LogicalTableScan(table=[[SUPPLIER]])
> })]), id = 17
> LogicalTableScan(table=[[PARTSUPP]]), id = 1
> {code}
> Plan after applying the sub_query_to_correlate rules:
> {code}
> [Post to Correlate plan]
> LogicalProject(EXPR$0=[$5], EXPR$1=[$6]), id = 41
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{0}]), id = 39
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{0}]), id = 28
> LogicalTableScan(table=[[PARTSUPP]]), id = 1
> LogicalAggregate(group=[{}], EXPR$0=[COUNT()]), id = 26
> LogicalFilter(condition=[=($0, $cor0.ps_partkey)]), id = 24
> LogicalTableScan(table=[[PART]]), id = 3
> LogicalAggregate(group=[{}], EXPR$0=[COUNT()]), id = 37
> LogicalFilter(condition=[=($0, CASE(>($5, CAST(0):DOUBLE NOT NULL),
> +($cor0.ps_partkey, 1), 1234))]), id = 35
> LogicalTableScan(table=[[SUPPLIER]]), id = 8
> {code}
> Whats very strange is that if I use a different CorrelateFactory that
> produces Something other than LogicalCorrelate in the RelBuilder used in the
> decorrelation, no exception is produced, and decorrelation is performed
> (though I have not checked the correctness:
> {code:java}
> RelNode decorrelated = RelDecorrelator.decorrelateQuery(
> afterToCorrelate,
> // Special builder with CorrelateFactory override
> RelBuilder.proto(
> Contexts.of(
> new CorrelateFactory() {
> // MyCorrelate which is a mirror of LogicalCorrelate
> class MyCorrelate extends Correlate{
> public MyCorrelate(
> RelOptCluster cluster,
> RelTraitSet traitSet,
> List<RelHint> hints,
> RelNode left,
> RelNode right,
> CorrelationId correlationId,
> ImmutableBitSet requiredColumns,
> JoinRelType joinType) {
> super(
> cluster,
> traitSet,
> hints,
> left,
> right,
> correlationId,
> requiredColumns,
> joinType);
> }
> @Override public MyCorrelate copy(
> RelTraitSet traitSet,
> RelNode left, RelNode right, CorrelationId correlationId,
> ImmutableBitSet requiredColumns, JoinRelType joinType
> ) {
> return new MyCorrelate(getCluster(), traitSet, hints, left,
> right, correlationId, requiredColumns, joinType);
> }
> @Override public RelNode accept(RelShuttle shuttle) {
> return shuttle.visit(this);
> }
> }
> public RelNode createCorrelate(RelNode left, RelNode right,
> List<RelHint> hints,
> CorrelationId correlationId, ImmutableBitSet requiredColumns,
> JoinRelType joinType) {
> // Mirror LogicalCorrelate.create
> final RelTraitSet traitSet =
> cluster.traitSetOf(Convention.NONE);
> return new MyCorrelate(cluster, traitSet, hints, left, right,
> correlationId,requiredColumns, joinType);
> }
> }
> )
> ).create(cluster, catalogReader)
> );
> {code}
> Producing this plan:
> {code}
> [Decorrelated plan]
> LogicalProject(EXPR$0=[$5], EXPR$1=[CASE(IS NULL($7), 0:BIGINT, $7)]), id =
> 182
> LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $6)], joinType=[left]), id
> = 180
> LogicalProject(ps_partkey=[$0], ps_suppkey=[$1], ps_availqty=[$2],
> ps_supplycost=[$3], ps_comment=[$4], EXPR$0=[CASE(IS NULL($6), 0:BIGINT,
> $6)]), id = 139
> LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $5)], joinType=[left]),
> id = 137
> LogicalTableScan(table=[[PARTSUPP]]), id = 1
> LogicalProject(p_partkey=[$0], EXPR$0=[CASE(IS NOT NULL($2), $2,
> 0)]), id = 135
> LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
> joinType=[left]), id = 133
> LogicalAggregate(group=[{0}]), id = 124
> LogicalTableScan(table=[[PARTSUPP]]), id = 1
> LogicalAggregate(group=[{0}], EXPR$0=[COUNT()]), id = 131
> LogicalProject(p_partkey=[$0]), id = 129
> LogicalFilter(condition=[IS NOT NULL($0)]), id = 127
> LogicalTableScan(table=[[PART]]), id = 3
> LogicalProject(ps_partkey=[$0], EXPR$0=[CASE(IS NOT NULL($2), $2, 0)]),
> id = 178
> LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)], joinType=[left]),
> id = 176
> LogicalAggregate(group=[{0}]), id = 160
> LogicalProject(ps_partkey=[$0]), id = 158
> LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $5)],
> joinType=[left]), id = 156
> LogicalTableScan(table=[[PARTSUPP]]), id = 1
> LogicalProject(p_partkey=[$0], EXPR$0=[CASE(IS NOT NULL($2),
> $2, 0)]), id = 154
> LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
> joinType=[left]), id = 152
> LogicalAggregate(group=[{0}]), id = 143
> LogicalTableScan(table=[[PARTSUPP]]), id = 1
> LogicalAggregate(group=[{0}], EXPR$0=[COUNT()]), id = 150
> LogicalProject(p_partkey=[$0]), id = 148
> LogicalFilter(condition=[IS NOT NULL($0)]), id = 146
> LogicalTableScan(table=[[PART]]), id = 3
> LogicalAggregate(group=[{0}], EXPR$0=[COUNT()]), id = 174
> LogicalProject(ps_partkey=[$7]), id = 172
> LogicalJoin(condition=[=($0, CASE(>($5, 0.0E0), +($7, 1),
> 1234))], joinType=[inner]), id = 184
> LogicalTableScan(table=[[SUPPLIER]]), id = 8
> LogicalAggregate(group=[{0}]), id = 166
> LogicalProject(ps_partkey=[$0]), id = 164
> LogicalTableScan(table=[[PARTSUPP]]), id = 1
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
