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