Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
On Mon, May 6, 2013 at 9:39 PM, Keith Medcalf wrote: >> I read this: >> >> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a; >> >> as saying "select all a from t1 that are not in t2, *then* order that >> by that a". > > No, that is not what it says, actually. The translation of your statement > into SQL Declarative would be: Certainly if you look at SQL books and the SQLite docs you get the impression that the ordering term applies to the final result set. There's no point ordering, say, correlated sub-queries. Some RDBMSes seem to allow it, though I'm not sure what that means if anything outside the context of group_concat()-like aggregate functions -- perhaps it works only as a hint to the query planner that ordering an intermediate result set will net better performance? In any case, SQLite3 only allows ORDER BY at the very end of a select, not in sub-selects. And it applies the ORDER BY to the result set. It seems outrageous to think that a COLLATE clause on ORDER BY should be applied transitively to anything other than ordering the final set unless the result is guaranteed to be the same. At the very least it's weird and surprising. There *is* a point to ordering sets for aggregate function computation, because some aggregate functions are produce different results for the same input set in different orders. E.g., group_concat(). But this is a different story. Outside of such aggregate functions there's no point to ordering anything but the final result set. > select a from t1 where not exists (select * from t2 where a = t1.a) order by > a; You can rewrite EXCEPT that way. That doesn't mean that the first statement is not equivalent to this one; on the contrary, you can rewrite that way because the result is equivalent to the first. And this might well be logical proof that there's bug here! But I'm probably stretching something... Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
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 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 > > 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 > (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
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
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 > 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 (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
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
On Mon, May 6, 2013 at 10:29 PM, Simon Slavin 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) xy: 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
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
> I read this: > > SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a; > > as saying "select all a from t1 that are not in t2, *then* order that > by that a". No, that is not what it says, actually. The translation of your statement into SQL Declarative would be: select a from t1 where not exists (select * from t2 where a = t1.a) order by a; which, when a collation is applied in the order by, applies only to the ordering of the result set and not to the computation of the result set. --- () 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 Nico Williams > Sent: Monday, 06 May, 2013 19:36 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE > > On Mon, May 6, 2013 at 4:22 PM, Keith Medcalf wrote: > > It is difficult to determine what is correct behaviour. I would think > that the "order by" clause applies to the set operation, therefore any > collation being applied has to be applied also to the component sets > before the operation is performed. This implies the current operation is > correct although it may lead to, perhaps, non-intuitive results. > > I read this: > > SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a; > > as saying "select all a from t1 that are not in t2, *then* order that > by that a". > > I read this: > > SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase; > > to mean "select a from t1 that are not in t2, *then* order that by a > with the nocase collation". > > I don't understand why the COLLATE clause on the ordering term should > be applied transitively to the sub-expressions in the select instead > of only to the result set of the select. It makes no sense, > intuitively, and smells like a bug. But what does the standard say? > > Note that there's a workaround: > > SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2) ORDER BY > a COLLATE nocase; > > Nico > -- > ___ > 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
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
On 7 May 2013, at 2:35am, Nico Williams wrote: > I read this: > >SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a; > > as saying "select all a from t1 that are not in t2, *then* order that > by that a". > > I read this: > >SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase; > > to mean "select a from t1 that are not in t2, *then* order that by a > with the nocase collation". > > I don't understand why the COLLATE clause on the ordering term should > be applied transitively to the sub-expressions in the select instead > of only to the result set of the select. 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 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
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
On Mon, May 6, 2013 at 4:22 PM, Keith Medcalf wrote: > It is difficult to determine what is correct behaviour. I would think that > the "order by" clause applies to the set operation, therefore any collation > being applied has to be applied also to the component sets before the > operation is performed. This implies the current operation is correct > although it may lead to, perhaps, non-intuitive results. I read this: SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a; as saying "select all a from t1 that are not in t2, *then* order that by that a". I read this: SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase; to mean "select a from t1 that are not in t2, *then* order that by a with the nocase collation". I don't understand why the COLLATE clause on the ordering term should be applied transitively to the sub-expressions in the select instead of only to the result set of the select. It makes no sense, intuitively, and smells like a bug. But what does the standard say? Note that there's a workaround: SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2) ORDER BY a COLLATE nocase; Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
Richard,
sqlite> CREATE TABLE t1(a);
sqlite> INSERT INTO t1 VALUES('abc');
sqlite> INSERT INTO t1 VALUES('def');
sqlite> CREATE TABLE t2(a);
sqlite> INSERT INTO t2 VALUES('DEF');
sqlite>
sqlite> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a;
abc
def
sqlite> SELECT '-';
-
sqlite> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase;
abc
sqlite> SELECT '-';
-
sqlite> SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2)
...> ORDER BY a COLLATE nocase;
abc
def
sqlite> select a from t1 where not exists (select * from t2 where a=t1.a);
abc
def
sqlite> select a from t1 where not exists (select * from t2 where a=t1.a) order
by a collate nocase;
abc
def
sqlite> explain query plan SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a
COLLATE nocase;
SELECT {1:0}
FROM {1,*} = t1
ORDERBY {1:0}.COLLATE(nocase)
EXCEPT
SELECT {0:0}
FROM {0,*} = t2
ORDERBY {0:0}.COLLATE(nocase)
END
sele order from deta
-
1 0 0 SCAN TABLE t1 (~100 rows)
1 0 0 USE TEMP B-TREE FOR ORDER BY
2 0 0 SCAN TABLE t2 (~100 rows)
2 0 0 USE TEMP B-TREE FOR ORDER BY
0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
sqlite> explain query plan select a from t1 where not exists (select * from t2
where a=t1.a) order by a collate nocase;
SELECT {0:0}
FROM {0,*} = t1
WHERE NOT(EXISTS(SELECT {1:0}
FROM {1,*} = t2
WHERE EQ({1:0},{0:0})
LIMIT 1
END))
ORDERBY {0:0}.COLLATE(nocase)
END
sele order from deta
-
0 0 0 SCAN TABLE t1 (~50 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 1
1 0 0 SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (a=?)
(~7 rows)
0 0 0 USE TEMP B-TREE FOR ORDER BY
The plans make is pretty clear what is happening. The order by is being bumped
up to the b-tree's used to perform the EXCEPT operation, while on the
semantically equivalent not exists query, it is not.
Try the same queries using the other set operations (union and intersect).
sqlite> select a from t1 union select a from t2 order by a;
DEF
abc
def
sqlite> select a from t1 union select a from t2 order by a collate nocase;
abc
DEF
sqlite> select a from (select a from t1 union select a from t2) order by a
collate nocase;
abc
DEF
def
sqlite> select a from t1 intersect select a from t2 order by a;
sqlite> select a from t1 intersect select a from t2 order by a collate nocase;
def
sqlite> select a from (select a from t1 intersect select a from t2) order by a
collate nocase;
It is difficult to determine what is correct behaviour. I would think that the
"order by" clause applies to the set operation, therefore any collation being
applied has to be applied also to the component sets before the operation is
performed. This implies the current operation is correct although it may lead
to, perhaps, non-intuitive results.
---
() 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 09:01
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
>
> On Mon, May 6, 2013 at 10:56 AM, Simon Slavin
> wrote:
>
> >
> > Unless you include your collation code, it's hard to reproduce your
> > behaviour. Can you please try to find a demonstration with a collation
> > order that's native to SQLite ? Perhaps a three-row example using
> "COLLATE
> > NOCASE" ?
> >
> >
> You are correct, Simon, that Staffan's message was really just a vague
> hint
> of something wrong. But based on that meager hint, I have been able to
> come up with a suspicious-looking test script using the built-in "nocase"
> collation. To wit:
>
> CREATE TABLE t1(a);
> INSERT INTO t1 VALUES('abc');
> INSERT INTO t1 VALUES('def');
> CREATE TABLE t2(a);
> INSERT INTO t2 VALUES('DEF');
>
> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a;
> SELECT '-';
> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase;
> SELECT '-';
> SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2)
> ORDER BY a COLLATE nocase;
>
> --
> 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
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
Richard's script seems to confirm that something is buggy, so I hold fire
trying to produce something else that only shows the same thing.
Staffan
On Mon, May 6, 2013 at 5:00 PM, Richard Hipp wrote:
> On Mon, May 6, 2013 at 10:56 AM, Simon Slavin
> wrote:
>
> >
> > Unless you include your collation code, it's hard to reproduce your
> > behaviour. Can you please try to find a demonstration with a collation
> > order that's native to SQLite ? Perhaps a three-row example using
> "COLLATE
> > NOCASE" ?
> >
> >
> You are correct, Simon, that Staffan's message was really just a vague hint
> of something wrong. But based on that meager hint, I have been able to
> come up with a suspicious-looking test script using the built-in "nocase"
> collation. To wit:
>
> CREATE TABLE t1(a);
> INSERT INTO t1 VALUES('abc');
> INSERT INTO t1 VALUES('def');
> CREATE TABLE t2(a);
> INSERT INTO t2 VALUES('DEF');
>
> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a;
> SELECT '-';
> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase;
> SELECT '-';
> SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2)
> ORDER BY a COLLATE nocase;
>
> --
> 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
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
On Mon, May 6, 2013 at 10:56 AM, Simon Slavin wrote:
>
> Unless you include your collation code, it's hard to reproduce your
> behaviour. Can you please try to find a demonstration with a collation
> order that's native to SQLite ? Perhaps a three-row example using "COLLATE
> NOCASE" ?
>
>
You are correct, Simon, that Staffan's message was really just a vague hint
of something wrong. But based on that meager hint, I have been able to
come up with a suspicious-looking test script using the built-in "nocase"
collation. To wit:
CREATE TABLE t1(a);
INSERT INTO t1 VALUES('abc');
INSERT INTO t1 VALUES('def');
CREATE TABLE t2(a);
INSERT INTO t2 VALUES('DEF');
SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a;
SELECT '-';
SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase;
SELECT '-';
SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2)
ORDER BY a COLLATE nocase;
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
On 6 May 2013, at 3:47pm, Staffan Tylen wrote: > I've got a SELECT clause in the following format: > > SELECT a FROM t1 > EXCEPT > SELECT a FROM t2 > ORDER BY a COLLATE myorder; > > All a's in t1 get selected (not expected). > > SELECT a FROM t1 > EXCEPT > SELECT a FROM t2 > ORDER BY a; > > All a's in t1 except the a's in t2 get selected (as expected). > > I haven't experienced any errors using COLLATE myorder in general. > Looks like a bug to me. Unless you include your collation code, it's hard to reproduce your behaviour. Can you please try to find a demonstration with a collation order that's native to SQLite ? Perhaps a three-row example using "COLLATE NOCASE" ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
