Re: Works in MySQL but not in PG - why?

Hi Tom (+ group), and thanks for gettng back to me,

>  Maybe the GROUP BY
> clause should just be "GROUP BY t1key, t2key"?

No "maybe" about it Tom - I continued working on it and the query I
finally came up with (which now works perfectly for both PG and MySQL)

SELECT
  DISTINCT LEAST(t1key, t2key) AS "lst",
        GREATEST(t1key, t2key) AS "gst",
  COUNT(LEAST(t1key, t2key)) AS "mn_c"  -- << NOT NECESSARY - SHOWS NO. OF DUPS
  -- 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 t1.t1key, t1.t2key  --- <<<< Exactly as you suggested
HAVING
COUNT(LEAST(t1key, t2key))    --  <<<<
  = (SELECT COUNT(tab.t_key) FROM tab WHERE t_key = t1.t1key)
AND
COUNT(GREATEST(t1key, t2key))
  = (SELECT COUNT(tab.t_key) FROM tab WHERE t_key = t1.t2key)
ORDER BY 1, 2;

And now the two "reciprocal" HAVING clauses pick out the required
records perfectly. I can sort of see the PG philiosphy of being
stricter - the ONLY_FULL_GROUP_BY fiasco springs to mind.

The PG fiddle
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e48caa900335a27e390a5394f4faef28

and MySQL one
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1b89903cb96d44c145b48a8e5172f289

It shows exactly the result desired - sets of records grouped by t_key
which are identical in both t_name and t_value are chosen


gst mn_c
75 76 4
75 78 4
76 78 4
85 86 3
92 93 2
94 95 1

So 75 is identical to 76 and 78. 85 is identical to 86 and so on.

The beauty of having chosen to test with PostgreSQL is that if I
hadn't done it, my original semi-working MySQL solution could have
failed under production conditions (sorry not could, would have
failed). Again proving the (virtually) infinite superiority of
PostgreSQL over MySQL.

Thanks again and rgs,

Pól...







>                         regards, tom lane