[ https://issues.apache.org/jira/browse/CALCITE-2265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16484278#comment-16484278 ]
Julian Hyde commented on CALCITE-2265: -------------------------------------- Can you please rebase on master branch? There are conflicts. > 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)