Hello,

Excuse me in the first message, I cut and paste the second query
(having clause).
Today, I ran the first query and had the same message, but when I
change count(e.emp_no)
to count(*) or max(e.dept_no) (queries 3 and 4), I got the exception.
See details below.

Query 1 :
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)

Result 1 :
SQL Message : -104
Invalid token

Engine Code : 335544569
Engine 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)
=======================
Query 2 :
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)
Result 2 : OK
DEPT_NO COUNT
000 2
100 2
======================
Queries 3 and 4 :
select e.dept_no, count(e.emp_no)
from EMPLOYEE e
group by e.dept_no
having count(*) not in (select j.job_grade
from job j)

select e.dept_no, count(e.emp_no)
from EMPLOYEE e
group by e.dept_no
having max(e.dept_no) not in (select j.job_grade
from job j)

Result 3 :
In firebird.log after execution

AMC1 (Server) Wed Feb 22 19:42:30 2006
Database: C:\FB153\EXAMPLES\EMPLOYEE.FDB
internal gds software consistency check (EVL_expt: invalid
operation (232))

AMC1 (Server) Wed Feb 22 20:30:08 2006
Database: C:\FB153\EXAMPLES\EMPLOYEE.FDB
internal gds software consistency check (EVL_expt: invalid
operation (232))

========================

Regards

Alexandre Moradell
--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
wrote:

>
> 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
>