Skip to content

Support Sort on unprojected columns #1372

Closed

Description

@houqp

Describe the bug

SELECT a FROM table ORDER BY b will throw an error in datafusion because b is not a projected column.

The error can be reproduced with the following dataframe code as well:

        let schema = Schema::new(vec![
            Field::new("a", DataType::Int32, false),
            Field::new("b", DataType::Int32, false),
        ]);

        let batch = RecordBatch::try_new(
            Arc::new(schema.clone()),
            vec![
                Arc::new(Int32Array::from(vec![1, 10, 10, 100])),
                Arc::new(Int32Array::from(vec![2, 12, 12, 120])),
            ],
        )
        .unwrap();

        let mut ctx = ExecutionContext::new();
        let provider = MemTable::try_new(Arc::new(schema), vec![vec![batch]]).unwrap();
        ctx.register_table("t", Arc::new(provider)).unwrap();
        let df = ctx.table("t").unwrap()
            .select(vec![col("a")])
            .unwrap()
            .sort(vec![Expr::Sort {
                expr: Box::new(col("b")),
                asc: false,
                nulls_first: true,
            }])
            .unwrap();
        let results = df.collect().await.unwrap();

To Reproduce

Try to sort on unprojected column.

Expected behavior

SELECT a FROM table ORDER BY b should work without an error

Additional context

roapi/roapi#110

Activity

  1. houqp commented on Dec 2, 2021

    @houqp
    MemberAuthor

    Doing a projection "push up" could be a quick workaround to enable this in datafusion. For example, in logical plan builder's sort method, we can detect whether any of the sort columns are missing in the input plan's schema, if it is, we rewrite downstream projection plans by adding the missing sort columns, then add a new projection plan after the sort plan to remove the "pushed up" sort columns.

  2. added and removed
    bugSomething isn't working
    on Feb 10, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions