4 ms·
Agreed. I read: > it turned out that this was technically not allowed. And thought “no, it turned out this query made no sense and had ill-defined results”.
by amluto 4y ago
Agreed. I read:
> it turned out that this was technically not allowed.
And thought “no, it turned out this query made no sense and had ill-defined results”.
But the rest of the description is odd:
> In particular, it was not allowed to have a grouped table ordered by a column that is not exposed by the SELECT list of the grouped table.
And I’m wondering if the example query was wrong and they meant:
SELECT product.product_id, sum(product_part.num)
FROM product JOIN product_part ON product.product_id = product_part.product_id
GROUP BY product.product_id, product.product_code
ORDER BY product.product_code;
In which case the change makes sense and is inoffensive.
- Dylan16807 4y agoIt's already grouping by the primary key of `product`. Why would you need to add another column in `product` to the group by?
- jbverschoor 4y agoIf that’s the very specific case, then they should be clear about it. But my interpretation was that our order by ungrouped column If it’s about preferring by non selected columns, that’d be fine. Something is wrong somewhere imo
- amluto 4y agoI assume that query optimizers are able to detect a GROUP BY of a primary key and other columns in the same table and optimize accordingly. But using this trick to change the set of valid queries seems odd. I think I'd rather see special syntax for it: GROUP BY table.*