3 ms·
Other tricky cases: WITH c AS ( SELECT random() AS r FROM t1 ) SELECT * FROM c WHERE r < 0.5; I've seen bugs where this returned rows with r > 0.5. The sema
by Sesse__ 3y ago
Other tricky cases:
WITH c AS (
SELECT random() AS r FROM t1
)
SELECT * FROM c WHERE r < 0.5;
I've seen bugs where this returned rows with r > 0.5. The semantics of such functions can be very tricky when the database engine assumes it can freely evaluate everything at any point. Similarly:
SELECT random() AS r FROM t1 WHERE r < 0.5 HAVING r < 0.5;
ANSI SQL doesn't allow this query (projection comes after filtering, so you cannot filter on something that is only created in projection), but some databases do, and can give counterintuitive results.