At 07:29 AM 22/02/2006, you wrote:

>Hello,
>
>The following query is not supported by Firebird 1.5.x :
>"having" and "not in (select..." are not compatible
>but "having" and "in (select..." is ok
>
>select e.dept_no, count(e.emp_no)
>from EMPLOYEE e
>group by e.dept_no
>having count(e.emp_no) not in (select j.job_grade
> from job j)
>
>In firebird.log after execution
>
>AMC1 (Server) Tue Feb 21 21:00:18 2006
> Database: C:\FB153\EXAMPLES\EMPLOYEE.FDB
> internal gds software consistency check (EVL_expt: invalid
>operation (232))
>
>I have to disconnect from the database (every client must also
>disconnect)
>and reconnect

I don't get this result (consistency check) with Fb 1.5.3. Instead,
I get the exception:

ISC ERROR CODE:335544569

ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Invalid expression in the HAVING clause (neither an aggregate
function nor a part of the GROUP BY clause)

-- which is "correct" for the way v.1.5 distributes the evaluation of
NOT conditions in groupings.

-- see this, which works in 1.5.3:
select e.dept_no, count(e.emp_no)
from EMPLOYEE e
group by e.dept_no
having count(e.emp_no) in (select j.job_grade from job j)

>I have to disconnect from the database (every client must also
>disconnect)
>and reconnect

This aspect of your report is suspicious. If you are getting a
consistency check instead of the exception, then you have a mismatch
of componetry there somewhere, perhaps the wrong client library.

>The solution :
>
>select e.dept_no, count(e.emp_no)
>from EMPLOYEE e
>group by e.dept_no
>having not exists (select j.job_grade
> from job j
> where j.job_grade=count(e.emp_no))

The logic of this is not the same as the first query - however, it is
the right logic to resolve the distribution problem. As a general
rule, one should be as explicit as possible about expressions that
need to be predicated with NOT.

> In Firebird 2.0 beta 2, it works fine.

A lot was done between Fb 1.5.3 and Fb 2 to extend the distribution
logic of NOT tests. If you can fix up your test environment so that
you are getting the exception instead of the consistency check, this
would be an interesting one to put up as a testcase in the bug
tracker, as an argument for backporting those Fb 2 enhancements to Fb 1.5.4.

./heLen