[
https://issues.apache.org/jira/browse/CALCITE-7435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18063846#comment-18063846
]
Zhen Chen commented on CALCITE-7435:
------------------------------------
I run these case in blank.iq. The last create statement will throw an error,
but I'm not sure if it's the problem you're referring to.
{code:java}
CREATE TABLE data (
t0 TIMESTAMP NOT NULL,
location INT NOT NULL
);
(0 rows modified)
!update
CREATE VIEW IT AS SELECT (t0 - TIMESTAMP '2020-01-01 00:00:00') SECOND AS t,
location FROM data;
(0 rows modified)
!update
CREATE VIEW V AS
SELECT
*,
COUNT(*) OVER(
PARTITION BY location
ORDER BY t
RANGE BETWEEN INTERVAL '2' DAYS PRECEDING AND INTERVAL '1' DAYS
PRECEDING) AS c
FROM IT;
(0 rows modified)
!update {code}
Error message:
> java.sql.SQLException: Error while executing SQL "CREATE VIEW V AS
> SELECT
> *,
> COUNT(*) OVER(
> PARTITION BY location
> ORDER BY t
> RANGE BETWEEN INTERVAL '2' DAYS PRECEDING AND INTERVAL '1' DAYS
> PRECEDING) AS c
> FROM IT": From line 1, column 77 to line 1, column 92: Data type of ORDER BY
> prohibits use of RANGE clause
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeLargeUpdate(AvaticaStatement.java:247)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeUpdate(AvaticaStatement.java:241)
> at net.hydromatic.quidem.Quidem.update(Quidem.java:268)
> at net.hydromatic.quidem.Quidem.access$2700(Quidem.java:54)
> at net.hydromatic.quidem.Quidem$ContextImpl.update(Quidem.java:1787)
> at net.hydromatic.quidem.Quidem$UpdateCommand.execute(Quidem.java:1090)
> at net.hydromatic.quidem.Quidem$CompositeCommand.execute(Quidem.java:1522)
> at net.hydromatic.quidem.Quidem.execute(Quidem.java:204)
> at org.apache.calcite.test.QuidemTest.checkRun(QuidemTest.java:353)
> at org.apache.calcite.test.QuidemTest.test(QuidemTest.java:543)
> at org.apache.calcite.test.CoreQuidemTest.main(CoreQuidemTest.java:54)
> WINDOW functions do not allolw ORDER BY fields of type INTERVAL
> ---------------------------------------------------------------
>
> Key: CALCITE-7435
> URL: https://issues.apache.org/jira/browse/CALCITE-7435
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Mihai Budiu
> Priority: Minor
>
> {code:java}
> CREATE TABLE data (
> t0 TIMESTAMP NOT NULL,
> location INT NOT NULL
> );
>
> CREATE VIEW IT AS SELECT (t0 - TIMESTAMP '2020-01-01 00:00:00') SECOND AS t,
> location FROM data;
>
> CREATE VIEW V AS
> SELECT
> *,
> COUNT(*) OVER(
> PARTITION BY location
> ORDER BY t
> RANGE BETWEEN INTERVAL '2' DAYS PRECEDING AND INTERVAL '1' DAYS
> PRECEDING) AS c
> FROM IT; {code}
> Calcite's syntax for arithmetic on timestamps is not accepted by other
> databases, so I could not validate this query, but I see no reason it should
> be rejected.
> Validation fails in SqlTypeFamily.allowableDifferenceTypes, which only
> accepts NUMERIC, DATE, TIME, and TIMESTAMP types. But since INTERVAL types
> support the minus operation, I think they should be allowed too.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
