I posted this originally in the Flex forum (http://forums.adobe.com/thread/507363?tstart=0) -- didn't see this one, where it's more appropriate.
Here is a summary of the problem.
I have a 500MB SQLite database which I know to be working correctly. Queries executed in Visual Studio, Razor, and SQLMaestro jibe with one another, and with my knowledge of the data. However, the same queries executed in Flex code or pasted into the SQLAdmin GUI (written in Flex using the SQLConnection libraries) are returning incorrect values, and they're both identically wrong.
The query is basically like this:
select t1.id, t1.descrip, t2.title
from t1 inner join t2 on t1.id = t2.id
where t1.descrip = 'xxx'
The correct rows are being returned by Flex, but the t2.title values are always wrong.
t1.id and t2.id are both declared as int in SQLite.
When I delved into the problem and examined the t2.title rows in the TITLE table, I saw that the primary keys of the incorrect title rows were very close to the t1.id values:
t1.id ...................PK value of the title row returned by the inner join
140.......................1400
160.......................1610
So I have a hunch that this serious bug may have something to do with sorting of an index, and data-types, text versus int, or with index offsets being off by 1. 140 and 1400, 160 and 1610 would be next to each other in an alpha sort.
That three other tools get the query correct, and that two Flex-based tools get identically wrong answers, leads me to conclude with some certainty that this is a Flex database library issue.
