3 ms·
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. Ho
by _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 agoOut of curiosity, is there a list of built-in non-deterministic SQL functions?
- Sesse__ 3y agoIn Postgres, run: select proname from pg_catalog.pg_proc where provolatile='v';
- maxbond 3y agoWhy shouldn't I be able to randomly sample a table? Some common use cases include queues and A/B tests.
- _a_a_a_ 3y agoYou 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...