[
https://issues.apache.org/jira/browse/CALCITE-5465?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xurenhe updated CALCITE-5465:
-----------------------------
Description:
The rule _*AGGREGATE_EXPAND_DISTINCT_AGGREGATES*_ transform wrong plan, when
the sql is rollup aggregate(no simple aggregate) and calculates distinct
agg-call.
For example:
{code:java}
// table schema and datas
CREATE TABLE t1 (id VARCHAR(10), c1 VARCHAR(10));
INSERT INTO t1 VALUES ('1', 'A1');
INSERT INTO t1 VALUES ('2', 'A2');
INSERT INTO t1 VALUES ('3', 'A3');
INSERT INTO t1 VALUES ('3', 'A3');
INSERT INTO t1 VALUES ('3', 'A2');
INSERT INTO t1 VALUES (null, 'A4');{code}
{code:java}
// sql
select id, count(distinct c1) from t1 group by rollup(id) {code}
Before executing rule(plan1):
{code:java}
LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT(DISTINCT $1)])
LogicalTableScan(table=[[default, t1]]) {code}
After executing rule(plan2):
{code:java}
LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT($1) FILTER $2])
LogicalProject(id=[$0], c1=[$1], $g_0=[=($2, 0)])
LogicalAggregate(group=[{0, 1}], $g=[GROUPING($0, $1)])
LogicalTableScan(table=[[default, t1]]) {code}
In the {_}*plan2*{_}, value of $g_0 is always true, so plan2 is equal to
{code:java}
LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT($1)])
LogicalProject(id=[$0], c1=[$1])
LogicalAggregate(group=[{0, 1}]])
LogicalTableScan(table=[[default, t1]]) {code}
Equivalent sql:
{code:java}
SELECT `id`, COUNT(`c1`)
FROM (SELECT `id`, `c1`
FROM `t1`
GROUP BY `id`, `c1`) AS `t0`
GROUP BY ROLLUP(`id`) {code}
And result by executing the rule of _*AGGREGATE_EXPAND_DISTINCT_AGGREGATES*_
will return
{code:java}
+------+-------------+
| id | |
+------+-------------+
| NULL | 1 | // group by id, which id's value is null
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| NULL | 5 | // empty grouping
+------+-------------+ {code}
But, MySQL, MS SQL Server, Oracle will return result:
{code:java}
+------+--------------------+
| id | |
+------+--------------------+
| NULL | 1 | // group by id, which id's value is null
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| NULL | 4 | // empty grouping
+------+--------------------+{code}
was:
The rule _*AGGREGATE_EXPAND_DISTINCT_AGGREGATES*_ transform wrong plan, when
the sql is rollup aggregate(no simple aggregate) and calculates distinct
agg-call.
For example:
{code:java}
// table schema and datas
CREATE TABLE t1 (id VARCHAR(10), c1 VARCHAR(10));INSERT INTO t1 VALUES ('1',
'A1');
INSERT INTO t1 VALUES ('2', 'A2');
INSERT INTO t1 VALUES ('3', 'A3');
INSERT INTO t1 VALUES ('3', 'A3');
INSERT INTO t1 VALUES ('3', 'A2');
INSERT INTO t1 VALUES (null, 'A4');{code}
{code:java}
// sql
select id, count(distinct c1) from t1 group by rollup(id) {code}
Before executing rule(plan1):
{code:java}
LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT(DISTINCT $1)])
LogicalTableScan(table=[[default, t1]]) {code}
After executing rule(plan2):
{code:java}
LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT($1) FILTER $2])
LogicalProject(id=[$0], c1=[$1], $g_0=[=($2, 0)])
LogicalAggregate(group=[{0, 1}], $g=[GROUPING($0, $1)])
LogicalTableScan(table=[[default, t1]]) {code}
In the {_}*plan2*{_}, value of $g_0 is always true, so plan2 is equal to
{code:java}
LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT($1)])
LogicalProject(id=[$0], c1=[$1])
LogicalAggregate(group=[{0, 1}]])
LogicalTableScan(table=[[default, t1]]) {code}
Equivalent sql:
{code:java}
SELECT `id`, COUNT(`c1`)
FROM (SELECT `id`, `c1`
FROM `t1`
GROUP BY `id`, `c1`) AS `t0`
GROUP BY ROLLUP(`id`) {code}
And result by executing the rule of _*AGGREGATE_EXPAND_DISTINCT_AGGREGATES*_
will return
{code:java}
+------+-------------+
| id | |
+------+-------------+
| NULL | 1 | // group by id, which id's value is null
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| NULL | 5 | // empty grouping
+------+-------------+ {code}
But, MySQL, MS SQL Server, Oracle will return result:
{code:java}
+------+--------------------+
| id | |
+------+--------------------+
| NULL | 1 | // group by id, which id's value is null
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| NULL | 4 | // empty grouping
+------+--------------------+{code}
> Rule of AGGREGATE_EXPAND_DISTINCT_AGGREGATES transforms wrong plan, when sql
> has distinct agg-call with rollup
> ---------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-5465
> URL: https://issues.apache.org/jira/browse/CALCITE-5465
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Xurenhe
> Priority: Major
>
> The rule _*AGGREGATE_EXPAND_DISTINCT_AGGREGATES*_ transform wrong plan, when
> the sql is rollup aggregate(no simple aggregate) and calculates distinct
> agg-call.
> For example:
> {code:java}
> // table schema and datas
> CREATE TABLE t1 (id VARCHAR(10), c1 VARCHAR(10));
> INSERT INTO t1 VALUES ('1', 'A1');
> INSERT INTO t1 VALUES ('2', 'A2');
> INSERT INTO t1 VALUES ('3', 'A3');
> INSERT INTO t1 VALUES ('3', 'A3');
> INSERT INTO t1 VALUES ('3', 'A2');
> INSERT INTO t1 VALUES (null, 'A4');{code}
> {code:java}
> // sql
> select id, count(distinct c1) from t1 group by rollup(id) {code}
>
> Before executing rule(plan1):
> {code:java}
> LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT(DISTINCT $1)])
> LogicalTableScan(table=[[default, t1]]) {code}
>
> After executing rule(plan2):
> {code:java}
> LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT($1) FILTER
> $2])
> LogicalProject(id=[$0], c1=[$1], $g_0=[=($2, 0)])
> LogicalAggregate(group=[{0, 1}], $g=[GROUPING($0, $1)])
> LogicalTableScan(table=[[default, t1]]) {code}
>
> In the {_}*plan2*{_}, value of $g_0 is always true, so plan2 is equal to
> {code:java}
> LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT($1)])
> LogicalProject(id=[$0], c1=[$1])
> LogicalAggregate(group=[{0, 1}]])
> LogicalTableScan(table=[[default, t1]]) {code}
>
> Equivalent sql:
> {code:java}
> SELECT `id`, COUNT(`c1`)
> FROM (SELECT `id`, `c1`
> FROM `t1`
> GROUP BY `id`, `c1`) AS `t0`
> GROUP BY ROLLUP(`id`) {code}
>
> And result by executing the rule of _*AGGREGATE_EXPAND_DISTINCT_AGGREGATES*_
> will return
> {code:java}
> +------+-------------+
> | id | |
> +------+-------------+
> | NULL | 1 | // group by id, which id's value is null
> | 1 | 1 |
> | 2 | 1 |
> | 3 | 2 |
> | NULL | 5 | // empty grouping
> +------+-------------+ {code}
> But, MySQL, MS SQL Server, Oracle will return result:
> {code:java}
> +------+--------------------+
> | id | |
> +------+--------------------+
> | NULL | 1 | // group by id, which id's value is null
> | 1 | 1 |
> | 2 | 1 |
> | 3 | 2 |
> | NULL | 4 | // empty grouping
> +------+--------------------+{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
