Hi all,

Today I ran across an interesting problem in a stored procedure I've inherited. The query was basically like this:

INSERT INTO someTable
    (fld1, fld2, fld3, fld4, fld5, fld6, ...)
SELECT fld1, fld2, fld3, 11 as fld4, sum(fld5) as fld5, sum(fld6) as fld6 ...
    FROM (
        SELECT fld1, fld2, fld3, 11 as fld4, fld5, fld6 ...
            FROM subTable1 WITH (NOLOCK)
            WHERE someCondition
        UNION ALL
        SELECT fld1, fld2, fld3, 11 as fld4, fld5, fld6 ...
            FROM subTable2 WITH (NOLOCK)
            WHERE someCondition
        UNION ALL
        SELECT fld1, fld2, fld3, 11 as fld4, fld5, fld6 ...
            FROM subTable3 WITH (NOLOCK)
            WHERE someCondition
        UNION ALL
        SELECT fld1, fld2, fld3, 11 as fld4, fld5, fld6 ...
            FROM subTable4 WITH (NOLOCK)
            WHERE someCondition
        UNION ALL
    ) SI
    GROUP BY fld1, fld2, fld3
    HAVING fld5 <> 0 OR fld6 <> 0 ...

someTable has a primary key of (fld1, fld2, fld3, fld4)
The insert fails because of primary key violations.
If I execute just the SELECT portion, sure enough, there are places where two rows exist that should be aggregated together. If I take the SELECT portion and wrap another SELECT...GROUP BY around it, it's fine. Also, if I run the SELECT portion into a TEMP table, then issue the INSERT...SELECT...FROM TEMP TABLE...GROUP BY... it works fine.

Has anyone seen any instances like this where grouping union all'd subqueries don't group properly? And if so, is there a proper work around. Right now, I'm using the TEMP table solution, but I'm curious to know if it's the right way or not.

TIA,
Chad