SQLite Library issue

Windows specific forum
User avatar
purebuilt
User
User
Posts: 46
Joined: Sun Dec 17, 2006 5:30 pm
Location: Milwaukee, WI, USA

SQLite Library issue

Post by 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
- DB
User avatar
skywalk
Addict
Addict
Posts: 4211
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: SQLite Library issue

Post by 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...
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
User avatar
purebuilt
User
User
Posts: 46
Joined: Sun Dec 17, 2006 5:30 pm
Location: Milwaukee, WI, USA

Re: SQLite Library issue

Post by purebuilt »

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

Thanks
- DB
User avatar
deeproot
Enthusiast
Enthusiast
Posts: 284
Joined: Thu Dec 17, 2009 12:00 pm
Location: Llangadog, Wales, UK
Contact:

Re: SQLite Library issue

Post by 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?
User avatar
purebuilt
User
User
Posts: 46
Joined: Sun Dec 17, 2006 5:30 pm
Location: Milwaukee, WI, USA

Re: SQLite Library issue

Post by 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
- DB
Fred
Administrator
Administrator
Posts: 18162
Joined: Fri May 17, 2002 4:39 pm
Location: France
Contact:

Re: SQLite Library issue

Post by Fred »

I have updated it for the next beta.
User avatar
skywalk
Addict
Addict
Posts: 4211
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: SQLite Library issue

Post by skywalk »

Thanks Fred :!:
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
User avatar
deeproot
Enthusiast
Enthusiast
Posts: 284
Joined: Thu Dec 17, 2009 12:00 pm
Location: Llangadog, Wales, UK
Contact:

Re: SQLite Library issue

Post by 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!
User avatar
Kiffi
Addict
Addict
Posts: 1486
Joined: Tue Mar 02, 2004 1:20 pm
Location: Amphibios 9

Re: SQLite Library issue

Post by Kiffi »

Fred wrote:I have updated it for the next beta.
good news! Thanks a lot!

Greetings ... Kiffi
Hygge
User avatar
purebuilt
User
User
Posts: 46
Joined: Sun Dec 17, 2006 5:30 pm
Location: Milwaukee, WI, USA

Re: SQLite Library issue

Post by purebuilt »

Thanks Fred! You're the best!
- DB
Fred
Administrator
Administrator
Posts: 18162
Joined: Fri May 17, 2002 4:39 pm
Location: France
Contact:

Re: SQLite Library issue

Post by Fred »

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