Hi again, I have found that, when you mix UNION and subqueries, Ultralite doesn't return the results I have expected. Let me post the simplest example I could think of: SELECT (SELECT 1) UNION SELECT (SELECT 1) Instead of returning 1, it returns 0. Similarly: SELECT (SELECT 'a') UNION SELECT (SELECT 'a') Instead of returning 'a', it returns an empty string. I think this also extrapolates to more complex cases, such as when I put a subquery in a CASE, like this one: SELECT 1 as id, CASE id WHEN 1 THEN (SELECT 'a') ELSE (SELECT 'b') END as name UNION SELECT 2 as id, CASE id WHEN 1 THEN (SELECT 'a') ELSE (SELECT 'b') END as name It returns: 1 | (empty string) 2 | (empty string) While if you put the literals without the subquery it works fine: SELECT 1 as id, CASE id WHEN 1 THEN ('a') ELSE ('b') END as name UNION SELECT 2 as id, CASE id WHEN 1 THEN ('a') ELSE ('b') END as name It returns: 1 | 'a' 2 | 'b' asked 17 Aug '12, 19:41 André Freitas |
Thank you André for reporting this bug. This (incorrect evaluation of subqueries with UNION) has been fixed as of 12.0.1.3787 in CR #718317. answered 04 Sep '12, 18:39 Tim McClements Jeff Albion Great news Tim, thank you.
(04 Sep '12, 19:21)
André Freitas
|
What version and build are you seeing this behavior. I have tried both 11.0.1 and 12.0.1 but are not seeing the behaviours described.
Hi Chris,
I'm using version 12.0.1 build 3352, on Mac.
This is a quite old build - I would try with a current EBF like 12.0.1.3744 (or read through its readme to look for possibly related bugfixes).
Note: I do not claim at all that this will behave differently - I just don't know.
I have looked through the readmes and didn't noticed anything related. Perhaps I have missed something, I will update my dev machine and test it again.
I agree - the sub-selects are not evaluated properly within the union.
A fix is in progress.