I have encountered a problem with a query that involves a group by and a
union clause. The query does not return the expected result.

I have generated an simple example to demonstrate the problem:

Given the following table

CREATE TABLE TBL (
PKEY INTEGER NOT NULL,
TYPE_OF_ROW INTEGER,
AMOUNT NUMERIC(10,2),
CONSTRAINT PK_TBL PRIMARY KEY (PKEY)
);

Try populating the table with the following rows:

1, 0, 5.00
2, 0, 7.00
3, 1, 11.00
4, 1, 11.00

Then execute the following query:

SELECT SUM(AMOUNT)
FROM TBL
WHERE TYPE_OF_ROW = 0
GROUP BY TYPE_OF_ROW
UNION
SELECT AMOUNT
FROM TBL
WHERE TYPE_OF_ROW <> 0

The expected result is:

11.00
11.00
12.00

Instead, the result is

11.00
12.00


if the value of amount of row 4 is set to 20.00 instead of 11, the result is
correct, i.e.

11.00
12.00
20.00

---------

I have also tried another combination

1, 0, 5.00
2, 0, 7.00
3, 0, 12.00
4, 0, 12.00


Running the same query the result is a single row: 12.00 instead of three
rows having the same value.

Has anyone reported this bug?

Salim