On 2015/11/27 4:44 PM, Adam Devita wrote: > I think the OP meant to write: > "If the expression is an aggregate expression, it is evaluated across > all rows in the group. Otherwise, it is evaluated against a single > arbitrarily chosen row from within the group. " > > Is there a way I could programmatically determine that a query is > going to use an arbitrarily chosen row from within the group at query > prepare time?
I think you are correct about what the OP meant. Which makes it easier to answer: There is no need to programmatically check whether the row selected will be arbitrary or not. If the reference is inside an aggregate function, then it is evaluated across all rows (i.e the result is some unknown), and if it isn't, then an arbitrary row is picked and the result is equally unknown before the query completes. Programmatically you can just always assume the result will be "some" value in the database. Let me explain better, let's assume the query contains MAX(x)... The result will be from whichever row contains the max, and if the x was not in the aggregate function, it will be from some row (which may or may not be the one with the max value). In both cases, you have NO chance of knowing which row that would be until the query finishes, so ALWAYS assume an arbitrary row (or Always-non-deterministic, to use the OP's words). Other SQL engines enforces a more strict (and I have to say: more correct) semantic where every term NOT in the GROUP BY clause MUST be contained in an aggregate function. Either way, the returned result will never be pre-determinable by an onlooker function (which isn't inspecting the data via other means) and as such there is no difference between being contained in an aggregate function or not - the resulting value will never be pre-determinable - and in the case of SQLite (where not contained in an aggregate function), the result may even differ from a previous run (according to documentation, though my experience is that it always returns the exact same result, so it maintains determinism, but you shouldn't bank on it.) Bottom line: Just put the darn query fields in aggregate functions. If you are making an SQLite wrapper, I would even advise enforcing this behaviour even though SQLite can technically make do without it.
