Problem with multiple SUMs

Started by J.M.almost 27 years ago9 messages

J.M.

darcy@druid.net

almost 27 years ago

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

maillist@candle.pha.pa.us

almost 27 years ago

In reply to: J.M. (#1)

Re: [HACKERS] Problem with multiple SUMs

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, Pennsylvania 19026

Tom Lane

tgl@sss.pgh.pa.us

almost 27 years ago

In reply to: Bruce Momjian (#2)

Re: [HACKERS] Problem with multiple SUMs

"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

Import Notes

Reply to msg id not found: YourmessageofWed27Jan1999075155-0500m105URn-0000bnC@druid.net | Resolved by subject fallback

Bruce Momjian

maillist@candle.pha.pa.us

almost 27 years ago

In reply to: Tom Lane (#3)

Re: [HACKERS] Problem with multiple SUMs

Fixed now.

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, Pennsylvania 19026

-- 
  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, Pennsylvania 19026

Import Notes

Reply to msg id not found: FromenvmaillistatJan27199992928am | Resolved by subject fallback

Bruce Momjian

maillist@candle.pha.pa.us

almost 27 years ago

In reply to: Tom Lane (#3)

Re: [HACKERS] Problem with multiple SUMs

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, Pennsylvania 19026

J.M.

darcy@druid.net

almost 27 years ago

In reply to: Bruce Momjian (#4)

Re: [HACKERS] Problem with multiple SUMs

Thus spake Bruce Momjian

Fixed now.

And just seconds short of 3 hours after my report. I wonder how long
it would take to get a fix from Oracle for something like this.

Thanks.

-- 
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.

Jackson, DeJuan

djackson@cpsgroup.com

almost 27 years ago

In reply to: J.M. (#6)

RE: [HACKERS] Problem with multiple SUMs

And just seconds short of 3 hours after my report. I wonder how long
it would take to get a fix from Oracle for something like this.

Thanks.

That depends on weather they already have the patch and were just
waiting for you to ask for it before giving it to you, or if the really
have to patch it because you're the first to notice. Always hope
they're holding out on you. %^|
-DEJ

Import Notes

Resolved by subject fallback

J.M.

darcy@druid.net

almost 27 years ago

In reply to: Jackson, DeJuan (#7)

Re: [HACKERS] Problem with multiple SUMs

Thus spake Jackson, DeJuan

And just seconds short of 3 hours after my report. I wonder how long
it would take to get a fix from Oracle for something like this.

That depends on weather they already have the patch and were just
waiting for you to ask for it before giving it to you, or if the really

I don't believe that they would get me an updated version of their product
within three hours anyway. That was three hours from the time that I
sent the bug notice to the time I had a useable product!

-- 
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.

Jackson, DeJuan

djackson@cpsgroup.com

almost 27 years ago

In reply to: J.M. (#8)

RE: [HACKERS] Problem with multiple SUMs

Thus spake Jackson, DeJuan

And just seconds short of 3 hours after my report. I

wonder how long

it would take to get a fix from Oracle for something like this.

That depends on weather they already have the patch and were just
waiting for you to ask for it before giving it to you, or

if the really

I don't believe that they would get me an updated version of
their product
within three hours anyway. That was three hours from the time that I
sent the bug notice to the time I had a useable product!

I never claimed that it would be 3 hours or less, because I know that it
wouldn't. What I was saying is that they could already have a fix/patch
and just not let you know about it, which I find to be absurd.
-DEJ

Import Notes

Resolved by subject fallback