You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by "xiedeyantu (via GitHub)" <gi...@apache.org> on 2025/09/20 16:28:41 UTC

[PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

xiedeyantu opened a new pull request, #4548:
URL: https://github.com/apache/calcite/pull/4548

   See: [CALCITE-7189](https://issues.apache.org/jira/browse/CALCITE-7189)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "xiedeyantu (via GitHub)" <gi...@apache.org>.
xiedeyantu commented on code in PR #4548:
URL: https://github.com/apache/calcite/pull/4548#discussion_r2370480302


##########
core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java:
##########
@@ -526,6 +534,34 @@ private boolean expandSelectItem(final SqlNode selectItem, SqlSelect select,
     return false;
   }
 
+  /**
+   * Returns true if the node is a non-aggregated, non-grouped column in SELECT.
+   */
+  private boolean isNonAggregatedNonGroupedColumn(SqlNode node, SqlSelect select) {
+    if (aggFinder.findAgg(node) != null) {
+      return false;
+    }
+
+    if (node instanceof SqlIdentifier) {
+      SqlNodeList groupList = select.getGroup();
+      if (groupList == null) {
+        return true;
+      }
+      return groupList.getList().stream()
+          .noneMatch(groupItem -> groupItem != null
+              && node.equalsDeep(groupItem, Litmus.IGNORE));
+    }
+
+    if (node instanceof SqlCall) {

Review Comment:
   Do you mean I should add a any_value(x) in sql to see the execution result? I don't quite understand the necessity of doing this. Could you tell me the specific issue you're concerned about?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "xiedeyantu (via GitHub)" <gi...@apache.org>.
xiedeyantu commented on code in PR #4548:
URL: https://github.com/apache/calcite/pull/4548#discussion_r2365931048


##########
core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java:
##########
@@ -130,6 +130,12 @@ enum SelectAliasLookup {
    */
   SelectAliasLookup isSelectAlias();
 
+  /**
+   * Allow non-GROUP BY columns in SELECT (MySQL loose mode).
+   * If enabled, wrap with ANY_VALUE().

Review Comment:
   Got it!



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "xiedeyantu (via GitHub)" <gi...@apache.org>.
xiedeyantu merged PR #4548:
URL: https://github.com/apache/calcite/pull/4548


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "xiedeyantu (via GitHub)" <gi...@apache.org>.
xiedeyantu commented on code in PR #4548:
URL: https://github.com/apache/calcite/pull/4548#discussion_r2366250051


##########
core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java:
##########
@@ -487,6 +487,14 @@ private boolean expandSelectItem(final SqlNode selectItem, SqlSelect select,
       // calls.
       selectScope = getSelectScope(select);
       expanded = expandSelectExpr(selectItem, scope, select, expansions);
+
+      // Non-strict GROUP BY: wrap non-aggregated, non-grouped columns in ANY_VALUE()
+      if (isAggregate(select)
+          && config.conformance().isNonStrictGroupBy()
+          && isNonAggregatedNonGroupedColumn(expanded, select)) {

Review Comment:
   I have enriched the test cases in agg.iq. Please take a look to see if there are any scenarios that are not yet covered.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #4548:
URL: https://github.com/apache/calcite/pull/4548#discussion_r2370482844


##########
core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java:
##########
@@ -526,6 +534,34 @@ private boolean expandSelectItem(final SqlNode selectItem, SqlSelect select,
     return false;
   }
 
+  /**
+   * Returns true if the node is a non-aggregated, non-grouped column in SELECT.
+   */
+  private boolean isNonAggregatedNonGroupedColumn(SqlNode node, SqlSelect select) {
+    if (aggFinder.findAgg(node) != null) {
+      return false;
+    }
+
+    if (node instanceof SqlIdentifier) {
+      SqlNodeList groupList = select.getGroup();
+      if (groupList == null) {
+        return true;
+      }
+      return groupList.getList().stream()
+          .noneMatch(groupItem -> groupItem != null
+              && node.equalsDeep(groupItem, Litmus.IGNORE));
+    }
+
+    if (node instanceof SqlCall) {

Review Comment:
   read the next question



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #4548:
URL: https://github.com/apache/calcite/pull/4548#issuecomment-3316028400

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=4548) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [1 New issue](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=4548&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=4548&issueStatuses=ACCEPTED)
   
   Measures  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=4548&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [89.4% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=4548&metric=new_coverage&view=list)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=4548&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=4548)
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #4548:
URL: https://github.com/apache/calcite/pull/4548#issuecomment-3315096437

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=4548) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [1 New issue](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=4548&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=4548&issueStatuses=ACCEPTED)
   
   Measures  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=4548&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [90.9% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=4548&metric=new_coverage&view=list)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=4548&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=4548)
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #4548:
URL: https://github.com/apache/calcite/pull/4548#discussion_r2370549534


##########
core/src/test/resources/sql/agg.iq:
##########
@@ -4126,4 +4126,40 @@ GROUP BY DEPTNO, JOB;
 
 !ok
 
+# [CALCITE-7189] Support MySQL-style non-standard GROUP BY
+!use scott-babel

Review Comment:
   Can you add a comment explaining why this test is stable and how the results were validated?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #4548:
URL: https://github.com/apache/calcite/pull/4548#discussion_r2370318193


##########
core/src/test/resources/sql/agg.iq:
##########
@@ -4126,4 +4126,40 @@ GROUP BY DEPTNO, JOB;
 
 !ok
 
+# [CALCITE-7189] Support MySQL-style non-standard GROUP BY
+!use scott-babel

Review Comment:
   if any_value is non-deterministic, I wonder how these results were validated
   



##########
core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java:
##########
@@ -526,6 +534,34 @@ private boolean expandSelectItem(final SqlNode selectItem, SqlSelect select,
     return false;
   }
 
+  /**
+   * Returns true if the node is a non-aggregated, non-grouped column in SELECT.
+   */
+  private boolean isNonAggregatedNonGroupedColumn(SqlNode node, SqlSelect select) {
+    if (aggFinder.findAgg(node) != null) {
+      return false;
+    }
+
+    if (node instanceof SqlIdentifier) {
+      SqlNodeList groupList = select.getGroup();
+      if (groupList == null) {
+        return true;
+      }
+      return groupList.getList().stream()
+          .noneMatch(groupItem -> groupItem != null
+              && node.equalsDeep(groupItem, Litmus.IGNORE));
+    }
+
+    if (node instanceof SqlCall) {

Review Comment:
   I assume this function is non-deterministic



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "xiedeyantu (via GitHub)" <gi...@apache.org>.
xiedeyantu commented on code in PR #4548:
URL: https://github.com/apache/calcite/pull/4548#discussion_r2370604579


##########
core/src/test/resources/sql/agg.iq:
##########
@@ -4126,4 +4126,40 @@ GROUP BY DEPTNO, JOB;
 
 !ok
 
+# [CALCITE-7189] Support MySQL-style non-standard GROUP BY
+!use scott-babel

Review Comment:
   I've noticed that this test case I wrote is quite coincidental, as it simultaneously verifies that any_value(sal) and max(sal) are equal. Therefore, I'll add a note here explaining that the any_value function is currently implemented using the max function. Is that acceptable?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "xiedeyantu (via GitHub)" <gi...@apache.org>.
xiedeyantu commented on code in PR #4548:
URL: https://github.com/apache/calcite/pull/4548#discussion_r2365931480


##########
core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java:
##########
@@ -526,6 +534,45 @@ private boolean expandSelectItem(final SqlNode selectItem, SqlSelect select,
     return false;
   }
 
+  /**
+   * Returns true if the node is a non-aggregated, non-grouped column in SELECT.
+   */
+  private boolean isNonAggregatedNonGroupedColumn(SqlNode node, SqlSelect select) {
+    if (aggFinder.findAgg(node) != null) {
+      return false;
+    }
+
+    if (node instanceof SqlIdentifier) {
+      SqlNodeList groupList = select.getGroup();
+      if (groupList == null) {
+        return true;
+      }
+      return groupList.getList().stream()
+          .noneMatch(groupItem -> groupItem != null
+              && equalAsIdentifier((SqlIdentifier) node, groupItem));
+    }
+
+    if (node instanceof SqlCall) {
+      return ((SqlCall) node).getOperandList().stream()
+          .allMatch(operand -> isNonAggregatedNonGroupedColumn(operand, select));
+    } else if (node instanceof SqlLiteral) {
+      return true;
+    }
+
+    return false;
+  }
+
+  /** Determines whether two SqlIdentifier objects are equivalent. */
+  private boolean equalAsIdentifier(SqlIdentifier id1, SqlNode node2) {
+    if (!(node2 instanceof SqlIdentifier)) {

Review Comment:
   I really need to think it over again.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #4548:
URL: https://github.com/apache/calcite/pull/4548#issuecomment-3322034245

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=4548) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [1 New issue](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=4548&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=4548&issueStatuses=ACCEPTED)
   
   Measures  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=4548&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [89.4% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=4548&metric=new_coverage&view=list)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=4548&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=4548)
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on code in PR #4548:
URL: https://github.com/apache/calcite/pull/4548#discussion_r2365923602


##########
core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java:
##########
@@ -518,4 +518,13 @@ public enum SqlConformanceEnum implements SqlConformance {
       return false;
     }
   }
+
+  @Override public boolean isNonStrictGroupBy() {
+    switch (this) {
+    case BABEL:
+      return true;

Review Comment:
   Whether isNonStrictGroupBy should be enabled by default for MySQL. This mode is very common in MySQL.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #4548:
URL: https://github.com/apache/calcite/pull/4548#discussion_r2370607985


##########
core/src/test/resources/sql/agg.iq:
##########
@@ -4126,4 +4126,40 @@ GROUP BY DEPTNO, JOB;
 
 !ok
 
+# [CALCITE-7189] Support MySQL-style non-standard GROUP BY
+!use scott-babel

Review Comment:
   yes; I have approved this PR



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "xiedeyantu (via GitHub)" <gi...@apache.org>.
xiedeyantu commented on code in PR #4548:
URL: https://github.com/apache/calcite/pull/4548#discussion_r2370543860


##########
core/src/test/resources/sql/agg.iq:
##########
@@ -4126,4 +4126,40 @@ GROUP BY DEPTNO, JOB;
 
 !ok
 
+# [CALCITE-7189] Support MySQL-style non-standard GROUP BY
+!use scott-babel

Review Comment:
   Oh, I see your point now. I found its implementation, and it looks like it's implemented using 
   "MinMax" (https://github.com/apache/calcite/pull/735/files#diff-2095e662d66d6c1b2851915fa0d73934c3a847550545396835e02d88a9f5e2daR447). So within Calcite, it might be a deterministic function. In the PR that implemented 
   "ANY_VALUE", they also used 
   ".iq" tests to verify the results. So this test is probably safe?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "xiedeyantu (via GitHub)" <gi...@apache.org>.
xiedeyantu commented on code in PR #4548:
URL: https://github.com/apache/calcite/pull/4548#discussion_r2366224893


##########
core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java:
##########
@@ -518,4 +518,13 @@ public enum SqlConformanceEnum implements SqlConformance {
       return false;
     }
   }
+
+  @Override public boolean isNonStrictGroupBy() {
+    switch (this) {
+    case BABEL:
+      return true;

Review Comment:
   I checked and found that MySQL has always supported the [ONLY_FULL_GROUP_BY](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by) mode, but it wasn't until version 5.7.5 that it was enabled by default. In Calcite, there is only one MYSQL_5 Conformance, and I'm not sure if adding MYSQL_5 here is appropriate.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [CALCITE-7189] Support MySQL-style non-standard GROUP BY [calcite]

Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #4548:
URL: https://github.com/apache/calcite/pull/4548#discussion_r2365857527


##########
core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java:
##########
@@ -487,6 +487,14 @@ private boolean expandSelectItem(final SqlNode selectItem, SqlSelect select,
       // calls.
       selectScope = getSelectScope(select);
       expanded = expandSelectExpr(selectItem, scope, select, expansions);
+
+      // Non-strict GROUP BY: wrap non-aggregated, non-grouped columns in ANY_VALUE()
+      if (isAggregate(select)
+          && config.conformance().isNonStrictGroupBy()
+          && isNonAggregatedNonGroupedColumn(expanded, select)) {

Review Comment:
   I wonder how this interacts with other config options such as isGroupByAlias or isSelectAlias.
   But perhaps this is fine, since this check is after expandSelectExpr.



##########
core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java:
##########
@@ -526,6 +534,45 @@ private boolean expandSelectItem(final SqlNode selectItem, SqlSelect select,
     return false;
   }
 
+  /**
+   * Returns true if the node is a non-aggregated, non-grouped column in SELECT.
+   */
+  private boolean isNonAggregatedNonGroupedColumn(SqlNode node, SqlSelect select) {
+    if (aggFinder.findAgg(node) != null) {
+      return false;
+    }
+
+    if (node instanceof SqlIdentifier) {
+      SqlNodeList groupList = select.getGroup();
+      if (groupList == null) {
+        return true;
+      }
+      return groupList.getList().stream()
+          .noneMatch(groupItem -> groupItem != null
+              && equalAsIdentifier((SqlIdentifier) node, groupItem));
+    }
+
+    if (node instanceof SqlCall) {
+      return ((SqlCall) node).getOperandList().stream()
+          .allMatch(operand -> isNonAggregatedNonGroupedColumn(operand, select));
+    } else if (node instanceof SqlLiteral) {
+      return true;
+    }
+
+    return false;
+  }
+
+  /** Determines whether two SqlIdentifier objects are equivalent. */
+  private boolean equalAsIdentifier(SqlIdentifier id1, SqlNode node2) {
+    if (!(node2 instanceof SqlIdentifier)) {

Review Comment:
   This function needs many more tests. 
   Ideally you would not compare identifiers, but column indexes in inputs.
   Are you sure that the compared identifiers are both fully expanded at this point?



##########
core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java:
##########
@@ -130,6 +130,12 @@ enum SelectAliasLookup {
    */
   SelectAliasLookup isSelectAlias();
 
+  /**
+   * Allow non-GROUP BY columns in SELECT (MySQL loose mode).
+   * If enabled, wrap with ANY_VALUE().

Review Comment:
   I don't think that SqlConformance should dictate how this is implemented.
   The second line should not be here.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org