3

I found this solution online to a HackerRank problem:

SELECT c.hacker_id, h.name, COUNT(c.challenge_id) AS cnt 
FROM Hackers AS h 
JOIN Challenges AS c ON h.hacker_id = c.hacker_id
GROUP BY c.hacker_id, h.name 
HAVING
    cnt = (SELECT COUNT(c1.challenge_id) FROM Challenges AS c1 GROUP BY c1.hacker_id ORDER BY COUNT(*) DESC LIMIT 1) OR
    cnt NOT IN (SELECT COUNT(c2.challenge_id) FROM Challenges AS c2 GROUP BY c2.hacker_id HAVING c2.hacker_id <> c.hacker_id)
ORDER BY cnt DESC, c.hacker_id;

As you can see, cnt is defined in SELECT, and then used in HAVING. My understanding was that, logically speaking, GROUP BY and HAVING run before SELECT. Therefore I would expect that SQL would not recognize cnt when executing GROUP BY, and throw an error. However, the code gives the correct solution.

Can anyone explain why? Maybe MySQL corrects for this artificially?

GMB
  • 216,147
  • 25
  • 84
  • 135
spheroidic
  • 199
  • 7
  • 1
    *As you can see, cnt is defined in SELECT, and then used in HAVING.* This is a norma. Moreover, using HAVING instead of WHERE is common technique to refer to complex output expression even when GROUP BY is not used. Formally - output expressions are available after GROUP BY (except the expressions which uses window functions which are evaluated over output rowset). And using output column alias in HAVING clause while GROUP BY is present is not even an extension - this is standard SQL constructon. – Akina Oct 16 '20 at 20:16
  • MySQL special. Not allowed in ANSI/ISO SQL. – jarlh Oct 16 '20 at 20:25

1 Answers1

1

Your assumption that aliases defined in the SELECT clause cannot be used in the HAVING clause is generally true: this is not allowed in standard SQL (also called ANSI SQL), and most databases do not allow that.

However, MySQL extends the standard, as explained in the documentation:

The MySQL extension permits the use of an alias in the HAVING clause for the aggregated column.

So the code you are showing is not valid ANSI SQL, but it is a valid MySQL query. There are many other extensions to ANSI SQL that MySQL implements, and that are described in the linked documentation.

More generally speaking, there is no database that I know about that fully and strictly complies with the ANSI spec. They all have missing features and extensions. Learning SQL, you also need to learn the specificities of the database(s) you are running.

GMB
  • 216,147
  • 25
  • 84
  • 135