1 person found this helpful
So, your condition translates to:
and (((pos1-pos2) <= 1) and ((pos1-pos2) >= -1)) ?
Does this give you the same result?
See post immediately above your response. Culprit is actually a rowid aliasing bug in Flex libraries for SQLite. Adobe is treating any PK integer column, no matter whether its datatype is INTEGER or INT, as an alias for the rowid, which is incorrect, since the PK value and the rowid are not always the same.
I had originally though the ABS() function was to blame because when I removed it, I got some rows instead of no rows. But on closer inspection, values in the rows were incorrect.
In any case, thanks for taking time to post a possible workaround for the ABS() issue, though it turned out not to be the issue.
I already logged it earlier today and added comments to some other bugs I reported, linking them to the new actual culprit.
Votes, there should be no matter of a vote. This is a showstopper bug, IMO, Paul, and needs to be fixed ASAP. It is the kind of bug that causes lawyers to put "We will not be responsible for business loss you may suffer as a result of using our product" in licensing agreements.
But if a vote is necessary, by discussing it here perhaps the raised awareness will generate some voterly interest. How else would people affected by the bug (everyone who uses a primary key defined as INT) find out about it?
It is a nefarious bug because it can go unnoticed, inasmuch as the rowid will be close to the actual PK when they are not the same value, and values will be returned (in many instances) from the joined table -- just the wrong values.But they will seem perfectly plausible values, again, in many instances.
I tried to emphasize the importance of the bug, although with a bit of levity:
select crimes.perp, prisoner.name as deadmanwalking
from crimes inner join prisoners
on crimes.perpid = prisoners.id
where crimes.type = "murder-premeditated"
Prisoner.name will be incorrect.
OR a shipping label program, something more likely:
select product.id, customer.name, customer.city, customer.state, customer.zip
inner join product on purchases.productid = product.id
inner join customer on purchases.customerid = customer.id
The wrong products will be sent out to the wrong people if you print picklists and labels from the query above, and have defined product.id and customer.id as INT datatype in SQLite.
P.S. The bug reporting system does not give the reporter any way to identify a DATA LIBRARY BUG.