Inner Query Coalesce Causes Incorrect Query Results?

We have come across a peculiar situation where we get some very different
behaviour when an inner query contains a coalesce. We are flattening out a
list into a comma separated string variable. The desired goal is to have a
comma separated list of people in alphabetical order.

People are stored in table #tblA and their names can optionally be
overridden in #tblB in a 1-to-1 relationship. We are aggregating the names
in an outer query that appends to a local variable.

Below is the code illustrating 4 different scenarios, none of which fully
work. This code can be pasted directly into a SQL Server 2005 or SQL Server
2008 query window and executed without any setup.

The ResultFailed column should have returned 3 items, however it only
returns 1. This is the code that makes most sense and should work. The
ResultsSuccess column returns the correct results, however it is not
guaranteed to be in alphabetical order (we removed the order by clause). The
ResultsSuccessGroupBy column does return required results, however there is a
Group By clause in the outer query that shouldn't be necessary (there are no
aggregate functions involved). The ResultsSuccessNoInnerCoalesce column
shows the correct number of items in the list, however it is missing the
overrides provided by the inner coalesce.

Please review and let us know what is wrong with the code. In our opinion,
this appears to be a bug in the way SQL Server is executing this query.

Thanks,
Keith


if object_id('tempdb.dbo.#tblA') is not null drop table #tblA
create table #tblA (PersonID int not null, FullName varchar(25) not null)

insert into #tblA(PersonID, FullName)
select 1, 'Person1' union select 2, 'Person2'

if object_id('tempdb.dbo.#tblB') is not null drop table #tblB
create table #tblB (PersonID int not null, FullName varchar(25) null)

insert into #tblB(PersonID, FullName)
select 1, null union select 2, 'Person2 override' union select 3, 'Person3
override'


declare @flattenFails varchar(max)
declare @flattenWorksButNotOrdered varchar(max)
declare @flattenWorksWithGroupBy varchar(max)
declare @flattenWorksNoInnerCoalesce varchar(max)

select @flattenWorksButNotOrdered = coalesce(@flattenWorksButNotOrdered + ',
', '') + x.FullName
from (
select #tblB.PersonID, coalesce(#tblB.FullName, #tblA.FullName) as
FullName
from #tblB left join #tblA on #tblB.PersonID = #tblA.PersonID
) x
--order by x.FullName

select @flattenWorksWithGroupBy = coalesce(@flattenWorksWithGroupBy + ', ',
'') + x.FullName
from (
select #tblB.PersonID, coalesce(#tblB.FullName, #tblA.FullName) as
FullName
from #tblB left join #tblA on #tblB.PersonID = #tblA.PersonID
) x
group by x.FullName
order by x.FullName

select @flattenWorksNoInnerCoalesce = coalesce(@flattenWorksNoInnerCoalesce
+ ', ', '') + x.FullName
from (
select #tblB.PersonID, #tblB.FullName as FullName
from #tblB left join #tblA on #tblB.PersonID = #tblA.PersonID
) x
order by x.FullName

select @flattenFails = coalesce(@flattenFails + ', ', '') + x.FullName
from (
select #tblB.PersonID, coalesce(#tblB.FullName, #tblA.FullName) as
FullName
from #tblB left join #tblA on #tblB.PersonID = #tblA.PersonID
) x
order by x.FullName

select @flattenFails as ResultFailed, @flattenWorksButNotOrdered as
ResultSuccessNotOrdered, @flattenWorksWithGroupBy as ResultsSuccessGroupBy,
@flattenWorksNoInnerCoalesce as ResultsSuccessNoInnerCoalesce

drop table #tblA
drop table #tblB

Keith,

What it boils down to, is that you are currently using this:

select @flattenFails = coalesce(@flattenFails + ', ', '') + x.FullName
from ...

The problem is, that for multirow selections, the value of the variable
is undefined. And even if it returns a concatenated list of strings,
then you don't get to determine the order of the items. So it is
impossible to guarantee alphabetical order using this method.

There are many possible solutions, but using the "FOR XML" method is
probably easiest. See URL below (may wrap)

http://groups.google.nl/group/comp.databases.ms-sqlserver/browse_threa...hread/f

--
Gert-Jan



Keith Steele wrote:
>
> We have come across a peculiar situation where we get some very different
> behaviour when an inner query contains a coalesce. We are flattening out a
> list into a comma separated string variable. The desired goal is to have a
> comma separated list of people in alphabetical order.
>
> People are stored in table #tblA and their names can optionally be
> overridden in #tblB in a 1-to-1 relationship. We are aggregating the names
> in an outer query that appends to a local variable.
>
> Below is the code illustrating 4 different scenarios, none of which fully
> work. This code can be pasted directly into a SQL Server 2005 or SQL Server
> 2008 query window and executed without any setup.
>
> The ResultFailed column should have returned 3 items, however it only
> returns 1. This is the code that makes most sense and should work. The
> ResultsSuccess column returns the correct results, however it is not
> guaranteed to be in alphabetical order (we removed the order by clause). The
> ResultsSuccessGroupBy column does return required results, however there is a
> Group By clause in the outer query that shouldn't be necessary (there are no
> aggregate functions involved). The ResultsSuccessNoInnerCoalesce column
> shows the correct number of items in the list, however it is missing the
> overrides provided by the inner coalesce.
>
> Please review and let us know what is wrong with the code. In our opinion,
> this appears to be a bug in the way SQL Server is executing this query.
>
> Thanks,
> Keith
>
> if object_id('tempdb.dbo.#tblA') is not null drop table #tblA
> create table #tblA (PersonID int not null, FullName varchar(25) not null)
>
> insert into #tblA(PersonID, FullName)
> select 1, 'Person1' union select 2, 'Person2'
>
> if object_id('tempdb.dbo.#tblB') is not null drop table #tblB
> create table #tblB (PersonID int not null, FullName varchar(25) null)
>
> insert into #tblB(PersonID, FullName)
> select 1, null union select 2, 'Person2 override' union select 3, 'Person3
> override'
>
> declare @flattenFails varchar(max)
> declare @flattenWorksButNotOrdered varchar(max)
> declare @flattenWorksWithGroupBy varchar(max)
> declare @flattenWorksNoInnerCoalesce varchar(max)
>
> select @flattenWorksButNotOrdered = coalesce(@flattenWorksButNotOrdered + ',
> ', '') + x.FullName
> from (
> select #tblB.PersonID, coalesce(#tblB.FullName, #tblA.FullName) as
> FullName
> from #tblB left join #tblA on #tblB.PersonID = #tblA.PersonID
> ) x
> --order by x.FullName
>
> select @flattenWorksWithGroupBy = coalesce(@flattenWorksWithGroupBy + ', ',
> '') + x.FullName
> from (
> select #tblB.PersonID, coalesce(#tblB.FullName, #tblA.FullName) as
> FullName
> from #tblB left join #tblA on #tblB.PersonID = #tblA.PersonID
> ) x
> group by x.FullName
> order by x.FullName
>
> select @flattenWorksNoInnerCoalesce = coalesce(@flattenWorksNoInnerCoalesce
> + ', ', '') + x.FullName
> from (
> select #tblB.PersonID, #tblB.FullName as FullName
> from #tblB left join #tblA on #tblB.PersonID = #tblA.PersonID
> ) x
> order by x.FullName
>
> select @flattenFails = coalesce(@flattenFails + ', ', '') + x.FullName
> from (
> select #tblB.PersonID, coalesce(#tblB.FullName, #tblA.FullName) as
> FullName
> from #tblB left join #tblA on #tblB.PersonID = #tblA.PersonID
> ) x
> order by x.FullName
>
> select @flattenFails as ResultFailed, @flattenWorksButNotOrdered as
> ResultSuccessNotOrdered, @flattenWorksWithGroupBy as ResultsSuccessGroupBy,
> @flattenWorksNoInnerCoalesce as ResultsSuccessNoInnerCoalesce
>
> drop table #tblA
> drop table #tblB