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);
On Tue, Apr 10, 2018 at 8:44 AM, Alexander Farber <
alexander.farber@gmail.com> wrote:
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?
JOIN words_social s ON (g.player1 = s.uid OR g.player2 = s.uid)
I'm more surprised by the single and triple than the doubles. Your join
against social, which has a record for each user, and games which has two
users, should result in an output with two rows, one for each of the users
in the games row. One of those users wins, and one of them loses. How you
have 2 winners in 1847 I cannot tell without seeing data. Why there is no
loser for 1926 is likewise a mystery.
David J.
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.
On Tue, Apr 10, 2018 at 6:01 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Tue, Apr 10, 2018 at 8:44 AM, Alexander Farber <
alexander.farber@gmail.com> wrote: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?JOIN words_social s ON (g.player1 = s.uid OR g.player2 = s.uid)
I'm more surprised by the single and triple than the doubles. Your join
against social, which has a record for each user, and games which has two
users, should result in an output with two rows, one for each of the users
in the games row. One of those users wins, and one of them loses. How you
have 2 winners in 1847 I cannot tell without seeing data. Why there is no
loser for 1926 is likewise a mystery.
I don't know why this triple is there, but it really is :-)
Regards
Alex
Or actually I can not use SELECT UNION here, because then I only get 10
records of the condition uid = player1 and then nothing would be left for
the other condition uid = player2
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;