Ultralite strange behavior with UNION and subselects

[Former Member]

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'

[chris_keating]

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.

[Former Member]

Hi Chris,

I'm using version 12.0.1 build 3352, on Mac.

[VolkerBarth]

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.

[Former Member]

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.

[former_SQLA_member1694876]

I agree - the sub-selects are not evaluated properly within the union.

A fix is in progress.

[former_SQLA_member1694876 · Accepted solution]

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.

[Former Member · Accepted solution]

Great news Tim, thank you.