Hi, The problem described here occurs both with the x32 and x64 versions of the expert personal 4 (Windows 10). Hereafter, a little database to show the bug.
The table "sample" is used to store words occurring in texts. Texts are identified by an id number. CREATE TABLE IF NOT EXISTS sample ( textid INT, word VARCHAR(100), UNIQUE (textid,word) ); CREATE INDEX [word index] ON [sample] ([word]); INSERT INTO sample VALUES (1,"hello"), (1,"world"), (1,"apple"), (1,"fruit"), (2,"fruit"), (2,"banana"), (3,"database") ; Now, one wants to list all the tuples corresponding to the texts containing the word "fruit". In the table above, only the texts 1 and 2 contains the word "fruit". Therefore, the expected result must be : RecNo textid word ----- ------ ------ 1 1 apple 2 1 fruit 3 1 hello 4 1 world 5 2 banana 6 2 fruit The following SQL request should achieve the goal : SELECT l2.textid, l2.[word] FROM sample AS l1, sample AS l2 WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) ; But il does not since it delivers the wrong answer : RecNo textid word ----- ------ ----- 1 1 fruit 2 1 fruit 3 1 fruit 4 1 fruit 5 2 fruit 6 2 fruit However, by adjoining in the SELECT part of the above request either a constant string or the command DISTINCT , then the result becomes correct ! SELECT "happy new year", l2.textid, l2.[word] FROM sample AS l1, sample AS l2 WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) ; RecNo 'happy new year' textid word ----- ---------------- ------ ------ 1 happy new year 1 apple 2 happy new year 1 fruit 3 happy new year 1 hello 4 happy new year 1 world 5 happy new year 2 banana 6 happy new year 2 fruit SELECT DISTINCT l2.textid, l2.[word] FROM sample AS l1, sample AS l2 WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) ; RecNo textid word ----- ------ ------ 1 1 apple 2 1 fruit 3 1 hello 4 1 world 5 2 banana 6 2 fruit Thank you for your reading. Please, notice that this "strange" behavior does not occur with the version 3 of Sqlite expert personal. Claude Del Vigna _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
