You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "cseidman (via GitHub)" <gi...@apache.org> on 2024/04/09 12:26:07 UTC
[I] HAVING doesn't work with ORDER BY [arrow-datafusion]
cseidman opened a new issue, #10013:
URL: https://github.com/apache/arrow-datafusion/issues/10013
### Describe the bug
I found a case where a query fails when there is both a HAVING clause and an ORDER BY clause. The query will work if it has either the HAVING clause or the ORDER BY clause .. but not both.
Here is a failing query:
```sql
with revs
as (
SELECT
customer,
cast(round(CAST(substring(revenue,2) AS float)) as INT) AS visit_revenue
FROM visits
)
SELECT
customer,
SUM(visit_revenue) as total_revenue
FROM revs
group by customer
having SUM(visit_revenue) > 999
order by SUM(visit_revenue)
```
The error I get is:
```shell
Error: SchemaError(FieldNotFound { field: Column { relation: None, name: "visit_revenue" }, valid_fields: [Column { relation: Some(Bare { table: "revs" }), name: "customer" }, Column { relation: None, name: "total_revenue" }, Column { relation: Some(Bare { table: "revs" }), name: "customer" }, Column { relation: None, name: "SUM(revs.visit_revenue)" }] }, Some(""))
```
If I comment out either the line with `having SUM(visit_revenue) > 999` OR `order by SUM(visit_revenue)` the the query will succeed.
### To Reproduce
To reproduce, run this program with the attached CSV file as data.
```rust
use datafusion::arrow::datatypes::{DataType, Field, Schema};
use datafusion::prelude::{CsvReadOptions, SessionContext};
#[tokio::main]
async fn main() -> datafusion::error::Result<()> {
let file_name = "data/test.csv";
let schema = Schema::new(vec![
Field::new("date", DataType::Utf8, false),
Field::new("weekday", DataType::Utf8, false),
Field::new("time", DataType::Utf8, false),
Field::new("customer", DataType::Utf8, false),
Field::new("revenue", DataType::Utf8, false),
]);
let options = CsvReadOptions::new()
.schema(&schema)
.delimiter(b'\t')
.has_header(true)
;
let ctx = SessionContext::new();
ctx.register_csv("visits", file_name, options).await?;
let df = ctx.sql(r#"
with revs
as (
SELECT
customer,
cast(round(CAST(substring(revenue,2) AS float)) as INT) AS visit_revenue
FROM visits
)
SELECT
customer,
SUM(visit_revenue) as total_revenue
FROM revs
group by customer
having SUM(visit_revenue) > 999
order by SUM(visit_revenue)
"#).await?;
df.show().await?;
Ok(())
}
```
The attached file contains the data:
[test.csv](https://github.com/apache/arrow-datafusion/files/14918379/test.csv)
### Expected behavior
The expectation is that the result set is both filtered for records where the sum of `visit_revenue` > 999 AND ordered by the same without crashing.
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] HAVING doesn't work with ORDER BY [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #10013: HAVING doesn't work with ORDER BY
URL: https://github.com/apache/datafusion/issues/10013
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] HAVING doesn't work with ORDER BY [arrow-datafusion]
Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao commented on issue #10013:
URL: https://github.com/apache/arrow-datafusion/issues/10013#issuecomment-2059365016
A reproducible query in CLI is:
```sh
DataFusion CLI v37.0.0
> create table t(a int);
0 row(s) fetched.
Elapsed 0.005 seconds.
> SELECT
SUM(a)
FROM t
having SUM(a) > 0
order by SUM(a);
Schema error: No field named a. Valid fields are "SUM(t.a)", "SUM(t.a)".
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] HAVING doesn't work with ORDER BY [arrow-datafusion]
Posted by "tinfoil-knight (via GitHub)" <gi...@apache.org>.
tinfoil-knight commented on issue #10013:
URL: https://github.com/apache/arrow-datafusion/issues/10013#issuecomment-2045361264
@cseidman
Try using `SUM(revs.visit_revenue)` or the alias you set i.e. `total_revenue` instead of `SUM(visit_revenue)` in the `HAVING` & `ORDER BY` clause.
```sql
with revs
as (
SELECT
customer,
cast(round(CAST(substring(revenue,2) AS float)) as INT) AS visit_revenue
FROM visits
)
SELECT
customer,
SUM(visit_revenue) as total_revenue
FROM revs
group by customer
having SUM(revs.visit_revenue) > 999
order by SUM(revs.visit_revenue)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] HAVING doesn't work with ORDER BY [arrow-datafusion]
Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao commented on issue #10013:
URL: https://github.com/apache/arrow-datafusion/issues/10013#issuecomment-2062867665
take
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] HAVING doesn't work with ORDER BY [arrow-datafusion]
Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao commented on issue #10013:
URL: https://github.com/apache/arrow-datafusion/issues/10013#issuecomment-2061696223
I will try to fix it in the following days, if no one has already started working on it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org