Aggregate functions over an ordered set

[Denis Simonov]

Hello everyone.

I'm trying to implement the PERCENTILE_DISC and PERCENTILE_CONT inverse distribution functions. The current implementation can be found here: https://github.com/sim1984/firebird/tree/percentile-functions
(https://github.com/sim1984/firebird/commit/398a9f5f42573a44e87d60c3f8178c474bce7de8)

I'm almost ready to create a pull request, but there's one issue left.

The PERCENTILE_DISC and PERCENTILE_CONT functions generally look like this.

PERCENTILE_DISC(<percent>) WITHIN GROUP (ORDER BY <expr> [ASC | DESC])
  OVER (PARTITION BY <part_expr>)

PERCENTILE_CONT(<percent>) WITHIN GROUP (ORDER BY <expr> [ASC | DESC])
  OVER (PARTITION BY <part_expr>)

In this case, <expr> acts not only as a sort key, but also as an implicit argument to the function.

The problem is that the sorting data and the record format are described by the first argument of the <percent> function.

I was able to resolve the issue with the sorting data, and the PERCENTILE_CONT function works as expected. PERCENTILE_DISC works with numeric data types, but refuses to work with any others.

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY FIRST_NAME) 

FROM employee;

PERCENTILE_DISC
===============
Statement failed, SQLSTATE = 22018
conversion error from string "Mark"

Investigation reveals that the error occurs at the final stage of the aggregate function. If I understand correctly, this checks whether the aggregate function's return type extends the type of the first argument. But this isn't entirely correct. A more logical check would be to check the extendability of the implicit <expr> argument.

template <typename ThisType, typename NextType>
void BaseAggWinStream<ThisType, NextType>::aggExecute(thread_db* tdbb, Request* request,
const NestValueArray& sourceList, const NestValueArray& targetList) const
{
const NestConst<ValueExprNode>* const sourceEnd = sourceList.end();

for (const NestConst<ValueExprNode>* source = sourceList.begin(),
*target = targetList.begin();
 source != sourceEnd;
 ++source, ++target)
{
const AggNode* aggNode = nodeAs<AggNode>(*source);

if (aggNode)
{
const FieldNode* field = nodeAs<FieldNode>(*target);
const USHORT id = field->fieldId;
Record* record = request->req_rpb[field->fieldStream].rpb_record;

dsc* desc = aggNode->execute(tdbb, request);
if (!desc || !desc->dsc_dtype)
record->setNull(id);
else
{
MOV_move(tdbb, desc, EVL_assign_to(tdbb, *target));
record->clearNull(id);
}
}
}
}

Please tell me how to fix this. If it's easier, I can create a pull request and fix the error there.

[Denis Simonov]

I've created a draft PR where you can work on solving the problem https://github.com/FirebirdSQL/firebird/pull/8807

четверг, 20 ноября 2025 г. в 20:36:23 UTC+3, Denis Simonov: