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.

Sign up for GitHub

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

Jump to bottom

Order by is ignored #13483

Open
simonvandel opened this issue Nov 19, 2024 · 2 comments · May be fixed by #13497
Open

Order by is ignored #13483

simonvandel opened this issue Nov 19, 2024 · 2 comments · May be fixed by #13497
Labels
bug Something isn't working

Comments

@simonvandel
Copy link
Contributor

Describe the bug

In the following query

EXPLAIN SELECT 
    CASE 
        WHEN name = 'name1' THEN 0.0
        WHEN name = 'name2' THEN 0.5
    END AS a
FROM (
    SELECT 'name1' AS name
    UNION ALL
    SELECT 'name2'
)
ORDER BY a DESC;

the order of a is not matching the ORDER BY a DESC.

The resulting plan looks like this:

+---------------+-------------------------------------------------------------------------------------------------------------------+
| plan_type     | plan                                                                                                              |
+---------------+-------------------------------------------------------------------------------------------------------------------+
| logical_plan  | Sort: a DESC NULLS FIRST                                                                                          |
|               |   Projection: CASE WHEN name = Utf8("name1") THEN Float64(0) WHEN name = Utf8("name2") THEN Float64(0.5) END AS a |
|               |     Union                                                                                                         |
|               |       Projection: Utf8("name1") AS name                                                                           |
|               |         EmptyRelation                                                                                             |
|               |       Projection: Utf8("name2") AS name                                                                           |
|               |         EmptyRelation                                                                                             |
| physical_plan | CoalescePartitionsExec                                                                                            |
|               |   ProjectionExec: expr=[CASE WHEN name@0 = name1 THEN 0 WHEN name@0 = name2 THEN 0.5 END as a]                    |
|               |     UnionExec                                                                                                     |
|               |       ProjectionExec: expr=[name1 as name]                                                                        |
|               |         PlaceholderRowExec                                                                                        |
|               |       ProjectionExec: expr=[name2 as name]                                                                        |
|               |         PlaceholderRowExec                                                                                        |
|               |                                                                                                                   |
+---------------+-------------------------------------------------------------------------------------------------------------------+

As can be seen the Sort from the logical plan gets transformed into a CoalescePartitionsExec in the physical plan.
However, the docs for CoalescePartitionsExec https://docs.rs/datafusion/latest/datafusion/physical_plan/coalesce_partitions/struct.CoalescePartitionsExec.html says that "No guarantees are made about the order of the resulting partition."

To Reproduce

In datafusion-cli v 43:

EXPLAIN SELECT 
    CASE 
        WHEN name = 'name1' THEN 0.0
        WHEN name = 'name2' THEN 0.5
    END AS a
FROM (
    SELECT 'name1' AS name
    UNION ALL
    SELECT 'name2'
)
ORDER BY a DESC;

Expected behavior

Resulting order is consistent

Additional context

Running

EXPLAIN VERBOSE SELECT 
    CASE 
        WHEN name = 'name1' THEN 0.0
        WHEN name = 'name2' THEN 0.5
    END AS a
FROM (
    SELECT 'name1' AS name
    UNION ALL
    SELECT 'name2'
)
ORDER BY a DESC;

shows that the EnforceSorting pass seems to remove the SortExec
@simonvandel simonvandel added the bug Something isn't working label Nov 19, 2024
@simonvandel simonvandel changed the title Order by gets ignored Order by is ignored Nov 19, 2024
@Omega359
Copy link
Contributor

I wonder if this is related to changes to push down sorts, specifically #11875 ?

@akurmustafa akurmustafa linked a pull request Nov 20, 2024 that will close this issue
Open
@alamb
Copy link
Contributor

alamb commented Nov 20, 2024

PR from @akurmustafa up for reivew:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

[bug]: Fix wrong order by removal from plan akurmustafa/datafusion
3 participants
@Omega359 @alamb @simonvandel