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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
