> On Mon, Jun 20, 2016 at 5:18 PM, Keith Medcalf <[email protected]> > wrote: > > > > > You have all ill-phrased correlated subquery. Only by putting c.id in > > the select list and adding c.id to the group go do you have a properly > > phrased correlated subquery. > > > > However, > > > > The projection (join) will be performed first using whatever order and > > indexes the optimizer decides to use to do that. Whenever it is going > to > > "pop out" a result the subquery will be executed to provide that data. > > That means that if there are multiple values of c.id to go with a single > > value of a.id, you will get a result for "one of" those c.id values. > > Which one is an implementation detail but is entirely deterministic. > > > > > saying [some] 'one of' mean it isn't deterministic . As data changes, > id's get added, analyzes and optimizations are performed... the results > may > change with time. If you're collapsing a whole branch of a tree into a > single line that has one of the values from somewhere on the branch, you > might want to think again about what you're doing. as long as the dataset > is exactly the same or is modified in ways that don't break the index > lookup ordering you'll be 'stable' > > I dunno it's not deterministic for all time
Of course it is. As long as the inputs are the same, the output will be the same. There is no randomness involved. If you change an input (data in the database, statistics, optimizer, indices, etc) then the output may be change -- but it is still determined by the inputs and only the inputs. However, for any given state of the inputs it is possible to determine the output, and if the inputs are held constant, the output will remain constant. Hence it is indeed deterministic. However, like celestial mechanics, you may not know all the inputs and their interactions to know if one of them has changed or not. That does not make the output non-deterministic. It just makes it complicated. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
