Skip to content

ORDER BY does not work with literals: Sort operation is not applicable to scalar value 'foo' #1195

Closed

Description

@alamb

Reproducer:

echo "1" > /tmp/foo.csv
cargo run -p datafusion-cli
CREATE EXTERNAL TABLE foo(x int)
STORED AS CSV
LOCATION '/tmp/foo.csv';
> select x from foo order by 'foo';
ArrowError(ExternalError(Internal("Sort operation is not applicable to scalar value foo")))
select x from foo order by null;
ArrowError(ExternalError(Internal("Sort operation is not applicable to scalar value NULL")))
>  select x from foo order by x, null;
ArrowError(ExternalError(Internal("Sort operation is not applicable to scalar value NULL")))```

I found this while working on #1184, but I am not sure it is related to null literals. I think we simply need to teach the sort operation about literals.

Activity

  1. changed the title [-]Sort does not work with NULL literals[/-] [+]Sort does not work with literals: Sort operation is not applicable to scalar value 'foo'[/+] on Oct 28, 2021
  2. changed the title [-]Sort does not work with literals: Sort operation is not applicable to scalar value 'foo'[/-] [+]ORDER BY does not work with literals: Sort operation is not applicable to scalar value 'foo'[/+] on Oct 28, 2021
  3. xudong963 commented on Oct 28, 2021

    @xudong963
    Member

    @alamb I'll do it 😊

  4. jimexist commented on Oct 28, 2021

    @jimexist
    Member

    I don't think order by can be followed by a string literal, no?

    PostgreSQL won't allow it

    [postgres] # select 1 as c order by 'foo';
    ERROR:  42601: non-integer constant in ORDER BY
    LINE 1: select 1 as c order by 'foo';
                                   ^
    LOCATION:  findTargetlistEntrySQL92, parse_clause.c:2012
    
  5. alamb commented on Oct 28, 2021

    @alamb
    ContributorAuthor

    @jimexist you are right. Maybe we can just adjust the error message so it isn't Internal in this case?

    alamb=# select * from foo order by null;
    ERROR:  non-integer constant in ORDER BY
    LINE 1: select * from foo order by null;
                
    
  6. jimexist commented on Oct 28, 2021

    @jimexist
    Member

    sqlite allows string literal but i think we should block this as this is error prone and supporting it really isn't going to be useful.

    it shall be a planning error or SQL syntax error.

  7. xudong963 commented on Oct 28, 2021

    @xudong963
    Member

    Thanks, @jimexist @alamb

    How about adding an error hint in src/sql/planner.rs

      /// Wrap the logical in a sort
      fn order_by(
          &self,
          plan: LogicalPlan,
          order_by: &[OrderByExpr],
      ) -> Result<LogicalPlan> {
          if order_by.is_empty() {
              return Ok(plan);
          }
    
          let order_by_rex = order_by
              .iter()
              .map(|e| {
                  match e.clone().expr {
                      SQLExpr::Value(value) => Err(DataFusionError::Plan(format!("order by {:?} err", value))),
                      _ => self.order_by_to_sort_expr(e, plan.schema())
                  }
              })
              .collect::<Result<Vec<_>>>()?;
    
          LogicalPlanBuilder::from(plan).sort(order_by_rex)?.build()
      }
    > select '1' as c order by null;
    Plan("order by Null err")
    > select '1' as c order by 'foo';
    Plan("order by SingleQuotedString(\"foo\") err")
    > select '1' as c order by c;
    +---+
    | c |
    +---+
    | 1 |
    +---+
    1 row in set. Query took 0.005 seconds.

    Another question, if we should keep https://github.com/apache/arrow-datafusion/blob/2c8e65bcca9fe41ad80116d99ad974c86cb59654/datafusion/src/physical_plan/expressions/mod.rs#L119 to do double defense?

  8. alamb commented on Oct 28, 2021

    @alamb
    ContributorAuthor

    One can run datafusion queries without SQL (using the DataFrame API directly, for example) so I think we definitely need to add it in https://github.com/apache/arrow-datafusion/blob/2c8e65bcca9fe41ad80116d99ad974c86cb59654/datafusion/src/physical_plan/expressions/mod.rs#L119

    In fact, it seems like it would be sufficient to just check in the physcal_expressions module -- maybe we can skip checking in the planner entirely

  9. xudong963 commented on Oct 28, 2021

    @xudong963
    Member

    One can run datafusion queries without SQL (using the DataFrame API directly, for example

    Sorry, I always forget we have DF API😅. After waking up, I'll update the related PR.

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

    bugSomething isn't workinggood first issueGood for newcomers

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions