Ultralite cannot execute a select with UNION and null

[Former Member]

Hi guys,

I've found a possible bug related to Ultralite and SELECTs commands with UNION. If I try to execute a select command union all with another select command, and one of these commands have a null column, the Ultralite throws an error, or, in some cases, leaves the ResultSet in a invalid state. It seems like the Ultralite want to convert the varchar column to numeric so that it can merge the result sets.

-- Error
SELECT '22222222222222222222222222222' as c2 UNION ALL
SELECT null as c2

-- Invalid State
SELECT null as c2 UNION ALL
SELECT '22222222222222222222222222222' as c2

When an error occurs, the following message is shown:

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
[UltraLite Database] Cannot convert 22222222222222222222222222222 to a numeric
SQLCODE=-157, ODBC 3 State="07006"

Best Regards,

[Breck_Carter]

Is that your real code? Or do you have "UNION ALL SELECT something-else" that happens to be NULL?

As a workaround, try casting the null: CAST ( NULL AS VARCHAR ( 29 ) )

[Former Member]

Hi Breck. No, this isn't a real code, but we are constructing a product that is supposed to work with any type of query (other devs can use our product as a tool).

[Former Member · Accepted solution]

Thanks for reporting the problem. It appears that UL doesn't do a good job of inferring the column type when it sees the constant null in the UNION. I did a little experimentation and it seems that if the null comes from a column (or some expression where the type is better determined) UL does the right thing.

Breck's workaround is the right one (I confirmed it works with version 12 at least).

I'll add the issue to our bug list. In the meantime, hopefully Breck's workaround does the trick for you.

[Former Member · Accepted solution]

Thanks for the replying Paul, but unfortunatelly this workaround doesn't help us so much, because our queries are constructed in runtime. As our product is used as a dev tool, we do not have so much control in which type we should cast to.

But, I hope the fix for this bug be released soon. Besides that, I'm going to publish this behavior as a limitation of our product.

Thank you very much.