Interesting investigation. I'd argue that the SQL query itself is wrong in that the predicate is in no way related to the tables so the entire thing should be rejected.
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...
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)