retitle 501340 sqlite3 is wrong on SELECT DISTINCT a WHERE a IS NOT NULL over a TEXT UNIQUE column thanks
OK, i'm narrowing down the case where this seems to be a problem. A
couple notes:
* if the "a" column is not declared UNIQUE, the misbehavior goes
away.
* including the NULL entry isn't necessary to replicate the
misbehavior.
* using unusual characters is not necessary either.
* the specific size of the varchar is not necessary either; you can
use CHAR, VARCHAR or TEXT (at least -- INTEGER works as expected,
and i haven't tried other types)
* The SELECT clause needs both "DISTINCT" and "WHERE a IS NOT NULL";
otherwise, the behavior is correct.
So the simplest case i've found so far is this:
-------------
CREATE TABLE foo (a CHAR UNIQUE);
INSERT INTO foo (a) VALUES ('x');
INSERT INTO foo (a) VALUES ('y');
SELECT DISTINCT a FROM foo WHERE a IS NOT NULL;
-------------
this should return:
-------------
x
y
-------------
but instead sqlite3 returns:
-------------
y
-------------
Here's me trying out the other select statements:
sqlite> CREATE TABLE foo (a TEXT UNIQUE);
sqlite> INSERT INTO foo (a) VALUES ('x');
sqlite> INSERT INTO foo (a) VALUES ('y');
sqlite> SELECT a FROM foo;
x
y
sqlite> SELECT DISTINCT a FROM foo;
x
y
sqlite> SELECT a FROM foo WHERE a IS NOT NULL;
x
y
sqlite> SELECT DISTINCT a FROM foo WHERE a IS NOT NULL;
y
sqlite>
hth,
--dkg
pgph7yTeBRmAx.pgp
Description: PGP signature
