David Empson wrote: 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.
Moreover if one submits this bug report to the "SQLite Expert" mailing list using the email address: supp...@sqliteexpert.com one may qualify for a $50 credit towards one's next purchase: "Submit a bug report and get $50 discount when purchasing SQLite Expert Professional! If you submit one or more bug reports in either SQLite Expert Personal or Professional, you will receive a promotional code by email which you can use when purchasing SQLite Expert Professional for $50 discount (over 50% of the original price). Conditions: - You must submit at least one bug report to qualify for this offer. - Feature requests do not count as bug reports. - The reported bug must be reproducible with the latest version of SQLite Expert. - Multiple bug reports do not qualify for cumulative discount. - If you already purchased SQLite Expert Professional, you are not entitled to a partial refund if you submit a bug report. However, you qualify for a discount if you wish to purchase an additional license." http://www.sqliteexpert.com/support.html I am not affiliated with Coral Creek Software and the only information I could find out about the company as opposed to the product (in less than 2 minutes of Google searching) is: https://www.bizapedia.com/fl/coral-creek-software.html Jim Callahan Orlando, FL On Mon, Jan 2, 2017 at 5:15 PM, David Empson <demp...@emptech.co.nz> wrote: > > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
