[jira] [Commented] (CALCITE-4274) Cascaded table-valued function windowing contains ambiguous window_start/window_end columns

["Rui Wang (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4274?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17201906#comment-17201906 ] 

Rui Wang commented on CALCITE-4274:
-----------------------------------

Per discussion in email thread, the right approach is to differentiate two window starts (and ends) by alias. For example:

 

 SELECT T.window_start, T.window_end, T.timestamp, F.window_start,
F.window_end
  FROM TABLE(TUMBLE(TABLE(input_table) AS T, DESCRIPTOR(timestamp),
INTERVAL '5' MINUTES)) AS F  

> Cascaded table-valued function windowing contains ambiguous window_start/window_end columns
> -------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-4274
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4274
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.23.0
>            Reporter: liupengcheng
>            Priority: Major
>
> When executing the following sql, the ValidationException is thrown:
> sql:
> {code:java}
> with subquery1 as (select * from table(TUMBLE(table MyTable3, " +
>   "descriptor(eventtime1), interval '1' minute))) " +
>   "select window_end from table(TUMBLE(table subquery1, " +
>   "descriptor(eventtime1), interval '1' minute))
> {code}
> exceptions:
> {code:java}
> org.apache.calcite.runtime.CalciteContextException: From line 1, column 117 to line 1, column 126: Column 'window_end' is ambiguousorg.apache.calcite.runtime.CalciteContextException: From line 1, column 117 to line 1, column 126: Column 'window_end' is ambiguous at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:457) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:857) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:842) at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5114) at org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:278) at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6012) at org.apache.calcite.sql.validate.SqlValidatorImpl$SelectExpander.visit(SqlValidatorImpl.java:6175) at org.apache.calcite.sql.validate.SqlValidatorImpl$SelectExpander.visit(SqlValidatorImpl.java:6161) at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:321) at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectExpr(SqlValidatorImpl.java:5595) at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:454) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4279) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3538) at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1111) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1085) at org.apache.calcite.sql.validate.WithNamespace.validateImpl(WithNamespace.java:57) at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1111) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWith(SqlValidatorImpl.java:3929) at org.apache.calcite.sql.SqlWith.validate(SqlWith.java:71) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1060) at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:767)
> {code}
> I looked into the code, I think it's because we are now appending `window_start`/`window_end` directly when deriving row type.
> I think it's a bug in Calcite, we can fix it by deduplicate the window_start/window_end columns.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)