Window function ORDER BY ignored in result set

1

I wanted to echo a thread from mid-2019 with no responses regarding window functions.

The ORDER BY clause appears to be ignored. The following example uses RANK, but the behavior has been equivalent for DENSE_RANK and ROW_NUMBER as well. The underlying table is just distinct dates in 2020 for which I had data in another table (used for demonstration purposes).

SELECT
    AsOfDate,
    RANK() OVER(ORDER BY AsOfDate DESC) AS days_past
FROM
    dates

Below is a partial result and should suffice to demonstrate the issue’s existence. The earliest date in the set is given the lowest number and the field’s value increases from there:

image
image1638×243 6.71 KB

I have tried explicitly ordering the table before applying the window function and not specifying an ORDER BY clause and seen the same results. The application of the window function appears to be applying some default ordering and not changing it.

I am using Dremio 4.1.4 Community Edition.

2

Hello Tyler,

I did some tests by picking up the date column using Rank, dense_rank & row_number and I got the expected results all the time.

So, are you seeing this issue only with the date column or for integers as well? This helps to narrow down the issue.

Is it possible to provide the profile for this query for further investigations?

Thanks,
Rakesh

3

Thanks for the reply Rakesh.

I tried the window functions ordering by an INT and simple letter VARCHARs and got the results I was expecting. So, at this stage, I’ve only seen it with DATE. I’ve also tried explicitly casting to DATE in the order by and seen the same reversed order.

The data source I’m using is proprietary, so I’m not comfortable providing a query plan for this query specifically. However, I’ll work on loading some test data in and trying the window functions on it, and send over the query plans should it fail once more.

Some details I can provide: the root virtual dataset is unreflected and pulls from a view in a SQL Server database. In that view, the date in question is of type date.

I’ll work on some further testing with less sensitive data and see what more I can provide you. Thanks again for your time.

4

Hello Tyler,

I’m just following up on this.

Have you done with your testing?

Any update?

Thanks,
Rakesh