[
https://issues.apache.org/jira/browse/CALCITE-5594?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
asdfgh19 updated CALCITE-5594:
------------------------------
Description:
I add a test case to LatticeTest and it fails as shown below.
If I set enableMaterializations to false, the result returns 1997 12 86837,
whereas if I set enableMaterializations to true, the result returns 1997 12 12.
{code:java}
@Test void testLatticeWithNoMeasures3() {
foodmartModel(" auto: false,\n"
+ " tiles: [ {\n"
+ " dimensions: [ 'the_year', ['t', 'month_of_year'] ],\n"
+ " measures: [ ]\n"
+ " } ]\n")
.query("select \"the_year\", max(\"month_of_year\"),
count(\"month_of_year\")\n"
+ "from \"foodmart\".\"sales_fact_1997\" as s\n"
+ "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n"
+ "group by \"the_year\"")
.enableMaterializations(false)
.sameResultWithMaterializationsDisabled();
}{code}
The reason for this may be in AggregateStarTableRule#rollUp.
Maybe we can change it to something like this.
{code:java}
private static @Nullable AggregateCall rollUp(int groupCount, RelBuilder
relBuilder,
AggregateCall aggregateCall, TileKey tileKey) {
if (aggregateCall.isDistinct()) {
return null;
}
final SqlAggFunction aggregation = aggregateCall.getAggregation();
final Pair<SqlAggFunction, List<Integer>> seek =
Pair.of(aggregation, aggregateCall.getArgList());
final int offset = tileKey.dimensions.cardinality();
final ImmutableList<Lattice.Measure> measures = tileKey.measures;
// First, try to satisfy the aggregation by rolling up an aggregate in the
// materialization.
final int i = find(measures, seek);
tryRoll:
if (i >= 0) {
final SqlAggFunction roll = aggregation.getRollup();
if (roll == null) {
break tryRoll;
}
return AggregateCall.create(roll, false, aggregateCall.isApproximate(),
aggregateCall.ignoreNulls(), ImmutableList.of(offset + i), -1,
aggregateCall.distinctKeys, aggregateCall.collation,
groupCount, relBuilder.peek(), null, aggregateCall.name);
}
SqlKind aggregationKind = aggregation.getKind();
if (offset > 0 && aggregationKind != SqlKind.MIN && aggregationKind !=
SqlKind.MAX
&& aggregationKind != SqlKind.ANY_VALUE && aggregationKind !=
SqlKind.SINGLE_VALUE) {
return null;
}
// Second, try to satisfy the aggregation based on group set columns.
tryGroup:
{
List<Integer> newArgs = new ArrayList<>();
for (Integer arg : aggregateCall.getArgList()) {
int z = tileKey.dimensions.indexOf(arg);
if (z < 0) {
break tryGroup;
}
newArgs.add(z);
}
return AggregateCall.create(aggregation, false,
aggregateCall.isApproximate(), aggregateCall.ignoreNulls(),
newArgs, -1, aggregateCall.distinctKeys, aggregateCall.collation,
groupCount, relBuilder.peek(), null, aggregateCall.name);
}
// No roll up possible.
return null;
}{code}
Or we can remove the tryGroup entirely.
{code:java}
private static @Nullable AggregateCall rollUp(int groupCount, RelBuilder
relBuilder,
AggregateCall aggregateCall, TileKey tileKey) {
if (aggregateCall.isDistinct()) {
return null;
}
final SqlAggFunction aggregation = aggregateCall.getAggregation();
final Pair<SqlAggFunction, List<Integer>> seek =
Pair.of(aggregation, aggregateCall.getArgList());
final int offset = tileKey.dimensions.cardinality();
final ImmutableList<Lattice.Measure> measures = tileKey.measures;
// First, try to satisfy the aggregation by rolling up an aggregate in the
// materialization.
final int i = find(measures, seek);
tryRoll:
if (i >= 0) {
final SqlAggFunction roll = aggregation.getRollup();
if (roll == null) {
break tryRoll;
}
return AggregateCall.create(roll, false, aggregateCall.isApproximate(),
aggregateCall.ignoreNulls(), ImmutableList.of(offset + i), -1,
aggregateCall.distinctKeys, aggregateCall.collation,
groupCount, relBuilder.peek(), null, aggregateCall.name);
}
// No roll up possible.
return null;
} {code}
I guess the reason might be this. When grouping by a certain dimension, the
same records will be merged into one, so aggregate functions like sum and count
act on this dimension, and the results are inconsistent with those before
grouping
was:
I add a test case to LatticeTest and it fails as shown below.
If I set enableMaterializations to false, the result returns 1997 12 86837,
whereas if I set enableMaterializations to true, the result returns 1997 12 12.
{code:java}
@Test void testLatticeWithNoMeasures3() {
foodmartModel(" auto: false,\n"
+ " tiles: [ {\n"
+ " dimensions: [ 'the_year', ['t', 'month_of_year'] ],\n"
+ " measures: [ ]\n"
+ " } ]\n")
.query("select \"the_year\", max(\"month_of_year\"),
count(\"month_of_year\")\n"
+ "from \"foodmart\".\"sales_fact_1997\" as s\n"
+ "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n"
+ "group by \"the_year\"")
.enableMaterializations(false)
.sameResultWithMaterializationsDisabled();
}{code}
The reason for this may be in AggregateStarTableRule#rollUp.
Maybe we can change it to something like this.
{code:java}
private static @Nullable AggregateCall rollUp(int groupCount, RelBuilder
relBuilder,
AggregateCall aggregateCall, TileKey tileKey) {
if (aggregateCall.isDistinct()) {
return null;
}
final SqlAggFunction aggregation = aggregateCall.getAggregation();
final Pair<SqlAggFunction, List<Integer>> seek =
Pair.of(aggregation, aggregateCall.getArgList());
final int offset = tileKey.dimensions.cardinality();
final ImmutableList<Lattice.Measure> measures = tileKey.measures;
// First, try to satisfy the aggregation by rolling up an aggregate in the
// materialization.
final int i = find(measures, seek);
tryRoll:
if (i >= 0) {
final SqlAggFunction roll = aggregation.getRollup();
if (roll == null) {
break tryRoll;
}
return AggregateCall.create(roll, false, aggregateCall.isApproximate(),
aggregateCall.ignoreNulls(), ImmutableList.of(offset + i), -1,
aggregateCall.distinctKeys, aggregateCall.collation,
groupCount, relBuilder.peek(), null, aggregateCall.name);
}
SqlKind aggregationKind = aggregation.getKind();
if (offset > 0 && aggregationKind != SqlKind.MIN && aggregationKind !=
SqlKind.MAX
&& aggregationKind != SqlKind.ANY_VALUE && aggregationKind !=
SqlKind.SINGLE_VALUE) {
return null;
}
// Second, try to satisfy the aggregation based on group set columns.
tryGroup:
{
List<Integer> newArgs = new ArrayList<>();
for (Integer arg : aggregateCall.getArgList()) {
int z = tileKey.dimensions.indexOf(arg);
if (z < 0) {
break tryGroup;
}
newArgs.add(z);
}
return AggregateCall.create(aggregation, false,
aggregateCall.isApproximate(), aggregateCall.ignoreNulls(),
newArgs, -1, aggregateCall.distinctKeys, aggregateCall.collation,
groupCount, relBuilder.peek(), null, aggregateCall.name);
}
// No roll up possible.
return null;
}{code}
Or we can remove the tryGroup entirely.
{code:java}
private static @Nullable AggregateCall rollUp(int groupCount, RelBuilder
relBuilder,
AggregateCall aggregateCall, TileKey tileKey) {
if (aggregateCall.isDistinct()) {
return null;
}
final SqlAggFunction aggregation = aggregateCall.getAggregation();
final Pair<SqlAggFunction, List<Integer>> seek =
Pair.of(aggregation, aggregateCall.getArgList());
final int offset = tileKey.dimensions.cardinality();
final ImmutableList<Lattice.Measure> measures = tileKey.measures;
// First, try to satisfy the aggregation by rolling up an aggregate in the
// materialization.
final int i = find(measures, seek);
tryRoll:
if (i >= 0) {
final SqlAggFunction roll = aggregation.getRollup();
if (roll == null) {
break tryRoll;
}
return AggregateCall.create(roll, false, aggregateCall.isApproximate(),
aggregateCall.ignoreNulls(), ImmutableList.of(offset + i), -1,
aggregateCall.distinctKeys, aggregateCall.collation,
groupCount, relBuilder.peek(), null, aggregateCall.name);
}
// No roll up possible.
return null;
} {code}
> AggregateStarTableRule optimized plan does not get the same result to the
> original one
> --------------------------------------------------------------------------------------
>
> Key: CALCITE-5594
> URL: https://issues.apache.org/jira/browse/CALCITE-5594
> Project: Calcite
> Issue Type: Test
> Components: core
> Affects Versions: 1.34.0
> Reporter: asdfgh19
> Priority: Minor
>
> I add a test case to LatticeTest and it fails as shown below.
> If I set enableMaterializations to false, the result returns 1997 12 86837,
> whereas if I set enableMaterializations to true, the result returns 1997 12
> 12.
> {code:java}
> @Test void testLatticeWithNoMeasures3() {
> foodmartModel(" auto: false,\n"
> + " tiles: [ {\n"
> + " dimensions: [ 'the_year', ['t', 'month_of_year'] ],\n"
> + " measures: [ ]\n"
> + " } ]\n")
> .query("select \"the_year\", max(\"month_of_year\"),
> count(\"month_of_year\")\n"
> + "from \"foodmart\".\"sales_fact_1997\" as s\n"
> + "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n"
> + "group by \"the_year\"")
> .enableMaterializations(false)
> .sameResultWithMaterializationsDisabled();
> }{code}
> The reason for this may be in AggregateStarTableRule#rollUp.
> Maybe we can change it to something like this.
> {code:java}
> private static @Nullable AggregateCall rollUp(int groupCount, RelBuilder
> relBuilder,
> AggregateCall aggregateCall, TileKey tileKey) {
> if (aggregateCall.isDistinct()) {
> return null;
> }
> final SqlAggFunction aggregation = aggregateCall.getAggregation();
> final Pair<SqlAggFunction, List<Integer>> seek =
> Pair.of(aggregation, aggregateCall.getArgList());
> final int offset = tileKey.dimensions.cardinality();
> final ImmutableList<Lattice.Measure> measures = tileKey.measures;
> // First, try to satisfy the aggregation by rolling up an aggregate in the
> // materialization.
> final int i = find(measures, seek);
> tryRoll:
> if (i >= 0) {
> final SqlAggFunction roll = aggregation.getRollup();
> if (roll == null) {
> break tryRoll;
> }
> return AggregateCall.create(roll, false, aggregateCall.isApproximate(),
> aggregateCall.ignoreNulls(), ImmutableList.of(offset + i), -1,
> aggregateCall.distinctKeys, aggregateCall.collation,
> groupCount, relBuilder.peek(), null, aggregateCall.name);
> }
> SqlKind aggregationKind = aggregation.getKind();
> if (offset > 0 && aggregationKind != SqlKind.MIN && aggregationKind !=
> SqlKind.MAX
> && aggregationKind != SqlKind.ANY_VALUE && aggregationKind !=
> SqlKind.SINGLE_VALUE) {
> return null;
> }
> // Second, try to satisfy the aggregation based on group set columns.
> tryGroup:
> {
> List<Integer> newArgs = new ArrayList<>();
> for (Integer arg : aggregateCall.getArgList()) {
> int z = tileKey.dimensions.indexOf(arg);
> if (z < 0) {
> break tryGroup;
> }
> newArgs.add(z);
> }
> return AggregateCall.create(aggregation, false,
> aggregateCall.isApproximate(), aggregateCall.ignoreNulls(),
> newArgs, -1, aggregateCall.distinctKeys, aggregateCall.collation,
> groupCount, relBuilder.peek(), null, aggregateCall.name);
> }
> // No roll up possible.
> return null;
> }{code}
> Or we can remove the tryGroup entirely.
> {code:java}
> private static @Nullable AggregateCall rollUp(int groupCount, RelBuilder
> relBuilder,
> AggregateCall aggregateCall, TileKey tileKey) {
> if (aggregateCall.isDistinct()) {
> return null;
> }
> final SqlAggFunction aggregation = aggregateCall.getAggregation();
> final Pair<SqlAggFunction, List<Integer>> seek =
> Pair.of(aggregation, aggregateCall.getArgList());
> final int offset = tileKey.dimensions.cardinality();
> final ImmutableList<Lattice.Measure> measures = tileKey.measures;
> // First, try to satisfy the aggregation by rolling up an aggregate in the
> // materialization.
> final int i = find(measures, seek);
> tryRoll:
> if (i >= 0) {
> final SqlAggFunction roll = aggregation.getRollup();
> if (roll == null) {
> break tryRoll;
> }
> return AggregateCall.create(roll, false, aggregateCall.isApproximate(),
> aggregateCall.ignoreNulls(), ImmutableList.of(offset + i), -1,
> aggregateCall.distinctKeys, aggregateCall.collation,
> groupCount, relBuilder.peek(), null, aggregateCall.name);
> }
> // No roll up possible.
> return null;
> } {code}
> I guess the reason might be this. When grouping by a certain dimension, the
> same records will be merged into one, so aggregate functions like sum and
> count act on this dimension, and the results are inconsistent with those
> before grouping
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
