You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
ORDER BY does not work with literals: Sort operation is not applicable to scalar value 'foo' #1195
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 bynull;
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.
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
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
[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
How about adding an error hint in src/sql/planner.rs
/// Wrap the logical in a sortfnorder_by(&self,plan:LogicalPlan,order_by:&[OrderByExpr],) -> Result<LogicalPlan>{if order_by.is_empty(){returnOk(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.
Activity
[-]Sort does not work with NULL literals[/-][+]Sort does not work with literals: Sort operation is not applicable to scalar value 'foo'[/+][-]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'[/+]xudong963 commentedon Oct 28, 2021
@alamb I'll do it 😊
jimexist commentedon Oct 28, 2021
I don't think order by can be followed by a string literal, no?
PostgreSQL won't allow it
alamb commentedon Oct 28, 2021
@jimexist you are right. Maybe we can just adjust the error message so it isn't
Internalin this case?jimexist commentedon Oct 28, 2021
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.
xudong963 commentedon Oct 28, 2021
Thanks, @jimexist @alamb
How about adding an error hint in src/sql/planner.rs
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?
alamb commentedon Oct 28, 2021
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
xudong963 commentedon Oct 28, 2021
Sorry, I always forget we have DF API😅. After waking up, I'll update the related PR.
minor: refactor decodeBatches to make private in broadcast exchange (a…
feat: add support for array_contains expression (apache#1163)