Re: [sqlite] Bug in query planner / optimizer
Answered my own question: The -DSQLITE_ENABLE_STAT3 compile-time option is required to see the problem. Good idea, I just compiled *without* this option, and my tests run fine. We'll disable stat3 here as a preliminary workaround. Best regards -Klaus -- __ Keppler IT GmbH - Die Hostingexperten. Dipl.-Inf. Klaus KepplerTel. (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
Re: [sqlite] Bug in query planner / optimizer
But after I remove the assert(), I cannot reproduce your problem. I always get four result rows, respectively: x2, x3, x4, and x5. I've tried this with 3.7.15, 3.7.16, 3.7.16.2, 3.7.17, and the latest trunk. And I've tried it using your original database and after dropping the SQLITE_STAT1 table. Do you have any further clues for me? This is *really* strange. I've created a dump of my test database, and just appended the SQL query to the end of that dump (inspired by your debugging example). When I now pipe that whole file into SQLite3, the query works. But when I run the same query again on that (now existing) database, there are *no* results any more. Maybe we have a timing problem with the journal here? Here are two files, one containing the schema and one with the query: http://download.liveconfig.com/tmp/test.schema (SHA1: f0e6f024d5f0d4dbcbfef9bb1c92b12042fd8cd2) http://download.liveconfig.com/tmp/test.sql (SHA1: f56970e0343a7418ed2a89ab3ffa4e8c30986bae) To reproduce: cat test.schema test.sql | ./sqlite3 tmp.db => returns the expected 4 result rows (x2/x3/x4/x5) cat test.sql | ./sqlite3 tmp.db => returns nothing Tested right now with 3.7.17-trunk and 3.7.16.2 Meanwhile, one of our users sent us another bug report concerning an "empty" query (another query, retuning no results even after executing ANALYZE command). I'll check this too, and will try to simplify the scheme by removing some columns. Best regards -Klaus -- ______ Keppler IT GmbH - Die Hostingexperten. Dipl.-Inf. Klaus KepplerTel. (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
Re: [sqlite] Bug in query planner / optimizer
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. Good to know (SQLITE_DEBUG), I'll consider this next time! So, I theorize that this problem was not introduced by recent changes, but rather recent changes exposed a long-standing problem. I tried this also with 3.7.16.2, which doesn't return a result at all. Only when I replace "SD_MAILSERVERID=MS_ID" with "SD_MAILSERVERID=1", the query works. But maybe this is just a side-effect and/or has been fixed with 3.7.17. Klaus: Can you please send written permission for us to use the schema, Of course - that's the least I can do. Best regards, -Klaus -- ______ Keppler IT GmbH - Die Hostingexperten. Dipl.-Inf. Klaus KepplerTel. (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
[sqlite] Bug in query planner / optimizer
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 KepplerTel. (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
Re: [sqlite] Please test the latest SQLite enhancements
Hi Richard, first of all - thanks for your great work! If you have a complex application that uses SQLite, please test your application with the SQLite amalgamation found at http://www.sqlite.org/sqlite3-20121009.zip and report to me (via private email) whether or not you encounter any problems. I'd love to do some tests here (we have some really complex queries), but we need the SQLite source with SQLITE_ENABLE_UPDATE_DELETE_LIMIT feature (AFAIK this is not contained in the amalgamation). So if you can drop a complete source ZIP anywhere, please let me know. Best regards, -Klaus -- __ Keppler IT GmbH - Die Hostingexperten. Dipl.-Inf. Klaus KepplerTel. (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
[sqlite] Bug report: null pointer dereference in SQLite 3.7.14 (SEGFAULT)
Hi, after upgrading from SQLite 3.7.13 to 3.7.14 our application crashed with a SEGFAULT located within SQLite. I boiled down the SQL statement and the tables used, and was able to reproduce this also with the SQLite standalone binary available at http://www.sqlite.org/sqlite-shell-linux-x86-3071400.zip (Debian 6, AMD64). I assume that the new optimizations on the query planner are causing this problem; with 3.7.13 everything works fine. HOW TO REPRODUCE: -- create these tables and fill with data: CREATE TABLE GROUPCUSTOMERS ( GC_GROUPID INTEGER NOT NULL, GC_CUSTOMERID INTEGER NOT NULL, GC_OBJECTTYPE INTEGER ); INSERT INTO GROUPCUSTOMERS VALUES (1, 1, NULL); CREATE TABLE GROUPPERMISSIONS ( GP_GROUPID INTEGER NOT NULL, GP_MODULEID INTEGER NOT NULL, GP_PERMISSIONID INTEGER NOT NULL ); INSERT INTO GROUPPERMISSIONS VALUES (1, 1, 1); CREATE TABLE GROUPUSERS ( GU_GROUPID INTEGER NOT NULL, GU_USERID INTEGER NOT NULL, GU_OBJECTTYPE INTEGER ); -- then run this query: SELECT * FROM ( SELECT GP_MODULEID AS CP_MODULEID, GP_PERMISSIONID AS CP_PERMISSIONID FROM GROUPCUSTOMERS, GROUPPERMISSIONS WHERE (GC_GROUPID = GP_GROUPID) ) AS A1 LEFT JOIN ( SELECT GP_MODULEID AS UP_MODULEID, GP_PERMISSIONID AS UP_PERMISSIONID FROM GROUPUSERS, GROUPPERMISSIONS WHERE (GU_GROUPID = GP_GROUPID) ) AS A2 ON (CP_MODULEID = UP_MODULEID) OR (UP_PERMISSIONID = 0) ; => SEGFAULT -- note that it is important to have these two records inserted, -- without them the SQL will work. -- AND: when running "ANALYZE" before running the SQL statement, the -- query also succeeds! In our application, the error occured at the sqlite3VdbeExec function at "case OP_NullRow:" on the line "u.bn.pC->nullRow = 1;" u.pn.pC is NULL at this point. (tested with GDB and the original (bloated) SQL statement from within our application) We did NOT use the flag SQLITE_ENABLE_STAT3. For any questions don't hezitate to contact me. Best regards -Klaus Keppler -- __ Keppler IT GmbH - Die Hostingexperten. Dipl.-Inf. Klaus KepplerTel. (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
[sqlite] Bug report: sqlite3_column_name vs. LIKE clause in prepared statements
Hi,
I'd like to submit the following bug report. However, before suspecting
the problem in SQLite, we've debugged and traced our own code for two
days, and at last got it reproducible within SQLite.
DESCRIPTION:
It's an absolute strange behaviour: if you do queries with the LIKE
operator using the new sqlite_prepare_v2() interface *and* using host
parameters, the column names returned by sqlite3_column_name() get
invalid after the first call to sqlite3_step()
According to the documentation, the string pointer returned by
sqlite3_column_name() "is valid until either the prepared statement is
destroyed by sqlite3_finalize() or until the next call to
sqlite3_column_name() [...] on the same column"
(see http://www.sqlite.org/capi3ref.html#sqlite3_column_name )
We've prepared a short sample code to illustrate the problem.
Code was tested with SQLite 3.7.5 using
SQLITE_ENABLE_UPDATE_DELETE_LIMIT; platform is AMD64 (with Debian 5)
When running this test either without host parameters, or with the "old"
sqlite_prepare() interface, everything works fine.
We don't have the resources to investigate deeper into the SQLite source
code, but I'm sure that the sample code makes the problem easily visible.
The issue is propably not critical, but within our application we
cache(d) the column names after preparing the statements once, so this
behaviour at least caused some serious trouble when trying to access the
row data by "our" column names.
The most straightforward "bug fix" would be an documentation update,
stating that the returned string pointers maybe are invalid after
sqlite3_step(). ;-)
For any questions don't hezitate to contact me.
Best regards
Klaus Keppler
Sample code
(please adjust to any test table; schema doesn't matter, we tried
many different variants)
---cut---
/* Test-Code; Public Domain */
#include
#include
#include
#define SQLITE_API
#define SQLITE_ENABLE_UPDATE_DELETE_LIMIT
#include "sqlite3.h"
int main(void) {
sqlite3 *dbh = NULL;
sqlite3_stmt *stmt = NULL;
int done;
const char *search = "%u%";
const char *sql = "SELECT LOG_MESSAGE FROM LOG WHERE LOG_MESSAGE LIKE
:1";
const char *name = NULL;
if (sqlite3_open("sqlite.db", ) != SQLITE_OK) {
printf("sqlite3_open() failed\n");
exit(-1);
}
if (sqlite3_prepare_v2(
dbh,
sql,
strlen(sql)+1,
,
NULL) != SQLITE_OK) {
printf("sqlite3_prepare() failed\n");
exit(-1);
}
/* get column name */
name = sqlite3_column_name(stmt, 0);
printf("Column name (1): '%s'\n", name == NULL ? "(NULL)" : name);
/* bind parameter */
if (sqlite3_bind_text(
stmt,
1,
search,
search == NULL ? 0 : strlen(search),
SQLITE_STATIC) != SQLITE_OK) {
printf("sqlite3_bind() failed\n");
exit(-1);
}
printf("Column name (2): '%s'\n", name == NULL ? "(NULL)" : name);
done = 0;
while (!done) {
int r = sqlite3_step(stmt);
printf("Column name (3): '%s'\n", name == NULL ? "(NULL)" :
name);
const unsigned char *ch;
switch (r) {
case SQLITE_ROW:
/* we have a result row */
ch = sqlite3_column_text(stmt, 0);
printf(" result: '%s'\n", ch == NULL ?
"(NULL)" : (char*)ch);
break;
case SQLITE_DONE:
/* we're done */
printf(" (done)\n");
done=1;
break;
default:
printf(" RESULT: %i\n", r);
exit(-1);
}
}
/* free statement */
sqlite3_finalize(stmt);
stmt = NULL;
/* close connection */
sqlite3_close(dbh);
dbh = NULL;
printf("Done.\n");
return(0);
}
---/cut---
Output: (the SELECT returned two rows)
---cut---
Column name (1): 'LOG_MESSAGE'
Column name (2): 'LOG_MESSAGE'
Column name (3): 'Ø$Z'
result: '[...]'
Column name (3): 'Ø$Z'
result: '[...]'
Column name (3): 'Ø$Z'
(done)
Done.
---/cut---
--
__
Keppler IT GmbH - Die Hostingexperten.
Dipl
