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%C3%A9%20Freitas's gravatar image

André Freitas
1964510
accept rate: 0%

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.

(20 Aug '12, 10:05) Chris Keating
Replies hidden
Comment Text Removed

Hi Chris,

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

(20 Aug '12, 13:55) André Freitas

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.

(21 Aug '12, 03:00) Volker Barth

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.

(21 Aug '12, 15:01) André Freitas

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

A fix is in progress.

(04 Sep '12, 13:44) Tim McClements

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.

permanent link

answered 04 Sep '12, 18:39

Tim%20McClements's gravatar image

Tim McClements
2.0k1830
accept rate: 35%

edited 05 Sep '12, 10:55

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175

Great news Tim, thank you.

(04 Sep '12, 19:21) André Freitas
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:

×161
×17
×12

question asked: 17 Aug '12, 19:41

question was seen: 2,313 times

last updated: 05 Sep '12, 10:55