#1393 Values scalar sub-query causes NullPointerException

[Julian Hyde]

A query that uses VALUES inside a scalar sub-query gives a NullPointerException.

Here is the query:

select (values (1)) from "foodmart"."department";
Error: java.lang.NullPointerException java.lang.NullPointerException (state=S1000,code=-458)
java.sql.SQLException: java.lang.NullPointerException java.lang.NullPointerException
    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 sqlline.Commands.execute(Commands.java:822)
    at sqlline.Commands.sql(Commands.java:732)
    at sqlline.SqlLine.dispatch(SqlLine.java:807)
    at sqlline.SqlLine.begin(SqlLine.java:681)
    at sqlline.SqlLine.start(SqlLine.java:398)
    at sqlline.SqlLine.main(SqlLine.java:292)
Caused by: org.hsqldb.HsqlException: java.lang.NullPointerException
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.result.Result.newErrorResult(Unknown Source)
    at org.hsqldb.result.Result.newErrorResult(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 8 more
Caused by: java.lang.NullPointerException
    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.XreadValueExpression(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSelect(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)
    ... 10 more

FYI, I am developing on Apache Calcite, on https://issues.apache.org/jira/browse/CALCITE-259.

I have a workaround so I don't consider this problem to be urgent. The workaround is as follows:

select (select null from (values 1) union all select null from (values 1)) from "foodmart"."department";

Error: cardinality violation (state=21000,code=-3201)
java.sql.SQLException: cardinality violation

(Yes, the desired effect is to produce a cardinality violation.)

[Fred Toussi]

Thanks for reporting Julian. We noticed this a while back and since version 2.3.2 we do not accept (VALUES(1) as a scalar subquery. The version in your POM is 2.3.1 and I would recommended switching to 2.3.3 (to be available in May) as it resolves some issues with complex subqueries. We might support it in the future if there is another code review for this syntax. I will check CALCITE later as it looks like a very good idea.

[Julian Hyde]

Thanks for the prompt response, Fred. Will look into 2.3.3 when it ships.

Thanks for your hard work. In Calcite (also in Sqlline and Mondrian, other open source projects I contribute to a lot) we use embedded hsqldb databases for testing and I am impressed at hsqldb's speed and compliance with modern SQL standards. It has improved a lot over the last few years.

You may be interested in net.hydromatic:foodmart-data-hsqldb and net.hydromatic:scott-data-hsqldb. Databases packaged as maven JARs and available via Maven central. Very convenient for testing.

[Fred Toussi]

I was aware of your work on Mondrian. Will check those test databases. Thanks.