Search code examples
sqlhsqldb

HSQLDB: Duplicate column name, unsupported internal operation: Type, invalid character for cast

I am beginning with HSQLDB, but I hit this issue and can't figure out what's going on:

CREATE TEXT TABLE ozizkaindata (
  jobName VARCHAR(255) NOT NULL,
  buildNumber VARCHAR(255) NOT NULL,
  config VARCHAR(255) NOT NULL,
  ar VARCHAR(255) NOT NULL,
  arFile VARCHAR(255) NOT NULL,
  deployDur VARCHAR(255) NOT NULL,
  warmupDur VARCHAR(255) NOT NULL,
  scale VARCHAR(255) NOT NULL
)

SELECT *, ((0.0 + warmupDur) / deployDur) AS xwarmupSlower FROM ozizkaindata ORDER BY deployDur

Throws:

Caused by: org.hsqldb.HsqlException: duplicate column name in derived table
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.QuerySpecification.addSelectColumnExpression(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSelect(Unknown Source)
    ...

SELECT ((0.0 + warmupDur) / deployDur) FROM ozizkaindata ORDER BY deployDur

Throws: 

Caused by: java.lang.RuntimeException: unsupported internal operation: Type
    at org.hsqldb.error.Error.runtimeError(Unknown Source)
    at org.hsqldb.types.Type.divide(Unknown Source)
    at org.hsqldb.ExpressionArithmetic.getValue(Unknown Source)
    at org.hsqldb.QuerySpecification.buildResult(Unknown Source)
    at org.hsqldb.QuerySpecification.getSingleResult(Unknown Source)
    at org.hsqldb.QuerySpecification.getResult(Unknown Source)
    at org.hsqldb.StatementQuery.getResult(Unknown Source)

SELECT ((0.0 + CAST(warmupDur AS INT)) / CAST(deployDur AS INT)) AS xwarmupSlower FROM ozizkaindata

Throws:

Caused by: org.hsqldb.HsqlException: data exception: invalid character value for cast
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.Scanner.convertToNumber(Unknown Source)
    at org.hsqldb.types.NumberType.convertToType(Unknown Source)
    at org.hsqldb.types.Type.castToType(Unknown Source)
    at org.hsqldb.ExpressionOp.getValue(Unknown Source)
    at org.hsqldb.ExpressionArithmetic.getValue(Unknown Source)
    at org.hsqldb.ExpressionArithmetic.getValue(Unknown Source)
    at org.hsqldb.QuerySpecification.buildResult(Unknown Source)
    at org.hsqldb.QuerySpecification.getSingleResult(Unknown Source)
    at org.hsqldb.QuerySpecification.getResult(Unknown Source)
Solution

  • All right, this was easy. I forgot to ignore the first line of my imported text data with a comment, so it was invalid syntax. What a shame :)

    Edit 2022:
    As I recall, I imported some data from CSV, and HSQLDB did not complain about a wrong format, until I queried it. On querying, it threw the errors above. However, the error message was not too clear.

    The current HSQLDB may provide better hints.

    The "duplicate column name in derived table" error may be caused by HSQLDB turning all names to upper case; and may be handled by escaping the names.