SQL Question

#1

Hi Everyone,

I have a query that uses aggregate functions and a subselect in its where
clause that selects on an aggregate function.

ie.
select a.A,a.B,count(a.A)
from table_a a, table_b b1, table_c c1
where blah and
b1.field=(
select max(b2.field)
from table_b b2, table_c c2
where blahblah
)
group by a.A,a.B
having count(a.A)>0;

If I run the query as above I get no rows.

But if I run the query without the 'having' clause I get 85000 rows all
with count(a.A) 1 or more!

So if I'm thinking straight then the original 'having' clause version
should also have selected the same 85000 rows.

I've tried this on II2.0, II2.6 patch 9465 and 9731 for DEC Unix.

I've checked the QEP and from this I'm convinced that the query is
correctly producing the temp tables required for the aggregations.

b1.field is a non nullable date field, ALL of which are full date and
times. I checked!

a.A and a.B are also non nullable fields, integer and char(3)
respectively.

Where am I going wrong?

Marty Bowes

PS. FWIW The real query is as follows

Quote:

select
sc.id_number,
sc.course_code,
count(sc.id_number)
from
student_course sc,
student_address sa1,
post_code pc1
where
sc.id_number = sa1.id_number and
sc.post_code_at_enrolment = sa1.postcode and
sa1.postcode = pc1.postcode and
sa1.suburb = pc1.locality and
sc.post_code_at_enrolment is not null and
sa1.address_type_code = 'PM' and
pc1.valid = 'Y' and
sa1.date_and_time_of_update =
(select
max(sa2.date_and_time_of_update)
from
student_address sa2,
post_code pc2
where
sa2.id_number = sc.id_number and
sa2.postcode = sc.post_code_at_enrolment and
sa2.postcode = pc2.postcode and
sa2.suburb = pc2.locality and
sa2.address_type_code = 'PM' and
pc2.valid = 'Y')
group by
sc.id_number,
sc.course_code
having
count(sc.id_number) > 0;

--
Random Farscape Quote #15:
John - They spit fire! Why doesn't anyone tell me these things!

#2

"Martin Bowes" <bowes (AT) bucket (DOT) its.unimelb.edu.au> wrote

Quote:

Hi Everyone,

I have a query that uses aggregate functions and a subselect in its
where
clause that selects on an aggregate function.

ie.
select a.A,a.B,count(a.A)
from table_a a, table_b b1, table_c c1
where blah and
b1.field=(
select max(b2.field)
from table_b b2, table_c c2
where blahblah
)
group by a.A,a.B
having count(a.A)>0;

If I run the query as above I get no rows.

But if I run the query without the 'having' clause I get 85000 rows
all
with count(a.A) 1 or more!

So if I'm thinking straight then the original 'having' clause version
should also have selected the same 85000 rows.

I've tried this on II2.0, II2.6 patch 9465 and 9731 for DEC Unix.

I've checked the QEP and from this I'm convinced that the query is
correctly producing the temp tables required for the aggregations.

b1.field is a non nullable date field, ALL of which are full date and
times. I checked!

a.A and a.B are also non nullable fields, integer and char(3)
respectively.

Where am I going wrong?

Well I haven't really bothered to look at the query because I shouldn't even
be taking the time to look at c.d.i. today, but I don't think I really need
to. HAVING is just a restriction applied after aggregation. If qualifying
rows are being wrongly restricted then your problem is a bug.

Roy

#3

Hi Karl,

Yeah I reckon its a bug too. But even better was when we finally cracked
the code issue by realising that some of the join conditions in the 'blah'
and 'blahblah' had not allowed for nullable fields.

ie. change a.this = b.that and b.that is not null
to ifnull(a.this,'')=ifnull(b.that,'') and b.that is not null

Once these changes are made in the code then the query works like a dream!

Why the 'having' is effected by the ifnull is beyond my imagination. I'll
ask CA about this one.

Marty

Quote:

Hi Everyone,

I have a query that uses aggregate functions and a subselect in its where
clause that selects on an aggregate function.

ie.
select a.A,a.B,count(a.A)
from table_a a, table_b b1, table_c c1
where blah and
b1.field=(
select max(b2.field)
from table_b b2, table_c c2
where blahblah
)
group by a.A,a.B
having count(a.A)>0;

If I run the query as above I get no rows.

But if I run the query without the 'having' clause I get 85000 rows all
with count(a.A) 1 or more!

My immediate reaction is that this is a bug, probably a code generation
bug. Try: having count(*) > 0.
Actually you shouldn't need the having because if there's anything to
count, the count is > 0, and if there's nothing to count, there's no
group to output. Perhaps something in the optimizer knows this but got
it the wrong way around.

If you are feeling immensely patient, try "set trace point op150" and
compare with/without the having clause. The output of op150 does make
sense if you squint at it just right.

Karl

--
Random Farscape Quote #7:
John - Seems like Nebari Mental Cleansing doesn't get the tough stains out.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SQL Question

comp.databases.ingres comp.databases.ingres

SQL Question - 01-20-2004 , 10:59 PM

Re: SQL Question - 01-21-2004 , 05:13 AM

Re: SQL Question - 01-21-2004 , 04:00 PM