-
-
Save webdevilopers/06617b75c77f0499f8c8bffc49284648 to your computer and use it in GitHub Desktop.
Table structure: | |
test_id place_id | |
9b949b1a-414b-43f0-8940-9b821f1e2098 a3358c22-af08-485f-933e-f4d6ce900882 | |
5177ab63-5ecd-4241-913e-36fb9d917efd a3358c22-af08-485f-933e-f4d6ce900882 | |
c16ac09c-8ccf-45b9-8593-7ffceb4d5d9e a3358c22-af08-485f-933e-f4d6ce900882 | |
MySQL: SELECT test_id, place_id FROM transmissions GROUP BY place_id | |
Result (CORRECT): | |
test_id place_id | |
9b949b1a-414b-43f0-8940-9b821f1e2098 a3358c22-af08-485f-933e-f4d6ce900882 | |
PostgreSQL: | |
SELECT test_id, place_id FROM transmissions | |
GROUP BY place_id | |
Error: [42803] ERROR: column "transmissions.test_id" must appear in the GROUP BY clause or be used in an aggregate function Position: 8 | |
Attempt: | |
SELECT test_id, place_id FROM transmissions | |
GROUP BY place_id, test_id | |
Result (WRONG): | |
test_id place_id | |
c16ac09c-8ccf-45b9-8593-7ffceb4d5d9e a3358c22-af08-485f-933e-f4d6ce900882 | |
5177ab63-5ecd-4241-913e-36fb9d917efd a3358c22-af08-485f-933e-f4d6ce900882 | |
9b949b1a-414b-43f0-8940-9b821f1e2098 a3358c22-af08-485f-933e-f4d6ce900882 | |
Workaround: | |
SELECT | |
DISTINCT ON (place_id) test_id, place_id | |
FROM transmissions | |
Result (CORRECT): | |
test_id place_id | |
9b949b1a-414b-43f0-8940-9b821f1e2098 a3358c22-af08-485f-933e-f4d6ce900882 | |
What if I wanted? | |
MySQL: SELECT test_id, place_id FROM tests GROUP BY place_id ORDER BY test_id ASC | |
Result: | |
test_id place_id | |
c16ac09c-8ccf-45b9-8593-7ffceb4d5d9e a3358c22-af08-485f-933e-f4d6ce900882 | |
PostgreSQL: | |
SELECT | |
DISTINCT ON (place_id) test_id, place_id | |
FROM transmissions | |
ORDER BY test_id ASC | |
Error: [42P10] ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions Position: 25 | |
Attempt: | |
SELECT | |
DISTINCT ON (place_id, test_id) test_id, place_id | |
FROM transmissions | |
ORDER BY test_id ASC, place_id | |
Result (WRONG): | |
test_id place_id | |
5177ab63-5ecd-4241-913e-36fb9d917efd a3358c22-af08-485f-933e-f4d6ce900882 | |
c16ac09c-8ccf-45b9-8593-7ffceb4d5d9e a3358c22-af08-485f-933e-f4d6ce900882 | |
9b949b1a-414b-43f0-8940-9b821f1e2098 a3358c22-af08-485f-933e-f4d6ce900882 |
As a partial explanation of why what worked in MySQL doesn't directly work in PgSQL (or other DBs):
MySQL allowed some loose queries when using GROUP BY. You can make it stricter using the ONLY_FULL_GROUP_BY sql_mode (which is - or should be, if your distro doesn't mess with the defaults - enabled by default on new installs).
See https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
I also recommend reading up on the other sql_mode values and making sure you're at least using the 5.7+ default sql_mode. These can highlight other issues (data corruption through value truncation), especially when developing using languages such as PHP which ignore MySQL's warnings.
Thanks for your feedback @AllenJB ! I will recommend those MySQL settings to our DBA. Before migrating to PgSQL he should "fix" those queries.
It feels a little bit like introducing "strict mode" and "type hinting" to legacy code in PHP! ;)
Came from: https://twitter.com/webdevilopers/status/1477974865911193606