Window recipe not producing expected results when using DSS engine

Options
KKhatib
KKhatib Registered Posts: 6

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)

SalaryMax(Salary)
2200025000
2300025000
2400025000
2500025000

What is in fact happening: (This is what is happening in DSS Engine)

SalaryMax(Salary)
2200022000
2300023000
2400024000
2500025000
Operating system used: Windows

Answers

  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,737 Neuron
    Options

    Works for me in DSS engine mode if you set the Window recipe like this:

    Screenshot 2024-06-14 at 19.58.28.pngScreenshot 2024-06-14 at 19.58.32.pngScreenshot 2024-06-14 at 19.58.45.png

  • KKhatib
    KKhatib Registered Posts: 6
    Options

    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
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,737 Neuron
    Options

    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.

Setup Info
    Tags
      Help me…