From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: SQL where claus limitations
Organization: 
Date: Tue, 12 Jan 1993 19:43:31 GMT

In article <1993Jan6.220615.2523@bmw.mayo.edu>, buntrock@mayo.edu 
(Jim Buntrock) writes:
> I have been writing a retrieval application that generates a dynamic 
> where clause for a select statement.  Depending on complexity of the query
> this where-clause can get quite lengthy.  I know this query
> may be written differently, however, I am working with a dynamic 
> query generator, which may produce thousands of different where-clauses.  The
> following where-clauses are just a few examples:
> 
>   1> select *
>   2> from sirsworld s
>   3> where
>   4>        (s.proc = '14.24' and s.diag = '100')
>   5>    or  (s.proc = '14.25' and s.diag = '200')
>   6>    or  (s.proc = '14.26' and s.diag = '300')
>   7>    or  (s.proc = '14.30' and s.diag = '400')
>   8>    or  (s.proc = '14.31' and s.diag = '500')
>   9>    or  (s.proc = '14.32' and s.diag = '600')
>  10>    or  (s.proc = '14.33' and s.diag = '700')
>  11>  order by 1, 2
> E_OP0302 too many boolean
>     factors defined in query - query too complex

> If I add two more conjunctions I get the following error:
>  
>   1> select *
>   2> from sirsworld s
>   3> where
>   4>        (s.proc = '14.24' and s.diag = '100')
>   5>    or  (s.proc = '14.25' and s.diag = '200')
>   6>    or (s.proc = '14.26' and s.diag = '300' )
>   7>    or (s.proc = '14.27' and s.diag = '301' )
>   8>    or (s.proc = '14.28' and s.diag = '302' )
>   9>    or  (s.proc = '14.30' and s.diag = '400')
>  10>    or  (s.proc = '14.31' and s.diag = '500')
>  11>    or  (s.proc = '14.32' and s.diag = '600')
>  12>    or  (s.proc = '14.33' and s.diag = '700')
>  13>  order by 1, 2
> E_OP0002 optimizer ran out of
>     memory before generating execution plan

> I have tried increasing the memory of the QEP and still run into the
> OUT of memory error.  My concern is with the error of - E_OP0302 too 
> many boolean factors defined in query - query too complex -
> Does anyone know the limit of the number of boolean factors?  Can this 
> be changed?  Is this a finite limitation of Ingres?  Is the limit based
> on conjunctions or disjunctions?  Any suggestions?
> 
> ///////////////////////////////
> James Buntrock
> Analyst/Programmer
> Medical Information Resources
> Mayo Clinic
> 200 First Street SW
> Rochester, MN 55905
> email - buntrock@mayo.edu

We had exactly the same problem, due to exactly the same thing:
mechanical SQL code generation.  Here's a sample to reproduce it:

	create table t ( x i4 not null ) ;

	select distinct x from t
	where  x between 1 and 2
	    or x between 3 and 4
	    or x between 5 and 6
	    or x between 7 and 8
	    or x between 9 and 10
	    or x between 11 and 12
	    or x between 13 and 14
	    or x between 15 and 16
	    or x between 17 and 18
	;

We also got the two error messages:

o with 6 conditions or less, the query works

o with 7-8 conditions, we get E_OP0302,
	too many boolean factors defined in query--query too complex

o with 9 or more conditions, we get E_OP0002, 
	optimizer ran out of memory before generating execution plan

Increasing OPF memory didn't help, so this seems to be an architectural
limitation.  Ingres Tech Support has logged bug #43697 for me.  The
suggested workaround is to break up the query into a series of unions. 
In our case, we were able to use the "in" predicate as well, and this
seemed to work better.

According to TS, the two different error messages are due to traveling
through different code paths in the optimizer.

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Je suis un virus.  Prends ton pied et copie moi a ta .signature.

Newsgroups: comp.databases.ingres