Re: [sqlite] Stack usage
> Change the 5000 to 5 and it crashes here on linux too. I guess your compiler's default stack limit is big. I tested real query on linux too and it crashed really fast. But satck size was limited by default java's value, not gcc's limit. > Maybe > we need to limit the number of SELECT statements that can be > combined in a compound. Anybody can lower stack limit during compilation. It would be hard to guess correct number. But maybe a test case that creates maximum number of selects would at least help to determine quite fast the correct number. > Another case (haven't tested it though): > > SELECT * FROM (SELECT * FROM (SELECT * FROM ...))) With default values it works - error is returned (realy fast, only 13 selects are allowed). It will crash if YYSTACKDEPTH is set to 0. > > I was thinking about sqlite based solution (other solutions are: don't > make > > such query or increase stack limit). I guess the best way is: > > -include multiSelect in sqlite3Select - that's the easy part > > -instead of recursive calls to sqlite3Select use gotos with self written > > stack created on the heap - unfortunatelly it requires to keep track of > all > > created variables. They are mostly in front of the function but still > it's > > annoying to have to remember all of them. > > > > Using MSVC it's easy prevent a crash: __try, __except and _resetstkoflw. > > I have no idea yet hot to do this with gcc. Signal can be caught, but I > > wasn't able to do anything more with demaged stack. > > Even if it worked it would leak resources and maybe leave SQLite's > internals in an inconsistent state. The goto way won't leak. But yes, _resetstkoflw may cause problems. I was thinking of closing a db connection after that error. It should solve inconsistency problem. Maybe some kind of rollback would be even better. Leaks would probably remain, but its better than a crash. -- Po meczu.kurde...:) >>> http://link.interia.pl/f1a72 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Stack usage
On Wed, 2007-06-06 at 13:38 +0200, [EMAIL PROTECTED] wrote:
> > There have been some recent changes to try to address this
> > by placing various limits on number of columns, length of
> > SQL expressions, length of SQL statements etc. See:
> >
> > http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/limits.h&v=1.6
>
> Nice. Limit is much better than a crash. But I hope that I will always remain
> configurable and will be possible to specify really large values.
>
> > But so that we can check, can you post the database schema
> > and the actual SQL statement that caused the crash in the
> > sqlite shell? Or mail it to me off-list if it's too large or
> > something.
>
> Below is a simple C programme that causes a crash with default stack size
> (tested with MSVC i cygwin's gcc):
>
> #include
> #include
> #include
> #include "sqlite3.h"
>
> int main(int argc, char **argv){
> sqlite3 *db;
> char *str = (char *) malloc(100);
> int rc, i;
>
> strcpy(str, "select * from (select 1");
> for (i = 0; i < 5000; ++i) {
> strcat(str, " union select 1");
> }
> strcat(str, ")");
>
> rc = sqlite3_open("test.db3", &db);
> printf("rc: %d\n", rc);
> rc = sqlite3_exec(db, str, NULL, 0, NULL);
> printf("rc: %d\n", rc);
> rc = sqlite3_close(db);
> printf("rc: %d\n", rc);
>
> return 0;
> }
>
> If you still want the big query let me know, but it basically does the same -
> it doesn't even need any table.
Change the 5000 to 5 and it crashes here on linux too. Maybe
we need to limit the number of SELECT statements that can be
combined in a compound. Another case (haven't tested it though):
SELECT * FROM (SELECT * FROM (SELECT * FROM ...)))
probably overflows the stack too.
> I was thinking about sqlite based solution (other solutions are: don't make
> such query or increase stack limit). I guess the best way is:
> -include multiSelect in sqlite3Select - that's the easy part
> -instead of recursive calls to sqlite3Select use gotos with self written
> stack created on the heap - unfortunatelly it requires to keep track of all
> created variables. They are mostly in front of the function but still it's
> annoying to have to remember all of them.
>
> Using MSVC it's easy prevent a crash: __try, __except and _resetstkoflw.
> I have no idea yet hot to do this with gcc. Signal can be caught, but I
> wasn't able to do anything more with demaged stack.
Even if it worked it would leak resources and maybe leave SQLite's
internals in an inconsistent state.
Dan.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Stack usage
> There have been some recent changes to try to address this
> by placing various limits on number of columns, length of
> SQL expressions, length of SQL statements etc. See:
>
> http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/limits.h&v=1.6
Nice. Limit is much better than a crash. But I hope that I will always remain
configurable and will be possible to specify really large values.
> But so that we can check, can you post the database schema
> and the actual SQL statement that caused the crash in the
> sqlite shell? Or mail it to me off-list if it's too large or
> something.
Below is a simple C programme that causes a crash with default stack size
(tested with MSVC i cygwin's gcc):
#include
#include
#include
#include "sqlite3.h"
int main(int argc, char **argv){
sqlite3 *db;
char *str = (char *) malloc(100);
int rc, i;
strcpy(str, "select * from (select 1");
for (i = 0; i < 5000; ++i) {
strcat(str, " union select 1");
}
strcat(str, ")");
rc = sqlite3_open("test.db3", &db);
printf("rc: %d\n", rc);
rc = sqlite3_exec(db, str, NULL, 0, NULL);
printf("rc: %d\n", rc);
rc = sqlite3_close(db);
printf("rc: %d\n", rc);
return 0;
}
If you still want the big query let me know, but it basically does the same -
it doesn't even need any table.
I was thinking about sqlite based solution (other solutions are: don't make
such query or increase stack limit). I guess the best way is:
-include multiSelect in sqlite3Select - that's the easy part
-instead of recursive calls to sqlite3Select use gotos with self written
stack created on the heap - unfortunatelly it requires to keep track of all
created variables. They are mostly in front of the function but still it's
annoying to have to remember all of them.
Using MSVC it's easy prevent a crash: __try, __except and _resetstkoflw.
I have no idea yet hot to do this with gcc. Signal can be caught, but I
wasn't able to do anything more with demaged stack. I was looking at
-fstack-check - it does print information but i dont know how to catch the
error ant recover.
--
Kasia Cichopek eksponuje biust
>>> http://link.interia.pl/f1a6f
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Stack usage
On Tue, 2007-06-05 at 13:35 +0200, [EMAIL PROTECTED] wrote: > I'v read in change log that some stack allocted memory were moved to the > heap, but I think that there is still to much allocated memory on the stack. > After creating a table with 2000 columns, jdbc driver created a query that > run out of stack. Default java's stack limit is low, but it wasn't hard to > create simillar query that crashed C application with default stack limit. > And the fact that it crashed instead repoting an error isn't really nice. > The query created by the driver looks like that: > > select null as TABLE_CAT, null as TABLE_SCHEM, 'test' as TABLE_NAME, cn as > COLUMN_NAME, dt as DATA_TYPE, tn as TYPE_NAME, 20 as COLUMN_SIZE, > 20 as BUFFER_LENGTH, 10 as DECIMAL_DIGITS, 10 as NUM_PREC_RADIX, > colnullable as NULLABLE, null as REMARKS, null as COLUMN_DEF, 0as > SQL_DATA_TYPE, 0as SQL_DATETIME_SUB, 20 as CHAR_OCTET_LENGTH, > ordpos as ORDINAL_POSITION, (case colnullable when 0 then 'N' when 1 then 'Y' > else '' end)as IS_NULLABLE, null as SCOPE_CATLOG, null as SCOPE_SCHEMA, > null as SCOPE_TABLE, null as SOURCE_DATA_TYPE from (select 0 as ordpos, 1 as > colnullable, 'col1' as cn, 'double' as tn, 8 as dt union all select 1 as > ordpos, 1 as colnullable, 'col2' as cn, 'double' as tn, 8 as dt union all > select 2 as ordpos, 1 as colnullable, 'col3' as cn, 'double' as tn, 8 as dt > union all select 3 as ordpos, 1 as colnullable, 'col4' as cn, 'double' as tn, > 8 as dt union all select 4 as ordpos, 1 as colnullable, 'lastcol' as cn, > 'double' as tn, 8 as dt); > > but uses more columns. There have been some recent changes to try to address this by placing various limits on number of columns, length of SQL expressions, length of SQL statements etc. See: http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/limits.h&v=1.6 So you could try with cvs head and the problem might have already been fixed. But so that we can check, can you post the database schema and the actual SQL statement that caused the crash in the sqlite shell? Or mail it to me off-list if it's too large or something. Thanks, Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Stack usage
--- [EMAIL PROTECTED] wrote: > My application's doesn't create any databases itself. It allows users to > store any data. And users need to be able to store any number of columns in > 1 table (the most I'v heard about is about 1, but I wouldn't be > surprised if they had more). Trust me, they need it and they can't do > anything about it.. > But the problem isnt't actually caused by many columns in the table. The > query i posted before doesn't need any table in the database - it was > created based on the a table but any similar query will crash SQLite. The > query was created by JDBC driver - I will change driver's code and the > problem will be solved for now, but it doesn't mean that everything is ok. > Similar automatically created queries may be created based on anything, for > example rows of some table - 1 rows isn't too many, right? And that > query may cause any application to crash. And possibly there are other > innocent looking queries that are implemented using recursion that will do > the same damage (maybe nested selects ?). Since your program uniquely requires such a high level of fault tolerance and you have very demanding customers, you should consider buying some commercial support: http://www.hwaci.com/sw/sqlite/prosupport.html Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Stack usage
At 20:31 05/06/2007, you wrote: > Excuse me for make this question but have you > normalized your database? Try to design/define it in at least 3NormalForm. My application's doesn't create any databases itself. It allows users to store any data. And users need to be able to store any number of columns in 1 table (the most I'v heard about is about 1, but I wouldn't be surprised if they had more). Trust me, they need it and they can't do anything about it.. But the problem isnt't actually caused by many columns in the table. The query i posted before doesn't need any table in the database - it was created based on the a table but any similar query will crash SQLite. The query was created by JDBC driver - I will change driver's code and the problem will be solved for now, but it doesn't mean that everything is ok. Similar automatically created queries may be created based on anything, for example rows of some table - 1 rows isn't too many, right? And that query may cause any application to crash. And possibly there are other innocent looking queries that are implemented using recursion that will do the same damage (maybe nested selects ?). The number of rows is not a problem, the number of columns may be. My database teacher said that "the best table have 2 columns" too theoric for me but several hundreds or thousands... too practical. - Antivirus Warning: User detected. Please keep away from keyboard or you will be erased. Thank you. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Stack usage
> Excuse me for make this question but have you > normalized your database? Try to design/define it in at least 3NormalForm. My application's doesn't create any databases itself. It allows users to store any data. And users need to be able to store any number of columns in 1 table (the most I'v heard about is about 1, but I wouldn't be surprised if they had more). Trust me, they need it and they can't do anything about it.. But the problem isnt't actually caused by many columns in the table. The query i posted before doesn't need any table in the database - it was created based on the a table but any similar query will crash SQLite. The query was created by JDBC driver - I will change driver's code and the problem will be solved for now, but it doesn't mean that everything is ok. Similar automatically created queries may be created based on anything, for example rows of some table - 1 rows isn't too many, right? And that query may cause any application to crash. And possibly there are other innocent looking queries that are implemented using recursion that will do the same damage (maybe nested selects ?). -- Nowy darmowy serwis og³oszeniowy Populada. Kup, sprzedaj, zamieñ >>> http://link.interia.pl/f1a8a - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Stack usage
At 16:40 05/06/2007, you wrote: Joe Wilson napisa³(a): > Please respond to the mailing list in the future. Sorry. Different client. I didn't notice the adress. > At least there's a known workaround, so no problem. Workaround is not a solution. > > > > hence your problem. > > > > Sure it is. Just like any bug or missing feature in any application. But > I'v always hoped that > > SQLite won't constraint me. It's sad the it does, and horrible that it > crashes instead returning > > an error. > > You should ask for your money back. Yes it's free. But I think that no user (or author) of software like when software crashes. Unfortunatelly SQLite does. Last time wasn't sent to the mailing list so once more: Does anyone know other places apart from unions where deep recursion may be used? Excuse me for make this question but have you normalized your database? Try to design/define it in at least 3NormalForm. A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing on usenet and in e-mail? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Stack usage
--- [EMAIL PROTECTED] wrote:
> Joe Wilson napisa³(a):
> > Please respond to the mailing list in the future.
>
> Sorry. Different client. I didn't notice the adress.
>
> > At least there's a known workaround, so no problem.
>
> Workaround is not a solution.
Increasing the stack will fix your problem.
> > > > hence your problem.
> > >
> > > Sure it is. Just like any bug or missing feature in any application. But
> > I'v always hoped that
> > > SQLite won't constraint me. It's sad the it does, and horrible that it
> > crashes instead returning
> > > an error.
> >
> > You should ask for your money back.
>
> Yes it's free. But I think that no user (or author) of software like when
> software crashes.
> Unfortunatelly SQLite does.
And you're also free to patch it.
> Last time wasn't sent to the mailing list so once more:
> Does anyone know other places apart from unions where deep recursion may be
> used?
Because you've asked so nicely, it seems that SQLite already handles this:
#if SQLITE_MAX_EXPR_DEPTH>0
/* The following three functions, heightOfExpr(), heightOfExprList()
** and heightOfSelect(), are used to determine the maximum height
** of any expression tree referenced by the structure passed as the
** first argument.
**
** If this maximum height is greater than the current value pointed
** to by pnHeight, the second parameter, then set *pnHeight to that
** value.
*/
static void heightOfExpr(Expr *p, int *pnHeight){
if( p ){
if( p->nHeight>*pnHeight ){
*pnHeight = p->nHeight;
}
}
}
static void heightOfExprList(ExprList *p, int *pnHeight){
if( p ){
int i;
for(i=0; inExpr; i++){
heightOfExpr(p->a[i].pExpr, pnHeight);
}
}
}
static void heightOfSelect(Select *p, int *pnHeight){
if( p ){
heightOfExpr(p->pWhere, pnHeight);
heightOfExpr(p->pHaving, pnHeight);
heightOfExpr(p->pLimit, pnHeight);
heightOfExpr(p->pOffset, pnHeight);
heightOfExprList(p->pEList, pnHeight);
heightOfExprList(p->pGroupBy, pnHeight);
heightOfExprList(p->pOrderBy, pnHeight);
heightOfSelect(p->pPrior, pnHeight);
}
}
/*
** Set the Expr.nHeight variable in the structure passed as an
** argument. An expression with no children, Expr.pList or
** Expr.pSelect member has a height of 1. Any other expression
** has a height equal to the maximum height of any other
** referenced Expr plus one.
*/
void sqlite3ExprSetHeight(Expr *p){
int nHeight = 0;
heightOfExpr(p->pLeft, &nHeight);
heightOfExpr(p->pRight, &nHeight);
heightOfExprList(p->pList, &nHeight);
heightOfSelect(p->pSelect, &nHeight);
p->nHeight = nHeight + 1;
}
/*
** Return the maximum height of any expression tree referenced
** by the select statement passed as an argument.
*/
int sqlite3SelectExprHeight(Select *p){
int nHeight = 0;
heightOfSelect(p, &nHeight);
return nHeight;
}
#endif
Food fight? Enjoy some healthy debate
in the Yahoo! Answers Food & Drink Q&A.
http://answers.yahoo.com/dir/?link=list&sid=396545367
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Stack usage
On 05 Jun 2007 16:40:32 +0200, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Joe Wilson napisał(a): > At least there's a known workaround, so no problem. Workaround is not a solution. For an embedded (and lite) SQL engine like SQLite, you have to bear in mind some features will never be implemented, but you can always open a ticket and "lobby" for that feature to be implemented. > You should ask for your money back. Yes it's free. But I think that no user (or author) of software like when software crashes. Unfortunatelly SQLite does. I would agree with you that it is a bug if the the library crashes, but I don't think there is any portable way for sqlite to know the stack size (or available stack bytes), so it's that kind of error SQLite simply can't do nothing about. To tell the truth, I don't know of any easy non-portable way of doing it either in Windows or Linux. You should file a bug report, anyway, because the developers know better than me. If for nothing else, it could be used to update the documentation about unions to warn about this case. Last time wasn't sent to the mailing list so once more: Does anyone know other places apart from unions where deep recursion may be used? I believe your question is more for other places where you can avoid the deep recursion (as the deep recursion will always lead to the same problem). Best regards, ~Nuno Lucas -- Kasia Cichopek eksponuje biust >>> http://link.interia.pl/f1a6f
Re: [sqlite] Stack usage
Joe Wilson napisał(a): > Please respond to the mailing list in the future. Sorry. Different client. I didn't notice the adress. > At least there's a known workaround, so no problem. Workaround is not a solution. > > > > hence your problem. > > > > Sure it is. Just like any bug or missing feature in any application. But > I'v always hoped that > > SQLite won't constraint me. It's sad the it does, and horrible > > that it > crashes instead returning > > an error. > > You should ask for your money back. Yes it's free. But I think that no user (or author) of software like when software crashes. Unfortunatelly SQLite does. Last time wasn't sent to the mailing list so once more: Does anyone know other places apart from unions where deep recursion may be used? -- Kasia Cichopek eksponuje biust >>> http://link.interia.pl/f1a6f - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Stack usage
Such a statement would never be issued on a low memory device. This is an exceptional case involving a select with 2000 unions - I would not worry about it. --- [EMAIL PROTECTED] wrote: > This is very worrying since it means that the statement cannot be compiled on > a > low memory device. > I am new to Sqlite, but I would guess that a precompiled query could be used, > where memory is low > and I also suppose that variable values could be bound to that precompiled > query. TV dinner still cooling? Check out "Tonight's Picks" on Yahoo! TV. http://tv.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Stack usage
Is it wise to have a database with 2000 columns? Wouldn't it be better to
split this into several tables and query them separately?
Jonas
On 6/5/07, [EMAIL PROTECTED] <
[EMAIL PROTECTED]> wrote:
This is very worrying since it means that the statement cannot be compiled
on a
low memory device.
I am new to Sqlite, but I would guess that a precompiled query could be
used,
where memory is low
and I also suppose that variable values could be bound to that precompiled
query.
Clive
Joe Wilson <[EMAIL PROTECTED]> on 05/06/2007 14:33:42
Please respond to sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
cc:(bcc: clive/Emultek)
Subject: Re: [sqlite] Stack usage
--- [EMAIL PROTECTED] wrote:
> I'v read in change log that some stack allocted memory were moved to the
heap,
but I think that
> there is still to much allocated memory on the stack.
> After creating a table with 2000 columns, jdbc driver created a query
that run
out of stack.
> Default java's stack limit is low, but it wasn't hard to create simillar
query
that crashed C
> application with default stack limit. And the fact that it crashed
instead
repoting an error
> isn't really nice.
> The query created by the driver looks like that:
>
> select null as TABLE_CAT, null as TABLE_SCHEM, 'test' as TABLE_NAME, cn
as
COLUMN_NAME, dt as
> DATA_TYPE, tn as TYPE_NAME, 20 as COLUMN_SIZE, 20 as
BUFFER_LENGTH, 10 as
> DECIMAL_DIGITS, 10 as NUM_PREC_RADIX, colnullable as NULLABLE, null as
REMARKS, null as
> COLUMN_DEF, 0as SQL_DATA_TYPE, 0as SQL_DATETIME_SUB, 20
as
CHAR_OCTET_LENGTH,
> ordpos as ORDINAL_POSITION, (case colnullable when 0 then 'N' when 1
then 'Y'
else '' end)as
> IS_NULLABLE, null as SCOPE_CATLOG, null as SCOPE_SCHEMA, null as
SCOPE_TABLE,
null as
> SOURCE_DATA_TYPE from (select 0 as ordpos, 1 as colnullable, 'col1' as
cn,
'double' as tn, 8 as
> dt union all select 1 as ordpos, 1 as colnullable, 'col2' as cn,
'double' as
tn, 8 as dt union
> all select 2 as ordpos, 1 as colnullable, 'col3' as cn, 'double' as tn,
8 as
dt union all select
> 3 as ordpos, 1 as colnullable, 'col4' as cn, 'double' as tn, 8 as dt
union all
select 4 as
> ordpos, 1 as colnullable, 'lastcol' as cn, 'double' as tn, 8 as dt);
>
> but uses more columns.
SQLite uses recursion to generate code for SELECT UNION chains.
** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
**
** This statement is parsed up as follows:
**
** SELECT c FROM t3
** |
** `-> SELECT b FROM t2
**|
**`--> SELECT a FROM t1
So for your example you will have a stack of 2000 nested calls
of sqlite3Select's (via multiSelect).
Using gcc -O2 on x86 I see that each level's stack is 480 bytes.
So for 2000 unions in a select, SQLite will consume at least
480 bytes/frame x 2000 frames = 960,000 bytes. I'm guessing that Java
limits the stack to 1M per thread in your case, hence your problem.
It is not easy to adapt SQLite's code to not use recursion for
code generation, although with enough time and effort anything is
possible. Conceivably, the processing of compound queries could
be turned into a for loop.
The SQLite authors have recently added a number of maximum limits
via #defines. Perhaps the SQLITE_MAX_EXPR_DEPTH limit could also
be applied here:
/* If there is are a sequence of queries, do the earlier ones first.
*/
if( p->pPrior ){
if( p->pRightmost==0 ){
Select *pLoop;
for(pLoop=p; pLoop; pLoop=pLoop->pPrior){
pLoop->pRightmost = p;
}
}
return multiSelect(pParse, p, eDest, iParm, aff);
}
It's here! Your new message!
Get new email alerts with the free Yahoo! Toolbar.
http://tools.search.yahoo.com/toolbar/features/mail/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Stack usage
This is very worrying since it means that the statement cannot be compiled on a
low memory device.
I am new to Sqlite, but I would guess that a precompiled query could be used,
where memory is low
and I also suppose that variable values could be bound to that precompiled
query.
Clive
Joe Wilson <[EMAIL PROTECTED]> on 05/06/2007 14:33:42
Please respond to sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
cc:(bcc: clive/Emultek)
Subject: Re: [sqlite] Stack usage
--- [EMAIL PROTECTED] wrote:
> I'v read in change log that some stack allocted memory were moved to the heap,
but I think that
> there is still to much allocated memory on the stack.
> After creating a table with 2000 columns, jdbc driver created a query that run
out of stack.
> Default java's stack limit is low, but it wasn't hard to create simillar query
that crashed C
> application with default stack limit. And the fact that it crashed instead
repoting an error
> isn't really nice.
> The query created by the driver looks like that:
>
> select null as TABLE_CAT, null as TABLE_SCHEM, 'test' as TABLE_NAME, cn as
COLUMN_NAME, dt as
> DATA_TYPE, tn as TYPE_NAME, 20 as COLUMN_SIZE, 20 as
BUFFER_LENGTH, 10 as
> DECIMAL_DIGITS, 10 as NUM_PREC_RADIX, colnullable as NULLABLE, null as
REMARKS, null as
> COLUMN_DEF, 0as SQL_DATA_TYPE, 0as SQL_DATETIME_SUB, 20 as
CHAR_OCTET_LENGTH,
> ordpos as ORDINAL_POSITION, (case colnullable when 0 then 'N' when 1 then 'Y'
else '' end)as
> IS_NULLABLE, null as SCOPE_CATLOG, null as SCOPE_SCHEMA, null as SCOPE_TABLE,
null as
> SOURCE_DATA_TYPE from (select 0 as ordpos, 1 as colnullable, 'col1' as cn,
'double' as tn, 8 as
> dt union all select 1 as ordpos, 1 as colnullable, 'col2' as cn, 'double' as
tn, 8 as dt union
> all select 2 as ordpos, 1 as colnullable, 'col3' as cn, 'double' as tn, 8 as
dt union all select
> 3 as ordpos, 1 as colnullable, 'col4' as cn, 'double' as tn, 8 as dt union all
select 4 as
> ordpos, 1 as colnullable, 'lastcol' as cn, 'double' as tn, 8 as dt);
>
> but uses more columns.
SQLite uses recursion to generate code for SELECT UNION chains.
** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
**
** This statement is parsed up as follows:
**
** SELECT c FROM t3
** |
** `-> SELECT b FROM t2
**|
**`--> SELECT a FROM t1
So for your example you will have a stack of 2000 nested calls
of sqlite3Select's (via multiSelect).
Using gcc -O2 on x86 I see that each level's stack is 480 bytes.
So for 2000 unions in a select, SQLite will consume at least
480 bytes/frame x 2000 frames = 960,000 bytes. I'm guessing that Java
limits the stack to 1M per thread in your case, hence your problem.
It is not easy to adapt SQLite's code to not use recursion for
code generation, although with enough time and effort anything is
possible. Conceivably, the processing of compound queries could
be turned into a for loop.
The SQLite authors have recently added a number of maximum limits
via #defines. Perhaps the SQLITE_MAX_EXPR_DEPTH limit could also
be applied here:
/* If there is are a sequence of queries, do the earlier ones first.
*/
if( p->pPrior ){
if( p->pRightmost==0 ){
Select *pLoop;
for(pLoop=p; pLoop; pLoop=pLoop->pPrior){
pLoop->pRightmost = p;
}
}
return multiSelect(pParse, p, eDest, iParm, aff);
}
It's here! Your new message!
Get new email alerts with the free Yahoo! Toolbar.
http://tools.search.yahoo.com/toolbar/features/mail/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: Re: [sqlite] Stack usage
On 05 Jun 2007 14:59:40 +0200, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Are you sure it is sqlite that used the stack and not the jdbc driver > (or your application)? yes > What happens if you run that query from the sqlite shell? That query I pasted works. Bigger doesn't. Ok, I confused 2000 columns with 2000 rows. With that many columns I'm not surprised, but also think you "get what you deserve". It seems the stack is used in a critical path, I mean, not using the stack would hurt performance (in terms of cpu cache) in the general case so, in my opinion, your case doesn't "deserve" to be fixed. You can make the generator create a temporary table, insert the data on it, make the select and then drop the table, even if that would involve more coding (at least to handle the final table drop after geting the result). Regards, ~Nuno Lucas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Stack usage
--- [EMAIL PROTECTED] wrote:
> I'v read in change log that some stack allocted memory were moved to the
> heap, but I think that
> there is still to much allocated memory on the stack.
> After creating a table with 2000 columns, jdbc driver created a query that
> run out of stack.
> Default java's stack limit is low, but it wasn't hard to create simillar
> query that crashed C
> application with default stack limit. And the fact that it crashed instead
> repoting an error
> isn't really nice.
> The query created by the driver looks like that:
>
> select null as TABLE_CAT, null as TABLE_SCHEM, 'test' as TABLE_NAME, cn as
> COLUMN_NAME, dt as
> DATA_TYPE, tn as TYPE_NAME, 20 as COLUMN_SIZE, 20 as
> BUFFER_LENGTH, 10 as
> DECIMAL_DIGITS, 10 as NUM_PREC_RADIX, colnullable as NULLABLE, null as
> REMARKS, null as
> COLUMN_DEF, 0as SQL_DATA_TYPE, 0as SQL_DATETIME_SUB, 20 as
> CHAR_OCTET_LENGTH,
> ordpos as ORDINAL_POSITION, (case colnullable when 0 then 'N' when 1 then 'Y'
> else '' end)as
> IS_NULLABLE, null as SCOPE_CATLOG, null as SCOPE_SCHEMA, null as SCOPE_TABLE,
> null as
> SOURCE_DATA_TYPE from (select 0 as ordpos, 1 as colnullable, 'col1' as cn,
> 'double' as tn, 8 as
> dt union all select 1 as ordpos, 1 as colnullable, 'col2' as cn, 'double' as
> tn, 8 as dt union
> all select 2 as ordpos, 1 as colnullable, 'col3' as cn, 'double' as tn, 8 as
> dt union all select
> 3 as ordpos, 1 as colnullable, 'col4' as cn, 'double' as tn, 8 as dt union
> all select 4 as
> ordpos, 1 as colnullable, 'lastcol' as cn, 'double' as tn, 8 as dt);
>
> but uses more columns.
SQLite uses recursion to generate code for SELECT UNION chains.
** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
**
** This statement is parsed up as follows:
**
** SELECT c FROM t3
** |
** `-> SELECT b FROM t2
**|
**`--> SELECT a FROM t1
So for your example you will have a stack of 2000 nested calls
of sqlite3Select's (via multiSelect).
Using gcc -O2 on x86 I see that each level's stack is 480 bytes.
So for 2000 unions in a select, SQLite will consume at least
480 bytes/frame x 2000 frames = 960,000 bytes. I'm guessing that Java
limits the stack to 1M per thread in your case, hence your problem.
It is not easy to adapt SQLite's code to not use recursion for
code generation, although with enough time and effort anything is
possible. Conceivably, the processing of compound queries could
be turned into a for loop.
The SQLite authors have recently added a number of maximum limits
via #defines. Perhaps the SQLITE_MAX_EXPR_DEPTH limit could also
be applied here:
/* If there is are a sequence of queries, do the earlier ones first.
*/
if( p->pPrior ){
if( p->pRightmost==0 ){
Select *pLoop;
for(pLoop=p; pLoop; pLoop=pLoop->pPrior){
pLoop->pRightmost = p;
}
}
return multiSelect(pParse, p, eDest, iParm, aff);
}
It's here! Your new message!
Get new email alerts with the free Yahoo! Toolbar.
http://tools.search.yahoo.com/toolbar/features/mail/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Stack usage
On 05 Jun 2007 13:35:33 +0200, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I'v read in change log that some stack allocted memory were moved to the heap, but I think that there is still to much allocated memory on the stack. After creating a table with 2000 columns, jdbc driver created a query that run out of stack. Default java's stack limit is low, but it wasn't hard to create simillar query that crashed C application with default stack limit. And the fact that it crashed instead repoting an error isn't really nice. The query created by the driver looks like that: select null as TABLE_CAT, null as TABLE_SCHEM, 'test' as TABLE_NAME, cn as COLUMN_NAME, dt as DATA_TYPE, tn as TYPE_NAME, 20 as COLUMN_SIZE, 20 as BUFFER_LENGTH, 10 as DECIMAL_DIGITS, 10 as NUM_PREC_RADIX, colnullable as NULLABLE, null as REMARKS, null as COLUMN_DEF, 0as SQL_DATA_TYPE, 0as SQL_DATETIME_SUB, 20 as CHAR_OCTET_LENGTH, ordpos as ORDINAL_POSITION, (case colnullable when 0 then 'N' when 1 then 'Y' else '' end)as IS_NULLABLE, null as SCOPE_CATLOG, null as SCOPE_SCHEMA, null as SCOPE_TABLE, null as SOURCE_DATA_TYPE from (select 0 as ordpos, 1 as colnullable, 'col1' as cn, 'double' as tn, 8 as dt union all select 1 as ordpos, 1 as colnullable, 'col2' as cn, 'double' as tn, 8 as dt union all select 2 as ordpos, 1 as colnullable, 'col3' as cn, 'double' as tn, 8 as dt union all select 3 as ordpos, 1 as colnullable, 'col4' as cn, 'double' as tn, 8 as dt union all select 4 as ordpos, 1 as colnullable, 'lastcol' as cn, 'double' as tn, 8 as dt); but uses more columns. Are you sure it is sqlite that used the stack and not the jdbc driver (or your application)? What happens if you run that query from the sqlite shell? The reason I ask is because I used sqlite on WinCE (but not currently) and only 1 time I had problem with stack usage (a version that made sqlite use the stack instead of the heap, but got corrected right away in the next version). I'm sure a lot of sqlite users in the embedded field would be "ranting" about sqlite stack usage if that was true. Regards, ~Nuno Lucas - To unsubscribe, send email to [EMAIL PROTECTED] -
