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

["Dylan Adams (JIRA)" <ji...@apache.org>]

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

Dylan Adams commented on CALCITE-2265:
--------------------------------------

It seems like the conversion to the equivalent OR would be good feature to have. But it'd be unnecessary for HSQLDB or Postgres, which have good support for ROW comparison.

I did a bit of debugging and the cast is being added in StandardConvertletTable.convertExpressionList. The types of the two ROW operands are RecordType(VARCHAR(10), VARCHAR(9)) and RecordType(CHAR(5),CHAR(7)) for the column and literal rows, respectively.

The casts are added because StandardConvertletTable.consistentType doesn't consider structs when it checks to see if the input types have the same families.

PR: https://github.com/apache/calcite/pull/696

 

> 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
>
> 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)