5 ms·

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 r

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.

progmetaldev 3y ago
I think that would probably be best, unless this type of thing is well documented (although I don't see it entering ANSI SQL).
Sesse__ 3y ago
You cannot reject a predicate just because it doesn't refer to a table. SELECT * FROM t1 WHERE 2+2=4 is a completely valid query and should be allowed.
- _a_a_a_ 3y ago
  It depends. For what you're suggesting, we have the cross join/cartesian join. That would be the correct thing to use in this case, not what you've put. 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.
  - Sesse__ 3y ago
    I have no idea what a Cartesian product would have to do with my query; there's only a single table there. > 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.
    - _a_a_a_ 3y ago
      I'm an idiot sorry about that. Okay, let's try again, suppose you wrote 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.
      - Sesse__ 3y ago
        There are _tons_ of SQL queries that involve redundant and/or stupid things. A lot of them come from autogenerated queries where the user has nearly zero control over the actual SQL. An optimizer's job is to optimize that away, not reject them as “please be more efficient”. They are legal queries. All legal queries should be accepted and executed, no matter how silly one may consider them. 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).
      - tremon 3y ago
        It's not my style, but a previous team would always add a where-clause starting with WHERE 1=1 Because then any and all query predicates would be nicely aligned on the following lines, like this: WHERE 1=1 AND t1.col1 = 1 AND t2.col2 = 1 (Why they couldn't just lower the indent to read WHERE t1.col1 = 1 AND t2.col2 = 1 Is beyond me) Point being, don't assume that humans will never write an always-true predicate.
        lgas 3y ago
        Presumably it's to make copy/pasting or otherwise adding/removing entries easy. In the latter case you have to muck about with WHERE vs AND if you modify the top entry whereas if the immediate condition after WHERE is 1=1 then all the real conditions take on the identical form of 'AND x' which can be copy/pasted, reordered, etc without complications.
        throwaway201606 3y ago
        100% this: part of this crew. 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
        3y ago
Someone 3y ago
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...

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.

progmetaldev 3y ago

I think that would probably be best, unless this type of thing is well documented (although I don't see it entering ANSI SQL).

Sesse__ 3y ago

You cannot reject a predicate just because it doesn't refer to a table. SELECT * FROM t1 WHERE 2+2=4 is a completely valid query and should be allowed.

_a_a_a_ 3y ago
It depends. For what you're suggesting, we have the cross join/cartesian join. That would be the correct thing to use in this case, not what you've put. 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.
- Sesse__ 3y ago
  I have no idea what a Cartesian product would have to do with my query; there's only a single table there. > 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.
  - _a_a_a_ 3y ago
    I'm an idiot sorry about that. Okay, let's try again, suppose you wrote 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.
    - Sesse__ 3y ago
      There are _tons_ of SQL queries that involve redundant and/or stupid things. A lot of them come from autogenerated queries where the user has nearly zero control over the actual SQL. An optimizer's job is to optimize that away, not reject them as “please be more efficient”. They are legal queries. All legal queries should be accepted and executed, no matter how silly one may consider them. 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).
    - tremon 3y ago
      It's not my style, but a previous team would always add a where-clause starting with WHERE 1=1 Because then any and all query predicates would be nicely aligned on the following lines, like this: WHERE 1=1 AND t1.col1 = 1 AND t2.col2 = 1 (Why they couldn't just lower the indent to read WHERE t1.col1 = 1 AND t2.col2 = 1 Is beyond me) Point being, don't assume that humans will never write an always-true predicate.
      - lgas 3y ago
        Presumably it's to make copy/pasting or otherwise adding/removing entries easy. In the latter case you have to muck about with WHERE vs AND if you modify the top entry whereas if the immediate condition after WHERE is 1=1 then all the real conditions take on the identical form of 'AND x' which can be copy/pasted, reordered, etc without complications.
        throwaway201606 3y ago
        100% this: part of this crew. 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
      - 3y ago

Someone 3y ago

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...