You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by GitBox <gi...@apache.org> on 2022/11/02 07:38:42 UTC
[GitHub] [arrow-datafusion] mustafasrepo opened a new issue, #4076: Wrong result for FIRST_VALUE AND LAST_VALUE window functions
mustafasrepo opened a new issue, #4076:
URL: https://github.com/apache/arrow-datafusion/issues/4076
**Describe the bug**
Window functions `FIRST_VALUE` AND `LAST_VALUE` give wrong results when run with the query below
```sql
SELECT
FIRST_VALUE(c4) OVER(ORDER BY c9 ASC ROWS BETWEEN 10 PRECEDING AND 1 FOLLOWING) as first_value1,
FIRST_VALUE(c4) OVER(ORDER BY c9 ASC ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING) as first_value2,
LAST_VALUE(c4) OVER(ORDER BY c9 ASC ROWS BETWEEN 10 PRECEDING AND 1 FOLLOWING) as last_value1,
LAST_VALUE(c4) OVER(ORDER BY c9 ASC ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING) as last_value2
FROM aggregate_test_100
ORDER BY c9
LIMIT 5
```
**To Reproduce**
The test below reproduces the error
```sql
#[tokio::test]
async fn test_window_frame_first_value_last_value_aggregate() -> Result<()> {
let config = SessionConfig::new();
let ctx = SessionContext::with_config(config);
register_aggregate_csv(&ctx).await?;
let sql = "SELECT
FIRST_VALUE(c4) OVER(ORDER BY c9 ASC ROWS BETWEEN 10 PRECEDING AND 1 FOLLOWING) as first_value1,
FIRST_VALUE(c4) OVER(ORDER BY c9 ASC ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING) as first_value2,
LAST_VALUE(c4) OVER(ORDER BY c9 ASC ROWS BETWEEN 10 PRECEDING AND 1 FOLLOWING) as last_value1,
LAST_VALUE(c4) OVER(ORDER BY c9 ASC ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING) as last_value2
FROM aggregate_test_100
ORDER BY c9
LIMIT 5";
let actual = execute_to_batches(&ctx, sql).await;
let expected = vec![
"+--------------+--------------+-------------+-------------+",
"| first_value1 | first_value2 | last_value1 | last_value2 |",
"+--------------+--------------+-------------+-------------+",
"| -16110 | -16110 | 3917 | -1114 |",
"| -16110 | -16110 | -16974 | 15673 |",
"| -16110 | -16110 | -1114 | 13630 |",
"| -16110 | 3917 | 15673 | -13217 |",
"| -16110 | -16974 | 13630 | 20690 |",
"+--------------+--------------+-------------+-------------+",
];
assert_batches_eq!(expected, &actual);
Ok(())
}
```
**Expected behavior**
I expect to get true result or get an error showing functionality is not supported
**Additional context**
N.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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb closed issue #4076: Wrong result for FIRST_VALUE AND LAST_VALUE window functions
Posted by GitBox <gi...@apache.org>.
alamb closed issue #4076: Wrong result for FIRST_VALUE AND LAST_VALUE window functions
URL: https://github.com/apache/arrow-datafusion/issues/4076
--
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