Window recipe not producing expected results when using DSS engine
Hi there,
The issue I am having is that the DSS engine is producing a completely different result than when I use the SQL engine. Has anyone faced a similar issue? I would appreciate some insight on this.
Basically, all I want to do is produce a columns with the MAX() value inferred from another column. No partitions, no order bys, simple enough? At least that's what I thought.
It looks like DSS is Ordering By a hidden index on its own and then creating a Window Frame that takes the current row and all preceding rows. Here is an example table to show you what is supposed to happen and what is in fact happening:
Supposed to happen: (This is what is happening in SQL (In-database) engine)
Salary | Max(Salary) |
22000 | 25000 |
23000 | 25000 |
24000 | 25000 |
25000 | 25000 |
What is in fact happening: (This is what is happening in DSS Engine)
Salary | Max(Salary) |
22000 | 22000 |
23000 | 23000 |
24000 | 24000 |
25000 | 25000 Operating system used: Windows |
Answers
-
Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 2,072 Neuron
Works for me in DSS engine mode if you set the Window recipe like this:
-
Yes, a member on my team mentioned this to me as well. Thank you. However, I was wondering if anyone knew why this is happening in DSS. Is this a bug?
-
Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 2,072 Neuron
Different engines have different requirements, compute things differently and behave differently. You already have a solution to get the result you expect in DSS Engine mode. And you already said you get what you expect with your SQL Engine mode. So not sure there is anything else left to solve in this post. It's exactly the same thing than when you see a different result between MS-SQL and MySQL. It's just the way it is and there isn't much you can do about it.