Works in MySQL but not in PG - why?

Hi all,

I have a query which works in MySQL but not in PostgreSQL and I would
be very grateful to receive an explanation as to why.

The scenario is this. I have records like this (fiddles for MySQL and
PG given at bottom)

CREATE TABLE tab
(
  t_id    SERIAL NOT NULL PRIMARY KEY,
  t_key   INTEGER NOT NULL,
  t_name  VARCHAR(10) NOT NULL,
  t_value VARCHAR(10) NOT NULL
);

INSERT INTO tab (t_key, t_name, t_value)
VALUES
(75, 'Couleur', 'Bleu'),
(75, 'Taille', 'Grand'),
(75, 'Poids',  '20'),
(75, 'Teint',  'Y'),

(76, 'Couleur', 'Bleu'),
(76, 'Taille', 'Grand'),
(76, 'Poids',  '20'),
(76, 'Teint',  'Y'),

(77, 'Couleur', 'Bleu'),
(77, 'Taille', 'Grand'),
(77, 'Poids',  '20'),
(77, 'Teint',  'N');

Now, I want to be able to 75 and 76 as matching because they match on
all values of both t_name and t_value. 77 doesn't match because Teint
is 'N' whereas for the others it's 'Y'. OK, so, I have the following
query (which works for MySQL 5.7 and 8.0.17 (but not 5.6 strangely).

SELECT
  DISTINCT LEAST(t1key, t2key) AS "lst",
        GREATEST(t1key, t2key) AS "gst",
  COUNT(LEAST(t1key, t2key)) AS "mn_c"
  -- COUNT(GREATEST(t1key, t2key)) AS mx_c
FROM
(

  SELECT t1.t_key AS "t1key", t1.t_name AS "t1name", t1.t_value AS "t1value",
         t2.t_key AS "t2key", t2.t_name AS "t2name", t2.t_value AS "t2value"
  FROM tab t1
  JOIN tab t2
  ON t1.t_key != t2.t_key
    AND t1.t_name = t2.t_name
    AND t1.t_value = t2.t_value
  ORDER BY t1.t_id, t2.t_id
)
AS t1
GROUP BY LEAST(t1key, t2key), GREATEST(t1key, t2key)
HAVING COUNT(LEAST(t1key, t2key))/2 = (SELECT COUNT(tab.t_key) FROM
tab WHERE t_key = t1key)
ORDER BY 1, 2, 3;

Now, in MySQL this gives

lst gst mn_c
75 76  8

but in PG, I get the following error

ERROR: subquery uses ungrouped column "t1.t1key" from outer query
LINE 20: ...)/2 = (SELECT COUNT(tab.t_key) FROM tab WHERE t_key = t1key)

                                     ^

The PG fiddle is here
https://dbfiddle.uk/?rdbms=postgres_10&fiddle=51d80aa3ce4e82cf18691eea7c7a1075

and the MySQL one is here

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=50ba15f39909c98958bceb2a79b36ac7

I have fiddled around (pardon the put 8-) ) but can't seem to get this to work.

I would be grateful for a) a working query in PG and more especially
b) an explanation of what's going on and why the MySQL query (which
appers valid to me) won't work for PG. Now, I'm fully aware that PG is
**WAY** more standards compliant than MySQL, but this one has me
baffled.

I know that I could probably introduce another level of outer query to
get the result I require but that strikes me as inelegant. I'm
probably missing some fundamental part of set theory and relational
algebra. Any references, URLS, other sources that would explain this
to me would be gratefully received.

TIA and rgs,


Pól...