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,

asked 01 Jul '12, 14:07

Alex's gravatar image

Alex
1.0k214554
accept rate: 25%

edited 01 Jul '12, 16:48

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889

2

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

(01 Jul '12, 16:45) Breck Carter
Replies hidden

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

(02 Aug '12, 15:43) Alex

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.

permanent link

answered 02 Jul '12, 14:09

Paul's gravatar image

Paul
19134
accept rate: 20%

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.

(02 Aug '12, 15:50) Alex
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×146
×52
×19
×10
×5

question asked: 01 Jul '12, 14:07

question was seen: 1,673 times

last updated: 02 Aug '12, 15:50