sqlite 3.7.9 is old and has bug which means it incorrectly processes sql

[Alan Chandler]

Impact:
Some sql commands can return wrong values

Test Case:
- run sqlite3
- copy those commands

"CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
CREATE TABLE t3(p INTEGER PRIMARY KEY, q);
INSERT INTO t1 VALUES(2,3,4,5);
INSERT INTO t1 VALUES(3,4,5,6);
INSERT INTO t2 VALUES(2,4);
INSERT INTO t3 VALUES(5,55);

SELECT * FROM t1 LEFT JOIN t2 ON y=b JOIN t3 WHERE c==p OR d==p;"

- the result should be 2 rows, only one is displayed on precise before the SRU

Regression Testing:
sqlite is used in quite some place, check that applications running it still work correctly

---

I have just installed ubuntu-server 12.04 after a hardware crash replacing a Debian squeeze server. One of my applications uses fairly complex SQL with sqlite3.

Back in March this year, I was finding a discontinuity between the results of my application running on the Debian server using sqlite 3.7.3 and by Debian Unstable Desktop, which at that time was running sqlite 3.7.10. This is reported at http://www.sqlite.org/src/info/b7c8682cc1

I have just discovered that this bug (the ticket reported above gives a test case) does in fact manifest itself in sqlite 3.7.9

This is now causing me the same problems (ie the application silently gives the wrong results) as it did before.

Given that I suspect this issue could silently cause other people problems, I believe the version in use in 12.04 needs to be updated to a later release (at least 3.7.11 where the issue is fixed).

Obviously this doesn't just affect the sqlite3 package itself, but all the other related packages (in my case php5-sqlite).

[Micah Gersten (micahg)]

This is fixed in quantal which has 3.7.13-1

[Sebastien Bacher (seb128)]

The commit to fix it seems to be
http://www.sqlite.org/src/vpatch?from=efee39e64bd95c28&to=0dc4cb935514131c

if somebody wants to have a look at backporting it

[Sebastien Bacher (seb128)]

I will have a look to that

[Steve Langasek (vorlon)]

Hello Alan, or anyone else affected,

Accepted sqlite3 into precise-proposed. The package will build now and be available at http://launchpad.net/ubuntu/+source/sqlite3/3.7.9-2ubuntu1.1 in a few hours, and then in the -proposed repository.

Please help us by testing this new package. See https://wiki.ubuntu.com/Testing/EnableProposed for documentation how to enable and use -proposed. Your feedback will aid us getting this update out to other Ubuntu users.

If this package fixes the bug for you, please change the bug tag from verification-needed to verification-done. If it does not, change the tag to verification-failed. In either case, details of your testing will help us make a better decision.

Further information regarding the verification process can be found at https://wiki.ubuntu.com/QATeam/PerformingSRUVerification . Thank you in advance!

[Alan Chandler (alan-chandlerfamily)]

Tested it and it works fine. Changed the tag to verification-done

[Adam Conrad (adconrad)]

The verification of this Stable Release Update has completed successfully and the package has now been released to -updates. Subsequently, the Ubuntu Stable Release Updates Team is being unsubscribed and will not receive messages about this bug report. In the event that you encounter a regression using the package from -updates please report a new bug using ubuntu-bug and tag the bug report regression-update so we can easily find any regresssions.

[Launchpad Janitor (janitor)]

This bug was fixed in the package sqlite3 - 3.7.9-2ubuntu1.1

---------------
sqlite3 (3.7.9-2ubuntu1.1) precise-proposed; urgency=low

  * debian/patches/upstream_incorrect_sql.patch:
    - upstream patch for "Incorrect result from LEFT JOIN with OR in
      the WHERE clause status still"
      (lp: #1021044)
 -- Sebastien Bacher <email address hidden> Thu, 02 Aug 2012 18:06:53 +0200