# Error when running window ROW\_NUMBER function **URL:** \ **Category:** Uncategorized\ **Created:** [October 11, 2017, 4:19am UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360 "2017-10-11T04:19:18Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![Chris\_du\_Toit](https://avatars.discourse-cdn.com/v4/letter/c/a4c791/32.png) [@Chris\_du\_Toit](https://community.dremio.com/u/Chris_du_Toit)\ **Post date:** [October 11, 2017, 4:19am UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/1 "2017-10-11T04:19:19Z")
Any advice on getting my window function query to work would be great. I have created a dataset based on some SQL Server data and running the following query gives an error of “Cannot convert RexNode to equivalent Dremio expression. RexNode Class: org.apache.calcite.rex.RexOver, RexNode Digest: DENSE\_RANK() OVER (PARTITION BY $16 ORDER BY $4 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)” select Invoice\_ID ,TaskID ,ROW\_NUMBER() OVER(partition by TaskID order by Invoice\_ID DESC) as unq ,Invoice\_Status ,BillableRate FROM “@admin”."apientry.trftb\_StageWFMInvoiceEntries" where TaskID IS NOT NULL ---
**Author:** ![jduong](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jduong/32/21_2.png) [@jduong](https://community.dremio.com/u/jduong)\ **Post date:** [October 11, 2017, 4:30am UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/2 "2017-10-11T04:30:01Z")
Hi @Chris_du_Toit, There’s currently a problem running window functions and OVER clauses when connecting Dremio to relational databases. We’re investigating this issue and will let you know when we have more information. ---
**Author:** ![Chris\_du\_Toit](https://avatars.discourse-cdn.com/v4/letter/c/a4c791/32.png) [@Chris\_du\_Toit](https://community.dremio.com/u/Chris_du_Toit)\ **Post date:** [October 11, 2017, 4:36am UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/3 "2017-10-11T04:36:08Z")
Thanks for the quick reply. How do I turn a query from relational to a query on Dremio? In other words, how do I move the data from the relational source to Dremio’s in-memory structures? ---
**Author:** ![kelly](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/kelly/32/11_2.png) [@kelly](https://community.dremio.com/u/kelly)\ **Post date:** [October 11, 2017, 10:19am UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/4 "2017-10-11T10:19:07Z")
When you access data from any source, the data is read into Apache Arrow in-memory buffers automatically. You can also create Data Reflections, which maintain physically optimized representations of the source data in Dremio’s Reflection Store. Dremio’s cost-based optimizer will then consider all reflections when generating a query plan, and will usually determine running the query on one or more reflections is more cost effective than pushing the query into the source. You can read more about Data Reflections here: [http://docs.dremio.com/acceleration/](http://docs.dremio.com/acceleration/) ---
**Author:** ![Chris\_du\_Toit](https://avatars.discourse-cdn.com/v4/letter/c/a4c791/32.png) [@Chris\_du\_Toit](https://community.dremio.com/u/Chris_du_Toit)\ **Post date:** [October 16, 2017, 1:07am UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/5 "2017-10-16T01:07:21Z")
Thanks, I am now learning the Dremio and I see a reflection based on a RDBMS source also doesn’t support the OVER clause. (1) Is there any workaround I can currently bring in to have the OVER clause work or is this just a feature/fix I need to wait for? (2) Also, what’s the best way to monitor progress on this becoming available? ---
**Author:** ![kelly](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/kelly/32/11_2.png) [@kelly](https://community.dremio.com/u/kelly)\ **Post date:** [October 16, 2017, 1:33am UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/6 "2017-10-16T01:33:12Z")
Can you attach the query profile? Also, are you still seeing this on 1.2.2? Kelly ---
**Author:** ![Chris\_du\_Toit](https://avatars.discourse-cdn.com/v4/letter/c/a4c791/32.png) [@Chris\_du\_Toit](https://community.dremio.com/u/Chris_du_Toit)\ **Post date:** [October 16, 2017, 2:29am UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/7 "2017-10-16T02:29:20Z")
Hi Kelly, Yes, I have upgraded to 1.2.2 from 1.2.1. Please find the query profile here [d2d22ab4-195e-4bab-ae09-b66cf0801b08.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/fd2a2abc3f01077c855d7a76f346cf905d8c1370.zip) (2.7 KB) Thanks for the quick response to this. It is really appreciated and makes for a great user experience. Chris ---
**Author:** ![Chris\_du\_Toit](https://avatars.discourse-cdn.com/v4/letter/c/a4c791/32.png) [@Chris\_du\_Toit](https://community.dremio.com/u/Chris_du_Toit)\ **Post date:** [October 17, 2017, 2:57am UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/8 "2017-10-17T02:57:57Z")
Hi Kelly, Any idea what I might be doing wrong? ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [October 17, 2017, 5:59pm UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/9 "2017-10-17T17:59:45Z")
Hi @Chris_du_Toit, Currently analyzing the profile you have uploaded. Will get back to you in a few … Thanks, @balaji.ramaswamy ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [October 17, 2017, 11:03pm UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/10 "2017-10-17T23:03:28Z")
Hi @Chris_du_Toit, If you have a JSON or a CSV file that has these columns the query will work fine. We have a bug when we need to push this to a RDBMS (JDBC pushdown) . We are working on the bug and it should arrive in our next release. Kindly let me know if you have any further questions Thanks, @balaji.ramaswamy ---
**Author:** ![Chris\_du\_Toit](https://avatars.discourse-cdn.com/v4/letter/c/a4c791/32.png) [@Chris\_du\_Toit](https://community.dremio.com/u/Chris_du_Toit)\ **Post date:** [October 18, 2017, 7:57pm UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/11 "2017-10-18T19:57:19Z")
Thanks, what is the best way to stay up to date with new releases? ---
**Author:** ![kelly](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/kelly/32/11_2.png) [@kelly](https://community.dremio.com/u/kelly)\ **Post date:** [October 19, 2017, 2:40pm UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/12 "2017-10-19T14:40:34Z")
Hi Chris, We announce new releases here. You can expect ~1/month for the next several months. Kelly ---
**Author:** ![Chris\_du\_Toit](https://avatars.discourse-cdn.com/v4/letter/c/a4c791/32.png) [@Chris\_du\_Toit](https://community.dremio.com/u/Chris_du_Toit)\ **Post date:** [December 4, 2017, 9:59pm UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/13 "2017-12-04T21:59:17Z")
Hi Kelly, I know it’s not in the Release Notes for v1.3, but I just wanted to let you know that the WINDOW function error is still popping up v1.3. BTW - Love the row numbers in the query windows. Thanks Chris ---
**Author:** ![can](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/can/32/40_2.png) [@can](https://community.dremio.com/u/can)\ **Post date:** [January 25, 2018, 2:57am UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/14 "2018-01-25T02:57:50Z")
@Chris_du_Toit following up on your comment on [Possible View of Dremio Roadmap](https://community.dremio.com/t/possible-view-of-dremio-roadmap/668/3). We’re currently targeting 1.6 (2 releases out) for fixing this issue – it turned out to be a more involved fix than initially estimated. ---
**Author:** ![gpaparisteidis](https://avatars.discourse-cdn.com/v4/letter/g/839c29/32.png) [@gpaparisteidis](https://community.dremio.com/u/gpaparisteidis)\ **Post date:** [September 4, 2019, 12:07pm UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/15 "2019-09-04T12:07:18Z")
Hey, has this been fixed? Am seeing an issue on Rank() window function, where ordering is ignored, and would like to know if it is related with that. ---
**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\ **Post date:** [September 4, 2019, 4:03pm UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/16 "2019-09-04T16:03:31Z")
@gpaparisteidis, it’s possible, as we’ve fixed some errors around Window functions. What version of Dremio are you using? Can you share a query profile for the failed job? > **[How To Share A Query Profile](https://www.dremio.com/tutorials/share-query-profile-dremio/)** > > Tutorial explaining how to share a query profile in Dremio. ---
**Author:** ![gpaparisteidis](https://avatars.discourse-cdn.com/v4/letter/g/839c29/32.png) [@gpaparisteidis](https://community.dremio.com/u/gpaparisteidis)\ **Post date:** [September 5, 2019, 8:08am UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/17 "2019-09-05T08:08:52Z")
Hey @ben , thanks for your response. This the query profile of the job. [0474078f-a8f6-4497-80d5-a6d2710adcf5.zip](https://community.dremio.com/uploads/short-url/q6R7aHdXiBEHejbkjQaCrpgUysU.zip) (9.2 KB) As I said the problem lies with the ordering of the results, where `ORDER BY DESC` seems to be ignored. I am using version 3.1.1 ---
**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\ **Post date:** [September 5, 2019, 3:02pm UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/18 "2019-09-05T15:02:03Z")
Hello @gpaparisteidis, So the issue is not an error, but what looks like “wrong results”. In your query Dremio actually pushes the query down into the underlying source, MS SQL Server: ``` SELECT [IssueType] COLLATE LATIN1_GENERAL_BIN2 AS [IssueType], [ActualImpact], RANK() OVER (PARTITION BY [IssueType] COLLATE LATIN1_GENERAL_BIN2 ORDER BY CASE WHEN [ActualImpact] IS NULL THEN 1 ELSE 0 END DESC, [ActualImpact]) AS [issue_rank] FROM [1-BaApi].[dbo].[Issues] WHERE [ActualImpact] IS NOT NULL] ``` But Dremio orders `null` values as highest while [SQL Server orders them lowest](https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017#arguments) so that’s why you see the `CASE` statement in the pushed down query and probably the unexpected ordering. Does that explain what you are seeing? ---
**Author:** ![gpaparisteidis](https://avatars.discourse-cdn.com/v4/letter/g/839c29/32.png) [@gpaparisteidis](https://community.dremio.com/u/gpaparisteidis)\ **Post date:** [September 17, 2019, 3:26pm UTC](https://community.dremio.com/t/error-when-running-window-row-number-function/360/19 "2019-09-17T15:26:05Z")
Hi @ben, thanks for your response and sorry for the delayed reply, I was on holidays. While I understand how Dremio and SQL Server order `NULL` values differently, what I don’t get is how is this affecting the results in my case, since there is a `WHERE` clause that only returns `NON-NULL` values. I addition to that, the query that you have posted above seems to be defining ordering between `NULL` and `NON-NULL` values, however what I am seeing is un-ordered (or to be more specific ordered in ASC) results between `NON-NULL` values. ```auto SELECT IssueType, ActualImpact, RANK() OVER (PARTITION BY IssueType ORDER BY ActualImpact ASC) issue_rank FROM "1-BaApi"."1-BaApi".dbo.Issues WHERE ActualImpact IS NOT NULL ``` ```auto SELECT IssueType, ActualImpact, RANK() OVER (PARTITION BY IssueType ORDER BY ActualImpact DESC) issue_rank FROM "1-BaApi"."1-BaApi".dbo.Issues WHERE ActualImpact IS NOT NULL ``` These two queries produce the same results. Appreciate your help.