You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "fugang (Jira)" <ji...@apache.org> on 2024/05/14 09:26:00 UTC
[jira] [Commented] (CALCITE-5516) RelOptRulesTest AggregateReduceFunctionsRule optimized plan not semantically equivalent to the original one after conversion
[ https://issues.apache.org/jira/browse/CALCITE-5516?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17846244#comment-17846244 ]
fugang commented on CALCITE-5516:
---------------------------------
For the first example, the main issue lies in the function {{{}COALESCE(SUM(SAL), 0){}}}. By converting it to
sql复制代码
| |CAST(CASE WHEN COUNT(*) = 0 THEN NULL ELSE SUM(SAL) END / COUNT(*) AS INTEGER)|
we can avoid such inconsistencies, and this problem is not only present in the {{{}PostgresqlSqlDialect{}}}, but also in the {{{}MysqlSqlDialect{}}}.
By modifying the {{reduceSum}} method and the {{matches}} method in the {{{}AggregateReduceFunctionsRule{}}}, it is possible to correct this issue. However, I am not sure if eliminating the conversion of {{SUM}} to {{SUM0}} would have any impact on other dialects.
Although Postgresql itself does not support syntax like {{{}t WITHIN DISTINCT{}}}, Calcite itself is not tied to a specific SQL version or standard, and I have not found any distinction based on dialects in the validator module, which I believe is reasonable.
> RelOptRulesTest AggregateReduceFunctionsRule optimized plan not semantically equivalent to the original one after conversion
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-5516
> URL: https://issues.apache.org/jira/browse/CALCITE-5516
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.32.0
> Reporter: Pinhan Zhao
> Priority: Blocker
>
> After converting the "plan before" and "plan after" of testReduceWithNonTypePredicate (https://github.com/apache/calcite/blob/fb340ece8c5e60ecfd6371950f8dcb665c85a712/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java#L6820) to PostgreSQL queries, we obtained two queries:
> {code:sql}
> SELECT AVG(SAL), AVG(SAL) FROM EMP{code}
> and
> {code:sql}
> SELECT AVG(SAL), CAST(CASE WHEN COUNT(*) = 0 THEN NULL ELSE COALESCE(SUM(SAL), 0) END / COUNT(*) AS INTEGER) FROM EMP{code}
>
> The code we used to convert the plans to PostgreSQL queries is:
> {code:java}
> converter.visitRoot(rel).asStatement().toSqlString(PostgresqlSqlDialect.DEFAULT).getSql().replace('\n', ''){code}
>
> However, these queries are not semantically equivalent. With a counterexample below:
> {code:sql}
> --------DEPT----------
> CREATE TABLE DEPT (
> DEPTNO INTEGER PRIMARY KEY,
> NAME VARCHAR(20)
> );
> INSERT INTO DEPT VALUES (0,'0');
> INSERT INTO DEPT VALUES (1,'0');
> --------EMP----------
> CREATE TABLE EMP (
> EMPNO INTEGER PRIMARY KEY,
> DEPTNO INTEGER REFERENCES DEPT(DEPTNO),
> ENAME VARCHAR(20),
> JOB VARCHAR(20),
> MGR INTEGER,
> HIREDATE DATE,
> SAL INTEGER,
> COMM INTEGER,
> SLACKER BOOLEAN
> );
> INSERT INTO EMP VALUES (0,0,'1','0',0,'2000-01-01',NULL,0,True);
> INSERT INTO EMP VALUES (-1,1,'0','0',0,'2000-01-01',NULL,0,True);{code}
>
> we can have two queries produce different output:
> {code:java}
> avg | avg
> -----+-----
> |
> (1 row) {code}
> (output of the plan before optimization, with converted to PostgreSQL query)
>
> {code:java}
> avg | int4
> -----+------
> | 0
> (1 row)
> {code}
> (output of the plan after optimization, with converted to PostgreSQL query)
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)