error from eduPersonScopedAffiliation in IDP 3.1.2

[Rob Ansaldo]

> On Sep 2, 2015, at 11:49 AM, Cantor, Scott <cantor.2 at osu.edu> wrote:
> 
> On 9/2/15, 11:45 AM, "users on behalf of Peter Schober" <users-bounces at shibboleth.net on behalf of peter.schober at univie.ac.at> wrote:
> 
> 
> 
>> * Rob Ansaldo <rlansaldo at amherst.edu> [2015-09-02 17:32]:
>>> Modify the SQL query used to derive the attribute using the COALESCE function, something like this:
>>> 
>>> <![CDATA[
>>>  SELECT COALESCE(myAttribute, ‘NONE’) AS myAttribute FROM table WHERE uid = ‘${requestContext.principalName}’
>>> ]]> 
>>> 
>>> this substitutes the string NONE where the user has a NULL for this
>>> particular attribute - this keeps the error from occurring since it
>>> is no longer empty. Then to keep the NONE from being released, add
>>> an attribute filter policy to block the release if it is set to
>>> NONE:
>> 
>> You're likely selecting more than that one myAttribute in that query,
>> right (and you wouldn't want to lose the results because of a NULL
>> myAttribute), right? Otherwise you could simply add a not-NULL check
>> to the WHERE clause, which would save you to check for the replacement
>> string (and prevent release) later in the filter.
> 
> Yeah, I assumed the problem was that some kind of left join was involved, and losing the nulls would lose the whole record.
> 
> Querying a separate table directly, just do a where is not null and be done with it. Empty result sets should not create any problems.
> 
> — Scott
> 

Sorry - I was simplifying the solution for posting, but the actual query has a number of other columns in it that have data that I need. Also the table in question is guaranteed to have a single row per principalName (that is a unique primary key), so I will not be getting back multiple rows.