Page 1 of 1

Aggregator output nullability

Posted: Tue Nov 24, 2009 11:21 pm
by chulett
Playing some aggregator games today and had some interesting issues, probably old news to many here but new to me (not having done much PX aggregation yet) and thought I would share. Needed to group by one field, sum three others and generate a count for each group.

Is the output from calculation fields always assumed to be nullable regardless of the setting in the stage? In spite of the fact that all fields had Nullable set to No all the way through the job, was still seeing messages like this:

Code: Select all

Aggregator_1: When checking operator: When binding output interface field "X" to field "X": Converting a nullable source to a non-nullable result; a fatal runtime error could occur; use the modify operator to specify a value to which the null should be converted.
OK... my source isn't nullable but I'll play the Modify game. Added one between the Aggregator and the Sequential File target of its handiwork and did the whole handle_null thing. It then complained about that:

Code: Select all

Modify_14: When checking operator: When binding output schema variable "outRec": When binding output interface field "X" to field "X": Null value handling is specified with a non-nullable source field and no conversion; ignoring.
Pfffttt... now suddenly my source is non-nullable so you don't need to handle nulls, you idiot. I had to switch the output columns of my Aggregator to actually show as 'Nullable' before this warning would go away.

Is this normal? Is calculation output always considered to be nullable even when I know it will never be and I mark it as such in the job? :?

Posted: Tue Nov 24, 2009 11:29 pm
by ray.wurlod
Have you set any of the null-handling properties of the Aggregator stage itself?

Posted: Tue Nov 24, 2009 11:46 pm
by chulett
Such as? All I've done is set the Nullable attribute on the Columns tab to 'No' at first for all fields (later changed to 'Yes' as noted) and I see the default stage property of 'Allow Null Output' is set to 'False'. I don't see what else I can do there.

Posted: Wed Nov 25, 2009 1:27 am
by ray.wurlod
If you set Allow Null Output (I couldn't recall the property name) then it might eliminate the warning. No idea why, but it might be worth a try.

There's always message handlers!

Re: Aggregator output nullability

Posted: Wed Nov 25, 2009 4:19 am
by dashpriya
We also faced same issue, but we are also not able to remove those warnings.

Posted: Wed Nov 25, 2009 8:02 am
by chulett
As posted, I've already handled all of the warnings and the job runs completely clean right now. I just wanted to get a better idea of what was going on and why I needed to jump though hoops to make it happy.

I'll try changing Allow Null Output to True and see how that affects what I have to do downstream.

Aggregator output nullability

Posted: Thu Dec 17, 2009 11:56 am
by krishna.m
Hi,

I am also facing the same problem.I had tried using the "Allow Null Output = True" in the aggregator stage.This is not helping to eliminate the warnings. Could anyone suggest how to eliminate the warnings without hopping....(I mean changing the columns to "Null" and again handling them in Modify stage).

Thanks in advance..
Krishna.

Posted: Mon Aug 16, 2010 7:23 am
by PhilHibbs
Also getting this. I suspect it is because an aggregation function can return a NULL even if the input column is not nullable. What happens if you specify a "Missing Value" (e.g. 0) and all the values in an aggregation set are that value? Will you get a NULL output from a non-nullable input? I suspect so, therefore aggregation function results should always be considered nullable, hence this warning.