Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2265

Strange cast created for ROW comparison

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.16.0
    • 1.17.0
    • core
    • None

    Description

      A comparison using ROWs results in queries that can't be executed by the database due to strange casts like 

      CAST(ROW('Blake', 'Manager') AS ROW)

      Direct query against HSQLDB:

       

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

       

      Query through calcite:

       

      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
      

       

      Attachments

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            julianhyde Julian Hyde
            dadams Daniel Adams
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment