topic Re: Dense rank possible bug in Data Engineering

Dense rank possible bug

Łukasz — Tue, 18 Jul 2023 12:02:06 GMT

I have the case of deduplicating data source over specific business key using dense_rank function. Currently the data source does not have any duplicates, so the function should return 1 in all cases. The issue is that dense rank does not return proper integer, although data type is of integer:

When filtering rank function equal to 1, it gives me "random" number of records. Most of dense_rank values with display values of 1 are getting dropped
When filtering rank < 1.1 it gives me the same results as above
When filtering rank > 0.9 it gives me the expected amount of rows
When casting rank function to double and then filtering it as equal to 1, it gives me expected number of rows

It happens on databricks runtime 13.1, so I am assuming spark 3.4 has this issue. It works with no problem with runtime 12.2

Re: Dense rank possible bug

Lakshay — Tue, 18 Jul 2023 12:13:02 GMT

Could you share a code snippet of how you are applying the rank function?

Re: Dense rank possible bug

Łukasz — Tue, 18 Jul 2023 12:45:39 GMT

SELECT * except(d.AssessmentNo, d.UnitClassSup, d.UnitTypeSup, d.UnitCodeSup, d.ProdUnitNo, d.QuestionAnswerId, d.hash_value, d.load_date) , dense_rank() OVER (PARTITION BY m.UnitClassSup, m.UnitTypeSup, m.UnitCodeSup, m.AssessmentYear, m.ProdUnitNo ORDER BY m.UpdDtime DESC, m.AnswerUpdDate DESC, m.QuestionAnswerId DESC) AS Rk

FROM delta.`/mnt/silver/path_main` m

JOIN delta.`/mnt/silver/path_detail` d

ON (ms.AssessmentNo = qe.AssessmentNo

AND ms.UnitClassSup = qe.UnitClassSup

AND ms.UnitTypeSup = qe.UnitTypeSup

AND ms.UnitCodeSup = qe.UnitCodeSup

AND ms.ProdUnitNo = qe.ProdUnitNo

AND ms.QuestionAnswerId = qe.QuestionAnswerId )

This is saved as cte and then queried with filter rk = 1

Re: Dense rank possible bug

Lakshay — Wed, 19 Jul 2023 18:27:22 GMT

I tried running a dense rank query using DBR 13.1. But I do not see this issue. Could you try a simple dense rank query on a table

Re: Dense rank possible bug

saipujari_spark — Wed, 19 Jul 2023 19:37:11 GMT

Hey @Łukasz

Thanks for reporting.

As I see Spark 3.4.0 introduced an improvement that looks to be the cause for this issue.

Improvement: https://issues.apache.org/jira/browse/SPARK-37099

This improvement [SPARK-37099] is included as part of DBR 13.1: https://docs.databricks.com/release-notes/runtime/13.1.html

That is the reason you are seeing this in DBR 13.1

As I have verified internally this seems to be fixed in DBR 13.1. I would request you to test it again once and let us know.

Re: Dense rank possible bug

Łukasz — Thu, 20 Jul 2023 07:57:38 GMT

Hello @Saniam

Thanks for answer, I have just tested and it seems to be working fine both in 13.1 and 13.2

On the other note, can you help me understand how the releases are done for spark? The one that you mention is said to be released in 3.5, which should come in new databricks runtime release.

Kind regards,

Łukasz

Re: Dense rank possible bug

saipujari_spark — Wed, 26 Jul 2023 15:24:37 GMT

Hey @Łukasz it's because any fixes which are important are backported to older spark versions in DBR, that's the reason you see this fixed in DBR 13.1