So you are saying that: Select a from t1 except select a from t2 order by a collate nocase;
Should internally be computed as Select a from (select a from t1 except select a from t2) order by a collate nocase; ? --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: Monday, 06 May, 2013 20:46 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE > > On Mon, May 6, 2013 at 10:29 PM, Simon Slavin <slav...@bigfraud.org> > wrote: > > > > > The problem, I think, is that a COLLATE shouldn't change any value > anyhow, > > no matter which SELECT the ORDER clause is attached to. The COLLATE > > modifier is part of the ORDER BY clause. It is there to change the > ORDER > > that the values are returned in, not the values themselves. > > > And, indeed, that is exactly what COLLATE is doing. > > The problem is this: When SQLite sees the ORDER BY clause on the EXCEPT > it > tries to do the EXCEPT using a merge. In other words, it computes two > subqueries: (SELECT .. FROM x ORDER BY ...) and (SELECT ... FROM y ORDER > BY ...). Then it looks at the output of these subqueries, row by row. > > (1) x<y: output x > (2) x>y: pop y > (3) x=y: pop and discard both x and y > > You can implement INTERSECT, UNION, and UNION ALL in much the same way, by > supplying different actions for each of the above cases. > > The above works great (and is very efficient) if the collating sequence of > the ORDER BY is the same as the natural collating sequence of the output > columns. If it isn't, then the above code gives the wrong answer. The > basic problem is that SQLite is not recognizing that the collating > sequences are different and is trying to use the algorithm above when it > it > shouldn't. > > This was an oversight when I first implemented the merging algorithm 5 > years ago. It didn't occur to me then (and apparently hasn't occurred to > anybody else in the last 5 years) that the collating sequence in the ORDER > BY might be different from the natural collating sequence of the result > columns. > > Unfortunately, the merge algorithm outlined above is the only means SQLite > currently has for doing a compound select that contains an ORDER BY. In > order to fix this, I'm going to have to come up with a whole new > algorithm, > just for this case. ON the other hand, since nobody has noticed it in 5 > years, presumably it doesn't come up that often, so there isn't a huge > rush > to get the fix in. So I'm going to take my time and try to come up with > the minimally disruptive fix. > > > > > And something like > > > > SELECT x EXCEPT y > > > > is subtracting one set from another, and in sets the order doesn't > matter. > > The problem is something like doing > > > > SELECT words FROM dictionary ORDER BY words COLLATE NOCASE > > > > and getting all the words back as capital letters. This shouldn't > happen. > > > > Simon. > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
