After further experimentation, I believe the problem may be in the way Flex is sorting an index. The where-condition is returning the correct rows with these integer FKs on which the inner join is based:
but the values returned from the joined table belong to rows whose (int) primary keys are 1400, 25230, 27520.
If a numeric index were being sorted as text, the values would not be in the correct numeric order. Just a hunch.
My query is quite simple, something like this:
select t1.type, t1.titleid, t2.title
from t1 inner join t2 on t1.titleid = t2.titleid
where t1.type in ( .... )
t1.titleid is always correct. t2.title is always wrong.
"But warden, I'm doing 2 to 5 for car theft!"
select crime.description, prisoner.name as deadmanwalking
from crime inner join prisoner on crime.perpid = prisoner.id
where crime.type = "murder-premeditated"
EDIT: Cause discovered: http://forums.adobe.com/thread/517449?tstart=0
Imentioned above that when issuing the query in SQLAdmin http://coenraets.org/blog/2008/02/sqlite-admin-for-air-10/ I got error #3115. That problem it turned out is because SQLAdmin does not like single quotes around text values but wants double quotes. When I execute the query with that minor correction, SQLAdmin has the same problem as Flex! It is returning the incorrect value from the joined table, but the rows are otherwise correct.