#1728 Union of DECIMAL and INTEGER in subqueries throws general error

[Pijus Krisiukenas]

The code below performs a UNION of SELECT queries containing subqueries as an attribute. The subqueries contain different but compatible types INTEGER and DECIMAL.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class UnionTest {

    public static void main(String[] args) throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:mymemdb", "SA", "");
        Statement statement = connection.createStatement();
        statement.execute("create table table1 (int int, dec dec)");
        statement.execute("insert into table1 values (1, 4.5)");

        statement.execute(
        "SELECT (SELECT int FROM table1) FROM table1 " +
            "UNION " +
            "SELECT (SELECT dec FROM table1) FROM table1"
        );

        statement.execute(
        "SELECT (SELECT dec FROM table1) FROM table1 " +
            "UNION " +
            "SELECT (SELECT int FROM table1) FROM table1"
        );

    }
}

Using HSQLDB 2.7.4, the code above throws a general exception. The order of UNION select statements matters: the exception is thrown only at the second retrieve statement.

Exception in thread "main" java.sql.SQLException: General error
    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 com.mendix.test.retrieves.oql.union.UnionTest.main(UnionTest.java:22)
Caused by: org.hsqldb.HsqlException: General error
    at org.hsqldb.error.Error.error(Unknown Source)
    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.StatementDMQL.execute(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 3 more

[Fred Toussi]

Thanks for reporting. You need to cast one or both of the column references for this type of query to work. This may be fixed in a future version.