The change in behavior occurs with http://www.sqlite.org/src/info/38852f158a
If you need a work-around, change INNER to CROSS in the queries and the old query plan will be restored. On Thu, Apr 18, 2013 at 3:27 AM, Mario M. Westphal <m...@mwlabs.de> wrote: > This is a SQL Script > > > /* > Application linking to SQLite using the Amalgation. > Build Tool: C++, Visual Studio 2012, Windows 7 64-Bit > > The table schema and the query enclosed below are in use > for over one year and various SQLite versions. > > After downloading and compiling in the SQLite 3.7.16.2, we have a problem. > > The INSERT statement which takes only a few seconds with previous builds > now does not return, at least not within several minutes. > It's hard to debug the amalgation in Visual Studio but it looks like > SQLite would be caught in an internal loop inside the step() function call. > > Replacing the latest version of SQLite with 3.7.15.1 (which was the one we > used before) > and re-compiling our application solves the problem. The INSERT works again > in a few seconds. > > We can provide a sample database etc. on request. > */ > > > /* Create */ > > CREATE VIRTUAL TABLE md_fts_core USING > fts4(group_oid,tag_oid,file_oid,lang,data); > > CREATE TABLE md_fts_core_tag (oid INTEGER PRIMARY KEY, type INTEGER); > CREATE INDEX idx_md_fts_core_tag_type ON md_fts_core_tag(type); > > CREATE TABLE md_tag (oid INTEGER PRIMARY KEY, class INTEGER, group_oid > INTEGER, id TEXT, tag TEXT, idx INTEGER, dtype INTEGER, ntype TEXT, cnt > INTEGER, repeat INTEGER, flags INTEGER, FOREIGN KEY(group_oid) REFERENCES > md_tag_group(oid) ON DELETE CASCADE); > CREATE INDEX idx_md_tag_tag ON md_tag(tag); > > CREATE TABLE md_tag_group (oid INTEGER PRIMARY KEY, src INTEGER, id TEXT); > > CREATE TABLE md_tag_data (oid INTEGER, tag_oid INTEGER, tdata TEXT, rdata > TEXT, lang TEXT, flags INTEGER, FOREIGN KEY(tag_oid) REFERENCES md_tag(oid) > ON DELETE CASCADE); > CREATE INDEX idx_md_tag_data_flags ON md_tag_data(flags); > CREATE INDEX idx_md_tag_data_oid ON md_tag_data(oid); > CREATE INDEX idx_md_tag_data_oid_tag_oid ON md_tag_data(oid,tag_oid); > CREATE INDEX idx_md_tag_data_tag_oid ON md_tag_data(tag_oid); > > > /* This insert does not return (at least not within several minutes in > 3.7.16.2, but takes about 5-10 seconds with build 3.7.15.1 */ > > INSERT INTO md_fts_core (group_oid,tag_oid,file_oid,lang,data) > > SELECT > g.oid, d.tag_oid, d.oid, d.lang, d.tdata from md_tag_data d > INNER JOIN > md_tag t ON d.tag_oid = t.oid > INNER JOIN > md_tag_group g ON t.group_oid = g.oid > INNER JOIN > md_fts_core_tag ct ON ct.type = 0 AND ct.oid = d.tag_oid /* which > tags to include */ > WHERE > d.oid IN > > (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3 > > 4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59 > > ,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84, > > 85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107 > ,108) > AND d.tag_oid IN (2157,7309,16265,16579) > > UNION > > SELECT > /* We use group_concat to fold multiple values for one tag into one > value for FTS */ > g.oid, d.tag_oid, d.oid, d.lang, group_concat(d.tdata,' ') > FROM > md_tag_data d > INNER JOIN > md_tag t ON d.tag_oid = t.oid > INNER JOIN > md_tag_group g ON t.group_oid = g.oid > INNER JOIN > md_fts_core_tag ct ON ct.type = 1 AND ct.oid = d.tag_oid /* which > tags to include */ > WHERE > d.oid IN > > (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3 > > 4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59 > > ,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84, > > 85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107 > ,108) > AND d.tag_oid IN (2157,7309,16265,16579) > GROUP BY > d.tag_oid,d.oid > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
