You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by "bvolpato (via GitHub)" <gi...@apache.org> on 2024/04/06 03:08:01 UTC

[PR] [CALCITE-6355] Fix subquery check when using ORDER BY X DESC NULLS LAST [calcite]

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

   
   There are some issues in the `needNewSubQuery` -> `hasSortByOrdinal` method in [SqlImplementor](https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1938C21-L1958) when using `RelToSqlConverter` specifying `NULLS LAST` in specific queries.
   
   The method `hasSortByOrdinal` fails to recognize that the order might contain nested calls. For example, we can easily produce `SqlSelect -> SqlNodeList order -> SqlBasicCall (nulls last) -> SqlBasicCall (desc) -> SqlNumericLiteral`, but it can only match correctly if there's a single `SqlBasicCall` in the chain.
   
   With that, this query:
   
   ```
   select "product_id"
   from "product"
   where "net_weight" is not null
   group by "product_id"
   order by MAX("net_weight") desc nulls last 
   ```
   
   Returns an invalid query. Notice the _order by 2_, however only one field was projected (`product_id`).
   
   ```
   SELECT "product_id"
   FROM "foodmart"."product"
   WHERE "net_weight" IS NOT NULL
   GROUP BY "product_id"
   ORDER BY 2 DESC NULLS LAST 
   ```
   
   
   
   (I've provided some context at https://issues.apache.org/jira/browse/CALCITE-6355).
   
   
   
   
   
   
   
   Trying to troubleshoot it, it appears that without the `NULLS LAST`, we have the following instance:
   
   
   
   
   SqlBasicCall -> SqlNumericLiteral 
   
   
   
   
   
   
   
   But when including it, it gets wrapped in another call:
   
   
   
   
   SqlBasicCall -> SqlBasicCall -> SqlNumericLiteral 
   
   
   
   
   
   
   
   So the [hasSortByOrdinal method](https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1938C21-L1958) ends up returning false, which causes `needNewSubQuery` to incorrectly report false too.


-- 
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-6355] RelToSqlConverter[ORDER BY] generates an incorrect order by when NULLS LAST is used in non-projected field [calcite]

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


-- 
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-6355] RelToSqlConverter[ORDER BY] generates an incorrect order by when NULLS LAST is used in non-projected field [calcite]

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

   ## [![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=3754) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [3 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3754&resolved=false&inNewCodePeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3754&metric=new_accepted_issues&view=list)
   
   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=3754&resolved=false&inNewCodePeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [96.7% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3754&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=3754&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3754)
   
   


-- 
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-6355] RelToSqlConverter[ORDER BY] generates an incorrect order by when NULLS LAST is used in non-projected field [calcite]

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


##########
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java:
##########
@@ -2162,6 +2162,48 @@ private SqlDialect nonOrdinalDialect() {
         .ok(prestoExpected);
   }
 
+  /**
+   * Test case for the base case of
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-6355">[CALCITE-6355]

Review Comment:
   Eventually after the PR is accepted you will have to squash all the commits to just one, and that one will have to have the exact same message as the JIRA title, and this PR title. Moreover, the JavaDoc for the test cases related to the fix also have to have a relatively rigid format, providing the link to the jira case and the full text in the jira title.
   



-- 
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-6355] Fix subquery check when using ORDER BY X DESC NULLS LAST [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on PR #3754:
URL: https://github.com/apache/calcite/pull/3754#issuecomment-2041009437

   @bvolpato Calcite's jira message does not allow the Fix keyword to begin with


-- 
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-6355] Improve subquery check to consider chained ORDER BY calls [calcite]

Posted by "bvolpato (via GitHub)" <gi...@apache.org>.
bvolpato commented on PR #3754:
URL: https://github.com/apache/calcite/pull/3754#issuecomment-2041649836

   Attempted to fix the checker errors (added annotations & handled nullness). It hadn't failed `./gradlew build` before, needed to explicitly `-PenableCheckerframework`.


-- 
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-6355] Improve subquery check to consider chained ORDER BY calls [calcite]

Posted by "bvolpato (via GitHub)" <gi...@apache.org>.
bvolpato commented on PR #3754:
URL: https://github.com/apache/calcite/pull/3754#issuecomment-2041692510

   This test is flaky / unrelated to my changes:
   
   ```
               Caused by: com.github.dockerjava.api.exception.DockerClientException: Could not pull image: [DEPRECATION NOTICE] Docker Image Format v1 and Docker Image manifest version 2, schema 1 support is disabled by default and will be removed in an upcoming release. Suggest the author of docker.io/library/redis:2.8.19 to upgrade the image to the OCI Format or Docker Image manifest v2, schema 2. More information at https://docs.docker.com/go/deprecated-image-specs/
   
                   at app//com.github.dockerjava.api.command.PullImageResultCallback.checkDockerClientPullSuccessful(PullImageResultCallback.java:97)
   
                   at app//com.github.dockerjava.api.command.PullImageResultCallback.throwFirstError(PullImageResultCallback.java:112)
   
                   at app//com.github.dockerjava.api.async.ResultCallbackTemplate.awaitCompletion(ResultCallbackTemplate.java:93)
   
                   at app//org.testcontainers.images.TimeLimitedLoggedPullImageResultCallback.awaitCompletion(TimeLimitedLoggedPullImageResultCallback.java:58)
   
                   at app//org.testcontainers.images.RemoteDockerImage.resolve(RemoteDockerImage.java:97)
   
                   ... 46 more
   
   
   
   FAILURE  10.5sec,    1 completed,   1 failed,   0 skipped, org.apache.calcite.adapter.redis.RedisAdapterCaseBase
   
   FAILURE  12.6sec,    2 completed,   1 failed,   1 skipped, Gradle Test Run :redis:test
   
   ```


-- 
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-6355] RelToSqlConverter[ORDER BY] generates an incorrect order by when NULLS LAST is used in non-projected field [calcite]

Posted by "bvolpato (via GitHub)" <gi...@apache.org>.
bvolpato commented on PR #3754:
URL: https://github.com/apache/calcite/pull/3754#issuecomment-2048225323

   > To merge this you will have to squash the commits into a single commit, and the commit message has to be exactly the JIRA title. Take a look at the other merged commits into main for examples.
   
   Done, thanks for your help @mihaibudiu!


-- 
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-6355] Improve subquery check to consider chained ORDER BY calls [calcite]

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

   ## [![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=3754) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [2 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3754&resolved=false&inNewCodePeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3754&metric=new_accepted_issues&view=list)
   
   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=3754&resolved=false&inNewCodePeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3754&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=3754&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3754)
   
   


-- 
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-6355] Improve subquery check to consider chained ORDER BY calls [calcite]

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

   ## [![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=3754) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [3 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3754&resolved=false&inNewCodePeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3754&metric=new_accepted_issues&view=list)
   
   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=3754&resolved=false&inNewCodePeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [96.7% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3754&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=3754&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3754)
   
   


-- 
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-6355] RelToSqlConverter[ORDER BY] generates an incorrect order by when NULLS LAST is used in non-projected field [calcite]

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


##########
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java:
##########
@@ -2162,6 +2162,48 @@ private SqlDialect nonOrdinalDialect() {
         .ok(prestoExpected);
   }
 
+  /**
+   * Test case for the base case of
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-6355">[CALCITE-6355]

Review Comment:
   Sorry, missed this. I've adjusted the title -- thank you. Do I have to reword commits? Or the title of the PR will be used when squashed?



##########
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java:
##########
@@ -2162,6 +2162,48 @@ private SqlDialect nonOrdinalDialect() {
         .ok(prestoExpected);
   }
 
+  /**
+   * Test case for the base case of
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-6355">[CALCITE-6355]

Review Comment:
   Sorry, missed this. I've adjusted the title here -- thank you. Do I have to reword commits? Or the title of the PR will be used when squashed?



-- 
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-6355] RelToSqlConverter[ORDER BY] generates an incorrect order by when NULLS LAST is used in non-projected field [calcite]

Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on PR #3754:
URL: https://github.com/apache/calcite/pull/3754#issuecomment-2048135248

   To merge this you will have to squash the commits into a single commit, and the commit message has to be exactly the JIRA title. Take a look at the other merged commits into main for examples.


-- 
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-6355] Improve subquery check to consider chained ORDER BY calls [calcite]

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


##########
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java:
##########
@@ -2162,6 +2162,48 @@ private SqlDialect nonOrdinalDialect() {
         .ok(prestoExpected);
   }
 
+  /**
+   * Test case for the base case of
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-6355">[CALCITE-6355]

Review Comment:
   So far we have required the JIRA case title, the PR case title, and the comments in the code to use the exact same text.
   This makes it easy to understand how these are related.
   Can you please follow these guidelines?
   There are lots of examples.
   



-- 
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-6355] RelToSqlConverter[ORDER BY] generates an incorrect order by when NULLS LAST is used in non-projected field [calcite]

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


##########
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java:
##########
@@ -2162,6 +2162,48 @@ private SqlDialect nonOrdinalDialect() {
         .ok(prestoExpected);
   }
 
+  /**
+   * Test case for the base case of
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-6355">[CALCITE-6355]

Review Comment:
   Sorry, missed this. I've adjusted the title here -- thank you. Do I have to reword commits? Or the title of the PR will be used when merged / squashed?



-- 
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-6355] Improve subquery check to consider chained ORDER BY calls [calcite]

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

   ## [![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=3754) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [3 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3754&resolved=false&inNewCodePeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3754&metric=new_accepted_issues&view=list)
   
   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=3754&resolved=false&inNewCodePeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [96.7% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3754&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=3754&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3754)
   
   


-- 
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