>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?
That seems plausible. But speaking as a programmer, I do not typically get much
value from such "armchair" debugging. Sometimes I get bug reports, and from the
information given I get the sense that a whole workgroup has spent the whole
morning reverse engineering some problem in my code. It is almost always more
efficient to just go ahead and report the error.
However, the economics change if you have the source code. You said that the
library is coming from a "major player." If it is written using Microsoft .NET,
you probably can decompile the source code very effectively using Lutz Roeder's
.NET Reflector, which is shareware (maybe even freeware). Then, you will no
longer be faced with a "black box" and you might even be able to extract the
code and recompile it with the sorting bug patched.
________________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On
Behalf Of Tim Romano [tim.rom...@yahoo.com]
Sent: Tuesday, November 03, 2009 5:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] What sort of programming errors would cause these strange
results?
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
The information contained in this e-mail is privileged and confidential
information intended only for the use of the individual or entity named. If
you are not the intended recipient, or the employee or agent responsible for
delivering this message to the intended recipient, you are hereby notified that
any disclosure, dissemination, distribution, or copying of this communication
is strictly prohibited. If you have received this e-mail in error, please
immediately notify the sender and delete any copies from your system.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
