[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?
On 11/17/15, Yuri wrote: > > Don't keep the bag, keep only one integer ID of the first failed > constraint. Therein lays the rub: there is no way to tell which (if any) FK constraint has failed until you have run the operation to the end and checked them all. Remember, an FK constraint is allowed to be violated during a transaction, as long as the violation is resolved before the end of the transaction. With CHECK, and UNIQUE, and NOT NULL constraints, you do know that the constraint has failed right away. And for those constraints, SQLite does provide more detail about exactly which constraint failed. But for FK constraints, you never know if a constraint that is failing right now might be resolved before the end of the transaction. -- D. Richard Hipp drh at sqlite.org
[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?
> It is a substantial change (basically a complete rewrite of the entire
> foreign key constraint mechanism) which would negatively impact both
> space and performance.
I think the argument is fallacious.
Don't keep the bag, keep only one integer ID of the first failed
constraint. That's all the users mostly care about.
// --- pseudo-code at the point of failure ---
if (!...check if constraint failed...) {
// fast branch: normal stuff when constraint is satisfied, not
impacted by the change at all
} else { // constraint failed
// slow branch: failure is normally unexpected, this is executed very
rarely
cntFailed++;
if (!savedFailedID) savedFailedID = currentFailedID; // the only
added line is
}
You only need to modify the slow branch. This has practically zero
performance impact, in any case it is exactly zero for the non-failure
operation.
Yuri
[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?
I think there's a solution for this, which is, loosely, to run the constraint tests twice, conditionally. That is, run it the current fast way as usual, and then only if there is a failure, run the tests again the slower way that keeps track of things so we know where the failure is. Since we only get the slowdown in the failure case, when the code is aborting anyway, it should be ok. The only negative then is that the code size increases somewhat, but it doesn't have to increase as much because the second run isn't to determine whether there will be a failure but to inform on a failure we already know happened. This add-on could also be a compile-time option to exclude if desired. -- Darren Duncan On 2015-11-17 2:32 PM, Richard Hipp wrote: > On 11/17/15, Yuri wrote: >> This message always leaves the user wondering: "Which constraint?" >> >> How hard is it to add this information to the message? Is this a matter >> of memorizing the ID of the constraint, and then printing its name in >> the message? > > It is a substantial change (basically a complete rewrite of the entire > foreign key constraint mechanism) which would negatively impact both > space and performance. > > The current foreign key constraint mechanism uses a single counter. > As constraints are violated, the counter increments, and as > constraints are resolved the counter decrements. At the end, if the > counter is greater than zero then a "foreign key constraint" error is > issued. > > To provide information about which constraint(s) failed, it would be > necessary to have a bag (a list or hash table or an associative array) > of all the constraints that have been violated and then remove > elements from the bag as constraints are resolved. > > A bag takes more run-time memory than a single counter. (Maybe a lot > more, depending on how many elements it holds.) Adding an element to > a bag takes more time than incrementing a counter. (In particular, > adding an element to a bag probably involves one or more calls to > malloc().) Removing an element from a bag takes more time than > decrementing a counter. >
[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?
On 11/17/15, Yuri wrote: > This message always leaves the user wondering: "Which constraint?" > > How hard is it to add this information to the message? Is this a matter > of memorizing the ID of the constraint, and then printing its name in > the message? > It is a substantial change (basically a complete rewrite of the entire foreign key constraint mechanism) which would negatively impact both space and performance. The current foreign key constraint mechanism uses a single counter. As constraints are violated, the counter increments, and as constraints are resolved the counter decrements. At the end, if the counter is greater than zero then a "foreign key constraint" error is issued. To provide information about which constraint(s) failed, it would be necessary to have a bag (a list or hash table or an associative array) of all the constraints that have been violated and then remove elements from the bag as constraints are resolved. A bag takes more run-time memory than a single counter. (Maybe a lot more, depending on how many elements it holds.) Adding an element to a bag takes more time than incrementing a counter. (In particular, adding an element to a bag probably involves one or more calls to malloc().) Removing an element from a bag takes more time than decrementing a counter. -- D. Richard Hipp drh at sqlite.org
[sqlite] Deference of Unitialized Pointer in shell_dbinfo_command in the sqlite shell (3.9.2)
On 11/17/15, Jonathan Metzman wrote: > > Thanks for the tip, I did get a bunch of garbage files that were written to > the directory I was fuzzing from. What do you recommend doing instead? > https://www.sqlite.org/afl/doc/trunk/README.md -- D. Richard Hipp drh at sqlite.org
[sqlite] Deference of Unitialized Pointer in shell_dbinfo_command in the sqlite shell (3.9.2)
Great! Thanks for the tip, I did get a bunch of garbage files that were written to the directory I was fuzzing from. What do you recommend doing instead? (I think I saw an sqlite_shell designed specifically for fuzzing somewhere on your site) On Tue, Nov 17, 2015 at 3:20 PM, Richard Hipp wrote: > On 11/17/15, Jonathan Metzman wrote: > > When fuzzing sqlite with American Fuzzy Lop, I believe I found the > > following bug in the sqlite shell: > > > > Thanks for the report. Joe has checked in a fix. > > Be careful about running AFL on the "sqlite3" command-line shell. AFL > might discover that it can run commands like: ".shell rm -rf ~" > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Deference of Unitialized Pointer in shell_dbinfo_command in the sqlite shell (3.9.2)
On 11/17/15, Jonathan Metzman wrote: > When fuzzing sqlite with American Fuzzy Lop, I believe I found the > following bug in the sqlite shell: > Thanks for the report. Joe has checked in a fix. Be careful about running AFL on the "sqlite3" command-line shell. AFL might discover that it can run commands like: ".shell rm -rf ~" -- D. Richard Hipp drh at sqlite.org
[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?
This message always leaves the user wondering: "Which constraint?" How hard is it to add this information to the message? Is this a matter of memorizing the ID of the constraint, and then printing its name in the message? Yuri
[sqlite] Deference of Unitialized Pointer in shell_dbinfo_command in the sqlite shell (3.9.2)
When fuzzing sqlite with American Fuzzy Lop, I believe I found the
following bug in the sqlite shell:
In shell.c:2563-2571 (in the amalgamated version), in the function
shell_dbinfo_command:
1. pFile is declared (2563): sqlite3_file *pFile;
2. Its address is passed to sqlite3_file_control
(2570): sqlite3_file_control(p->db, zDb, SQLITE_FCNTL_FILE_POINTER, );
3. pFile is checked to see if it is equal to NULL, and if not, it is
dereferenced (2571): if( pFile==0 || pFile->pMethods==0 ||
pFile->pMethods->xRead==0 ){
When I input the following commands into the sqlite shell:
create table t1(one small);
.d table
sqlite3_file_control() does not set pFile, so pFile remains unitialized
after the function returns.
By my understanding, this behavior is safe only if pFile is initialized to
0, which is not always the case.
When I compiled sqlite (with the following options, using either gcc or
clang: "shell.c sqlite3.c -lpthread -ldl -o sqlite3") with glibc 2.6.18,
pFile is initialized to 0x77df18f5 (it is some other nonzero value with
clang). As a result, given the input I mentioned above, the program will
dereference pFile on line 2571 and segfault. On later versions of glibc I
have found that this bug does not always present itself (since pFile
happens to be initialized to 0) but I have also reproduced this segfault
with glibc 2.6.32 (again, using the amalgated source from the main download
page and the instructions provided on how to compile it).
If your compiler fails to produce a binary vulnerable to this bug, you can
(probably) use the -fsanitize=memory flag that clang supports or the
-fsanitize=address flag that both clang and gcc support to reproduce the
crash.
I believe explicitly initializing pFile like so:
sqlite3_file *pFile = NULL;
Would fix this problem.
Thank you,
Jonathan Metzman
[sqlite] Are columns declared as 'INTEGER PRIMARY KEY' guaranteed not to change except from UPDATE statements?
Thanks a lot both Igor and Simon for the quick response! Your two answers settle my doubts. On Mon, Nov 16, 2015 at 3:48 PM, Simon Slavin wrote: > On 16 Nov 2015, at 12:24am, Randy Eels wrote: > > > My main question is: is an 'INTEGER PRIMARY KEY' column guaranteed not to > > change, except as a consequence of ordinary UPDATE statements? (And in > > particular, not as a consequence of a 'vacuum' statement.) > > Values in the rowid column (which has a number of other names) can change > only if that columns is not aliased by the table definition. Declaring a > named column as 'INTEGER PRIMARY KEY' counts as an alias. > > > I'd also thank further explanation as to why the 'vacuum' statement > doesn't > > change 'rowid's when an explicit 'INTEGER PRIMARY KEY' is declared. > > If you declare your own column as being INTEGER PRIMARY KEY, the rowid > will not change by anything SQLite does automatically. This is because > SQLite knows that you know what the column is called, so it thinks you > might be relying on its values. > > The programmer can still change rowid by explicitly updating it, or any > column aliased to it, using an UPDATE command. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Tricky SQL
With a compound index on ID and DATE performance is fine. Thanks again. RBS On Tue, Nov 17, 2015 at 12:45 AM, Bart Smissaert wrote: > Actually it is slower than I thought, taking some 90 secs for only 7000 > records. > Will if any compound index can speed this up. > > RBS > > On Tue, Nov 17, 2015 at 12:09 AM, Igor Tandetnik > wrote: > >> On 11/16/2015 6:37 PM, Bart Smissaert wrote: >> >>> Now, what I need is for every row the count of preceding rows where >>> the ID is the same as the ID of the current row, the date is less than >>> the >>> date of the current row and the value is >= 10 and <= 20. If the value is >>> outside >>> this range then it should stop the count for that current row. If the >>> value >>> of the current row is outside this 10 to 20 range than the result is 0 >>> and >>> there >>> is no need to count preceding rows. >>> >> >> Personally, I'd do it in application code. It can be done in single pass. >> If you insist on pure SQL solution, then something like this: >> >> select ID, DATE, VALUE, >> (select count(*) from TEST prev >> where prev.ID = T.ID and prev.DATE < T.DATE and prev.DATE > >>coalesce((select max(DATE) from TEST boundary >> where boundary.ID = T.ID and boundary.DATE <= T.DATE >> and not(boundary.VALUE between 10 and 20)), '') >> ) as COUNT_PRECEDING_IN_RANGE >> from TEST T; >> >> Performance will likely be, shall we say, less than stellar. >> -- >> Igor Tandetnik >> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > >
[sqlite] Tricky SQL
Actually it is slower than I thought, taking some 90 secs for only 7000 records. Will if any compound index can speed this up. RBS On Tue, Nov 17, 2015 at 12:09 AM, Igor Tandetnik wrote: > On 11/16/2015 6:37 PM, Bart Smissaert wrote: > >> Now, what I need is for every row the count of preceding rows where >> the ID is the same as the ID of the current row, the date is less than the >> date of the current row and the value is >= 10 and <= 20. If the value is >> outside >> this range then it should stop the count for that current row. If the >> value >> of the current row is outside this 10 to 20 range than the result is 0 and >> there >> is no need to count preceding rows. >> > > Personally, I'd do it in application code. It can be done in single pass. > If you insist on pure SQL solution, then something like this: > > select ID, DATE, VALUE, > (select count(*) from TEST prev > where prev.ID = T.ID and prev.DATE < T.DATE and prev.DATE > >coalesce((select max(DATE) from TEST boundary > where boundary.ID = T.ID and boundary.DATE <= T.DATE > and not(boundary.VALUE between 10 and 20)), '') > ) as COUNT_PRECEDING_IN_RANGE > from TEST T; > > Performance will likely be, shall we say, less than stellar. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Tricky SQL
Thanks for that. I find that an amazing solution and one that I would never have come up with. Indeed a lot simpler (for me, that is) in application code, but for my purpose this SQL is fine as my numbers are small, thousands at most. RBS On Tue, Nov 17, 2015 at 12:09 AM, Igor Tandetnik wrote: > On 11/16/2015 6:37 PM, Bart Smissaert wrote: > >> Now, what I need is for every row the count of preceding rows where >> the ID is the same as the ID of the current row, the date is less than the >> date of the current row and the value is >= 10 and <= 20. If the value is >> outside >> this range then it should stop the count for that current row. If the >> value >> of the current row is outside this 10 to 20 range than the result is 0 and >> there >> is no need to count preceding rows. >> > > Personally, I'd do it in application code. It can be done in single pass. > If you insist on pure SQL solution, then something like this: > > select ID, DATE, VALUE, > (select count(*) from TEST prev > where prev.ID = T.ID and prev.DATE < T.DATE and prev.DATE > >coalesce((select max(DATE) from TEST boundary > where boundary.ID = T.ID and boundary.DATE <= T.DATE > and not(boundary.VALUE between 10 and 20)), '') > ) as COUNT_PRECEDING_IN_RANGE > from TEST T; > > Performance will likely be, shall we say, less than stellar. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
