[jira] [Commented] (IGNITE-14597) Calcite engine. Exception when using FIRST
[
https://issues.apache.org/jira/browse/IGNITE-14597?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17402218#comment-17402218
]
Aleksey Plekhanov commented on IGNITE-14597:
[~tledkov-gridgain], thanks for the review! Merged to sql-calcite branch.
> Calcite engine. Exception when using FIRST
> --
>
> Key: IGNITE-14597
> URL: https://issues.apache.org/jira/browse/IGNITE-14597
> Project: Ignite
> Issue Type: Bug
> Components: sql
>Reporter: Konstantin Orlov
>Assignee: Aleksey Plekhanov
>Priority: Major
> Labels: calcite2-required, calcite3-required
> Time Spent: 40m
> Remaining Estimate: 0h
>
> Exception is thrown when running a query {{SELECT FIRST(NULL::DECIMAL)}}
> {code:java}
> class org.apache.ignite.IgniteException: Error at:
> decimal_aggregates.test:10. sql: SELECT FIRST(NULL::DECIMAL)
> at
> org.apache.ignite.internal.processors.query.calcite.logical.SqlScriptRunner$Query.execute(SqlScriptRunner.java:518)
> at
> org.apache.ignite.internal.processors.query.calcite.logical.SqlScriptRunner.run(SqlScriptRunner.java:93)
> at
> org.apache.ignite.internal.processors.query.calcite.logical.ScriptTestRunner$1.run(ScriptTestRunner.java:219)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: class
> org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to
> plan query.
> at
> org.apache.ignite.internal.processors.query.calcite.exec.ExecutionServiceImpl.prepareQuery(ExecutionServiceImpl.java:541)
> at
> org.apache.ignite.internal.processors.query.calcite.prepare.QueryPlanCacheImpl.queryPlan(QueryPlanCacheImpl.java:84)
> at
> org.apache.ignite.internal.processors.query.calcite.exec.ExecutionServiceImpl.executeQuery(ExecutionServiceImpl.java:398)
> at
> org.apache.ignite.internal.processors.query.calcite.CalciteQueryProcessor.query(CalciteQueryProcessor.java:246)
> at
> org.apache.ignite.internal.processors.query.calcite.logical.SqlScriptRunner.sql(SqlScriptRunner.java:111)
> at
> org.apache.ignite.internal.processors.query.calcite.logical.SqlScriptRunner.access$600(SqlScriptRunner.java:51)
> at
> org.apache.ignite.internal.processors.query.calcite.logical.SqlScriptRunner$Query.execute(SqlScriptRunner.java:513)
> ... 3 more
> Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1,
> column 8 to line 1, column 27: Function 'FIRST(NULL :: DECIMAL, 0)' can only
> be used in MATCH_RECOGNIZE
> 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:467)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:883)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:868)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5043)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:5574)
> at
> org.apache.ignite.internal.processors.query.calcite.prepare.IgniteSqlValidator.validateCall(IgniteSqlValidator.java:230)
> at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:116)
> at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:273)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4259)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4234)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3474)
> at
> org.apache.ignite.internal.processors.query.calcite.prepare.IgniteSqlValidator.validateSelect(IgniteSqlValidator.java:176)
> 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:1067)
> at
> org.apache.ignite.internal.processors.query.calcite.prepare.IgniteSqlValidator.validateNamespace(IgniteSqlValidator.java:190)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1041)
> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(Sq
[jira] [Commented] (IGNITE-14597) Calcite engine. Exception when using FIRST
[
https://issues.apache.org/jira/browse/IGNITE-14597?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17359881#comment-17359881
]
Aleksey Plekhanov commented on IGNITE-14597:
I propose to use {{ANY_VALUE}} aggregates in cases where {{FIRST}} aggregate is
used in scripts, looks like it covers all needs. {{FIRST}} - is not a standard
SQL aggregate. SQL have {{FIRST_VALUE}}/{{LAST_VALUE}}, but these aggregates
can only be used with windows (also see my [link
comment|https://issues.apache.org/jira/browse/IGNITE-14443?focusedCommentId=17359877&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-17359877])
Patch with {{ANY_VALUE}} is ready. [~korlov], [~tledkov-gridgain] can you
please have a look?
> Calcite engine. Exception when using FIRST
> --
>
> Key: IGNITE-14597
> URL: https://issues.apache.org/jira/browse/IGNITE-14597
> Project: Ignite
> Issue Type: Bug
> Components: sql
>Reporter: Konstantin Orlov
>Assignee: Aleksey Plekhanov
>Priority: Major
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Exception is thrown when running a query {{SELECT FIRST(NULL::DECIMAL)}}
> {code:java}
> class org.apache.ignite.IgniteException: Error at:
> decimal_aggregates.test:10. sql: SELECT FIRST(NULL::DECIMAL)
> at
> org.apache.ignite.internal.processors.query.calcite.logical.SqlScriptRunner$Query.execute(SqlScriptRunner.java:518)
> at
> org.apache.ignite.internal.processors.query.calcite.logical.SqlScriptRunner.run(SqlScriptRunner.java:93)
> at
> org.apache.ignite.internal.processors.query.calcite.logical.ScriptTestRunner$1.run(ScriptTestRunner.java:219)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: class
> org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to
> plan query.
> at
> org.apache.ignite.internal.processors.query.calcite.exec.ExecutionServiceImpl.prepareQuery(ExecutionServiceImpl.java:541)
> at
> org.apache.ignite.internal.processors.query.calcite.prepare.QueryPlanCacheImpl.queryPlan(QueryPlanCacheImpl.java:84)
> at
> org.apache.ignite.internal.processors.query.calcite.exec.ExecutionServiceImpl.executeQuery(ExecutionServiceImpl.java:398)
> at
> org.apache.ignite.internal.processors.query.calcite.CalciteQueryProcessor.query(CalciteQueryProcessor.java:246)
> at
> org.apache.ignite.internal.processors.query.calcite.logical.SqlScriptRunner.sql(SqlScriptRunner.java:111)
> at
> org.apache.ignite.internal.processors.query.calcite.logical.SqlScriptRunner.access$600(SqlScriptRunner.java:51)
> at
> org.apache.ignite.internal.processors.query.calcite.logical.SqlScriptRunner$Query.execute(SqlScriptRunner.java:513)
> ... 3 more
> Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1,
> column 8 to line 1, column 27: Function 'FIRST(NULL :: DECIMAL, 0)' can only
> be used in MATCH_RECOGNIZE
> 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:467)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:883)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:868)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5043)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:5574)
> at
> org.apache.ignite.internal.processors.query.calcite.prepare.IgniteSqlValidator.validateCall(IgniteSqlValidator.java:230)
> at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:116)
> at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:273)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4259)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4234)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3474)
> at
> org.apache.ignite.internal.processors.query.calcite.prepare.IgniteSqlValidator.validateSelect(IgniteSqlValidator.java:176)
> 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(SqlValida
