EDIT2: Cause discovered: http://forums.adobe.com/thread/517449?tstart=0
EDIT:I recreated the table yet again, and now I'm not seeing the behavior described below. I tried both the CONSTRAINT PRIMARY KEY approach and the PRIMARY KEY NOT NULL as part of the column def approach, and both are working as expected. The only differences between these two recreated versions of the table and the first recreation of it I did earlier today are (1) the earlier table had an index, although there was no index specified in the create-table statement, but SQLite Manager by Tarakant Tripath and Mrinal Kant tells me there is one [sqlite_autoindex_TTLS_1]; (2) I did not specify any size for the VARCHAR columns on the later recreations; and (3) I moved the order of columns around on the later ones.
As you can see from the attached screencapture, I have a SQLite table called TTLS.
The PK, ttlid, is defined as an integer.
What you cannot see from the screen capture but what you need to know is this:
The actual primary keys in this table are all evenly divisible by 10: 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120... etc
There is no PK 1 through 9, no 11, 12, 13, no 121, 122 and so on.
When I execute the following query
select ttlid, titles from TTLS
I should be seeing:
10 ...... The Cat in the Hat
20 ...... Green Eggs and Ham
110 ...... Of Mice and Men
but here is how the results are returned by Flex and by tools based on Flex, as though Flex were ignoring the actual PK value and generating its own auto-increment value:
1 ........The Cat in the Hat
2.........Green Eggs and Ham
11........ Of Mice and Men
This behavior is causing inner joins involving this table to be totally wrong in Flex. No other GUI tools for SQLite are doing this. They all show the true PK values that are evenly divisible by 10.
What is Flex doing here?