> On 3/01/2017, at 4:48 AM, claude.del-vi...@laposte.net wrote: > > 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 Expert Personal is a third party product which uses the SQLite database engine. It is not using “SQLite 4” (which is in early development stages and not been released), but will be using some version of SQLite 3. This mailing list is not an appropriate place to get support for products which use SQLite, but this looks like odd behaviour with SQLite itself, which may be worth investigating further. The current version of SQLite Expert Personal is 4.2.0, available here: http://www.sqliteexpert.com/download.html They don’t appear to give any clues as to which version of SQLite the application is using. I downloaded the 32-bit version, ran it under Windows 7 and tried the SQL you specified, and it produced the same result. 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") ; SELECT l2.textid, l2.[word] FROM sample AS l1, sample AS l2 WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) ; textid word 1 fruit 1 fruit 1 fruit 1 fruit 2 fruit 2 fruit Executing this command in SQLite Expert Personal 4.2.0: SELECT sqlite_version(); reports it is using version 3.15.2 of the SQLite database engine (as a DLL installed alongside the application), which was the latest version until version 3.16.0 was released today. Repeating the same test using the SQLite command line tool (version 3.15.2) does NOT produce the same behaviour. Here is what I get for the final select statement: SELECT l2.textid, l2.[word] FROM sample AS l1, sample AS l2 WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) ; 1|apple 1|fruit 1|hello 1|world 2|banana 2|fruit Therefore the problem is somehow specific to SQLite Expert Personal 4.2.0 (or the 32-bit DLL of SQLite 3.15.2), or maybe something in the way it has configured the SQLite database engine. Going back to SQLite Expert Personal, I checked the output of EXPLAIN QUERY PLAN and EXPLAIN and they appear to be identical to the command line tool. First, SQLite Expert: EXPLAIN QUERY PLAN SELECT l2.textid, l2.[word] FROM sample AS l1, sample AS l2 WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) ; selectid order from detail 0 0 0 SEARCH TABLE sample AS l1 USING INDEX word index (word=?) 0 1 1 SEARCH TABLE sample AS l2 USING COVERING INDEX sqlite_autoindex_sample_1 (textid=?) EXPLAIN SELECT l2.textid, l2.[word] FROM sample AS l1, sample AS l2 WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) ; addr opcode p1 p2 p3 p4 p5 comment 0 Init 0 22 0 00 Start at 22 1 OpenRead 0 2 0 2 00 root=2 iDb=0; sample 2 OpenRead 2 4 0 k(2,,) 02 root=4 iDb=0; word index 3 OpenRead 3 3 0 k(3,,,) 02 root=3 iDb=0; sqlite_autoindex_sample_1 4 String8 0 1 0 fruit 00 r[1]='fruit' 5 SeekGE 2 18 1 1 00 key=r[1] 6 IdxGT 2 18 1 1 00 key=r[1] 7 Seek 2 0 0 00 Move 0 to 2.rowid 8 Column 0 0 2 00 r[2]=sample.textid 9 IsNull 2 17 0 00 if r[2]==NULL goto 17 10 Affinity 2 1 0 D 00 affinity(r[2]) 11 SeekGE 3 17 2 1 00 key=r[2] 12 IdxGT 3 17 2 1 00 key=r[2] 13 Column 3 0 3 00 r[3]=sample.textid 14 Column 3 1 4 00 r[4]=sample.word 15 ResultRow 3 2 0 00 output=r[3..4] 16 Next 3 12 0 00 17 Next 2 6 1 00 18 Close 0 0 0 00 19 Close 2 0 0 00 20 Close 3 0 0 00 21 Halt 0 0 0 00 22 Transaction 0 0 2 0 01 usesStmtJournal=0 23 TableLock 0 2 0 sample 00 iDb=0 root=2 write=0 24 Goto 0 1 0 00 Here is the EXPLAIN QUERY PLAN output from the sqlite3 command line tool: 0|0|0|SEARCH TABLE sample AS l1 USING INDEX word index (word=?) 0|1|1|SEARCH TABLE sample AS l2 USING COVERING INDEX sqlite_autoindex_sample_1 (textid=?) Here is the EXPLAIN output from the sqlite3 command line tool: addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 22 0 00 Start at 22 1 OpenRead 0 2 0 2 00 root=2 iDb=0; sample 2 OpenRead 2 4 0 k(2,,) 02 root=4 iDb=0; word index 3 OpenRead 3 3 0 k(3,,,) 02 root=3 iDb=0; sqlite_autoindex_sample_1 4 String8 0 1 0 fruit 00 r[1]='fruit' 5 SeekGE 2 18 1 1 00 key=r[1] 6 IdxGT 2 18 1 1 00 key=r[1] 7 Seek 2 0 0 00 Move 0 to 2.rowid 8 Column 0 0 2 00 r[2]=sample.textid 9 IsNull 2 17 0 00 if r[2]==NULL goto 17 10 Affinity 2 1 0 D 00 affinity(r[2]) 11 SeekGE 3 17 2 1 00 key=r[2] 12 IdxGT 3 17 2 1 00 key=r[2] 13 Column 3 0 3 00 r[3]=sample.textid 14 Column 3 1 4 00 r[4]=sample.word 15 ResultRow 3 2 0 00 output=r[3..4] 16 Next 3 12 0 00 17 Next 2 6 1 00 18 Close 0 0 0 00 19 Close 2 0 0 00 20 Close 3 0 0 00 21 Halt 0 0 0 00 22 Transaction 0 0 2 0 01 usesStmtJournal=0 23 TableLock 0 2 0 sample 00 iDb=0 root=2 write=0 24 Goto 0 1 0 00 Investigating further in SQLite Expert Personal, it appears that the join operator is the critical detail. If I rewrite the SELECT statement as follows, replacing the comma operator with the word JOIN, it produces the correct result: SELECT l2.textid, l2.[word] FROM sample AS l1 JOIN sample AS l2 WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) ; textid word 1 apple 1 fruit 1 hello 1 world 2 banana 2 fruit The same output is produced if using INNER JOIN or CROSS JOIN. How can the comma join operator produce a different result to the word JOIN? Trying the select statement again without the WHERE clause and adding a few more columns produces interesting results: First, with JOIN: SELECT l1.textid, l1.word, l2.textid, l2.word FROM sample AS l1 JOIN sample AS l2 ; textid word textid_1 word_1 1 hello 1 hello 1 hello 1 world 1 hello 1 apple 1 hello 1 fruit 1 hello 2 fruit 1 hello 2 banana 1 hello 3 database 1 world 1 hello 1 world 1 world 1 world 1 apple 1 world 1 fruit 1 world 2 fruit 1 world 2 banana 1 world 3 database 1 apple 1 hello 1 apple 1 world 1 apple 1 apple 1 apple 1 fruit 1 apple 2 fruit 1 apple 2 banana 1 apple 3 database 1 fruit 1 hello 1 fruit 1 world 1 fruit 1 apple 1 fruit 1 fruit 1 fruit 2 fruit 1 fruit 2 banana 1 fruit 3 database 2 fruit 1 hello 2 fruit 1 world 2 fruit 1 apple 2 fruit 1 fruit 2 fruit 2 fruit 2 fruit 2 banana 2 fruit 3 database 2 banana 1 hello 2 banana 1 world 2 banana 1 apple 2 banana 1 fruit 2 banana 2 fruit 2 banana 2 banana 2 banana 3 database 3 database 1 hello 3 database 1 world 3 database 1 apple 3 database 1 fruit 3 database 2 fruit 3 database 2 banana 3 database 3 database Now with comma instead of JOIN: SELECT l1.textid, l1.word, l2.textid, l2.word FROM sample AS l1, sample AS l2 ; textid word textid_1 word_1 1 hello 1 hello 1 hello 1 hello 1 hello 1 hello 1 hello 1 hello 1 hello 1 hello 1 hello 1 hello 1 hello 1 hello 1 world 1 world 1 world 1 world 1 world 1 world 1 world 1 world 1 world 1 world 1 world 1 world 1 world 1 world 1 apple 1 apple 1 apple 1 apple 1 apple 1 apple 1 apple 1 apple 1 apple 1 apple 1 apple 1 apple 1 apple 1 apple 1 fruit 1 fruit 1 fruit 1 fruit 1 fruit 1 fruit 1 fruit 1 fruit 1 fruit 1 fruit 1 fruit 1 fruit 1 fruit 1 fruit 2 fruit 2 fruit 2 fruit 2 fruit 2 fruit 2 fruit 2 fruit 2 fruit 2 fruit 2 fruit 2 fruit 2 fruit 2 fruit 2 fruit 2 banana 2 banana 2 banana 2 banana 2 banana 2 banana 2 banana 2 banana 2 banana 2 banana 2 banana 2 banana 2 banana 2 banana 3 database 3 database 3 database 3 database 3 database 3 database 3 database 3 database 3 database 3 database 3 database 3 database 3 database 3 database This looks like the SELECT statement is somehow outputting values from l1 twice, instead of l1 and l2. Is there some pragma or compile option which makes SQLite handle a comma join operator differently? _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
