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