#1396 "Error: Incompatible data types" thrown during CASE Statement with Sub-Query

[YeongWei]

HSQLDB Version: 2.3.2
SQL Client: SQuirreL SQL Client Version 3.5.0

Error below is thrown,

Error: incompatible data types in combination
SQLState: 42562
ErrorCode: -5562

while running the SQL below,

SELECT 
    CASE (
        SELECT 
            COUNT("ENAME")
        FROM 
            "SCOTT"."EMP"
        WHERE 
            "JOB" = 'PRESIDENT'
        GROUP BY 
            "ENAME") 
    WHEN 0 THEN NULL 
    WHEN 1 THEN "ENAME" 
    ELSE (
        SELECT * FROM (SELECT * FROM (VALUES 1)
        UNION ALL
        SELECT * FROM (VALUES 1))) 
    END AS "$f0"
FROM 
    "SCOTT"."EMP"
WHERE 
    "JOB" = 'PRESIDENT'

Procedure to replicate,

1) Run the DDL.sql to create the Schema and Tables.
2) Run the DML.sql to populate data into the Tables.
3) Run the SQL as decsribed above.

Note,

1) This may be related to https://sourceforge.net/p/hsqldb/bugs/1393/ as I am currently looking into the https://issues.apache.org/jira/browse/CALCITE-259, to rewrite queries and push it down to the underlying data source (in my case HSQLDB).

Thanks!

[Fred Toussi]

The query looks wrong in general. The CASE is supposed to return a single value, but the SELECT below returns two rows (wrong) and the value is an INTEGER (wrong) because you want an alternative to "ENAME" which is a VARCHAR.

SELECT * FROM (SELECT * FROM (VALUES 1)

    UNION ALL

    SELECT * FROM (VALUES 1)))

Please check with the project what this is supposed to returned and I will help you with the query.

[Fred Toussi]

I think the intention is to return NULL when there is no PRESIDENT and throw an exception when there is more than one. This can be done very simply:

SELECT 
    (SELECT 
       "ENAME"
    FROM 
        "SCOTT"."EMP"
    WHERE 
        "JOB" = 'PRESIDENT'
    )
AS "$f0"

FROM (VALUES 1)

Note: updated query to return always 1 row with null or value (or exception when more than one president)

[YeongWei]

Hi Fred,

Appreciate with your responses. I apologize for not stating more information about the query presented in the description section above.

You are right that the purpose of the query is to throw the "more than one row" exception if the "COUNT(x)" is more than 1 row.

To add more information, the original query is actually,

SELECT SINGLE_VALUE("ENAME") FROM "SCOTT"."EMP" WHERE "JOB" = 'PRESIDENT'

The SINGLE_VALUE is an User-Defined function that is evaluated after some database scan occurred. This SINGLE_VALUE will inspect if the returned result set is 1 row else will bubble up the appropriate exception to the client. (An implementation of Calcite, https://github.com/apache/incubator-calcite)

Therefore the original query is rewritten into the one with the CASE Statement above and pushed down to the underlying database to be evaluated, there on the CASE Statement would capture the exception for scalar sub-query with more than one row.

Based on your response earlier,

1) It seems that HSQLDB expected the "THEN" Operands to have consistent data types? E.g. The "WHEN 1" clause returns the String type but the "ELSE" clause returns the Numeric type.

2) Can #1) to be made into an enhancement request? Say to have the return types to be converted into some common data types?

Thanks!

[Fred Toussi]

In Standard SQL, the values from "CASE a WHEN b" parts must be the same or convertible data type. Values of the "THEN x ELSE y" parts must be convertible. But there is no need for "a" and "x" to be convertible. In this case you could just use a string, such as "VALUES '1'" instead of "VALUES 1". For other cases, you can use CAST in the CASE expression.

Regarding the translation of SINGLE_VALUE("ENAME") function, it is possible to write the query to return this result:

Option 1: NULL when no rows, value when one row, exception when more than one row

Or to return this result

Option 2: exception when no rows, value when one row, exception when more than one row

So which option do you need?

[YeongWei]

Hi Fred,

Appreciate with the explanations and suggestions.

Allow me to have some time to discuss with my team and get back to you as soon as possible.

Thanks!

[Fred Toussi]

See updated query.

[YeongWei]

Hi Fred,

Appreciate if you could point out which updated query you are referring to.

Thanks!

[Fred Toussi]

The one like this returns one row with null or one value. It throws if there are two rows:

SELECT 
    (SELECT 
       "ENAME"
    FROM 
        "SCOTT"."EMP"
    WHERE 
        "JOB" = 'PRESIDENT'
    )
AS "$f0"

FROM (VALUES 1)

[Fred Toussi]

This is the original query with a minor correction to change the type of the result of UNION ALL to CHARACTER and avoid the type compatibility error. The short alternative given in the previous answer is obviously better.

SELECT
CASE (
SELECT COUNT("ENAME")
FROM "SCOTT"."EMP"
WHERE "JOB" = 'PRESIDENT'
GROUP BY "ENAME")
WHEN 0 THEN NULL
WHEN 1 THEN "ENAME"
ELSE (
SELECT * FROM (SELECT * FROM (VALUES '1')
UNION ALL
SELECT * FROM (VALUES '1')))
END AS "$f0"
FROM
"SCOTT"."EMP"
WHERE
"JOB" = 'PRESIDENT'