3 ms·

Replace random() < 0.5 by abc.a + random() < abc.a + 0.5 and you have (almost because of the inexactness of float computations) the same query, but that

Replace random() < 0.5 by abc.a + random() < abc.a + 0.5 and you have (almost because of the inexactness of float computations) the same query, but that isn’t “in no way related to the tables”. Now, that predicate is biased towards table abc, but that’s correctible: abc.a + def.d + random() < abc.a + def.d + 0.5 If the SQL engine is allowed to ignore the subtleties of floats, it can still prove that in this query the predicate isn’t really related to the tables, but it can’t do that in general. So, you can change the spec to make the simple cases invalid sql, but that won’t get rid of this problem.

_a_a_a_ 3y ago
You are completely correct, but please see below where I acknowledge that half the problem is that a nondeterministic input - the rand() - is the other half. Hopefully by banning nondeterministic functions in predicates, then requiring predicates to be linked to the tables, that should fix it. Good catch, thanks.
- chris_wot 3y ago
  Out of curiosity, is there a list of built-in non-deterministic SQL functions?
  - Sesse__ 3y ago
    In Postgres, run: select proname from pg_catalog.pg_proc where provolatile='v';
- maxbond 3y ago
  Why shouldn't I be able to randomly sample a table? Some common use cases include queues and A/B tests.
  - _a_a_a_ 3y ago
    You most definitely should be able to do that, and the SQL standard provides for this (sort of, kind of): TABLESAMPLE. 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-how-do-i-random-sample-from-table-based-on-the-proportion-of-each https://stackoverflow.com/questions/49061229/in-postgresql-h...

_a_a_a_ 3y ago

You are completely correct, but please see below where I acknowledge that half the problem is that a nondeterministic input - the rand() - is the other half. Hopefully by banning nondeterministic functions in predicates, then requiring predicates to be linked to the tables, that should fix it. Good catch, thanks.

chris_wot 3y ago
Out of curiosity, is there a list of built-in non-deterministic SQL functions?
- Sesse__ 3y ago
  In Postgres, run: select proname from pg_catalog.pg_proc where provolatile='v';
maxbond 3y ago
Why shouldn't I be able to randomly sample a table? Some common use cases include queues and A/B tests.
- _a_a_a_ 3y ago
  You most definitely should be able to do that, and the SQL standard provides for this (sort of, kind of): TABLESAMPLE. 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-how-do-i-random-sample-from-table-based-on-the-proportion-of-each https://stackoverflow.com/questions/49061229/in-postgresql-h...