9 Replies Latest reply on Nov 13, 2009 4:04 PM by adobe_paul

    bug-fix needs your vote: queries w/ joins against SQLite return incorrect values because Adobe treats PK col as alias for rowid when it should not

    timo888 Level 1

      For someone new to Adobe the forums and products can be bewildering. I've been advised to repost something I posted in Flash Data Integration in this forum.

       

      Here is the link to the post I put there:

       

      http://forums.adobe.com/message/2363777#2363777

       

      I have reported this bug: http://bugs.adobe.com/jira/browse/FB-23750

       

      I gather bugs get fixed if people vote for them to be fixed. Please vote for it to be fixed. It is serious, and you might not even realize you're suffering from it because the incorrect values returned by the query will seem perfectly plausible.

       

       

      If the link above doesn't work, here it is again:

       

      When I execute the following query in Flex and/or Lita:

       

      select wrdid, uspelling from WRD WHERE uspelling = 'wingeard'

       

      the results are:

       

      uspelling...wrdid

      wingeard   3137

       

       

      Look at what comes back when I execute this query using .NET provider by  Robert Simpson for SQLite and SQLite Manager by Mrinal Kant:

       

      SELECT     rowid, wrdid, uspelling
      FROM         WRD
      WHERE     (uspelling = 'wingeard')

       

       

      rowid.......wrdid...........uspelling

      3137........3042............wingeard

       

      No wonder none of my queries with joins is working correctly in Flex.

      wrdid is defined as "int" not INTEGER.

       

      http://www.sqlite.org/lang_createtable.html (see INTEGER PRIMARY KEY section):

       

      "The special behavior of INTEGER PRIMARY KEY is only available if the type name is exactly "INTEGER" (in any mixture of upper and lower case.)  Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid."  [emphasis added]

       

      Now, I happen to think the SQLite developers made a mistake here in failing to follow standards, preferring not to break legacy code. They'd rather break current code instead???  I would not characterize this as a "corner case" and the bug-at-hand is de facto evidence of that.

        • 1. Re: bug-fix needs your vote: queries w/ joins against SQLite return incorrect values because Adobe treats PK col as alias for rowid when it should not
          adobe_paul Adobe Employee

          I've read several of your various posts about this issue. I've also read your bug. Unfortunately, they all reference each other and I can't find enough information in any of them to get a clear understanding of how the data is getting generated and what you're expecting.

           

          1) How are you creating the database table -- are you making it using a tool (e.g. Lita), or using your own SQL, or something else?

           

          2) What is the SQL definition of the table (the SQL that's used to create the table)? Do you write the SQL yourself, or is it generated by some tool?

           

          3) How is the data added to the table? Are you adding it using SQL INSERT statements that you wrote, or some other way? Is the value for the wrdid column automatically generated, or is it a value that you specify when you're adding data to the table?

           

          3) What is the desired behavior for the SQL SELECT statement? Are you expecting wrdid to be identical to the ROWID alias?

          • 2. Re: bug-fix needs your vote: queries w/ joins against SQLite return incorrect values because Adobe treats PK col as alias for rowid when it should not
            timo888 Level 1

            I've been trying to track down the cause of various strange misbehaviors in queries for the past week or so. That's why there are several postings.  But no one had responded to my posts, that's why I put them in various forums.

             

            It suddently dawned on me to check the rowid, and bingo, that was it.  Adobe is not following SQLite documentation in the way it is handling primary key columns defined as datatype INT.   INT is different from INTEGER in SQLite.

             

            My primary key columns are defined as datatype "int".  According to the SQLite documentation (link given in my post above), INT columns must be treated as normal columns and not as aliases for the rowid. Only PK columns defined as "INTEGER" should be treated as aliases for the rowid. However, the AIR SQLite libraries are treating "int" PK columns as aliases for the rowid. Not good, because this means joins involving such PK columns will get the wrong values.

             

            create a new table test with id defined as "int" datatype (not "integer", "int") and make id the primary key.

            insert three rows:

             

            insert into test(id,name) values(1,"one")

            insert into  test(id,name) values(2,"two")

            insert into  test(id,name) values(7,"seven")

             

            Now issue this query:

             

            select rowid, id, name from test

             

            1.........1..........one

            2.........2..........two

            3.........7..........seven

             

             

            So you can see that treating id here as an alias for rowid (which is what Adobe is doing) will return the wrong value whenever table test is joined in a query.  Execute this query in Lita or Flex code, where foo is any table that has a row whose id = 3:

             

            select foo.id, test.name

            from foo inner join test

            on foo.id = test.id

            where foo.id = 3

             

            You should get no rows back because there is no row in test whose id=3. However you will get this:

            seven.....3

            • 3. Re: bug-fix needs your vote: queries w/ joins against SQLite return incorrect values because Adobe treats PK col as alias for rowid when it should not
              adobe_paul Adobe Employee

              Thanks for the clarification.

               

              I think I understand the issue you're describing, but in my tests I get slightly different results.

               

              I create a table using the following SQL:

              CREATE TABLE test (

              id int PRIMARY KEY,

              name String

              )

               

              I then run the exact INSERT statements you provided.

               

              Then I run the exact SELECT statement you provided:

              select rowid, id, name from test

               

              My results are the following:

              id     name

              1     one

              2     two

              7     seven

               

              (Your results had three columns. I'm not sure if the three-column result is what you expected to see, or what you actually are seeing.)

               

              Also, I don't have any issue joining the table to another table:

              CREATE TABLE foo
              (
              id int,
              value String
              )

               

              INSERT INTO foo (id, value) VALUES (1, "a")

              INSERT INTO foo (id, value) VALUES (2, "b")

              INSERT INTO foo (id, value) VALUES (3, "c")

               

               

              SELECT id, value FROM foo

               

              id     value

              1     a

              2     b

              3     c

               

               

              SELECT foo.id, test.name
              FROM foo INNER JOIN test ON foo.id = test.id
              WHERE foo.id = 3

               

              [0 rows returned]

              • 4. Re: bug-fix needs your vote: queries w/ joins against SQLite return incorrect values because Adobe treats PK col as alias for rowid when it should not
                timo888 Level 1

                Paul,

                There was a Lita upgrade this evening. Lita behavior has changed, but there is still a bug in Lita, and there is still a bug in Flex.


                Lita gets the join correct today, but Lita is still treating INT PK as an alias for ROWID:

                 

                select id, name from test

                1.........one

                2.........two

                3.........seven

                3  is NOT the id.  3 is the ROWID.  The" id" for the row that contains "seven"  is 7.

                 

                The results should be:

                 

                1.........one

                2.........two

                7.........seven

                 

                Remember, we inserted:

                 

                insert into test(id, name) values(1,'one')

                insert into test(id, name) values(2,'two')

                insert into test(id, name) values(7,'seven')

                 

                 

                This query:

                 

                select wrd.wrdid, test.name

                from wrd inner join test on wrd.wrdid=test.id

                where wrd.wrdid=3

                 

                should not bring back any rows because there is no row in table test whose id =3. But in Flex a row does come back (see attached screen capture).   Flex is doing the join on rowid.

                 

                Flex is treating INT PK columns as alias for rowid.

                 

                That is the bug.  Only "INTEGER" PK columns are to be treated as alias for rowid in SQLite.

                • 5. Re: bug-fix needs your vote: queries w/ joins against SQLite return incorrect values because Adobe treats PK col as alias for rowid when it should not
                  adobe_paul Adobe Employee

                  Did you try running the queries I posted? What were your results with those?

                   

                  What I am seeing is that when I use "int PRIMARY KEY" in a CREATE TABLE statement, that column becomes the special "rowid" column. I believe this is also what you are seeing.

                   

                  However, what confuses me is how you're getting a table with three columns "rowid", "id", and "name" in the first place. When I run this SQL...

                   

                  CREATE TABLE test

                  (

                  id int PRIMARY KEY,

                  name String

                  )

                   

                  ...I get a table with two columns: a normal column named "name", and a special primary key column named "id", which for this table is identical to the column represented by the rowid identifier.

                   

                  However, if I understand correctly, your table has three columns, "id", "name", and the special primary key column (i.e. "rowid"). Is that right? Can you give me the SQL that was used to create the table, or tell me how the table was created (e.g. if you used a tool like Lita) so I can try to re-create your exact situation? That would really be very very helpful -- it was the only detail that was missing in your last post, so I had to guess on that one detail.

                   

                  I tried something else to re-create your situation. I ran the following statement:

                  CREATE TABLE test

                  (

                  id int

                  name String

                  )

                   

                  That gave me a table with two real columns plus the rowid column. Then I ran the three insert statements on that table, and when I ran the select statement I got the expected result:

                   

                  id     name

                  1     one

                  2     two

                  7     seven

                   

                  Again, I'm guessing that your table was created differently than my test table, and that's the explanation for the difference.

                   

                  Some other possibilities to consider:

                  The screen shot doesn't show a SQLResult object, so it seems that you're using some wrapper library or code to execute the query, or at least that you've copied the SQLResult.data Array to another variable named results. Although it seems less likely to me, it's possible that somewhere in that code something is getting scrambled. (But I'd rather rule out AIR as the underlying cause first before attempting to explore those paths.)

                   

                  As a side note, if you really want the database to have three columns (the special rowid column and your two columns id and name), and you don't want id to be the special rowid column, then it sounds to me like you don't actually want to define id as the primary key. If you just want the id column to have a constraint that prevents duplicate values, you can define it as a UNIQUE column:

                  CREATE TABLE test

                  (

                  id int UNIQUE,

                  name String

                  )

                   

                  That gives you the same database-enforced constraint of not allowing duplicate values, but it tells the database explicitly that id isn't the same thing as the rowid primary key. (You can still join another table to the id column even if it's not defined as the primary key.)

                   

                   

                   

                  P.S. I'm sure you don't mean it this way, but using gigantic red text comes across like shouting -- it's very "loud". I'm trying my best to understand the issue you're having and help you resolve it, and using multiple colors and font sizes doesn't really make your post any more or less clear. Just because I ask you questions, or say that I'm seeing different results than you, doesn't mean I don't believe that you're seeing the results you're seeing. I've definitely seen strange variations and cases where something happens on my computer but others can't duplicate it on their computers -- so I believe that you are getting the results you're getting. I'm just trying to figure out how to make it so that I can also get those results, so that I can pass that on to the engineers who are in a position to make changes.

                  • 6. Re: bug-fix needs your vote: queries w/ joins against SQLite return incorrect values because Adobe treats PK col as alias for rowid when it should not
                    timo888 Level 1

                    Paul, you wrote:

                     

                    "What I am seeing is that when I use "int PRIMARY KEY" in a CREATE TABLE statement, that column becomes the special "rowid" column. I believe this is also what you are seeing."

                     

                    EXACTLY!

                    But this should not be happening. That is the bug. You have duplicated the bug!

                     

                    You are just not yet grasping the significance/severity of the bug: Flex will return a mixture of valid and invalid, correct and incorrect, true and spurious,  results from existing SQLite databases that use INT primary keys. Sometimes there will be errors of omission when rows that ought to be brought back won't be. Sometimes the wrong rows will be brought back from joined tables that use INT primary keys.

                     

                     

                    select * from TEST where id=7  // fails in Flex and Lita; no rows returned

                     

                    select * from foo inner join test on foo.id = test.id where test.id=7

                    // fails in Flex and Lita; no rows returned

                     

                    I am attaching another screen-capture to show you that this is happening in Flex, not just in Lita. I am also attaching a screenshot showing the same query being executed in Mrinal Kant's GUI tool for SQLite, where it is returning the correct results.

                     

                    Recall how TEST was created:

                     

                    CREATE TABLE [TEST] (
                        [id] int PRIMARY KEY,
                        [name] varchar(50) NOT NULL
                    )

                     

                    and that we did INSERT INTO TEST(id,name) VALUES(7,'seven')

                     

                     

                    INT and INTEGER are different in SQLite.

                     

                    http://www.sqlite.org/lang_createtable.html

                    From the "ROWIDs and the INTEGER PRIMARY KEY" section:

                     

                     

                    "The special behavior of INTEGER PRIMARY KEY is only available if the type name is exactly "INTEGER" (in any mixture of upper and lower case.)  Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid." (SQLite.org)

                     

                    Adobe is treating INT PRIMARY KEY as if it were INTEGER PRIMARY KEY which creates utter havoc with any existing SQLite database where INT PK columns are used. Adobe could be using the rowid to obtain the actual PK column value, but it is using the rowid as if it were the actual PK column value, which we have clearly shown is not always going to be true: see above where the rowid of that row where id=7, name='seven', is 3.

                     

                    P.S. I am using large fonts and different colors to show you precisely where you're missing the point, or precisely where the problem manifests itself, not to shout. Also, the fonts look smaller in edit mode than they do when the message is finally displayed.

                    • 7. Re: bug-fix needs your vote: queries w/ joins against SQLite return incorrect values because Adobe treats PK col as alias for rowid when it should not
                      timo888 Level 1

                      I have recreated my tables with unique indices on the INT column, removing the PRIMARY KEY constraints and things are working as they should.

                       

                      I've only lost a few weeks of my Flex trial period tracking down the cause of my queries not working. But others could fare worse.  There should be a prominent notice about this limitation (i.e. that INT primary keys are being treated just like INTEGER PRIMARY KEYS) in the docs, and that these PK constraints must be removed for queries to work correctly.  If there already is such a notice, my apologies. It should be


                      IN BIG LETTERS  

                      • 8. Re: bug-fix needs your vote: queries w/ joins against SQLite return incorrect values because Adobe treats PK col as alias for rowid when it should not
                        adobe_paul Adobe Employee

                        You are just not yet grasping the significance/severity of the bug: Flex will return a mixture of valid and invalid, correct and incorrect, true and spurious,  results from existing SQLite databases that use INT primary keys. Sometimes there will be errors of omission when rows that ought to be brought back won't be. Sometimes the wrong rows will be brought back from joined tables that use INT primary keys.

                        So this finally answers the question that has led to me not understanding.

                         

                        For databases created by AIR, this isn't an issue because they are created with the different behavior, and the data is added with the different behavior. However, if you are trying to open a SQLite database that was created by something other than AIR, this issue can lead to data problems.

                         

                        Is that correct? Is the database that you have been using one that was created by something other than an AIR application?

                         

                         

                        I absolutely agree with you that, in cases where AIR's behavior is different from "default" SQLite, the documentation should point that out. In fact, it does:

                        http://livedocs.adobe.com/flex/3/langref/localDatabaseSQLSupport.html#unsupportedSQL

                         

                        (although the issue you've discovered isn't mentioned there because, of course, you seem to be the first one to discover it)

                         

                        Now that I understand all the issues I will pass these details on to the engineering team. In any case, one of two outcomes will result. I'm honestly not sure which one will be chosen. AIR has intentionally chosen to deviate from SQLite in a few cases, and makes no promise of being compatible with databases created by other SQLite implementations. (But we also chose not to intentionally break compatibility with them):

                        - AIR will be changed to have the same behavior as default SQLite, or

                        - AIR won't be changed, and the documentation will be updated to indicate that this is an area where AIR differs from default SQLite

                         

                        Unfortunately the font choices are pretty limited for the documentation, so I'm afraid big text won't be an option =)

                        1 person found this helpful
                        • 9. Re: bug-fix needs your vote: queries w/ joins against SQLite return incorrect values because Adobe treats PK col as alias for rowid when it should not
                          adobe_paul Adobe Employee

                          I just wanted to tie up this thread with the final conclusion.

                           

                          I presented this issue to the responsible engineers. The decision was that the current behavior (which is different from "default" SQLite) is the most appropriate one for AIR, and is going to stay in place. I think the main reason for this is that one of the key differences AIR adds to SQLite is support for ActionScript data types as database data types. In ActionScript the int data type is a core data type, and a database column can be defined as an int and it maps exactly to an ActionScript int when data is retrieved. Because of this, int is considered to be exactly equivalent to INTEGER.

                           

                          The documentation has been updated to reflect this difference in behavior. (It hasn't been changed in the public documentation yet, but it should be included in the documentation with the AIR 2 public beta that is coming out pretty soon.)