Thread: Problem with multiple SUMs
After recent changes I find an error with SUM when summing more than one column. Here is the test sequence. DROP TABLE x; CREATE TABLE x (a int, b int); INSERT INTO x VALUES (1, 5); INSERT INTO x VALUES (2, 7); SELECT * FROM x; SELECT SUM(a) FROM x; SELECT SUM(b) FROM x; SELECT SUM(a), SUM(b) FROM x; The last three statements give the following expected results when run on a system compiled Jan 19. darcy=> SELECT SUM(a) FROM x; sum --- 3 (1 row) darcy=> SELECT SUM(b) FROM x; sum ---12 (1 row) darcy=> SELECT SUM(a), SUM(b) FROM x; sum|sum ---+--- 3| 12 (1 row) On a system compiled Jan 27, I see the following. darcy=> SELECT SUM(a) FROM x; sum --- 3 (1 row) darcy=> SELECT SUM(b) FROM x; sum ---12 (1 row) darcy=> SELECT SUM(a), SUM(b) FROM x; sum|sum ---+---12| 12 (1 row) See how the individual sums are correct but I can no longer get both sums in one select. -- D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
I am working on it. > After recent changes I find an error with SUM when summing more than > one column. Here is the test sequence. > > DROP TABLE x; > CREATE TABLE x (a int, b int); > INSERT INTO x VALUES (1, 5); > INSERT INTO x VALUES (2, 7); > SELECT * FROM x; > SELECT SUM(a) FROM x; > SELECT SUM(b) FROM x; > SELECT SUM(a), SUM(b) FROM x; > > The last three statements give the following expected results when > run on a system compiled Jan 19. > > darcy=> SELECT SUM(a) FROM x; > sum > --- > 3 > (1 row) > > darcy=> SELECT SUM(b) FROM x; > sum > --- > 12 > (1 row) > > darcy=> SELECT SUM(a), SUM(b) FROM x; > sum|sum > ---+--- > 3| 12 > (1 row) > > On a system compiled Jan 27, I see the following. > > darcy=> SELECT SUM(a) FROM x; > sum > --- > 3 > (1 row) > > darcy=> SELECT SUM(b) FROM x; > sum > --- > 12 > (1 row) > > darcy=> SELECT SUM(a), SUM(b) FROM x; > sum|sum > ---+--- > 12| 12 > (1 row) > > See how the individual sums are correct but I can no longer get both > sums in one select. > > -- > D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves > http://www.druid.net/darcy/ | and a sheep voting on > +1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner. > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
"D'Arcy" "J.M." Cain <darcy@druid.net> writes: > After recent changes I find an error with SUM when summing more than > one column. ... > See how the individual sums are correct but I can no longer get both > sums in one select. Good eye! Actually, it looks like *any* two aggregates conflict --- we're reporting the result of the rightmost aggregate for all aggregate functions in a SELECT. Using D'Arcy's test table, I also tried treetest=> SELECT AVG(a), SUM(a) FROM x; avg|sum ---+--- 3| 3 (1 row) treetest=> SELECT AVG(a), SUM(b) FROM x; avg|sum ---+---12| 12 (1 row) treetest=> SELECT AVG(a), COUNT(b) FROM x; avg|count ---+----- 2| 2 (1 row) Oops. This bug appears to explain some of the regression-test failures I'm seeing --- numerology and select_having both contain multiple-aggregate commands that are failing. In the select_having test, it looks like multiple aggregates used in the HAVING clause of a SELECT are suffering the same sort of fate as those in the target list. regards, tom lane
Fixed. > "D'Arcy" "J.M." Cain <darcy@druid.net> writes: > > After recent changes I find an error with SUM when summing more than > > one column. ... > > See how the individual sums are correct but I can no longer get both > > sums in one select. > > Good eye! > > Actually, it looks like *any* two aggregates conflict --- we're > reporting the result of the rightmost aggregate for all aggregate > functions in a SELECT. Using D'Arcy's test table, I also tried > > treetest=> SELECT AVG(a), SUM(a) FROM x; > avg|sum > ---+--- > 3| 3 > (1 row) > > treetest=> SELECT AVG(a), SUM(b) FROM x; > avg|sum > ---+--- > 12| 12 > (1 row) > > treetest=> SELECT AVG(a), COUNT(b) FROM x; > avg|count > ---+----- > 2| 2 > (1 row) > > Oops. > > This bug appears to explain some of the regression-test failures I'm > seeing --- numerology and select_having both contain multiple-aggregate > commands that are failing. > > In the select_having test, it looks like multiple aggregates used in > the HAVING clause of a SELECT are suffering the same sort of fate > as those in the target list. > > regards, tom lane > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026