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 (~1000000 rows)
1 0 0 USE TEMP B-TREE FOR ORDER BY
2 0 0 SCAN TABLE t2 (~1000000 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 (~500000 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 <slav...@bigfraud.org>
> 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
