postgresql函数在Where子句中使用时会导致行数不一致

Question

我有一个函数“实验。

例如，如果我称此函数为这样：

SELECT 
 experiments.get_random_experiment_group('1a8cd547-8de6-4602-9103-3cea12f57365');

它将始终返回与该实验相关的2组ID中的1个。

但是，如果我做这样的事情...：

SELECT 
 id, 
 experiment_id,
 proportion 
FROM 
 experiments."group" 
WHERE 
 id = experiments.get_random_experiment_group('1a8cd547-8de6-4602-9103-3cea12f57365');

有时我会得到1行，2行或根本没有行。

为什么在第一种情况下它的行为是按照预期的，而在第二种情况下则不如预期的那样？关于正在发生的事情有什么建议吗？

这是功能：

CREATE OR REPLACE FUNCTION experiments.get_random_experiment_group (experiment uuid)
    RETURNS uuid
    LANGUAGE plpgsql
AS 
    $function$
DECLARE
   result uuid;
BEGIN
    WITH 
        CTE 
    AS 
    ( 
        SELECT 
            random() * (SELECT SUM(g.proportion) FROM experiments."group" g WHERE g.experiment_id = experiment) AS r
    )
    SELECT 
        id
    INTO 
        result
    FROM 
    (
        SELECT 
            id, 
            SUM(proportion) OVER (ORDER BY id) AS s,
            r
        FROM 
        (
            SELECT 
                * 
            FROM 
                experiments."group" g
            WHERE 
                g.experiment_id = experiment
        ) a 
        CROSS JOIN 
            CTE
    ) q
WHERE 
    s >= r
ORDER BY 
    id
LIMIT 
    1;
return result;
end;
$function$;

此功能的目的是为给定的实验ID随机选择组ID。选择特定组的概率由分配给该组的“比例”给出。

And here are all of the groups associated with the experiment in my example:

id	experiment_id	proportion
95cc9e77-3dff-48d9-91cb-3e17c0c24d5a	1a8cd547-8de6-4602-9103-3cea12f57365	0.5
47469555-88fd-4cec-925f-9cf15b91bca0	1a8cd547-8de6- 4602-9103-3CEA12F57365	0.5

我正在使用Postgres版本13.4

Answer 1

该函数在“组”表中的每一行都被调用，因此仅当该行的组ID发生在函数调用返回的随机生成的ID时，才返回行。

感谢评论者指出了这一点。