[
https://issues.apache.org/jira/browse/CALCITE-2643?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Wamsi Viswanath updated CALCITE-2643:
-------------------------------------
Description:
Hello,
I have the below two issues which require assistance:
1)
Calcite is automatically generating TIMSTAMP(3) casts even though we explicitly
mention TIMSTAMP(6) or TIMSTAMP(9). Please see the below query and generated
calcite:
{code:java}
explain calcite select cast(m_9 as timestamp(6)) from test;
-------------------------------------------------------------
Explanation
LogicalProject(EXPR$0=[CAST($19):TIMESTAMP(3)])
EnumerableTableScan(table=[[mapd, test]])
select cast(m_9 as timestamp(6)) from test limit 2;
-----------------------------------------------------
2006-04-26 03:49:04.607
2006-04-26 03:49:04.607
explain calcite select cast(m_6 as timestamp(9)) from test limit 2;
---------------------------------------------------------------------------
Explanation
LogicalSort(fetch=[2])
LogicalProject(EXPR$0=[CAST($18):TIMESTAMP(3)])
EnumerableTableScan(table=[[mapd, test]]){code}
I found there was a related issue regarding this and was mentioned that it was
fixed in `1.13.0`, we are using `1.16.0` and the problem still persists.
[https://jira.apache.org/jira/browse/CALCITE-1690]
2)
I have a column name m_3 which is TIMESTAMP(3). When I am doing comparison
operations using `m_3` is get wrong results. I found reason to be calcite
actually interpreting it as TIMESTAMP(0) instead of TIMESTAMP(3) and therefore
end up generating two casts for the literal.
{code:java}
DDL Statement
---------------------------------------------------------------------------------
CREATE TABLE test (
m TIMESTAMP(0),
m_3 TIMESTAMP(3),
m_6 TIMESTAMP(6),
m_9 TIMESTAMP(9)))
----------------------------------------------------------------------------------
explain calcite SELect m_3 from test where m_3 = TIMESTAMP(3) '2014-12-13
22:23:15.323';
Explanation
LogicalProject(m_3=[$17])
LogicalFilter(condition=[=($17, CAST(CAST('2014-12-13
22:23:15.323'):TIMESTAMP(3) NOT NULL):TIMESTAMP(0) NOT NULL)])
EnumerableTableScan(table=[[mapd, test]])
--------------------------------------------------------------------------------------------
explain calcite SELect m_3 + 1 = TIMESTAMP(3) '2014-12-13 22:23:15.323' from
test;
Exception: Exception occurred:
org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to
line 1, column 16: Cannot apply '+' to arguments of type '<TIMESTAMP(0)> +
<INTEGER>'. Supported form(s): '<NUMERIC> + <NUMERIC>'
'<DATETIME_INTERVAL> + <DATETIME_INTERVAL>'
'<DATETIME> + <DATETIME_INTERVAL>'
'<DATETIME_INTERVAL> + <DATETIME>'
{code}
Is there a way to tell calcite that m_3 is TIMESTAMP(3) instead of TIMESTAMP(0)
?
was:
Hello,
I have the below two issues which require assistance:
1)
Calcite is automatically generating TIMSTAMP(3) casts even though we explicitly
mention TIMSTAMP(6) or TIMSTAMP(9). Please see the below query and generated
calcite:
{code:java}
explain calcite select cast(m_9 as timestamp(6)) from test;
-------------------------------------------------------------
Explanation
LogicalProject(EXPR$0=[CAST($19):TIMESTAMP(3)])
EnumerableTableScan(table=[[mapd, test]])
select cast(m_9 as timestamp(6)) from test limit 2;
-----------------------------------------------------
2006-04-26 03:49:04.607
2006-04-26 03:49:04.607
explain calcite select cast(m_6 as timestamp(9)) from test limit 2;
---------------------------------------------------------------------------
Explanation
LogicalSort(fetch=[2])
LogicalProject(EXPR$0=[CAST($18):TIMESTAMP(3)])
EnumerableTableScan(table=[[mapd, test]]){code}
I found there was a related issue regarding this and was mentioned that it was
fixed in `1.13.0`, we are using `1.16.0` and the problem still persists.
[https://jira.apache.org/jira/browse/CALCITE-1690]
2)
I have a column name m_3 which is TIMESTAMP(3). When I am doing comparison
operations using `m_3` is get wrong results. I found reason to be calcite
actually interpreting it as TIMESTAMP(0) instead of TIMESTAMP(3) and therefore
end up generating two casts for the literal.
{code:java}
DDL Statement
CREATE TABLE test (
m TIMESTAMP(0),
m_3 TIMESTAMP(3),
m_6 TIMESTAMP(6),
m_9 TIMESTAMP(9)))
----------------------------------------------------------------------------------
explain calcite SELect m_3 from test where m_3 = TIMESTAMP(3) '2014-12-13
22:23:15.323';
Explanation
LogicalProject(m_3=[$17])
LogicalFilter(condition=[=($17, CAST(CAST('2014-12-13
22:23:15.323'):TIMESTAMP(3) NOT NULL):TIMESTAMP(0) NOT NULL)])
EnumerableTableScan(table=[[mapd, test]])
--------------------------------------------------------------------------------------------
explain calcite SELect m_3 + 1 = TIMESTAMP(3) '2014-12-13 22:23:15.323' from
test;
Exception: Exception occurred:
org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to
line 1, column 16: Cannot apply '+' to arguments of type '<TIMESTAMP(0)> +
<INTEGER>'. Supported form(s): '<NUMERIC> + <NUMERIC>'
'<DATETIME_INTERVAL> + <DATETIME_INTERVAL>'
'<DATETIME> + <DATETIME_INTERVAL>'
'<DATETIME_INTERVAL> + <DATETIME>'
{code}
Is there a way to tell calcite that m_3 is TIMESTAMP(3) instead of TIMESTAMP(0)
?
> CAST operations on microsecond and nanosecond columns getting down cast to
> TIMSTAMP(3)
> --------------------------------------------------------------------------------------
>
> Key: CALCITE-2643
> URL: https://issues.apache.org/jira/browse/CALCITE-2643
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.16.0
> Reporter: Wamsi Viswanath
> Assignee: Julian Hyde
> Priority: Major
>
> Hello,
> I have the below two issues which require assistance:
> 1)
> Calcite is automatically generating TIMSTAMP(3) casts even though we
> explicitly mention TIMSTAMP(6) or TIMSTAMP(9). Please see the below query
> and generated calcite:
>
> {code:java}
> explain calcite select cast(m_9 as timestamp(6)) from test;
> -------------------------------------------------------------
> Explanation
> LogicalProject(EXPR$0=[CAST($19):TIMESTAMP(3)])
> EnumerableTableScan(table=[[mapd, test]])
> select cast(m_9 as timestamp(6)) from test limit 2;
> -----------------------------------------------------
> 2006-04-26 03:49:04.607
> 2006-04-26 03:49:04.607
> explain calcite select cast(m_6 as timestamp(9)) from test limit 2;
> ---------------------------------------------------------------------------
> Explanation
> LogicalSort(fetch=[2])
> LogicalProject(EXPR$0=[CAST($18):TIMESTAMP(3)])
> EnumerableTableScan(table=[[mapd, test]]){code}
> I found there was a related issue regarding this and was mentioned that it
> was fixed in `1.13.0`, we are using `1.16.0` and the problem still persists.
> [https://jira.apache.org/jira/browse/CALCITE-1690]
>
> 2)
> I have a column name m_3 which is TIMESTAMP(3). When I am doing comparison
> operations using `m_3` is get wrong results. I found reason to be calcite
> actually interpreting it as TIMESTAMP(0) instead of TIMESTAMP(3) and
> therefore end up generating two casts for the literal.
>
> {code:java}
> DDL Statement
> ---------------------------------------------------------------------------------
> CREATE TABLE test (
> m TIMESTAMP(0),
> m_3 TIMESTAMP(3),
> m_6 TIMESTAMP(6),
> m_9 TIMESTAMP(9)))
> ----------------------------------------------------------------------------------
> explain calcite SELect m_3 from test where m_3 = TIMESTAMP(3) '2014-12-13
> 22:23:15.323';
> Explanation
> LogicalProject(m_3=[$17])
> LogicalFilter(condition=[=($17, CAST(CAST('2014-12-13
> 22:23:15.323'):TIMESTAMP(3) NOT NULL):TIMESTAMP(0) NOT NULL)])
> EnumerableTableScan(table=[[mapd, test]])
> --------------------------------------------------------------------------------------------
> explain calcite SELect m_3 + 1 = TIMESTAMP(3) '2014-12-13 22:23:15.323' from
> test;
> Exception: Exception occurred:
> org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to
> line 1, column 16: Cannot apply '+' to arguments of type '<TIMESTAMP(0)> +
> <INTEGER>'. Supported form(s): '<NUMERIC> + <NUMERIC>'
> '<DATETIME_INTERVAL> + <DATETIME_INTERVAL>'
> '<DATETIME> + <DATETIME_INTERVAL>'
> '<DATETIME_INTERVAL> + <DATETIME>'
> {code}
>
>
> Is there a way to tell calcite that m_3 is TIMESTAMP(3) instead of
> TIMESTAMP(0) ?
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
