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.
The 1=1 doesn't hurt, as it will be removed by the query optimizer anyways.
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}"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
select * from table where (1=0)