* Laszlo Boszormenyi <[EMAIL PROTECTED]> [2008-10-01 20:02:13 +0200]:
> > CREATE INDEX foo_foo ON foo(foo);
> I'm sure sqlite3 just gets confused what do you mean on foo with
> v3.5.9 . I can't reproduce it on the same architecture with the same
> version if I try it as:
> CREATE TABLE store(col TEXT);
> INSERT INTO store VALUES('a');
> INSERT INTO store VALUES('b');
> SELECT DISTINCT col FROM store WHERE col IS NOT NULL;
>
> It gives the expected result.
Your example is missing the index; if you create an index on column
"col", then you still get the incorrect result:
sqlite> CREATE TABLE store(col TEXT);
sqlite> INSERT INTO store VALUES('a');
sqlite> INSERT INTO store VALUES('b');
sqlite> SELECT DISTINCT col FROM store WHERE col IS NOT NULL;
a
b
sqlite> CREATE INDEX foo_index ON store(col);
sqlite> SELECT DISTINCT col FROM store WHERE col IS NOT NULL;
b
--
mithrandi, i Ainil en-Balandor, a faer Ambar
signature.asc
Description: Digital signature
