3 ms·

> The author(s?) almost certainly have no idea what they're talking about. > They're using select * in a group by query with no aggregates. The author wrote s

> The author(s?) almost certainly have no idea what they're talking about. > They're using select * in a group by query with no aggregates. The author wrote something akin to SELECT A.*, B.x FROM A JOIN B GROUP BY A.id, b.x This is perfectly valid SQL, AS per the SQL standard: "Each <column reference> in the <group by clause> shall unambiguously reference a column of T. A column referenced in a <group by clause> is a grouping column". From SQL-92 reference, section 7.7, http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt When using offensive language, you'd better be sure of the technical quality of what you write, if you don't want to show an ugly portrait of yourself.

matwood 6y ago
A.* assumes every column in A is functionally dependent on the A.id. This may be the case, but is a huge source of bugs I have seen related to MySQL, and would set my spidey sense off if I saw it in code. The problem is that older versions of MySQL allowed non-functionaly dependent columns in the select list and would randomly put one of the values in the result set (every other sane database at the time properly errored out). At this point, I would consider it bad practice to run MySQL in anything other than only full group by mode. See here: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.ht... And here: https://www.percona.com/blog/2019/05/13/solve-query-failures-regarding-only_full_group_by-sql-mode/ https://www.percona.com/blog/2019/05/13/solve-query-failures...
gfody 6y ago
> This is perfectly valid SQL no, it's not. this query only runs because postgres is not compliant with SQL-99 T301. the query is unambiguously invalid under SQL-92. > When using offensive language fwiw I regret my tone but this article deserves to be criticized. I love and respect rdbms technology and it's exciting to see anything "SQL" in a headline on HN - but then it bums me out to see bogus terms tossed around and insane queries presented as normal. if this were some beginner blog writing about lessons learned that's one thing, but this is a professional consultancy firm writing with the air of "you can trust us, we're experts".

matwood 6y ago

A.* assumes every column in A is functionally dependent on the A.id. This may be the case, but is a huge source of bugs I have seen related to MySQL, and would set my spidey sense off if I saw it in code. The problem is that older versions of MySQL allowed non-functionaly dependent columns in the select list and would randomly put one of the values in the result set (every other sane database at the time properly errored out). At this point, I would consider it bad practice to run MySQL in anything other than only full group by mode. See here: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.ht... And here: https://www.percona.com/blog/2019/05/13/solve-query-failures-regarding-only_full_group_by-sql-mode/ https://www.percona.com/blog/2019/05/13/solve-query-failures...

gfody 6y ago

> This is perfectly valid SQL no, it's not. this query only runs because postgres is not compliant with SQL-99 T301. the query is unambiguously invalid under SQL-92. > When using offensive language fwiw I regret my tone but this article deserves to be criticized. I love and respect rdbms technology and it's exciting to see anything "SQL" in a headline on HN - but then it bums me out to see bogus terms tossed around and insane queries presented as normal. if this were some beginner blog writing about lessons learned that's one thing, but this is a professional consultancy firm writing with the air of "you can trust us, we're experts".