5 Replies Latest reply on Nov 3, 2009 11:18 AM by timo888

    Flex SQL library bug identified: absolute value function

    timo888 Level 1

      EDIT: I cannot be sure about the report below.  Queries that work fine in other tools are continuing to return zero rows in Flex, so something else may be causing the problem besides the ABS() function.  When I removed it before, the query worked.  Now, with some minor  changes to the query, it has stopped returning data, although the verbatim query continues to work in other SQLite front-ends.


      BTW, I'm converting all single-quotes to double-quotes (though SQLite accepts single quotes around strings, Flex does not in its SQLStatements).  In Flex I'm getting spurious errors that a columns called "wrdid" does not exist,yet it does exist; the query works fine in two other SQLite front-ends. It's not a programming error on my part inside Flex, building the text of the SQL Statement; the queries don't work in SQLite Admin either, a Flex-library-based GUI, in which I'm not concatenating strings to build the statement.


      I reported that a query was working fine in other SQLite tools but was returning zero rows in Flex:




      After a few hours of tracking down the cause, it turns out to be the use of the (SQLite) absolute value function.


      I use the absolute value of the difference between the ordinal position of two words in a text to see how close they are to each other independent of word order:


      ...and ABS( pos1 - pos2 ) <= 1

      The Flex query returns zero rows when this condition is added either as a WHERE condition or a JOIN condition. But this query works fine in other tools.

        • 1. Re: Flex SQL library bug identified: absolute value function
          timo888 Level 1

          EDIT: Cause discovered: http://forums.adobe.com/thread/517449?tstart=0



          I am very discouraged at this point.  Flex was turning out to be quite a nice tool.  But the SQL libraries for SQLite do not seem ready for prime time.

          • 2. Re: Flex SQL library bug identified: absolute value function
            camfieldaj Level 1

            So, your condition translates to:


            and (((pos1-pos2) <= 1) and ((pos1-pos2) >= -1)) ?


            Does this give you the same result?

            1 person found this helpful
            • 3. Re: Flex SQL library bug identified: absolute value function
              timo888 Level 1

              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.

              • 4. Re: Flex SQL library bug identified: absolute value function
                paul.williams Level 4

                If its a bug then log it here and get your friends and family to vote:



                • 5. Re: Flex SQL library bug identified: absolute value function
                  timo888 Level 1

                  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

                  from purchases

                  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.