[Bug]: PERCENTILE_DISC function does not work

[schuartzrussi]

What happened?

I am encountering an issue when using the PERCENTILE_DISC function in Babelfish. The following query works correctly in SQL Server:

SELECT PERCENTILE_DISC(0.50) WITHIN GROUP (ORDER BY s.Value) OVER (PARTITION BY s.Code)
FROM mydbschema.IssuePercentileDisc AS s

However, when I attempt to run this query in Babelfish, i receive the following error:

OVER is not supported for ordered-set aggregate percentile_disc.

Steps to Reproduce

1. Create the table structure and insert sample data using the following script:

CREATE TABLE IssuePercentileDisc (
  Id INT PRIMARY KEY IDENTITY(1,1),
  Value DECIMAL(19, 4) NOT NULL,
  Code INT NOT NULL
);
INSERT INTO IssuePercentileDisc (Value, Code) VALUES (123.4567, 201);
INSERT INTO IssuePercentileDisc (Value, Code) VALUES (234.5678, 201);
INSERT INTO IssuePercentileDisc (Value, Code) VALUES (345.6789, 50);
INSERT INTO IssuePercentileDisc (Value, Code) VALUES (456.7890, 789);

2. Execute the following query:

select PERCENTILE_DISC(0.50) WITHIN GROUP (ORDER BY s.Value) OVER (PARTITION BY s.Code)
from IssuePercentileDisc as s

Version

BABEL_4_X_DEV (Default)

Extension

None

Which flavor of Linux are you using when you see the bug?

No response

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct.

[robverschoor]

Functions PERCENTILE_DISC and PERCENTILE_CONT are not supported in the T-SQL syntax, which requires both WITHIN GROUP and OVER. They work fine in native PostgreSQL when the OVER clause is omitted, but that is not valid syntax in T-SQL. The Compass tool incorrectly flagged these functions as supported, this will be corrected in the next release of Compass.