Thread: Multiple records returned by a JOIN

Good evening,

in PostgreSQL 10.3 I have written the following custom function (trying to fetch 10 latest games played by a user):

CREATE OR REPLACE FUNCTION words_stat_games(
                in_social integer,
                in_sid    text
        ) RETURNS TABLE (
                out_gid    integer,
                out_reason text,
                out_state1 text,
                out_score1 integer,
                out_score2 integer
        ) AS
$func$
        SELECT
                g.gid,
                g.reason,
                CASE WHEN g.player1 = s.uid THEN g.state1 ELSE g.state2 END,
                CASE WHEN g.player1 = s.uid THEN g.score1 ELSE g.score2 END,
                CASE WHEN g.player1 = s.uid THEN g.score2 ELSE g.score1 END
        FROM    words_games g
        JOIN    words_social s ON (g.player1 = s.uid OR g.player2 = s.uid)
        WHERE   g.finished IS NOT NULL
        ORDER BY g.finished DESC
        LIMIT   10;

$func$ LANGUAGE sql STABLE;

Unfortunately, it returns multiple records and with wrong values too:

 # select * from words_stat_games(1, '109998440415755555271');
 out_gid | out_reason | out_state1 | out_score1 | out_score2
---------+------------+------------+------------+------------
    1978 | resigned   | lost       |          0 |          0
    1978 | resigned   | won        |          0 |          0
    1847 | resigned   | lost       |        234 |        441
    1847 | resigned   | won        |        441 |        234
    1847 | resigned   | won        |        441 |        234
    1800 | expired    | won        |         41 |          0
    1798 | expired    | lost       |          8 |         28
    1798 | expired    | won        |         28 |          8
    1800 | expired    | lost       |          0 |         41
    1926 | expired    | won        |         35 |         13
(10 rows)

Why does it return the game 1978 twice and also the out_state1 changes between 'lost' and 'won' values?

I hoped to handle that with my "CASE WHEN g.player1 = s.uid THEN g.state1 ELSE g.state2 END", but it obviously fails

Below are my 2 table definitions, thank you for any hints.

CREATE TABLE words_social (
        sid     text     NOT NULL,
        social  integer  NOT NULL CHECK (0 < social AND social <= 64),
        uid     integer  NOT NULL REFERENCES words_users ON DELETE CASCADE,
        PRIMARY KEY(sid, social)
)

CREATE TABLE words_games (
        gid SERIAL PRIMARY KEY,

        created timestamptz NOT NULL,
        finished timestamptz,

        player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL CHECK (player1 <> player2),
        player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,

        reason  text, -- regular, resigned, expired, banned
        state1  text, -- tie, winning, losing, draw, won, lost
        state2  text, -- tie, winning, losing, draw, won, lost

        score1  integer NOT NULL CHECK (score1 >= 0),
        score2  integer NOT NULL CHECK (score2 >= 0)
);
CREATE INDEX words_games_state1_index ON words_games(state1);
CREATE INDEX words_games_state2_index ON words_games(state2);
CREATE INDEX words_games_reason_index ON words_games(reason);

Ok thanks, I guess I should switch to a SELECT UNION (first on uid = player1 and the uid = player2) and that will fix the CASE ... END for me.

Last night I have inexplicably missed 2 conditions /facepalm

Now my JOIN works ok, without multiple records -

CREATE OR REPLACE FUNCTION words_stat_games(
                in_social integer,
                in_sid    text
        ) RETURNS TABLE (
                out_gid    integer,
                out_reason text,
                out_state1 text,
                out_score1 integer,
                out_score2 integer
        ) AS
$func$
        SELECT
                g.gid,
                g.reason,
                CASE WHEN g.player1 = s.uid THEN g.state1 ELSE g.state2 END,
                CASE WHEN g.player1 = s.uid THEN g.score1 ELSE g.score2 END,
                CASE WHEN g.player1 = s.uid THEN g.score2 ELSE g.score1 END
        FROM    words_games g
        JOIN    words_social s ON (g.player1 = s.uid OR g.player2 = s.uid)
        WHERE   g.finished IS NOT NULL
        AND     s.social = in_social                          -- MISSED CONDITION
        AND     s.sid = in_sid                                   -- MISSED CONDITION
        ORDER BY g.finished DESC
        LIMIT   10;

$func$ LANGUAGE sql STABLE;