The Case of a Curious SQL Query
buttondown.email
buttondown.email
https://www.postgresql.org/docs/15/xfunc-volatility.html
I do note that the postgres explain appears to keep the join filter on the top level.
explain select count(*) from
generate_series(0,999) a
inner join
generate_series(0,999)
on random() < 0.5
;
QUERY PLAN
--------------------------------------------------------------------------------------
Aggregate (cost=25843.34..25843.35 rows=1 width=8)
-> Nested Loop (cost=0.01..25010.01 rows=333333 width=0)
Join Filter: (random() < '0.5'::double precision)
-> Function Scan on generate_series a (cost=0.00..10.00 rows=1000 width=0)
-> Function Scan on generate_series (cost=0.00..10.00 rows=1000 width=0) create function random2() returns float as $$
begin
return random();
end;
$$ language plpgsql stable;
select count(*) from one_thousand join one_thousand b on random2() > 0.5;
Aggregate (cost=97615.13..97615.14 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=1)
-> Result (cost=0.25..81358.88 rows=6502500 width=0) (actual time=0.026..0.027 rows=0 loops=1)
One-Time Filter: (random3() > '0.5'::double precision)
-> Nested Loop (cost=0.25..81358.88 rows=6502500 width=0) (never executed)
-> Seq Scan on one_thousand (cost=0.00..35.50 rows=2550 width=0) (never executed)
-> Materialize (cost=0.00..48.25 rows=2550 width=0) (never executed)
-> Seq Scan on one_thousand b (cost=0.00..35.50 rows=2550 width=0) (never executed)> sqlite> explain select
About 5 years ago, sqlite introduced EXPLAIN QUERY PLAN to get something more like what other databases give with just EXPLAIN.
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.
This is often not true. SQL Server for one, and I know it isn't the only common DB that behaves this way:
SELECT RAND() AS NotSoRandom
, RAND() AS NotSoRandomAgain
FROM SomeTable
NotSoRandom NotSoRandomAgain
0.417713566427078 0.417713566427078
0.417713566427078 0.417713566427078
0.417713566427078 0.417713566427078
0.417713566427078 0.417713566427078
...
Now if you could mark a function as "pure", that is, it's outputs are strictly dependent on it's inputs, the optimizer would be free to, well, optimize it's location.
I think this is what postgres is trying to do with it's function volatility syntax(volatile, stable, immutable) one of the examples in the article, cockroachdb, has inherited this syntax. but they either drew a different conclusion as to what it means, or they ignore it.
[1] https://www.sqlite.org/src/info/64c9bc7494f3d220a27498137551...
But actually you have a point in that half the problem is the predicate is not linked to the tables, but the other half is the predicate is not predictable, after all it is a RAND(). That compounds the issue.
> But actually you have a point in that half the problem is the predicate is not linked to the tables
No, it really isn't a problem at all that the predicate isn't linked to the tables. That in itself is entirely legal and unproblematic.
SELECT * FROM t1 WHERE 2+2=4
Which is semantically identical to
SELECT * FROM t1
While the first is correct in that it has a semantic meaning and a valid output, wouldn't you rather have the system warn you that you've written something redundant? Because it's hardly likely a human would write the former if they meant the latter.
As an aside, C compilers generally try to give warnings (not errors) on things like this, but need tons of heuristics to distinguish the “obviously wrong” cases from the “not obviously wrong” cases (e.g., those that arose from inlining and/or constant folding and/or preprocessor expansion and/or dead code removal).
WHERE 1=1
WHERE 1=1
AND t1.col1 = 1
AND t2.col2 = 1
WHERE t1.col1 = 1
AND t2.col2 = 1
Point being, don't assume that humans will never write an always-true predicate.
I have, over the course of my career, found that the teams that tend to do this are pure-SQL teams ie in DB developers who spend all their time writing - and more importantly - reading SQL. Doing this actually removes a lot of debugging and code-fixing friction
where_clause = "where 1=1"
if video.nsfw:
where_clause += " and age > 18"
if whatever:
where_clause += " and whatever"
query = "select * from user {where_clause}"The 1=1 doesn't hurt, as it will be removed by the query optimizer anyways.
select * from table where (1=0)
random() < 0.5
abc.a + random() < abc.a + 0.5
Now, that predicate is biased towards table abc, but that’s correctible:
abc.a + def.d + random()
< abc.a + def.d + 0.5
So, you can change the spec to make the simple cases invalid sql, but that won’t get rid of this problem.
select proname from pg_catalog.pg_proc where provolatile='v';
This applies specifically to exactly one table so the issue of dubious predicate pushdown never arises.
That said, I understand the SQL standard samples at the page level so you get a database-page-worth of results (8k in MSSQL) rather than a properly scattered sample. AIUI the standard allows for any other kind of sampling, but MSSQL doesn't support that (yet) but I believe postgres does. https://stackoverflow.com/questions/49061229/in-postgresql-h...