I just wish to confirm that the workaround shown by Nico works fine for me which makes the issue low priority from my point of view. Thanks all.
Staffan On Tue, May 7, 2013 at 5:00 AM, Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
