On Mon, Jul 8, 2013 at 8:52 AM, Klaus Keppler <[email protected]> wrote:
> Hi again, > > we've stumbled upon another hard to reproduce bug in SQLite. This has most > propably something to do with the query planner optimizations since 3.7.16. > If you compile with SQLITE_DEBUG=1 then you get an assertion fault beginning with check-in (http://www.sqlite.org/src/info/38852f158a). The problem exists in both 3.7.17 and the new 3.8.0. So, I theorize that this problem was not introduced by recent changes, but rather recent changes exposed a long-standing problem. Either way, we will add a fix as soon as we figure one out. Thanks for the bug report. Klaus: Can you please send written permission for us to use the schema, the content of the sqlite_stat1 table, and the query as a test case in the standard SQLite test suite? In particular, I want you to grant unlimited permission for use to reproduce ( http://www.sqlite.org/tmp/klaus-bug-20130708.txt) as part of the test suite. Please send via private email directly to me. Thanks. > > We have a complex SQL query which should return 4 result rows. When > executing the whole statement (see below), no results are returned with > SQLite 3.7.17, 3.7.17-trunk ([9415db6ef2]) and at least 3.7.16.2. > With 3.7.15 everything works fine. > > If you run ANALYZE on the database, everything works fine also on 3.7.17. > If you leave out the "ORDER BY" clause, everything works fine. If you > replace some conditions (eg. "SD_MAILSERVERID = MS_ID" with > "SD_MAILSERVERID = 1", which is the same in this case), everything works > fine. > > This bug just drove me crazy, though I have respect for the complexity of > a query optimizer. To quote Knuth: "Premature optimization is the root of > all evil". ;-) > > HOW TO REPRODUCE: > > I simplified our database and the affected query as much as possible while > keeping the descibed behaviour. I'm sorry that this SQL is still quite > complex. > Please download the test database from here: > http://download.liveconfig.**com/tmp/test.db<http://download.liveconfig.com/tmp/test.db> > (SHA1: d8bde37329e689f87165cf90337840**3329e25b4c) > > This is the SQL statement: > SELECT D_NAME FROM > ( SELECT DISTINCT CP_OBJECTID FROM > ( SELECT GP_MODULEID AS CP_MODULEID, GP_PERMISSIONID AS CP_PERMISSIONID, > GC_OBJECTID AS CP_OBJECTID > FROM GROUPCUSTOMERS, GROUPPERMISSIONS WHERE GC_CUSTOMERID=1 AND > GC_GROUPID = GP_GROUPID AND GP_MODULEID=2 AND GP_PERMISSIONID=7 > UNION > SELECT CP_MODULEID, CP_PERMISSIONID, CP_OBJECTID FROM > CUSTOMERPERMISSIONS WHERE CP_CUSTOMERID=1 AND CP_MODULEID=2 AND > CP_PERMISSIONID=7 > ) CP, > ( SELECT GP_MODULEID AS UP_MODULEID, GP_PERMISSIONID AS UP_PERMISSIONID, > GU_OBJECTID AS UP_OBJECTID > FROM GROUPUSERS, GROUPPERMISSIONS WHERE GU_USERID=2 AND > GU_GROUPID=GP_GROUPID AND ((GP_MODULEID=0 AND GP_PERMISSIONID=0) OR > (GP_MODULEID=2 AND GP_PERMISSIONID=7)) > UNION > SELECT UP_MODULEID, UP_PERMISSIONID, UP_OBJECTID FROM USERPERMISSIONS > WHERE UP_USERID=1 AND ((UP_MODULEID=0 AND UP_PERMISSIONID=0) OR > (UP_MODULEID=2 AND UP_PERMISSIONID=7)) > ) UP > WHERE (CP_MODULEID = UP_MODULEID AND CP_PERMISSIONID = UP_PERMISSIONID > AND CP_OBJECTID=UP_OBJECTID) > OR (UP_MODULEID=0 AND UP_PERMISSIONID=0) > ) PERM, (HOSTINGCONTRACTS LEFT JOIN HOSTINGPLANS ON (HC_PLANID=HP_ID)), > SUBDOMAINS, DOMAINS, MAILSERVERS > WHERE PERM.CP_OBJECTID = HC_ID AND HC_ID=3 AND HC_DELETED=0 AND > HC_ID=D_CONTRACTID AND D_ID=SD_DOMAINID AND SD_MAILSERVERID IS NOT NULL > AND SD_MAILSERVERID = MS_ID > ORDER BY D_NAME > > We built SQLite with these parameters: > CPPFLAGS="-DSQLITE_ENABLE_**UPDATE_DELETE_LIMIT -DSQLITE_ENABLE_STAT3" \ > ./configure --disable-tcl --enable-threadsafe --disable-shared > > I did my tests actually on a Debian 6 (64 bit). If you need the compiled > sqlite3 binary, just drop me a line. > > Please let me know if/how I can help in tracking down this issue. > > Best regards > > -Klaus Keppler > > > -- > ______________________________**______________________________**__________ > Keppler IT GmbH - Die Hostingexperten. > > Dipl.-Inf. Klaus Keppler Tel. (09131) 691-480 > Geschäftsführer Fax: (09131) 691-489 > > Am Weichselgarten 7 UStID.-Nr. DE259788698 > 91058 Erlangen Amtsgericht Fürth, HRB 11477 > www.keppler-it.de Sitz d. Gesellschaft: Erlangen > ______________________________**______________________________**__________ > ______________________________**_________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
