Works in MySQL but not in PG - why? - Mailing list pgsql-novice
From | Pól Ua Laoínecháin |
---|---|
Subject | Works in MySQL but not in PG - why? |
Date | |
Msg-id | CAF4RT5Q13MLd-=80TkgphjkovHY9dw1X9F40Afg26kkJgA3=Ng@mail.gmail.com Whole thread Raw |
In response to | Re: Recommended Modeling Tools? (Morten <morten99@gmail.com>) |
Responses |
Re: Works in MySQL but not in PG - why?
|
List | pgsql-novice |
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...
pgsql-novice by date: