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.
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
(SHA1: d8bde37329e689f87165cf903378403329e25b4c)
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
