Wrong calculation of PERCENTILE_DISC if the same query contains PERCENTILE_CONT as well

lukaseder
SQL-Fighter

(I cannot seem to report bugs in JIRA (without a support contract?). I'll just report them here, unless there's a better way, in case of which, please let me know).

I'm using Exasol 7.0.7 on Windows with Docker:

docker run --name EXASOL -p 127.0.0.1:9563:8563 --detach --privileged --stop-timeout 120 exasol/docker-db:latest

With this setup, try running this query here:

select
  percentile_cont(0.5) within group (order by v),
  percentile_disc(0.5) within group (order by v)
from (values (1), (2), (3), (4)) as t(v)

It produces the following wrong result:

|PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY T.V)|PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY T.V)|
|------------------------------------------------|------------------------------------------------|
|2.5                                             |2.5                                             |

 The correct result can be obtained by calculating the two values separately:

select
  percentile_cont(0.5) within group (order by v)
from (values (1), (2), (3), (4)) as t(v);
select
  percentile_disc(0.5) within group (order by v)
from (values (1), (2), (3), (4)) as t(v);

Producing:

|PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY T.V)|
|------------------------------------------------|
|2.5                                             |

|PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY T.V)|
|------------------------------------------------|
|2                                               |
2 ACCEPTED SOLUTIONS

exa-Chris
Community Manager
Community Manager

Hi Lukas,
thank you for the feedback. You can launch tickets as a customer or partner with a valid contract. But also here the post is absolutely fine.

I will now alert the right persons internally to check this out.

Regards

Christian

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

View solution in original post

exa-SimonR
Team Exasol
Team Exasol
5 REPLIES 5

exa-SimonR
Team Exasol
Team Exasol

exa-SimonR
Team Exasol
Team Exasol

Hi Lukas,

I'm a developer in the Exasol compiler team. Thank you very much for your bug report. Having a concise example, like you provided, that reliably reproduces the issue is extremely helpful.

We already identified the underlying issue and fixed it. The fix still needs to be reviewed and tested, but upcoming 7.0 and 6.2 releases will contain it hopefully.

cheers

Simon

mwellbro
Xpert

reproducible under 6.2.8 as well:

 

mwellbro_1-1614596766958.png

 

exa-Chris
Community Manager
Community Manager

Hi Lukas,
thank you for the feedback. You can launch tickets as a customer or partner with a valid contract. But also here the post is absolutely fine.

I will now alert the right persons internally to check this out.

Regards

Christian

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

View solution in original post

lukaseder
SQL-Fighter

Thanks a lot Chris, for your quick reply. I might look into becoming a partner in the future, depending on what that means. We're currently adding support for Exasol in jOOQ as we have a customer requesting this integration, so partnering might definitely make sense.

In the meantime, I'll report such bugs here, then.