3 ms·

The author doesn't describe why they thought a cross product of two large tables, using a group by to remove duplicates, would be a good basis for this query.

by clcaev 6y ago

gfody 6y ago
The author(s?) almost certainly have no idea what they're talking about. Wtf are "diamond schemas" and "multi-branch joins"? This blog is the sole google result for that made up nonsense that's supposedly there to imply that it's somehow normal to put an OR expression with columns from two different tables into a join clause. They're using select * in a group by query with no aggregates. And they're talking about keywords as operations as if their SQL is directly executed and doing stuff rather than merely being the input for a plan generator (this despite the first paragraph - maybe one of them knows better than the other?). They say that query #2 is "the intuitive way of thinking through the problem, and something that someone with intermediate or advanced SQL skills could come up with" and is slow because it "eliminated the optimizer's ability to create an efficient query plan" - and they're wrong on both counts. That query is a nightmare no advanced SQL expert would write, and the plan that was generated was probably perfectly optimal to give them what they asked for. They just don't realize they asked for ridiculous nonsense that accidentally matches their expected results.
- idoubtit 6y ago
  > 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".

The author doesn't describe why they thought a cross product of two large tables, using a group by to remove duplicates, would be a good basis for this query.

by clcaev 6y ago

The author doesn't describe why they thought a cross product of two large tables, using a group by to remove duplicates, would be a good basis for this query.

gfody 6y ago

The author(s?) almost certainly have no idea what they're talking about. Wtf are "diamond schemas" and "multi-branch joins"? This blog is the sole google result for that made up nonsense that's supposedly there to imply that it's somehow normal to put an OR expression with columns from two different tables into a join clause. They're using select * in a group by query with no aggregates. And they're talking about keywords as operations as if their SQL is directly executed and doing stuff rather than merely being the input for a plan generator (this despite the first paragraph - maybe one of them knows better than the other?). They say that query #2 is "the intuitive way of thinking through the problem, and something that someone with intermediate or advanced SQL skills could come up with" and is slow because it "eliminated the optimizer's ability to create an efficient query plan" - and they're wrong on both counts. That query is a nightmare no advanced SQL expert would write, and the plan that was generated was probably perfectly optimal to give them what they asked for. They just don't realize they asked for ridiculous nonsense that accidentally matches their expected results.

idoubtit 6y ago
> 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".