[jira] [Updated] (CALCITE-3764) AggregateCaseToFilterRule handles NULL values correctly
[ https://issues.apache.org/jira/browse/CALCITE-3764?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-3764: Labels: pull-request-available (was: ) > AggregateCaseToFilterRule handles NULL values correctly > --- > > Key: CALCITE-3764 > URL: https://issues.apache.org/jira/browse/CALCITE-3764 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Assignee: Rui Wang >Priority: Major > Labels: pull-request-available > > {{AggregateCaseToFilterRule}} handles NULL values correctly. It converts > {code:sql} > SELECT COUNT(CASE WHEN b THEN NULL ELSE 1 END) FROM t > {code} > to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS FALSE) FROM t > {code} > which fails to count rows where {{b}} is UNKNOWN, so it should convert to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS NOT TRUE) FROM t > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3729) Filters failed to be pushed down when it's identical to join condition.
[ https://issues.apache.org/jira/browse/CALCITE-3729?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17028284#comment-17028284 ] Jin Xing commented on CALCITE-3729: --- Thanks a lot for merging [~vladimirsitnikov] ! > Filters failed to be pushed down when it's identical to join condition. > --- > > Key: CALCITE-3729 > URL: https://issues.apache.org/jira/browse/CALCITE-3729 > Project: Calcite > Issue Type: Improvement >Reporter: Jin Xing >Assignee: Jin Xing >Priority: Major > Labels: pull-request-available > Fix For: 1.22.0 > > Time Spent: 1h 10m > Remaining Estimate: 0h > > FilterJoinRule optimize below sql as > {code:java} > select * from sales.dept d inner join sales.emp e > on d.deptno = e.deptno and d.deptno > e.mgr > where d.deptno > e.mgr > LogicalProject(DEPTNO=[$0], NAME=[$1], EMPNO=[$2], ENAME=[$3], JOB=[$4], > MGR=[$5], HIREDATE=[$6], SAL=[$7], COMM=[$8], DEPTNO0=[$9], SLACKER=[$10]) > LogicalFilter(condition=[>($0, $5)]) > LogicalJoin(condition=[AND(=($0, $9), >($0, $5))], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > {code} > > The outer Filter failed to be pushed down into Join and removed. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3764) AggregateCaseToFilterRule handles NULL values correctly
[ https://issues.apache.org/jira/browse/CALCITE-3764?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17028278#comment-17028278 ] Rui Wang commented on CALCITE-3764: --- Ah. I did more Calcite tests locally and now I get it: Calcite does not convert FILTER for THEN ELSE unless it is a NULL value after THEN, which actually makes sense. Now I agreed what the proposed solution (e.g. convert to IS NOT TRUE) and it should work for other aggregation functions. > AggregateCaseToFilterRule handles NULL values correctly > --- > > Key: CALCITE-3764 > URL: https://issues.apache.org/jira/browse/CALCITE-3764 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Assignee: Rui Wang >Priority: Major > > {{AggregateCaseToFilterRule}} handles NULL values correctly. It converts > {code:sql} > SELECT COUNT(CASE WHEN b THEN NULL ELSE 1 END) FROM t > {code} > to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS FALSE) FROM t > {code} > which fails to count rows where {{b}} is UNKNOWN, so it should convert to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS NOT TRUE) FROM t > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Assigned] (CALCITE-3764) AggregateCaseToFilterRule handles NULL values correctly
[ https://issues.apache.org/jira/browse/CALCITE-3764?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rui Wang reassigned CALCITE-3764: - Assignee: Rui Wang > AggregateCaseToFilterRule handles NULL values correctly > --- > > Key: CALCITE-3764 > URL: https://issues.apache.org/jira/browse/CALCITE-3764 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Assignee: Rui Wang >Priority: Major > > {{AggregateCaseToFilterRule}} handles NULL values correctly. It converts > {code:sql} > SELECT COUNT(CASE WHEN b THEN NULL ELSE 1 END) FROM t > {code} > to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS FALSE) FROM t > {code} > which fails to count rows where {{b}} is UNKNOWN, so it should convert to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS NOT TRUE) FROM t > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3764) AggregateCaseToFilterRule handles NULL values correctly
[ https://issues.apache.org/jira/browse/CALCITE-3764?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17028277#comment-17028277 ] Rui Wang commented on CALCITE-3764: --- However, I am not sure if the proposed solution is correct. Converting to "WHERE b IS NOT TRUE" is correct only for the special case "THEN NULL ELSE 1 END". Think about a general case: {code:sql} SELECT AGG_FUNCTION(CASE WHEN b THEN result_1 ELSE result_2 END) FROM t {code} basically for an arbitrary aggregation function, FILTER clause shouldn't be used and CASE should be preserved. Because we cannot always ignore result_1. For example, {code:sql} SELECT SUM(CASE WHEN b THEN col_1 ELSE col_2 END) FROM t {code} cannot be converted to {code:sql} SELECT SUM(col_2) FILTER (WHERE b IS NOT TRUE) FROM t {code} Unless this jira tries to address "COUNT(CASE WHEN b THEN NULL ELSE 1 END)" as a special case, otherwise the right resolution is to not convert to FILTER if there is more than one THEN. I can try to open a PR for it. > AggregateCaseToFilterRule handles NULL values correctly > --- > > Key: CALCITE-3764 > URL: https://issues.apache.org/jira/browse/CALCITE-3764 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Priority: Major > > {{AggregateCaseToFilterRule}} handles NULL values correctly. It converts > {code:sql} > SELECT COUNT(CASE WHEN b THEN NULL ELSE 1 END) FROM t > {code} > to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS FALSE) FROM t > {code} > which fails to count rows where {{b}} is UNKNOWN, so it should convert to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS NOT TRUE) FROM t > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3764) AggregateCaseToFilterRule handles NULL values correctly
[ https://issues.apache.org/jira/browse/CALCITE-3764?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17028274#comment-17028274 ] Rui Wang commented on CALCITE-3764: --- I tried to verify the behaviour of cases in this Jira on Postgresql. First;y I created a test table and note that the third row's name column is "NULL". postgres=# select * from filter_test; sal | name -+-- 10 | n1 20 | n2 30 | (3 rows) Then here are three queries' results: postgres=# select count(case when name='n2' then null else 1 end) from filter_test; count --- 2 (1 row) postgres=# select count(*) FILTER (WHERE name='n2' IS FALSE) FROM filter_test; count --- 1 (1 row) postgres=# select count(*) FILTER (WHERE name='n2' IS NOT TRUE) FROM filter_test; count --- 2 (1 row) > AggregateCaseToFilterRule handles NULL values correctly > --- > > Key: CALCITE-3764 > URL: https://issues.apache.org/jira/browse/CALCITE-3764 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Priority: Major > > {{AggregateCaseToFilterRule}} handles NULL values correctly. It converts > {code:sql} > SELECT COUNT(CASE WHEN b THEN NULL ELSE 1 END) FROM t > {code} > to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS FALSE) FROM t > {code} > which fails to count rows where {{b}} is UNKNOWN, so it should convert to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS NOT TRUE) FROM t > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3764) AggregateCaseToFilterRule handles NULL values correctly
[ https://issues.apache.org/jira/browse/CALCITE-3764?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17028274#comment-17028274 ] Rui Wang edited comment on CALCITE-3764 at 2/2/20 4:40 AM: --- I tried to verify the behaviour of cases in this Jira on Postgresql. Firstly I created a test table and note that the third row's name column is "NULL". postgres=# select * from filter_test; sal | name -+-- 10 | n1 20 | n2 30 | (3 rows) Then here are three queries' results: postgres=# select count(case when name='n2' then null else 1 end) from filter_test; count --- 2 (1 row) postgres=# select count(*) FILTER (WHERE name='n2' IS FALSE) FROM filter_test; count --- 1 (1 row) postgres=# select count(*) FILTER (WHERE name='n2' IS NOT TRUE) FROM filter_test; count --- 2 (1 row) was (Author: amaliujia): I tried to verify the behaviour of cases in this Jira on Postgresql. First;y I created a test table and note that the third row's name column is "NULL". postgres=# select * from filter_test; sal | name -+-- 10 | n1 20 | n2 30 | (3 rows) Then here are three queries' results: postgres=# select count(case when name='n2' then null else 1 end) from filter_test; count --- 2 (1 row) postgres=# select count(*) FILTER (WHERE name='n2' IS FALSE) FROM filter_test; count --- 1 (1 row) postgres=# select count(*) FILTER (WHERE name='n2' IS NOT TRUE) FROM filter_test; count --- 2 (1 row) > AggregateCaseToFilterRule handles NULL values correctly > --- > > Key: CALCITE-3764 > URL: https://issues.apache.org/jira/browse/CALCITE-3764 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Priority: Major > > {{AggregateCaseToFilterRule}} handles NULL values correctly. It converts > {code:sql} > SELECT COUNT(CASE WHEN b THEN NULL ELSE 1 END) FROM t > {code} > to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS FALSE) FROM t > {code} > which fails to count rows where {{b}} is UNKNOWN, so it should convert to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS NOT TRUE) FROM t > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3750) Intermittent failures in PigRelBuilderStyleTest: ConcurrentModificationException
[ https://issues.apache.org/jira/browse/CALCITE-3750?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Vladimir Sitnikov updated CALCITE-3750: --- Summary: Intermittent failures in PigRelBuilderStyleTest: ConcurrentModificationException (was: PigRelBuilderStyleTest fails with ConcurrentModificationException) > Intermittent failures in PigRelBuilderStyleTest: > ConcurrentModificationException > > > Key: CALCITE-3750 > URL: https://issues.apache.org/jira/browse/CALCITE-3750 > Project: Calcite > Issue Type: Bug > Components: pig-adapter >Affects Versions: 1.21.0 >Reporter: Vladimir Sitnikov >Priority: Major > > {noformat} > PigRelBuilderStyleTest > testImplWithCountWithoutGroupBy() FAILED > java.util.ConcurrentModificationException > at java.util.Hashtable$Enumerator.next(Hashtable.java:1387) > at > org.apache.pig.impl.util.PropertiesUtil.loadDefaultProperties(PropertiesUtil.java:65) > at > org.apache.pig.impl.util.PropertiesUtil.loadDefaultProperties(PropertiesUtil.java:167) > at > org.apache.pig.ExecTypeProvider.fromString(ExecTypeProvider.java:76) > at org.apache.pig.test.Util.getLocalTestMode(Util.java:1395) > at > org.apache.calcite.test.PigRelBuilderStyleTest.setupDataFilesForPigServer(PigRelBuilderStyleTest.java:286) > FAILURE 0.5sec, org.apache.calcite.test.PigRelBuilderStyleTest > > testScanAndFilter() > {noformat} > {noformat} > PigRelBuilderStyleTest > testScanAndFilter() FAILED > java.util.ConcurrentModificationException > at java.util.Hashtable$Enumerator.next(Hashtable.java:1387) > at > org.apache.pig.impl.util.PropertiesUtil.loadDefaultProperties(PropertiesUtil.java:65) > at > org.apache.pig.impl.util.PropertiesUtil.loadDefaultProperties(PropertiesUtil.java:167) > at > org.apache.pig.ExecTypeProvider.fromString(ExecTypeProvider.java:76) > at org.apache.pig.test.Util.getLocalTestMode(Util.java:1395) > at > org.apache.calcite.test.PigRelBuilderStyleTest.setupDataFilesForPigServer(PigRelBuilderStyleTest.java:286) > {noformat} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3707) Implement COSH function
[ https://issues.apache.org/jira/browse/CALCITE-3707?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17028201#comment-17028201 ] Michael Mior commented on CALCITE-3707: --- Fixed in {{[bcac62e|https://github.com/apache/calcite/commit/bcac62e3dad6137511d4451135daa2d1762ec6ad]}} > Implement COSH function > --- > > Key: CALCITE-3707 > URL: https://issues.apache.org/jira/browse/CALCITE-3707 > Project: Calcite > Issue Type: Sub-task >Reporter: Forward Xu >Assignee: Forward Xu >Priority: Major > Labels: pull-request-available > Time Spent: 40m > Remaining Estimate: 0h > > Implement COSH function. > select cosh(1); > |EXPR$0| > |1.543080634815244| > (1 row) > !ok -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Resolved] (CALCITE-3707) Implement COSH function
[ https://issues.apache.org/jira/browse/CALCITE-3707?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Michael Mior resolved CALCITE-3707. --- Resolution: Fixed > Implement COSH function > --- > > Key: CALCITE-3707 > URL: https://issues.apache.org/jira/browse/CALCITE-3707 > Project: Calcite > Issue Type: Sub-task >Reporter: Forward Xu >Assignee: Forward Xu >Priority: Major > Labels: pull-request-available > Time Spent: 40m > Remaining Estimate: 0h > > Implement COSH function. > select cosh(1); > |EXPR$0| > |1.543080634815244| > (1 row) > !ok -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Resolved] (CALCITE-3695) Implement TANH function
[ https://issues.apache.org/jira/browse/CALCITE-3695?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Michael Mior resolved CALCITE-3695. --- Resolution: Fixed > Implement TANH function > --- > > Key: CALCITE-3695 > URL: https://issues.apache.org/jira/browse/CALCITE-3695 > Project: Calcite > Issue Type: Sub-task >Reporter: Forward Xu >Assignee: Forward Xu >Priority: Major > Labels: pull-request-available > Time Spent: 40m > Remaining Estimate: 0h > > Implement TANH function > select tanh(1); > |EXPR$0| > |0.7615941559557649| > (1 row) -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3695) Implement TANH function
[ https://issues.apache.org/jira/browse/CALCITE-3695?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17028199#comment-17028199 ] Michael Mior commented on CALCITE-3695: --- Fixed in [{{22c70ca}}|https://github.com/apache/calcite/commit/22c70caba8d1942dcde6fcd0039ce71d1525] > Implement TANH function > --- > > Key: CALCITE-3695 > URL: https://issues.apache.org/jira/browse/CALCITE-3695 > Project: Calcite > Issue Type: Sub-task >Reporter: Forward Xu >Assignee: Forward Xu >Priority: Major > Labels: pull-request-available > Time Spent: 40m > Remaining Estimate: 0h > > Implement TANH function > select tanh(1); > |EXPR$0| > |0.7615941559557649| > (1 row) -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Resolved] (CALCITE-1824) GROUP_ID returns wrong result
[ https://issues.apache.org/jira/browse/CALCITE-1824?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Michael Mior resolved CALCITE-1824. --- Resolution: Fixed > GROUP_ID returns wrong result > - > > Key: CALCITE-1824 > URL: https://issues.apache.org/jira/browse/CALCITE-1824 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Julian Hyde >Assignee: Feng Zhu >Priority: Major > Labels: pull-request-available > Time Spent: 5h 20m > Remaining Estimate: 0h > > We implemented the {{GROUP_ID()}} function in CALCITE-512 but we got the > specification wrong, and it returns the wrong result. > {{GROUP_ID}} is not in the SQL standard. It is implemented only by Oracle. > I mistakenly believed that {{GROUP_ID()}} is equivalent to {{GROUPING_ID(g1, > ..., gn)}} (in a query with {{GROUP BY g1, ..., gn}}). In fact, {{GROUP_ID}} > is useful only if you have duplicate grouping sets. If grouping sets are > distinct, {{GROUP_ID()}} will always return zero. > Example 1 > {code}SELECT deptno, job, GROUP_ID() AS g > FROM Emp > GROUP BY ROLLUP(deptno, job) > DEPTNO JOB G > -- - -- > 10 CLERK 0 > 10 MANAGER0 > 10 PRESIDENT 0 > 100 > 20 CLERK 0 > 20 ANALYST0 > 20 MANAGER0 > 200 > 30 CLERK 0 > 30 MANAGER0 > 30 SALESMAN 0 > 300 > 0 > {code} produces grouping sets (deptno, job), (deptno), (). These are > distinct, so GROUP_ID() is 0 for all rows. > Example 2 > {code}SELECT deptno, GROUP_ID() AS g > FROM Emp > GROUP BY GROUPING SETS (deptno, (), ()); > DEPTNOG > -- -- > 10 0 > 20 0 > 30 0 > 0 > 1 > {code} > As you can see, the grouping set () occurs twice. So there is one row in the > result for each occurrence: the first occurrence has g = 0; the second has g > = 1. > In my fix for CALCITE-1069, I will change GROUP_ID() to always return 0. This > is wrong, but nevertheless closer to the required behavior. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-1824) GROUP_ID returns wrong result
[ https://issues.apache.org/jira/browse/CALCITE-1824?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17028198#comment-17028198 ] Michael Mior commented on CALCITE-1824: --- Fixed in [1eb22d0|https://github.com/apache/calcite/commit/1eb22d03c11b9362b50208261b99eecc3de28fe8]. > GROUP_ID returns wrong result > - > > Key: CALCITE-1824 > URL: https://issues.apache.org/jira/browse/CALCITE-1824 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Julian Hyde >Assignee: Feng Zhu >Priority: Major > Labels: pull-request-available > Time Spent: 5h 20m > Remaining Estimate: 0h > > We implemented the {{GROUP_ID()}} function in CALCITE-512 but we got the > specification wrong, and it returns the wrong result. > {{GROUP_ID}} is not in the SQL standard. It is implemented only by Oracle. > I mistakenly believed that {{GROUP_ID()}} is equivalent to {{GROUPING_ID(g1, > ..., gn)}} (in a query with {{GROUP BY g1, ..., gn}}). In fact, {{GROUP_ID}} > is useful only if you have duplicate grouping sets. If grouping sets are > distinct, {{GROUP_ID()}} will always return zero. > Example 1 > {code}SELECT deptno, job, GROUP_ID() AS g > FROM Emp > GROUP BY ROLLUP(deptno, job) > DEPTNO JOB G > -- - -- > 10 CLERK 0 > 10 MANAGER0 > 10 PRESIDENT 0 > 100 > 20 CLERK 0 > 20 ANALYST0 > 20 MANAGER0 > 200 > 30 CLERK 0 > 30 MANAGER0 > 30 SALESMAN 0 > 300 > 0 > {code} produces grouping sets (deptno, job), (deptno), (). These are > distinct, so GROUP_ID() is 0 for all rows. > Example 2 > {code}SELECT deptno, GROUP_ID() AS g > FROM Emp > GROUP BY GROUPING SETS (deptno, (), ()); > DEPTNOG > -- -- > 10 0 > 20 0 > 30 0 > 0 > 1 > {code} > As you can see, the grouping set () occurs twice. So there is one row in the > result for each occurrence: the first occurrence has g = 0; the second has g > = 1. > In my fix for CALCITE-1069, I will change GROUP_ID() to always return 0. This > is wrong, but nevertheless closer to the required behavior. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3762) AggregateProjectPullUpConstantsRule causes fields to be out of order
[ https://issues.apache.org/jira/browse/CALCITE-3762?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde updated CALCITE-3762: - Fix Version/s: (was: 1.16.0) 1.22.0 > AggregateProjectPullUpConstantsRule causes fields to be out of order > > > Key: CALCITE-3762 > URL: https://issues.apache.org/jira/browse/CALCITE-3762 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.10.0, 1.16.0 >Reporter: hezhang >Priority: Major > Fix For: 1.22.0 > > Attachments: calcite-3762.patch, image-2020-02-01-01-29-49-479.png, > image-2020-02-01-01-33-54-111.png > > > the sql: > {code:java} > select * FROM( SELECT plat, category, rid, populary_num FROM > panda_com.crawler_anchor WHERE > par_date = '20180819' > AND plat = 'huya' > AND rid = 'meijiao' > ) a > JOIN > ( > SELECT DISTINCT > 'huya' plat , > edwin.privatehost , > edwin.profileroom > FROM > panda_com.ol_huya_isOnline edwin > WHERE > par_date = '20180819' ) m9 > ON > a.rid= m9.privatehost > AND a.plat = m9.plat{code} > the result: > > {code:java} > huya yule meijiao 30 huya 10001242 meijiao > {code} > > but the desired result is: > > {code:java} > huya yule meijiao 30 huya meijiao 10001242 > {code} > > *cause:* > hepPlanner use AggregateProjectPullUpConstantsRule: > !image-2020-02-01-01-29-49-479.png! > after add fix patch: > !image-2020-02-01-01-33-54-111.png! -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3762) AggregateProjectPullUpConstantsRule causes fields to be out of order
[ https://issues.apache.org/jira/browse/CALCITE-3762?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17028022#comment-17028022 ] Julian Hyde commented on CALCITE-3762: -- Can you add a test? Also, can you convert to a github pull request? We don’t do patches. > AggregateProjectPullUpConstantsRule causes fields to be out of order > > > Key: CALCITE-3762 > URL: https://issues.apache.org/jira/browse/CALCITE-3762 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.10.0, 1.16.0 >Reporter: hezhang >Priority: Major > Fix For: 1.16.0 > > Attachments: calcite-3762.patch, image-2020-02-01-01-29-49-479.png, > image-2020-02-01-01-33-54-111.png > > > the sql: > {code:java} > select * FROM( SELECT plat, category, rid, populary_num FROM > panda_com.crawler_anchor WHERE > par_date = '20180819' > AND plat = 'huya' > AND rid = 'meijiao' > ) a > JOIN > ( > SELECT DISTINCT > 'huya' plat , > edwin.privatehost , > edwin.profileroom > FROM > panda_com.ol_huya_isOnline edwin > WHERE > par_date = '20180819' ) m9 > ON > a.rid= m9.privatehost > AND a.plat = m9.plat{code} > the result: > > {code:java} > huya yule meijiao 30 huya 10001242 meijiao > {code} > > but the desired result is: > > {code:java} > huya yule meijiao 30 huya meijiao 10001242 > {code} > > *cause:* > hepPlanner use AggregateProjectPullUpConstantsRule: > !image-2020-02-01-01-29-49-479.png! > after add fix patch: > !image-2020-02-01-01-33-54-111.png! -- This message was sent by Atlassian Jira (v8.3.4#803005)