On 11/27/2015 08:30 PM, R Smith wrote: > > > 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?
Thanks, non-deterministic was probably the wrong term to use. I wanted to find situations where a query's result depends on an implementation detail of SQLite, and the behaviour is not fully specified by the query itself, i.e. it could change from one version to the next, or even with same version by slight changes to the DB internal structures. So far I know of two possible situations like this: the 'arbitrary row choice' in the question above, and order of results in an unordered select. For the unordered select there is 'PRAGMA reverse_unordered_selects' that I could use to find bugs, and I wanted to know if there is an equivalent functionality for the arbitrary row choice. However as shown below this is probably the wrong question to ask, I should avoid using columns that are not part of an aggregate function/group by in the first place (if using group by). > > 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. Thanks, this is actually what I was looking for. Can I enforce/detect when a query adheres to this more strict semantics in SQLite? > 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. Good advice, using SUM() for all columns in this case would've avoided my original problem (even where only one row is expected because a primary key for same table was in group by). > If you are making an SQLite wrapper, I would even advise enforcing this > behaviour even though SQLite can technically make do without it. I'm not writing a wrapper, just adding some checks to my application from time-to-time that print more details when it is possible to determine what went wrong via an SQLite API. For example if sqlite3_close fails with SQLITE_BUSY I loop through sqlite3_next_stmt and print the queries that were not finalized properly. -- Edwin T?r?k | Co-founder and Lead Developer Skylable open-source object storage: reliable, fast, secure http://www.skylable.com
