Packaged version of SQLite (3.5.9) returns incorrect results from SELECT DISTINCT query

[Jean-Paul Calderone]

exarkun@takkun:~$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=8.10
DISTRIB_CODENAME=intrepid
DISTRIB_DESCRIPTION="Ubuntu 8.10"
exarkun@takkun:~$ sqlite3
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> CREATE TABLE foo(foo TEXT);
sqlite> INSERT INTO foo VALUES ('a');
sqlite> INSERT INTO foo VALUES ('b');
sqlite> CREATE INDEX foo_foo ON foo(foo);
sqlite> SELECT DISTINCT foo FROM foo WHERE foo IS NOT NULL;
b
sqlite>

The correct result would have been the rows for a and b, not just b. This bug is apparently fixed in a later release of SQLite.

[Jean-Paul Calderone (exarkun)]

The upstream bug report seems to be <http://www.sqlite.org/cvstrac/tktview?tn=3236>. It is marked as a duplicate of another ticket which doesn't seem as related, but I'm sure the SQLite contributor who marked it as such is more familiar with the codebase than I. In any case, applying the diff in the revision linked from that ticket to the 3.5.9 source results in a SQLite which does not exhibit this particular bug.

[Tristan Seligmann (mithrandi)]

This is Debian bug #500792, but I have idea how to indicate that in Launchpad.

[Rolf Leggewie (r0lf)]

Can those affected by this bug please try installing sqlite3 and libsqlite3 from my PPA (https://launchpad.net/~r0lf/+archive) and report if that fixes the problem? In that case, we'd see a duplicate of bug 254228 where you can read more about the background.

[Jean-Paul Calderone (exarkun)]

I still seem to be able to reproduce this problem. I installed libsqlite3-0_3.5.9-3ubuntu1_i386.deb and then re-ran the above SQLite interaction with the same outcome.

[Michał Pasternak (dotz)]

Hi Rolf, thank you very much for looking at this issue. I installed both sqlite3 and libsqlite3 from your PPA, and as JP pointed out, it seems that the bug still exists - the SQL interaction gives exactly the same result.

[Jean-Paul Calderone (exarkun)]

I would like to help resolve this issue.

The upstream bug report is <http://www.sqlite.org/cvstrac/tktview?tn=3236>.

The patch to fix the bug is <http://www.sqlite.org/cvstrac/patchset?cn=5341>.

What else can I do?

[Rolf Leggewie (r0lf)]

Please take a look at the the latest sqlite3 and libsqlite3 packages from my PPA.

[Jean-Paul Calderone (exarkun)]

Hey Rolf,

I just tried the package in your PPA and the problem does appear to be fixed. The example in my first comment now produces two rows of results, "a" and "b", instead of just one.

Thanks!

[Rolf Leggewie (r0lf)]

Invalididating tasks for txcomputergrid and divmod, then

[Rolf Leggewie (r0lf)]

version in Jaunty already includes upstream fix. Closing for Jaunty, Intrepid task still open and being worked on.