I have the following postgres stored function. Last CASE WHEN in ORDER BY returns data with incorrect ordering. However, simiral second CASE WHEN works as expected. I have double checked that sort_column and sort_direction are correct. Any help is appreciated

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;

Function gets executed like that:

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

Actual result:

250
12
11
102
43
856
21
34
63
85

Expected result:

856
250
102
85
63
43
34
21
12
11

CodePudding user response：

Fix:

You need to wrap all your options in case statements in the order by clause for it to work as you expect:

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

Or switch to plpgsql and dynamic sql, to just concatenate your desired order by column and direction. Here's your db<>fiddle, fixed.

Explanation:

Your case statements constitute standalone order by clauses. I think you might assume that each line in that section is a different case, but they actually end up as a semi-constant set of columns to order by, so they would make more sense formatted like so:

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

And each line beginning with CASE will either end up being what you want, or a null, because you did not specify an else block. Order by will skip those case statements evaluated to null, so in your example call, it ends up like this

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

Note that PostgreSQL allows for a case or select in an order by to be evaluated to null, but doesn't allow a literal unless casted to something sortable.