Issue Details (XML | Word | Printable)

Key: CORE-2676
Type: Bug Bug
Status: Open Open
Priority: Minor Minor
Assignee: Dmitry Yemanov
Reporter: alex
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

order altered on join-returned values with LIST() function

Created: 12/Oct/09 04:00 PM   Updated: 18/Oct/16 06:50 PM
Component/s: Engine
Affects Version/s: 2.1.0, 2.1.1, 2.1.2, 2.1.3, 3.0 Initial, 2.5.0, 2.1.4
Fix Version/s: None

Environment: linux 2.6.28, Debian unstable on AMD Sempron, 1.5 Gb ram

QA Status: Cannot be tested
Test Details: No sense in test because LIST does not guarantee any order.


 Description  « Hide
I have a query that returns a set of rows that I want to join with LIST() as:

    select id, 0 col_order, id col_value from objectkm
    where deleted <> 1
    UNION
    select id, 1 col_order, creation_date col_value from objectkm
    where deleted <> 1
    UNION
    select id, 2 col_order, title col_value from objectkm
    where deleted <> 1
    UNION
    select id, 3 col_order, creator col_value from objectkm
    where deleted <> 1
    ORDER BY 1, 2

When executing this query (from FlameRobin) results are ok with the expected order, I mean:
id
creation_date
title
creator
id
creation_date
title
creator
id
creation_date
title
creator
...

So when I LIST(col_value, '@separator@') them, I want to get:

id@separator@creation_date@separator@title@separator@creator
id@separator@creation_date@separator@title@separator@creator
id@separator@creation_date@separator@title@separator@creator
id@separator@creation_date@separator@title@separator@creator

The final query:

SELECT OGR.ID, LIST(DETAILS.col_value, '@@SEPARATOR@@')
FROM OBJECTKM OGR
INNER JOIN (
    select id, 0 col_order, id col_value from objectkm
    where deleted <> 1
    UNION
    select id, 1 col_order, creation_date col_value from objectkm
    where deleted <> 1
    UNION
    select id, 2 col_order, title col_value from objectkm
    where deleted <> 1
    UNION
    select id, 3 col_order, creator col_value from objectkm
    where deleted <> 1
    ORDER BY 1, 2
) DETAILS ON (DETAILS.id = OGR.id)
GROUP BY OGR.id

But results are not the expected, I mean, on the 2nd column calculated by LIST(), sometimes appears:

id@separator@creation_date@separator@title@separator@creator

on another

creation_date@separator@id@separator@title@separator@creator

I am doing something wrong or LIST() is ignoring the order ?

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 12/Oct/09 04:14 PM
LIST does not guarantee any order, in fact it depends on the query plan.