[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:
[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=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=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=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=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=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)
