Multiple records returned by a JOIN

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Multiple records returned by a JOIN
Date: 2018-04-10 15:44:27
Message-ID: CAADeyWh-aPVZeF=o++No18NaCa8utLbJ13LSeRB1FWSf74ULiA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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);

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2018-04-10 16:01:55 Re: Multiple records returned by a JOIN
Previous Message Edson Carlos Ericksson Richter 2018-04-10 15:35:00 Re: Postgresql Split Brain: Which one is latest