The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
1.6k1623
accept rate: 34%

edited 05 Sep '12, 10:55

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174

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:

×128
×13
×10

question asked: 17 Aug '12, 19:41

question was seen: 993 times

last updated: 05 Sep '12, 10:55