I have a SQLite database working quite nicely. The three main tables
have 4 million rows and 275,000 rows respectively, and query response
times are excellent: I have used several GUI front ends to query the
data, and they jibe with each other, both with regard to the (accurate)
results they return and the time they take. Very happy with SQLite. But
all is not rosy.
I recently wrote a little app using a library I hadn't worked with
before (source code for it not available -- it's a black box) and this
library is making some very basic mistakes with the data. Their results
are always wrong. There is something fundamentally awry in the library code.
I will describe two basic problems, and maybe those two points will
create a line pointing to the problem. I hope the combined knowledge and
intuition of this group will allow me to suggest where the authors of
this library might look for bugs in their code. They are a major player.
Their library and only their library is having problems with my data.
Three other tools for SQLite (Razor, Maestro, .NET provider by Robert
Simpson) are returning correct results.
Bug #1
In a select involving a simple equijoin.
NOTE: ORDERS.custid and CUSTOMER.id are integer
select ORDERS.id, CUSTOMER.name
from ORDERS inner join CUSTOMER on ORDERS.custid = CUSTOMER.id
where ORDERS.ordertype in ('a','b','c')
The correct rows from ORDERS are being returned; the WHERE ordertype
IN(...) condition is working.
But the CUSTOMER.name values are all wrong!
When I examine the rows in CUSTOMER that hold the (wrong) names returned
by the query, I see that the PK of those rows (CUSTOMER.id) would be
found very near the ORDER.custid value **if these CUSTOMER.id integer
values were being sorted AS TEXT**. For example
ORDERS.custid.......CUSTOMER.id of the wrong customer name returned by
the query
140..........................1400
160..........................1600
2530........................25230
2760........................27520
Is it possible that the library is sorting the CUSTOMER.id list
alphabetically not numerically, and jumping into the sorted PK list at
the correct offset but finding the wrong value in that location as a
result of the improper sort?
Bug#2
In a select against a table with 275,000 rows:
select pkcol, textcol
where textcol = 'x'
Some pkcol values are incorrect! Some are correct.
There's a non-unique index on textcol, which is varchar.
Thanks for the help.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
