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?