SQLite Library issue

[purebuilt]

Hello PB Group,

I discovered that the libraries that PB uses for SQLite have an error in them with ORDER BY statements. This is true of any program using the older libraries (discovered it in the SQLite Manger plug-in for FireFox). I don't know how to update the libraries, is there a way to do that or a planned release of them? We find it a very useful feature of PB, but we need the most recent libraries.

Thanks

[skywalk]

PB v5.0 SQLite Version = 3.7.9.
What version of SQLite created the 'Order By' error and which version fixed it?
SQLite releases...

[purebuilt]

2012-06-11 (3.7.13) is working. But 3.7.9 or earlier does not.

Thanks

[deeproot]

@purebuilt - if possible could you describe the error you are getting with ORDER BY ?

I'm using ORDER BY on almost all queries and some are pretty complex with up to 9 joined tables. But I have seen no issues when testing - PB 5.00 & 5.10 all betas, SQLite 3.7.9. I also check tables using the FireFox plug-in.

The SQLite release history (skywalk's link) mentions an ORDER BY bug introduced with 3.7.15 and fixed 3.7.15.2 but I can't see any related report earlier.

Maybe I've just been lucky with type of query statements used?

[purebuilt]

Yes, you have been lucky. It took us a while to find it, but when we create a view with an ORDER BY statement:

Code: Select all

SELECT DISTINCT jobid,
datetime(start_tmx,'unixepoch') start,
datetime(end_tmx,'unixepoch') end,
(strftime('%s','now','localtime') - start_tmx) / 3600 age,
CASE WHEN end_tmx > 0 THEN
 end_tmx - start_tmx
ELSE
 strftime('%s','now','localtime') - start_tmx
END as duration,
upper(status) status 
FROM jcojobdata
GROUP BY jobid
ORDER BY jobid ASC, max(start_tmx) DESC;

Then do a simple query: SELECT * FROM jobsmonitor; <-- the name of the view.

Depending on the version of SQLite used, we get different results for the records (mostly first and last). This does not happen if we do the query with the latest versions, only in PB and FireFox where they use the earlier versions (before 3.7.13).

Thanks

[Fred]

I have updated it for the next beta.

[skywalk]

Thanks Fred

[deeproot]

Thanks for the info purebuilt - interesting. None of my queries use quite that kind of combination. SQLite has been really solid since I converted to it - hope it stays that way!

[Kiffi]

I have updated it for the next beta.

good news! Thanks a lot!

Greetings ... Kiffi

[purebuilt]

Thanks Fred! You're the best!

[Fred]

SQLite is used by a lot of programs, it's a very solid database manager, such bugs are probably very rare.