[jira] [Created] (CALCITE-3151) RexCall's Monotonicity is not considered in determining a Calc's collation
Zhenghua Gao created CALCITE-3151: - Summary: RexCall's Monotonicity is not considered in determining a Calc's collation Key: CALCITE-3151 URL: https://issues.apache.org/jira/browse/CALCITE-3151 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.19.0 Reporter: Zhenghua Gao In [FLINK-12999|https://issues.apache.org/jira/browse/FLINK-12999] I found a case that the planner can't generate a valid execution plan for a sample query: SELECT UUID() FROM (VALUES(1, 2, 3)) T(a, b, c) I made an investigation and found the root case was: RexCall's Monotonicity is not considered in determining a Calc's collation( should do the same as Project). -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-3199) DateTimeUtils.unixDateCeil returns the same value as unixDateFloor does
Zhenghua Gao created CALCITE-3199: - Summary: DateTimeUtils.unixDateCeil returns the same value as unixDateFloor does Key: CALCITE-3199 URL: https://issues.apache.org/jira/browse/CALCITE-3199 Project: Calcite Issue Type: Bug Affects Versions: avatica-1.16.0 Reporter: Zhenghua Gao Fix For: avatica-1.16.0 Internally, unixDateCeil & unixDateFloor call julianDateFloor and pass a boolean parameter to differentiate them. But unixDateCeil passes wrong parameter value and returns the same value as unixDateFloor does. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Created] (CALCITE-3497) Calcite TimestampString should provide getNanosInSecond interface
Zhenghua Gao created CALCITE-3497: - Summary: Calcite TimestampString should provide getNanosInSecond interface Key: CALCITE-3497 URL: https://issues.apache.org/jira/browse/CALCITE-3497 Project: Calcite Issue Type: Bug Components: core Reporter: Zhenghua Gao -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3528) TIMESTAMPADD/TIMESTAMPDIFF can't handle unit of MILLISECOND
Zhenghua Gao created CALCITE-3528: - Summary: TIMESTAMPADD/TIMESTAMPDIFF can't handle unit of MILLISECOND Key: CALCITE-3528 URL: https://issues.apache.org/jira/browse/CALCITE-3528 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.21.0 Reporter: Zhenghua Gao Calcite's SQL parser can't handle unit of MILLISECOND for TIMESTAMPADD/TIMESTAMPDIFF functions. And I checked MySql[1], which can't handle unit of MILLISECOND too. Is there any reason to not support unit of MILLISECOND of the two function? [1] [https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampadd] -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3529) TIMESTAMPDIFF function may overflow when handle unit of MICROSECOND/NANOSECOND
Zhenghua Gao created CALCITE-3529: - Summary: TIMESTAMPDIFF function may overflow when handle unit of MICROSECOND/NANOSECOND Key: CALCITE-3529 URL: https://issues.apache.org/jira/browse/CALCITE-3529 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.21.0 Reporter: Zhenghua Gao For unit of NANOSECOND, the TIMESTAMPDIFF function returns a BIGINT, which may overflow since BIGINT can't cover the huge range of nanosecond. For unit of MICROSECOND, the TIMESTAMPDIFF function returns a INTEGER, which may overflow too. TIMESTAMPDIFF(MICROSECOND, TIMESTAMP '-12-31 23:59:59.999', TIMESTAMP '0001-01-01 00:00:00.000') should returns '31553789759000' [1], calcite returns '879764032'. TIMESTAMPDIFF(NANOSECOND, TIMESTAMP '-12-31 23:59:59.999', TIMESTAMP '0001-01-01 00:00:00.000') should returns '31553789759000*000*' which is bigger than LONG.MAX_VALUE, calcite returns '-1943248345937622528'. My suggestion is: # Change the return type to BIGINT for unit of MICROSECOND # Disable support for unit of NANOSECOND or give a RISK message in the document of TIMESTAMPDIFF [1] the value is calculated by MySQL 5.6 from [http://sqlfiddle.com/] -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3530) TIMESTAMPADD/TIMESTAMPDIFF with microsecond/nanosecond unit lost precision
Zhenghua Gao created CALCITE-3530: - Summary: TIMESTAMPADD/TIMESTAMPDIFF with microsecond/nanosecond unit lost precision Key: CALCITE-3530 URL: https://issues.apache.org/jira/browse/CALCITE-3530 Project: Calcite Issue Type: Bug Components: core Reporter: Zhenghua Gao Since the TimestampAddConvertlet and TimestampDiffConvertlet tread TIMESTAMP as long (with millisecond precision), they lost precision even if the downstream can support microsecond or nanosecond. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3597) The conversion between java.sql.Timestamp and long is not asymmetric
Zhenghua Gao created CALCITE-3597: - Summary: The conversion between java.sql.Timestamp and long is not asymmetric Key: CALCITE-3597 URL: https://issues.apache.org/jira/browse/CALCITE-3597 Project: Calcite Issue Type: Bug Components: core Reporter: Zhenghua Gao In Calcite, we use SqlFunctions.toLong(Timestamp) and SqlFunctions.internalToTimestamp(long) to convert java.sql.Timestmap to internal long and vice versa. The main logical inside is +/- local time zone offset. But in the comments of TimeZone.getOffset(long date), the parameter represents in milliseconds since January 1, 1970 00:00:00 GMT. It means that there will one conversion above doesn't satisfy this hypothesis. This causes many surprise to users: (1) some Daylight Saving Time changes: {code:java} @Test public void testDayLightingSaving() { TimeZone tz = TimeZone.getDefault(); TimeZone.setDefault(TimeZone.getTimeZone("America/Los_Angeles")); java.sql.Timestamp dst2018Begin = java.sql.Timestamp.valueOf("2018-03-11 03:00:00"); assertThat(dst2018Begin, is(internalToTimestamp(toLong(dst2018Begin; TimeZone.setDefault(tz); }{code} fails with: {code:java} java.lang.AssertionError: Expected: is <2018-03-11 04:00:00.0> but: was <2018-03-11 03:00:00.0> Expected :is <2018-03-11 04:00:00.0> Actual :<2018-03-11 03:00:00.0>{code} (2) "1900-01-01 00:00:00" Changes in some TimeZone {code:java} @Test public void test() { TimeZone tz = TimeZone.getDefault(); TimeZone.setDefault(TimeZone.getTimeZone("Asia/Shanghai")); java.sql.Timestamp ts = java.sql.Timestamp.valueOf("1900-01-01 00:00:00"); assertThat(ts, is(internalToTimestamp(toLong(ts; TimeZone.setDefault(tz); }{code} fails with {code:java} java.lang.AssertionError: Expected: is <1899-12-31 23:54:17.0> but: was <1900-01-01 00:00:00.0> Expected :is <1899-12-31 23:54:17.0> Actual :<1900-01-01 00:00:00.0> {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3611) Introduce TIME/TIMESTAMP WITH TIME ZONE types
Zhenghua Gao created CALCITE-3611: - Summary: Introduce TIME/TIMESTAMP WITH TIME ZONE types Key: CALCITE-3611 URL: https://issues.apache.org/jira/browse/CALCITE-3611 Project: Calcite Issue Type: New Feature Reporter: Zhenghua Gao As we discussed in ML [1], this umbrella issue would introduce TIME/TIMESTAMP WITH TIME ZONE types in Calcite, and the follow-up works. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3612) Add TIME/TIMESTAMP WITH TIME ZONE in optimizer
Zhenghua Gao created CALCITE-3612: - Summary: Add TIME/TIMESTAMP WITH TIME ZONE in optimizer Key: CALCITE-3612 URL: https://issues.apache.org/jira/browse/CALCITE-3612 Project: Calcite Issue Type: Sub-task Components: core Reporter: Zhenghua Gao -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3613) Support TIME/TIMESTAMP WITH TIME ZONE in Calcite SQL parser and builtin functions
Zhenghua Gao created CALCITE-3613: - Summary: Support TIME/TIMESTAMP WITH TIME ZONE in Calcite SQL parser and builtin functions Key: CALCITE-3613 URL: https://issues.apache.org/jira/browse/CALCITE-3613 Project: Calcite Issue Type: Sub-task Reporter: Zhenghua Gao -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3614) Support TIME/TIMESTAMP WITH TIME ZONE literals
Zhenghua Gao created CALCITE-3614: - Summary: Support TIME/TIMESTAMP WITH TIME ZONE literals Key: CALCITE-3614 URL: https://issues.apache.org/jira/browse/CALCITE-3614 Project: Calcite Issue Type: Sub-task Reporter: Zhenghua Gao -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3615) Improve JDBC driver to support java.time classes
Zhenghua Gao created CALCITE-3615: - Summary: Improve JDBC driver to support java.time classes Key: CALCITE-3615 URL: https://issues.apache.org/jira/browse/CALCITE-3615 Project: Calcite Issue Type: Sub-task Reporter: Zhenghua Gao -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3881) SqlFunctions#addMonths yields incorrect results in some corner case
Zhenghua Gao created CALCITE-3881: - Summary: SqlFunctions#addMonths yields incorrect results in some corner case Key: CALCITE-3881 URL: https://issues.apache.org/jira/browse/CALCITE-3881 Project: Calcite Issue Type: Bug Components: avatica Affects Versions: avatica-1.16.0 Reporter: Zhenghua Gao SqlFunctions#addMonths use DateTimeUtils#ymdToUnixDate to calculate the JDN(julian day number). But in some corner cases it yields incorrent results. The root cause is: the algorithm of DateTimeUtils#ymdToUnixDate requires reasonable month(1 to 12)[1], but SqlFunctions#addMonths may pass in a month out of the reasonable range. BTW: I didn't find the reference of the original paper of the algorithm, but an jdn explanation. Please correct me if anyone can find the original paper. The following case can reproduce the bug: {code:java} @Test public void testAddMonths() { checkAddMonths(2019, 9, 1, 2020, 3, 1, 6); } {code} [1] [http://www.cs.utsa.edu/~cs1063/projects/Spring2011/Project1/jdn-explanation.html] -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-1558) Wrong field mapping of top level aggregate for cases when groupKey is used in aggregate function
Zhenghua Gao created CALCITE-1558: - Summary: Wrong field mapping of top level aggregate for cases when groupKey is used in aggregate function Key: CALCITE-1558 URL: https://issues.apache.org/jira/browse/CALCITE-1558 Project: Calcite Issue Type: Bug Reporter: Zhenghua Gao Assignee: Julian Hyde In AggregateExpandDistinctAggregatesRule.convertSingletonDistinct, if the groupKey is also used in some aggregate function, the field mappting of top level aggregate would be messy. Bad cases are: // Equivalent SQL: // SELECT deptno, COUNT(deptno), SUM(DISTINCT sal) // FROM emp // GROUP BY deptno // SELECT deptno, SUM(cnt), SUM(sal) // FROM // SELECT deptno, COUNT(deptno) AS cnt, sal // FROM emp // GROUP BY deptno, sal // GROUP BY deptno or a more complex case: // Equivalent SQL: // SELECT deptno, SUM(deptno), SUM(DISTINCT sal), MAX(deptno), MAX(comm) // FROM emp // GROUP BY deptno // SELECT deptno, SUM(sumOfInnerComm), SUM(sal), MAX(maxOfInnerDeptno), MAX(maxOfInnerComm) // FROM // SELECT deptno, sal, SUM(deptno) as sumOfInnerDeptno, MAX(deptno) as maxOfInnerDeptno, MAX(comm) AS maxOfInnerComm // FROM emp // GROUP BY deptno, sal // GROUP BY deptno I have fixed these cases, and will provide a patch later after more tests. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (CALCITE-4295) The minMin function of CompositeOperandTypeChecker should returns the minimum of lower bound of all SqlOperandCountRange
Zhenghua Gao created CALCITE-4295: - Summary: The minMin function of CompositeOperandTypeChecker should returns the minimum of lower bound of all SqlOperandCountRange Key: CALCITE-4295 URL: https://issues.apache.org/jira/browse/CALCITE-4295 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.25.0 Reporter: Zhenghua Gao If we define a type checker with: ``` OperandTypes.or( OperandTypes.variadic(SqlOperandCountRanges.from(3), OperandTypes.variadic(SqlOperandCountRanges.from(4)) ``` will throw a java.lang.IllegalArgumentException -- This message was sent by Atlassian Jira (v8.3.4#803005)