Question Re: Ultralite cannot execute a select with UNION and null in Technology Q&A

Ultralite cannot execute a select with UNION and null

Former Member — Sun, 01 Jul 2012 14:07:50 GMT

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,

Re: Ultralite cannot execute a select with UNION and null

Breck_Carter — Sun, 01 Jul 2012 16:45:14 GMT

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 ) )

Re: Ultralite cannot execute a select with UNION and null

Former Member — Mon, 02 Jul 2012 14:09:12 GMT

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.

Re: Ultralite cannot execute a select with UNION and null

Former Member — Thu, 02 Aug 2012 15:44:00 GMT

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).

Re: Ultralite cannot execute a select with UNION and null

Former Member — Thu, 02 Aug 2012 15:50:48 GMT

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.