MySQL breaking SQL standard of HAVING before SELECT?

Viewed 30

Q: Does MySQL break the SQL standard for evaluating HAVING before SELECT?

Detail: From MySQL Having Tutorial:

SQL standard specifies that the HAVING is evaluated before SELECT clause and after GROUP BY clause

So for the LeetCode problem 1459. Rectangles Area, with data schema:

Create table If Not Exists Points (id int, x_value int, y_value int)
Truncate table Points
insert into Points (id, x_value, y_value) values ('1', '2', '7')
insert into Points (id, x_value, y_value) values ('2', '4', '8')
insert into Points (id, x_value, y_value) values ('3', '2', '10')

as expected, the following query fails in both PostgreSQL and MySQL:

SELECT
    points1.id AS p1,
    points2.id AS p2,
    ABS(points2.x_value - points1.x_value) * ABS(points2.y_value - points1.y_value) AS area
FROM Points points1, Points points2
WHERE points1.id < points2.id
    AND area > 0
ORDER BY area DESC,p1 ASC,p2 ASC

since, as I interpet it, the query attempts to use area in WHERE before area is defined in SELECT. Easy enough.

But the following query fails in PostgreSQL (expected) while it succeeds in MySQL (unexpected!):

SELECT
    points1.id AS p1,
    points2.id AS p2,
    ABS(points2.x_value - points1.x_value) * ABS(points2.y_value - points1.y_value) AS area
FROM Points points1, Points points2
WHERE points1.id < points2.id
HAVING area > 0
ORDER BY area DESC,p1 ASC,p2 ASC

If MySQL is able to use area in HAVING before it's defined in SELECT, is MySQL not breaking the SQL standard? Is PostgreSQL not more correct in this way?

0 Answers
Related