[jira] [Resolved] (CALCITE-2265) Strange cast created for ROW comparison

["Julian Hyde (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-2265?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde resolved CALCITE-2265.
----------------------------------
       Resolution: Fixed
    Fix Version/s: 1.17.0

Fixed in [da568324|http://git-wip-us.apache.org/repos/asf/calcite/commit/da568324]; thanks for the PR, [~dadams]!

> Strange cast created for ROW comparison
> ---------------------------------------
>
>                 Key: CALCITE-2265
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2265
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.16.0
>            Reporter: Dylan Adams
>            Assignee: Julian Hyde
>            Priority: Major
>             Fix For: 1.17.0
>
>
> A comparison using ROWs results in queries that can't be executed by the database due to strange casts like 
> {noformat}
> CAST(ROW('Blake', 'Manager') AS ROW){noformat}
> Direct query against HSQLDB:
>  
> {noformat}
> sqlline>  !connect jdbc:hsqldb:res:scott SCOTT TIGER
> ...
> 0: jdbc:hsqldb:res:scott> SELECT empno FROM emp WHERE (ename, job) < ('Blake', 'Manager');
> +--------+
> | EMPNO  |
> +--------+
> | 7499   |
> | 7698   |
> | 7876   |
> +--------+{noformat}
>  
> Query through calcite:
>  
> {noformat}
> sqlline> !connect jdbc:calcite:schemaType=jdbc;schema.jdbcDriver=org.hsqldb.jdbcDriver;schema.jdbcUrl=jdbc:hsqldb:res:scott;schema.jdbcUser=SCOTT;schema.jdbcPassword=TIGER SCOTT TIGER
> Connecting to jdbc:calcite:schemaType=jdbc;schema.jdbcDriver=org.hsqldb.jdbcDriver;schema.jdbcUrl=jdbc:hsqldb:res:scott;schema.jdbcUser=SCOTT;schema.jdbcPassword=TIGER
> ...
> Connected to: Calcite (version 1.17.0-SNAPSHOT)
> Driver: Calcite JDBC Driver (version 1.17.0-SNAPSHOT)
> Autocommit status: true
> Transaction isolation: TRANSACTION_REPEATABLE_READ
> 0: jdbc:calcite:schemaType=jdbc> SELECT empno FROM emp WHERE (ename, job) < ('Blake', 'Manager');
> Error: Error while executing SQL "SELECT empno FROM emp WHERE (ename, job) < ('Blake', 'Manager')": while executing SQL [SELECT "EMPNO"
> FROM "EMP"
> WHERE ROW("ENAME", "JOB") < CAST(ROW('Blake', 'Manager') AS ROW)] (state=,code=0)
> java.sql.SQLException: Error while executing SQL "SELECT empno FROM emp WHERE (ename, job) < ('Blake', 'Manager')": while executing SQL [SELECT "EMPNO"
> FROM "EMP"
> WHERE ROW("ENAME", "JOB") < CAST(ROW('Blake', 'Manager') AS ROW)]
> 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:156)
> at org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:209)
> at sqlline.Commands.execute(Commands.java:823)
> at sqlline.Commands.sql(Commands.java:733)
> at sqlline.SqlLine.dispatch(SqlLine.java:795)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
> Caused by: java.lang.RuntimeException: while executing SQL [SELECT "EMPNO"
> FROM "EMP"
> WHERE ROW("ENAME", "JOB") < CAST(ROW('Blake', 'Manager') AS ROW)]
> at org.apache.calcite.runtime.ResultSetEnumerable.enumerator(ResultSetEnumerable.java:153)
> at org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:82)
> at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:174)
> at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:67)
> at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:44)
> at org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:630)
> at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:619)
> at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:638)
> at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:149)
> ... 7 more
> Caused by: java.sql.SQLSyntaxErrorException: type not found or user lacks privilege: ROW
> at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
> at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
> at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
> at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
> at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
> at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
> at org.apache.calcite.runtime.ResultSetEnumerable.enumerator(ResultSetEnumerable.java:143)
> ... 16 more
> Caused by: org.hsqldb.HsqlException: type not found or user lacks privilege: ROW
> at org.hsqldb.error.Error.error(Unknown Source)
> at org.hsqldb.error.Error.error(Unknown Source)
> at org.hsqldb.ParserDQL.readTypeDefinition(Unknown Source)
> at org.hsqldb.ParserDQL.readCastExpressionOrNull(Unknown Source)
> at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)
> at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
> at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
> at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
> at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
> at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
> at org.hsqldb.ParserDQL.XreadRowOrCommonValueExpression(Unknown Source)
> at org.hsqldb.ParserDQL.XreadRowValuePredicand(Unknown Source)
> at org.hsqldb.ParserDQL.XreadPredicateRightPart(Unknown Source)
> at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unknown Source)
> at org.hsqldb.ParserDQL.XreadBooleanTestOrNull(Unknown Source)
> at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unknown Source)
> at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknown Source)
> at org.hsqldb.ParserDQL.XreadBooleanValueExpression(Unknown Source)
> at org.hsqldb.ParserDQL.readWhereGroupHaving(Unknown Source)
> at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
> at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
> at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
> at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
> at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
> at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
> at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
> at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
> at org.hsqldb.ParserCommand.compilePart(Unknown Source)
> at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
> at org.hsqldb.Session.executeDirectStatement(Unknown Source)
> at org.hsqldb.Session.execute(Unknown Source)
> ... 21 more
> {noformat}
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)