Re: [sqlite] A Potential Bug
In this case, sqlite3VdbeAllocUnpackedRecord is called with pSpace = 0 and
szSpace = 0.
The calculated value of nOff will also be 0, since pSpace is 0. nByte must be
greater than zero, as it is the sum of two positive terms.
Therefore the test if( nByteszSpace+nOff ) will be true, and the code path
taken will allocate memory.
Not a bug.
On 16/07/2014, at 1:31 pm, Dongpeng Xu dongpengxu.maill...@gmail.com wrote:
Hi, all,
I am using our automatic bug finding tool to scan the source code of
sqlite. The tool is designed to find potential null dereference bug. It
issues warning for the function sqlite3VdbeAllocUnpackedRecord.
SQLITE_PRIVATE UnpackedRecord *sqlite3VdbeAllocUnpackedRecord(
KeyInfo *pKeyInfo, /* Description of the record */
char *pSpace, /* Unaligned space available */
int szSpace,/* Size of pSpace[] in bytes */
char **ppFree /* OUT: Caller should free this pointer */
){
UnpackedRecord *p; /* Unpacked record to return */
int nOff; /* Increment pSpace by nOff to align it */
int nByte; /* Number of bytes required for *p */
/* We want to shift the pointer pSpace up such that it is 8-byte aligned.
** Thus, we need to calculate a value, nOff, between 0 and 7, to shift
** it by. If pSpace is already 8-byte aligned, nOff should be zero.
*/
nOff = (8 - (SQLITE_PTR_TO_INT(pSpace) 7)) 7;
nByte = ROUND8(sizeof(UnpackedRecord)) + sizeof(Mem)*(pKeyInfo-nField+1);
if( nByteszSpace+nOff ){
p = (UnpackedRecord *)sqlite3DbMallocRaw(pKeyInfo-db, nByte);
*ppFree = (char *)p;
if( !p ) return 0;
}else{
p = (UnpackedRecord*)pSpace[nOff];
*ppFree = 0;
}
p-aMem = (Mem*)((char*)p)[ROUND8(sizeof(UnpackedRecord))];
assert( pKeyInfo-aSortOrder!=0 );
p-pKeyInfo = pKeyInfo;
p-nField = pKeyInfo-nField + 1;
return p;
}
The suspicious context is in the function sqlite3VdbeSorterInit, it calls
sqlite3VdbeAllocUnpackedRecord as below:
pSorter-pUnpacked = sqlite3VdbeAllocUnpackedRecord(pCsr-pKeyInfo, 0, 0,
d);
The second and third parameters are set to zero. However, in
sqlite3VdbeAllocUnpackedRecord, p is set to pSpace + nOff if nByte =
szSpace + nOff and will be dereferenced later.
I am wondering whether this is a real bug. Is there a concrete execution
path that reach the dereference point? Any comments are welcome. Thanks!
Sincerely,
Dongpeng
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] capturing and testing a hot journal
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of mm.w Sent: Tuesday, July 15, 2014 8:34 PM To: R Smith Cc: General Discussion of SQLite Database Subject: Re: [sqlite] capturing and testing a hot journal and I stay public even it looks weird, but seriously grew up and fast kiddo. As someone who is new to this thread aside from reading, could you please just stop unless you have something useful to say? I honestly can't tell by most of your comments whether you're a professional programmer or a high school whiner pretending to be something else, and it's pretty sad that it often takes you multiple emails to finish one thought because apparently you have no reasonable control over whatever mechanism you're using to post to this list. I don't appreciate your spam and I'm pretty sure no one else that receives individual emails from this list does either. If you can't stay on topic, don't participate in the topic at all. That being said, enjoy this, flame it and don't expect a response, because I don't want to be any more hypocritical than I already am by posting this in the first place. Eric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite FTS4 unicode61 tokenizer with Winrt 8.1
Hi, I am building a winrt app using sqlite, I need to use FTS3/FTS4 with unicode61 tokenize. Throws error when i try to create a table shown below with tokenize unicode61 create virtual table if not exists Address USING fts4 (Address1 TEXT, DATE_CREATED INTEGER,CHANGED_DATE INTEGER,tokenize=unicode61) Initially I used the SQLIte winrt 8.1 downloaded from http://visualstudiogallery.msdn.microsoft.com/1d04f82f-2fe9-4727-a2f9-a2db127ddc9a After reading several checkin comments of sqlite it seems like by default unicode61 is disabled. So I compile SQLite for Winrt with -DSQLITE_ENABLE_FTS4_UNICODE61=1 I modified makefile.msc and added the below line to enable it OPT_FEATURE_FLAGS = $(OPT_FEATURE_FLAGS) -DSQLITE_ENABLE_FTS4_UNICODE61=1 Still I am unable to get unicode61 working, it works fine with 'simple' tokenizer. it will be really helpful if some one points me to the right direction. Regards, Sony Arouje http://sonyarouje.com http://sonyarouje.wordpress.com/ http://lumiagraphs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting boundaries in a search
Igor Tandetnik wrote... On 7/15/2014 10:21 PM, jose isaias cabrera wrote: SELECT * from startcodes where code = 'e'; but I want to search only from id = 8 and = 14. Just say so: SELECT * from startcodes where code = 'e' and id between 8 and 14; I know I can do a WHERE id BETWEEN 8 AND 14, but is there another faster way? So you already know the answer. How exactly does it fail to satisfy your requirements? Well, it was not that is not satisfying, but I have a huge DB and I thought that I can set the boundaries before the search and allow the SELECT to have less rows to work with. But, your answer has satisfied me. Thanks again, o'great one. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting boundaries in a search
Simon Slavin wrote... On 16 Jul 2014, at 3:21am, jose isaias cabrera cabr...@wrc.xerox.com wrote: SELECT * from startcodes where code = 'e'; but I want to search only from id = 8 and = 14. Is there a way to set the boundary for that SELECT that will only search ids 8-14? I know I can do a WHERE id BETWEEN 8 AND 14, but is there another faster way? That way is not particularly slow. You just need to have a good index. A good index for that search would be CREATE INDEX sci ON startcodes (code,id) You will find that that SELECT will then be blisteringly fast even with millions of rows in your table. I do have that INDEX for that id and table. Thanks. :-) I was just trying to be greedy and see if I could become even faster. Thanks for your help. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting boundaries in a search
Hi, Can I add my very first piece of advice after listening and reading for the last 6-9 months :) I’ll second what Simon says, I use the very same technique for a table with 4M+ records in and its so fast I thought I had an error and looked for bugs in my code. I assumed (incorrectly) that it would be very slow, It isn’t. I’ll also add one other piece of advice to the people clogging up the list on hot journals with childish comments. I’m a very old developer, C and UNIX, well into my 4th decade of programming. I have learnt over the years that some things I know very well and some things (SQL and SQLIte are excellent examples) I’m a novice and a noob and a newbie and all those other words we use. This list is an excellent source of knowledge and very valuable (both in terms of time and money). I have learnt an awful lot from reading the mails here, there are often very good questions and normally excellent answers from people who take a significant amount of time to understand the problem and to write comprehensive replies. I thank all the people who write such good replies and maintain the high quality of the mailing list. It is very rare indeed to have short and curt answers to people who make the effort to write a decent question. Whilst I cannot add much to any SQL discussion (point one above excepted, of which I’m sinfully proud to have contributed something at long last, even if its only to confirm what somebody else has done), I have come to realise that the people who answer here are real experts, I will not embarrass them by naming names, and if they say something which disagrees with what I think or have done, my first thought now is to challenge what I think and how I do it, because there is a very, very, very good chance I am wrong and the people here on the list are right. I’m old enough not to be bothered by admitting I got it wrong, and that other people know SQLite better than I do. There, I’ll now go back to the rock from which I came and lurk for another 9 months :) Thanks for all the input and very best wishes, Rob On 16 Jul 2014, at 06:48, Simon Slavin slav...@bigfraud.org wrote: On 16 Jul 2014, at 3:21am, jose isaias cabrera cabr...@wrc.xerox.com wrote: SELECT * from startcodes where code = 'e'; but I want to search only from id = 8 and = 14. Is there a way to set the boundary for that SELECT that will only search ids 8-14? I know I can do a WHERE id BETWEEN 8 AND 14, but is there another faster way? That way is not particularly slow. You just need to have a good index. A good index for that search would be CREATE INDEX sci ON startcodes (code,id) You will find that that SELECT will then be blisteringly fast even with millions of rows in your table. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting boundaries in a search
On 2014/07/16 14:23, jose isaias cabrera wrote: Simon Slavin wrote... That way is not particularly slow. You just need to have a good index. A good index for that search would be CREATE INDEX sci ON startcodes (code,id) You will find that that SELECT will then be blisteringly fast even with millions of rows in your table. I do have that INDEX for that id and table. Thanks. :-) I was just trying to be greedy and see if I could become even faster. Thanks for your help. Greed is good in this regard :) Are all the searches unique or do you repeat a lot of searches for a very specific range? If the latter, then partitioning the table (well in SQLite that would really be a second derived table) might speed things up if space is not an issue, but I would only invest the design time for this once the standard query is proven to be slow - which might be the case. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting boundaries in a search
On 16 Jul 2014, at 1:23pm, jose isaias cabrera cabr...@wrc.xerox.com wrote: Simon Slavin wrote... CREATE INDEX sci ON startcodes (code,id) You will find that that SELECT will then be blisteringly fast even with millions of rows in your table. I do have that INDEX for that id and table. Thanks. :-) I was just trying to be greedy and see if I could become even faster. Executing ANALYZE (just once: the results are saved in the database) might help SQLite pick the best index. However, if you have that index I'd be stunned if you have any real problem with the speed of SQLite, unless you are hampering it in some way, perhaps with badly chosen PRAGMAs. To answer your original question, having that index and putting both terms in your WHERE clause is the recognised way of making SQL do the search you want. From there it's up to SQL to do its job quickly and it shouldn't be your problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting boundaries in a search
All my searches are unique and go across the whole table. The range I select from us normally between 500 and 600 rows. I benchmarked the select over the growth of the database to circa 4m records and the slowdown was negligible. I'm not looking at optimising it as I have far better candidates for optimisation (sadly). I'm still building the application and still adding data, and may double the test database size over the next week. I'm confident (famous last words) it won't be a problem (stop sniggering at the back there). -- Rob On Wednesday, 16 July 2014 at 15:49, RSmith wrote: On 2014/07/16 14:23, jose isaias cabrera wrote: Simon Slavin wrote... That way is not particularly slow. You just need to have a good index. A good index for that search would be CREATE INDEX sci ON startcodes (code,id) You will find that that SELECT will then be blisteringly fast even with millions of rows in your table. I do have that INDEX for that id and table. Thanks. :-) I was just trying to be greedy and see if I could become even faster. Thanks for your help. Greed is good in this regard :) Are all the searches unique or do you repeat a lot of searches for a very specific range? If the latter, then partitioning the table (well in SQLite that would really be a second derived table) might speed things up if space is not an issue, but I would only invest the design time for this once the standard query is proven to be slow - which might be the case. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting boundaries in a search
Rob Willett wrote... Hi, Can I add my very first piece of advice after listening and reading for the last 6-9 months :) I’ll second what Simon says, I use the very same technique for a table with 4M+ records in and its so fast I thought I had an error and looked for bugs in my code. I assumed (incorrectly) that it would be very slow, It isn’t. I’ll also add one other piece of advice to the people clogging up the list on hot journals with childish comments. I’m a very old developer, C and UNIX, well into my 4th decade of programming. I have learnt over the years that some things I know very well and some things (SQL and SQLIte are excellent examples) I’m a novice and a noob and a newbie and all those other words we use. This list is an excellent source of knowledge and very valuable (both in terms of time and money). I have learnt an awful lot from reading the mails here, there are often very good questions and normally excellent answers from people who take a significant amount of time to understand the problem and to write comprehensive replies. I thank all the people who write such good replies and maintain the high quality of the mailing list. It is very rare indeed to have short and curt answers to people who make the effort to write a decent question. Whilst I cannot add much to any SQL discussion (point one above excepted, of which I’m sinfully proud to have contributed something at long last, even if its only to confirm what somebody else has done), I have come to realise that the people who answer here are real experts, I will not embarrass them by naming names, and if they say something which disagrees with what I think or have done, my first thought now is to challenge what I think and how I do it, because there is a very, very, very good chance I am wrong and the people here on the list are right. I’m old enough not to be bothered by admitting I got it wrong, and that other people know SQLite better than I do. There, I’ll now go back to the rock from which I came and lurk for another 9 months :) Thanks for all the input and very best wishes, Rob Thanks for this Rob. I also want to thank the wonderful folks that provide support and answers to the newbies and oldies. Thanks so much. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting boundaries in a search
RSmith wrote... On 2014/07/16 14:23, jose isaias cabrera wrote: Simon Slavin wrote... That way is not particularly slow. You just need to have a good index. A good index for that search would be CREATE INDEX sci ON startcodes (code,id) You will find that that SELECT will then be blisteringly fast even with millions of rows in your table. I do have that INDEX for that id and table. Thanks. :-) I was just trying to be greedy and see if I could become even faster. Thanks for your help. Greed is good in this regard :) Are all the searches unique or do you repeat a lot of searches for a very specific range? If the latter, then partitioning the table (well in SQLite that would really be a second derived table) might speed things up if space is not an issue, but I would only invest the design time for this once the standard query is proven to be slow - which might be the case. Now that you mention this, I remember someone in this list that suggested for me to break this table down into closed projects (many), archived projects (the list keeps growing) and open projects. So, thanks for this reminder. I just have to write another email to this list later when I make those changes to see how I would search in all tables (Archived, Closed and Open) for all projects (archived, closed and opened) that belong to, say cust='Xerox', but, that will be later. :-) Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting boundaries in a search
Simon Slavin wrote... On 16 Jul 2014, at 1:23pm, jose isaias cabrera cabr...@wrc.xerox.com wrote: Simon Slavin wrote... CREATE INDEX sci ON startcodes (code,id) You will find that that SELECT will then be blisteringly fast even with millions of rows in your table. I do have that INDEX for that id and table. Thanks. :-) I was just trying to be greedy and see if I could become even faster. Executing ANALYZE (just once: the results are saved in the database) might help SQLite pick the best index. However, if you have that index I'd be stunned if you have any real problem with the speed of SQLite, unless you are hampering it in some way, perhaps with badly chosen PRAGMAs. The speed is fine. I am just a greedy punk. :-) To answer your original question, having that index and putting both terms in your WHERE clause is the recognised way of making SQL do the search you want. From there it's up to SQL to do its job quickly and it shouldn't be your problem. Yep. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting boundaries in a search
On 2014/07/16 14:55, Rob Willett wrote: I’ll second what Simon says, I use the very same technique for a table with 4M+ records in and its so fast I thought I had an error and looked for bugs in my code. I assumed (incorrectly) that it would be very slow, It isn’t. In a similar fashion I had made this system for basically loading CSV files into an SQLite DB, then running all kinds of rules on it (which I could make with a designer) such as search-replace, substitute column values, do checks, delete rows with empty values in a certain column, etc. etc. and then finally export it to a CSV again, all basically streamlining a datafeed alteration process into a one-click thing. The viewer I used was based on a method discussed in another thread where the virtual view would get actual data only for items in the visible field by primary key. I tested it with some CSV tables over 250MB big resulting in 10mil+ rows, and here was my surprise, whether I looked at the top of the list, or the bottom, or anywhere else, the data retrieval was instantaneous - retrieving a page worth of records at whatever speed I can scroll the vertical scrollbar - not a single slow-down as I got further down or indeed any other slowness. I have come to expect great performance from SQLite as a standard, but I am still often surprised at just how quick it can be. (btw: This app is freely shared if anyone needs something of the kind or fancy testing the above, just mail me) ...// I have come to realise that the people who answer here are real experts, I will not embarrass them by naming names//... Oh don't worry, we know exactly who you mean... ;) It's Igor right? We all want to be like Igor when we grow up... *sighs dreamily* ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
