[jira] [Updated] (CALCITE-6269) Fix missing/broken BigQuery date-time format elements
[ https://issues.apache.org/jira/browse/CALCITE-6269?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jerin John updated CALCITE-6269: Description: Calcite has the [FormatModels|https://github.com/apache/calcite/blob/2dadcd1a0e235f5fe1b29c9c32014035971fd45e/core/src/main/java/org/apache/calcite/util/format/FormatModels.java#L115] class which is missing support for or has incorrect implementation for the following DATE-TIME format elements: * [YYY / Y|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_year_as_string] - last three or 1 digits of year * [|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_year_as_string] - supports four or more digits in the year, Calcite using [DateString|https://github.com/apache/calcite/blob/3326475c766267d521330006cc80730c4e456191/core/src/main/java/org/apache/calcite/util/DateString.java] util throws: {{java.lang.IllegalArgumentException: Year out of range: [12018]}} * [MONTH|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_month_as_string] formats to "Jan" instead of "JANUARY" * [S|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_second_as_string] - seconds of the day (5 digits), only SS is available that gives seconds of the minute. * [FFn (n=1/2)|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_second_as_string] - always returns seconds with precision 3 (=FF3); also BQ supports n=1-9, calcite format models supports n=1-6, should we expand this range? * [AM/PM|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_meridian_as_string] - Meridian formats not available * [Parsing timestamp literals|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_tz_as_string] with timezones as used by BQ does not seem to be supported yet (format element TZR is unimplemented, BQ has TZH, TZM for hour and minute offsets) (eg: {{cast('2020.06.03 00:00:53+00' as timestamp format '.MM.DD HH:MI:SSTZH')}} * BQ format [timezone as string |https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_tz_as_string] can take an additional argument {{{}AT TIME ZONE 'Asia/Kolkata'{}}}, which would require additional parser changes and time zone parameter to be plumbed in to the cast operator call. was: Calcite has the [FormatModels|https://github.com/apache/calcite/blob/2dadcd1a0e235f5fe1b29c9c32014035971fd45e/core/src/main/java/org/apache/calcite/util/format/FormatModels.java#L115] class which is missing support for or has incorrect implementation for the following DATE-TIME format elements: * [YYY / Y|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_year_as_string] - last three or 1 digits of year * [|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_year_as_string] - supports four or more digits in the year, Calcite using [DateString|https://github.com/apache/calcite/blob/3326475c766267d521330006cc80730c4e456191/core/src/main/java/org/apache/calcite/util/DateString.java] util throws: {{java.lang.IllegalArgumentException: Year out of range: [12018]}} * [MONTH|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_month_as_string] formats to "Jan" instead of "JANUARY" * [S|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_second_as_string] - five digit seconds precision, only SS two digit precision is available * [FFn (n=1/2)|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_second_as_string] - always returns seconds with precision 3 (=FF3); also BQ supports n=1-9, calcite format models supports n=1-6, should we expand this range? * [AM/PM|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_meridian_as_string] - Meridian formats not available * [Parsing timestamp literals|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_tz_as_string] with timezones as used by BQ does not seem to be supported yet (format element TZR is unimplemented, BQ has TZH, TZM for hour and minute offsets) (eg: {{cast('2020.06.03 00:00:53+00' as timestamp format '.MM.DD HH:MI:SSTZH')}} * BQ format [timezone as string |https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_tz_as_string] can take an additional argument {{{}AT TIME ZONE 'Asia/Kolkata'{}}}, which would require additional parser changes and time zone parameter to be plumbed in to the cast operator call. > Fix missing/broken BigQuery date-time format elements > - > > Key: CALCITE-6269 > URL: h
[jira] [Commented] (CALCITE-6265) Type coercion is failing for numeric values in prepared statements
[ https://issues.apache.org/jira/browse/CALCITE-6265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17836721#comment-17836721 ] Mihai Budiu commented on CALCITE-6265: -- Tangentially related, I have a fix that implements ConvertChecked more thoroughly (but perhaps still not completely) in https://github.com/apache/calcite/pull/3589, which hasn't been reviewed. > Type coercion is failing for numeric values in prepared statements > -- > > Key: CALCITE-6265 > URL: https://issues.apache.org/jira/browse/CALCITE-6265 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Tim Nieradzik >Assignee: Ruben Q L >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > > Given a column of type {{{}INT{}}}. When providing a {{short}} value as a > placeholder in a prepared statement, a {{ClassCastException}} is thrown. > h2. Test case > {{final String sql =}} > {{ "select \"empid\" from \"hr\".\"emps\" where \"empid\" in (?, ?)";}}{{ > CalciteAssert.hr()}} > {{ .query(sql)}} > {{ .consumesPreparedStatement(p -> {}} > {{ p.setShort(1, (short) 100);}} > {{ p.setShort(2, (short) 110);}} > {{ })}} > {{ .returnsUnordered("empid=100", "empid=110");}} > h2. Stack trace > {{java.lang.ClassCastException: class java.lang.Short cannot be cast to class > java.lang.Integer (java.lang.Short and java.lang.Integer are in module > java.base of loader 'bootstrap')}} > {{ at Baz$1$1.moveNext(Unknown Source)}} > {{ at > org.apache.calcite.linq4j.Linq4j$EnumeratorIterator.(Linq4j.java:679)}} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6265) Type coercion is failing for numeric values in prepared statements
[ https://issues.apache.org/jira/browse/CALCITE-6265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17836719#comment-17836719 ] Mihai Budiu commented on CALCITE-6265: -- [~tnieradzik] can you check that this new proposed fix agrees with the problems you found? It does not affect the new tests you provided. > Type coercion is failing for numeric values in prepared statements > -- > > Key: CALCITE-6265 > URL: https://issues.apache.org/jira/browse/CALCITE-6265 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Tim Nieradzik >Assignee: Ruben Q L >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > > Given a column of type {{{}INT{}}}. When providing a {{short}} value as a > placeholder in a prepared statement, a {{ClassCastException}} is thrown. > h2. Test case > {{final String sql =}} > {{ "select \"empid\" from \"hr\".\"emps\" where \"empid\" in (?, ?)";}}{{ > CalciteAssert.hr()}} > {{ .query(sql)}} > {{ .consumesPreparedStatement(p -> {}} > {{ p.setShort(1, (short) 100);}} > {{ p.setShort(2, (short) 110);}} > {{ })}} > {{ .returnsUnordered("empid=100", "empid=110");}} > h2. Stack trace > {{java.lang.ClassCastException: class java.lang.Short cannot be cast to class > java.lang.Integer (java.lang.Short and java.lang.Integer are in module > java.base of loader 'bootstrap')}} > {{ at Baz$1$1.moveNext(Unknown Source)}} > {{ at > org.apache.calcite.linq4j.Linq4j$EnumeratorIterator.(Linq4j.java:679)}} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Reopened] (CALCITE-6265) Type coercion is failing for numeric values in prepared statements
[ https://issues.apache.org/jira/browse/CALCITE-6265?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ruben Q L reopened CALCITE-6265: Assignee: Ruben Q L (was: Tim Nieradzik) > Type coercion is failing for numeric values in prepared statements > -- > > Key: CALCITE-6265 > URL: https://issues.apache.org/jira/browse/CALCITE-6265 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Tim Nieradzik >Assignee: Ruben Q L >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > > Given a column of type {{{}INT{}}}. When providing a {{short}} value as a > placeholder in a prepared statement, a {{ClassCastException}} is thrown. > h2. Test case > {{final String sql =}} > {{ "select \"empid\" from \"hr\".\"emps\" where \"empid\" in (?, ?)";}}{{ > CalciteAssert.hr()}} > {{ .query(sql)}} > {{ .consumesPreparedStatement(p -> {}} > {{ p.setShort(1, (short) 100);}} > {{ p.setShort(2, (short) 110);}} > {{ })}} > {{ .returnsUnordered("empid=100", "empid=110");}} > h2. Stack trace > {{java.lang.ClassCastException: class java.lang.Short cannot be cast to class > java.lang.Integer (java.lang.Short and java.lang.Integer are in module > java.base of loader 'bootstrap')}} > {{ at Baz$1$1.moveNext(Unknown Source)}} > {{ at > org.apache.calcite.linq4j.Linq4j$EnumeratorIterator.(Linq4j.java:679)}} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6265) Type coercion is failing for numeric values in prepared statements
[ https://issues.apache.org/jira/browse/CALCITE-6265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17836560#comment-17836560 ] Ruben Q L edited comment on CALCITE-6265 at 4/12/24 11:16 AM: -- I have [opened a PR|https://github.com/apache/calcite/pull/3758] to fix the problems (partially reverting some of the original changes, partially making some adjustments): - It seems the original issue could be solved easier in RexToLixTranslator, by simply converting to Number and then to storageType in case of Numeric types. - All the bind* tests that were added on the first PR still work; plus a new test that I added to illustrate the issues that I found (with setBigDecimal) - The problems that I detected on my downstream project are also fixed. - The only thing that is missing (didn't have the time to dig deeper) from the original solution is the "check for overflow and throw". I have left the auxiliary method that generates this dynamic code (Expressions#convertChecked), and left a TODO on EnumUtils#convert for future work. I think that this was not part of the strict scope of the current ticket's description, so IMHO it would be acceptable open a separate ticket and work on that in the future, adding more thorough tests on this regard (and not just the one JdbcTest#bindOverflowingTinyIntParameter that was originally added, which I disabled on my branch). I'd appreciate some feedback, and if you think I can move on and merge my proposal. was (Author: rubenql): I have [opened a PR|https://github.com/apache/calcite/pull/3758] to fix the problems (partially reverting some of the original changes, partially making some adjustments): - It seems the original issue could be solved easier in RexToLixTranslator, by simply converting to Number and then to storageType in case of Numeric types. - All the bind* tests that were added on the first PR still work; plus a new test that I added to illustrate the issues that I found (with setBigDecimal) - The problems that I detected on my downstream project are also fixed. - The only thing that is missing (didn't have the time to dig deeper) from the original solution is the "check for overflow and throw". I have left the auxiliary method that generates this dynamic code, and left a TODO on EnumUtils#convert for future work. I think that this was not part of the strict scope of the current ticket's description, so IMHO it would be ok open a separate ticket and work on that in the future, adding more thorough tests (and not just the one that was originally added, which I disabled on my branch). I'd appreciate some feedback, and if you think I can move on and merge my proposal. > Type coercion is failing for numeric values in prepared statements > -- > > Key: CALCITE-6265 > URL: https://issues.apache.org/jira/browse/CALCITE-6265 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Tim Nieradzik >Assignee: Tim Nieradzik >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > > Given a column of type {{{}INT{}}}. When providing a {{short}} value as a > placeholder in a prepared statement, a {{ClassCastException}} is thrown. > h2. Test case > {{final String sql =}} > {{ "select \"empid\" from \"hr\".\"emps\" where \"empid\" in (?, ?)";}}{{ > CalciteAssert.hr()}} > {{ .query(sql)}} > {{ .consumesPreparedStatement(p -> {}} > {{ p.setShort(1, (short) 100);}} > {{ p.setShort(2, (short) 110);}} > {{ })}} > {{ .returnsUnordered("empid=100", "empid=110");}} > h2. Stack trace > {{java.lang.ClassCastException: class java.lang.Short cannot be cast to class > java.lang.Integer (java.lang.Short and java.lang.Integer are in module > java.base of loader 'bootstrap')}} > {{ at Baz$1$1.moveNext(Unknown Source)}} > {{ at > org.apache.calcite.linq4j.Linq4j$EnumeratorIterator.(Linq4j.java:679)}} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6265) Type coercion is failing for numeric values in prepared statements
[ https://issues.apache.org/jira/browse/CALCITE-6265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17836560#comment-17836560 ] Ruben Q L commented on CALCITE-6265: I have [opened a PR|https://github.com/apache/calcite/pull/3758] to fix the problems (partially reverting some of the original changes, partially making some adjustments): - It seems the original issue could be solved easier in RexToLixTranslator, by simply converting to Number and then to storageType in case of Numeric types. - All the bind* tests that were added on the first PR still work; plus a new test that I added to illustrate the issues that I found (with setBigDecimal) - The problems that I detected on my downstream project are also fixed. - The only thing that is missing (didn't have the time to dig deeper) from the original solution is the "check for overflow and throw". I have left the auxiliary method that generates this dynamic code, and left a TODO on EnumUtils#convert for future work. I think that this was not part of the strict scope of the current ticket's description, so IMHO it would be ok open a separate ticket and work on that in the future, adding more thorough tests (and not just the one that was originally added, which I disabled on my branch). I'd appreciate some feedback, and if you think I can move on and merge my proposal. > Type coercion is failing for numeric values in prepared statements > -- > > Key: CALCITE-6265 > URL: https://issues.apache.org/jira/browse/CALCITE-6265 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Tim Nieradzik >Assignee: Tim Nieradzik >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > > Given a column of type {{{}INT{}}}. When providing a {{short}} value as a > placeholder in a prepared statement, a {{ClassCastException}} is thrown. > h2. Test case > {{final String sql =}} > {{ "select \"empid\" from \"hr\".\"emps\" where \"empid\" in (?, ?)";}}{{ > CalciteAssert.hr()}} > {{ .query(sql)}} > {{ .consumesPreparedStatement(p -> {}} > {{ p.setShort(1, (short) 100);}} > {{ p.setShort(2, (short) 110);}} > {{ })}} > {{ .returnsUnordered("empid=100", "empid=110");}} > h2. Stack trace > {{java.lang.ClassCastException: class java.lang.Short cannot be cast to class > java.lang.Integer (java.lang.Short and java.lang.Integer are in module > java.base of loader 'bootstrap')}} > {{ at Baz$1$1.moveNext(Unknown Source)}} > {{ at > org.apache.calcite.linq4j.Linq4j$EnumeratorIterator.(Linq4j.java:679)}} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6265) Type coercion is failing for numeric values in prepared statements
[ https://issues.apache.org/jira/browse/CALCITE-6265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17836464#comment-17836464 ] Ruben Q L commented on CALCITE-6265: I just found a simple test (to be added to JdbcTest.java) related to my case A, that would have worked fine before this commit, and now it's broken: {code} @Test void bindDecimalParameter() { final String sql = "with cte as (select 2500.55 as val)" + "select * from cte where val = ?"; CalciteAssert.hr() .query(sql) .consumesPreparedStatement(p -> { p.setBigDecimal(1, new BigDecimal("2500.55")); }) .returnsUnordered("VAL=2500.55"); } {code} I'll try to provide a PR with a fix... > Type coercion is failing for numeric values in prepared statements > -- > > Key: CALCITE-6265 > URL: https://issues.apache.org/jira/browse/CALCITE-6265 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Tim Nieradzik >Assignee: Tim Nieradzik >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > > Given a column of type {{{}INT{}}}. When providing a {{short}} value as a > placeholder in a prepared statement, a {{ClassCastException}} is thrown. > h2. Test case > {{final String sql =}} > {{ "select \"empid\" from \"hr\".\"emps\" where \"empid\" in (?, ?)";}}{{ > CalciteAssert.hr()}} > {{ .query(sql)}} > {{ .consumesPreparedStatement(p -> {}} > {{ p.setShort(1, (short) 100);}} > {{ p.setShort(2, (short) 110);}} > {{ })}} > {{ .returnsUnordered("empid=100", "empid=110");}} > h2. Stack trace > {{java.lang.ClassCastException: class java.lang.Short cannot be cast to class > java.lang.Integer (java.lang.Short and java.lang.Integer are in module > java.base of loader 'bootstrap')}} > {{ at Baz$1$1.moveNext(Unknown Source)}} > {{ at > org.apache.calcite.linq4j.Linq4j$EnumeratorIterator.(Linq4j.java:679)}} -- This message was sent by Atlassian Jira (v8.20.10#820010)