ORDER BY中的最后一个案例执行了错误的postgres

Question

我有以下postgres存储函数。ORDER BY中的Last CASE WHEN返回排序不正确的数据。然而，类似的第二个CASE WHEN如预期的那样工作。我已经仔细检查了sort_column和sort_direction是否正确。感谢提供的任何帮助

DB Fiddle：https://dbfiddle.uk/?rdbms=postgres_14&fiddle=32ad76d21a9cb97518b92acf58ddf803

CREATE OR REPLACE FUNCTION find_experts(BIGINT, _sort_column VARCHAR(256), _sort_direction VARCHAR(256)) 
RETURNS TABLE (
user_id BIGINT,
name VARCHAR(256),
email VARCHAR(256),
email_validated BOOLEAN,
is_verified BOOLEAN,
rating FLOAT,
is_promoted BOOLEAN,
registry_date TIMESTAMP,
picture VARCHAR(256),
price FLOAT,
biography VARCHAR(2028),
short_bio VARCHAR(1000),
timeslots VARCHAR(256)[],
timezone VARCHAR(256)
) AS $$
SELECT users.user_id, users.name, users.email, users.email_validated, users.is_verified, users.rating, users.is_promoted, users.registry_date,
user_settings.picture, user_settings.price, user_settings.biography, user_settings.short_bio, user_settings.timeslots, user_settings.timezone
FROM users 
LEFT JOIN user_settings ON (users.user_id=user_settings.user_id)
WHERE users.user_id IN 
(SELECT user_id FROM users_tech) AND
active_role='expert' AND price IS NOT NULL
ORDER BY 
CASE WHEN _sort_column = 'rating' AND _sort_direction = 'DESC' THEN rating END DESC,
CASE WHEN _sort_column = 'price' AND _sort_direction = 'ASC' THEN price END ASC, rating ASC, registry_date ASC,
CASE WHEN _sort_column = 'price' AND _sort_direction = 'DESC' THEN price END DESC, rating DESC, registry_date ASC
OFFSET ($1 - 1) * 10
LIMIT 10
$$ LANGUAGE SQL;

函数执行如下：

pool.query(`SELECT * FROM find_experts($1, $2, $3)`, [1, 'price', 'DESC'])

实际结果：

250
12
11
102
43
856
21
34
63
85

预期结果：

856
250
102
85
63
43
34
21
12
11

Answer 1

修复：

您需要将所有选项包装在order by子句中的case语句中，以使其按预期工作：

ORDER BY 
CASE WHEN _sort_column = 'rating' AND _sort_direction = 'DESC' THEN rating END DESC,
CASE WHEN _sort_column = 'price' AND _sort_direction = 'ASC' THEN price END ASC, 
CASE WHEN _sort_column = 'price' AND _sort_direction = 'ASC' THEN rating END ASC, 
CASE WHEN _sort_column = 'price' AND _sort_direction = 'ASC' THEN registry_date END ASC,
CASE WHEN _sort_column = 'price' AND _sort_direction = 'DESC' THEN price END DESC, 
CASE WHEN _sort_column = 'price' AND _sort_direction = 'DESC' THEN rating END DESC, 
CASE WHEN _sort_column = 'price' AND _sort_direction = 'DESC' THEN registry_date END ASC

或者切换到plpgsql和动态sql，只需按列和方向连接所需的顺序。这是你的数据库<gt；小提琴，固定。

说明：

您的case语句构成独立的order by子句。我想你可能会假设该部分中的每一行都是不同的情况，但它们实际上最终是order by的一组半常量列，所以它们的格式更合理：

ORDER BY 
CASE WHEN _sort_column = 'rating' AND _sort_direction = 'DESC' THEN rating END DESC,--order column 1, sometimes skipped
CASE WHEN _sort_column = 'price' AND _sort_direction = 'ASC' THEN price END ASC, --order column 2, sometimes skipped
rating ASC,        --order column 3, never skipped
registry_date ASC, --order column 4, never skipped
CASE WHEN _sort_column = 'price' AND _sort_direction = 'DESC' THEN price END DESC, --order column 5, sometimes skipped
rating DESC,       --order column 6, never skipped
registry_date ASC  --order column 7, never skipped

以CASE开头的每一行最终都将是您想要的，或者是null，因为您没有指定else块。Order by将跳过那些被评估为null的case语句，所以在您的示例调用中，它最终会像这样

ORDER BY 
null::integer DESC, --skipped
null::integer ASC, --skipped
rating ASC, 
registry_date ASC,
price DESC, 
rating DESC, 
registry_date ASC

请注意，PostgreSQL允许将order by中的case或select求值为null，但不允许将文字强制转换为可排序的内容。