On Wed, 2010-06-30 at 09:04 -0400, Tim Romano wrote: > <snip> > The EXAMPLE: If you create a database in the authoritative version of SQLite > using INT PRIMARY KEY (rather than INTEGER PRIMARY KEY), when you share the > database with your Adobe-using affiliate, all hell will break loose. I will > repeat the example I gave above: > > Let's say you had created this table in authoritative SQLite using INT > rather than INTEGER PRIMARY KEY: > > CREATE TABLE FOO (id INT PRIMARY KEY, name TEXT) > insert into FOO(1,'Groucho') > insert into FOO(2,'Chico') > Insert into FOO(999, 'Harpo') > > And then you have another table MOVIES where FOOID is a foreign key pointing > back to FOO.id: > > MOVIES > fooid|moviename > 1|Duck Soup > 2|Duck Soup > 3|Duck Soup > > > ** Note that Harpo's id is 999 in FOO and that table MOVIES contains no such > foreign key. > > Now your Adobe affiliate executes this query: > > select MOVIES.moviename, FOO.name > from MOVIES INNER JOIN FOO > ON FOO.id = MOVIES.fooid > > Unless Adobe has since changed the behavior they told me they were not going > to change, the query above will produce this result in Adobe: > > Duck Soup|Groucho > Duck Soup|Chico > Duck Soup|Harpo > > But Harpo should *not* appear in the resultset. Adobe looks for the row in > FOO whose rowid =3 and finds Harpo. If Adobe were following authoritative > SQLite, it would look for the row in FOO whose "ordinary column" id = 3 and > find no such row. > > <snip>
All things considered, I would say this is clearly and 100% a bug in Adobe's code. The given SQL nowhere mentioned rowid, yet they are joining on rowid... that has got to be an error. And how is Adobe Air doing this? Did they write there own sql parser? There own VDBE? (Hopefully I got those initials correct.) Are they calling Sqlite or did they write their own library claiming it is compatible with the Sqlite file format? While I don't necessarily disagree with what you are saying, in this example you appear to be asking the Sqlite developers to emulate an error in someone else's code. David PS. Probably should remove my opinions from this, because the only reason I am sending this is because I am curious what the hell Adobe is doing that is causing this confusion. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
