Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

order altered on join-returned values with LIST() function [CORE2676] #3079

Open
firebird-automations opened this issue Oct 12, 2009 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: alex (aportela)

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 http://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 (http://DETAILS.id = http://OGR.id)
GROUP BY http://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 ?

====== Test Details ======

No sense in test because LIST does not guarantee any order.

@firebird-automations
Copy link
Collaborator Author

Modified by: alex (aportela)

description: 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 http://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 (http://DETAILS.id = http://OGR.id)
GROUP BY http://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 ?

=>

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 http://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 (http://DETAILS.id = http://OGR.id)
GROUP BY http://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 ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

LIST does not guarantee any order, in fact it depends on the query plan.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.1.4 [ 10361 ]

Version: 2.5.0 [ 10221 ]

Version: 3.0 Initial [ 10301 ]

Version: 2.1.3 [ 10302 ]

Version: 2.1.2 [ 10270 ]

Version: 2.1.1 [ 10223 ]

Version: 2.1.0 [ 10041 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Open [ 1 ] => Open [ 1 ]

QA Status: Cannot be tested

Test Details: No sense in test because LIST does not guarantee any order.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment