Re: [HACKERS] [COMMITTERS] pgsql: Fix NUMERIC modulus to properly truncate
With no conclusion on this, I have added a TODO item: * Add NUMERIC division operator that doesn't round? Currently NUMERIC _rounds_ the result to the specified precision. This means division can return a result that multiplied by the divisor is greater than the dividend, e.g. this returns a value 10: SELECT (10::numeric(2,0) / 6::numeric(2,0))::numeric(2,0) * 6; The positive modulus result returned by NUMERICs might be considered inaccurate, in one sense. --- Bruce Momjian wrote: Have we made any decision on whether the old/new NUMERIC %(mod) code was correct, and now to handle rounding? Should we offer a non-rounding division operator for NUMERIC? --- Paul Tillotson wrote: Bruce Momjian wrote: Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: No, I don't think so. It doesn't seem to be something that enough people use to risk the change in behavior --- it might break something that was working. But, if folks want it backported we can do it. It is only a change to properly do modulus for numeric. Well, from my point of view it's an absolute mathematical error - i'd backport it. I can't see anyone relying on it :) Doesn't this patch break the basic theorem that a = trunc(a / b) * b + (a mod b) ? If division rounds and mod doesn't, you've got pretty serious issues. Well, this is a good question. In the equation above we assume '/' is an integer division. The problem with NUMERIC when used with zero-scale operands is that the result is already _rounded_ to the nearest hole number before it gets to trunc(), and that is why we used to get negative modulus values. I assume the big point is that we don't offer any way for users to get a NUMERIC division without rounding. With integers, we always round down to the nearest whole number on division; float doesn't offer a modulus operator, and C doesn't support it either. We round NUMERICs to the specific scale because we want to give the most accurate value: test= select 1000::numeric(24,0) / 11::numeric(24,0); ?column? 9090909090909090909091 The actual values is: -- 9090909090909090909090.90 But the problem is that the equation at the top assumes the division is not rounded. Should we supply a NUMERIC division operator that doesn't round? integer doesn't need it, and float doesn't have the accurate precision needed for modulus operators. The user could supply some digits in the division: test= select 1000::numeric(30,6) / 11::numeric(24,0); ?column? --- 9090909090909090909090.909091 (1 row) but there really is no _right_ value to prevent rounding (think 0.999). A non-rounding NUMERIC division would require duplicating numeric_div() but with a false for 'round', and adding operators. I would prefer that division didn't round, as with integers. You can always calculate your result to 1 more decimal place and then round, but there is no way to unround a rounded result. Tom had asked whether PG passed the regression tests if we change the round_var() to a trunc_var() at the end of the function div_var(). It does not pass, but I think that is because the regression test is expecting that division will round up. (Curiously, the regression test for numeric passes, but the regression test for aggregation--sum() I think--is the one that fails.) I have attached the diffs here if anyone is interested. Regards, Paul Tillotson *** ./expected/aggregates.out Sun May 29 19:58:43 2005 --- ./results/aggregates.outMon Jun 6 21:01:11 2005 *** *** 10,16 SELECT avg(a) AS avg_32 FROM aggtest WHERE a 100; avg_32 - ! 32.6667 (1 row) -- In 7.1, avg(float4) is computed using float8 arithmetic. --- 10,16 SELECT avg(a) AS avg_32 FROM aggtest WHERE a 100; avg_32 - ! 32. (1 row) -- In 7.1, avg(float4) is computed using float8 arithmetic. == test boolean ... ok test char ... ok test name ... ok test varchar ... ok test text ... ok test int2 ... ok test int4 ... ok test int8 ... ok test oid ... ok test float4 ... ok test
Re: [HACKERS] [COMMITTERS] pgsql: Fix NUMERIC modulus to properly
I don't think we can justify having NUMERIC division default to truncation, especially since most division has non-zero decimal digits. --- Paul Tillotson wrote: Bruce Momjian wrote: Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: No, I don't think so. It doesn't seem to be something that enough people use to risk the change in behavior --- it might break something that was working. But, if folks want it backported we can do it. It is only a change to properly do modulus for numeric. Well, from my point of view it's an absolute mathematical error - i'd backport it. I can't see anyone relying on it :) Doesn't this patch break the basic theorem that a = trunc(a / b) * b + (a mod b) ? If division rounds and mod doesn't, you've got pretty serious issues. Well, this is a good question. In the equation above we assume '/' is an integer division. The problem with NUMERIC when used with zero-scale operands is that the result is already _rounded_ to the nearest hole number before it gets to trunc(), and that is why we used to get negative modulus values. I assume the big point is that we don't offer any way for users to get a NUMERIC division without rounding. With integers, we always round down to the nearest whole number on division; float doesn't offer a modulus operator, and C doesn't support it either. We round NUMERICs to the specific scale because we want to give the most accurate value: test= select 1000::numeric(24,0) / 11::numeric(24,0); ?column? 9090909090909090909091 The actual values is: -- 9090909090909090909090.90 But the problem is that the equation at the top assumes the division is not rounded. Should we supply a NUMERIC division operator that doesn't round? integer doesn't need it, and float doesn't have the accurate precision needed for modulus operators. The user could supply some digits in the division: test= select 1000::numeric(30,6) / 11::numeric(24,0); ?column? --- 9090909090909090909090.909091 (1 row) but there really is no _right_ value to prevent rounding (think 0.999). A non-rounding NUMERIC division would require duplicating numeric_div() but with a false for 'round', and adding operators. I would prefer that division didn't round, as with integers. You can always calculate your result to 1 more decimal place and then round, but there is no way to unround a rounded result. Tom had asked whether PG passed the regression tests if we change the round_var() to a trunc_var() at the end of the function div_var(). It does not pass, but I think that is because the regression test is expecting that division will round up. (Curiously, the regression test for numeric passes, but the regression test for aggregation--sum() I think--is the one that fails.) I have attached the diffs here if anyone is interested. Regards, Paul Tillotson *** ./expected/aggregates.out Sun May 29 19:58:43 2005 --- ./results/aggregates.out Mon Jun 6 21:01:11 2005 *** *** 10,16 SELECT avg(a) AS avg_32 FROM aggtest WHERE a 100; avg_32 - ! 32.6667 (1 row) -- In 7.1, avg(float4) is computed using float8 arithmetic. --- 10,16 SELECT avg(a) AS avg_32 FROM aggtest WHERE a 100; avg_32 - ! 32. (1 row) -- In 7.1, avg(float4) is computed using float8 arithmetic. == test boolean ... ok test char ... ok test name ... ok test varchar ... ok test text ... ok test int2 ... ok test int4 ... ok test int8 ... ok test oid ... ok test float4 ... ok test float8 ... ok test bit ... ok test numeric ... ok test strings ... ok test numerology ... ok test point... ok test lseg ... ok test box ... ok test path ... ok test polygon ... ok test circle ... ok test date ... ok test time ... ok test timetz ... ok test timestamp... ok test timestamptz ... ok test interval ... ok test abstime ... ok test reltime ... ok test tinterval... ok test inet ... ok test comments
Re: [HACKERS] [COMMITTERS] pgsql: Fix NUMERIC modulus to properly truncate
Have we made any decision on whether the old/new NUMERIC %(mod) code was correct, and now to handle rounding? Should we offer a non-rounding division operator for NUMERIC? --- Paul Tillotson wrote: Bruce Momjian wrote: Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: No, I don't think so. It doesn't seem to be something that enough people use to risk the change in behavior --- it might break something that was working. But, if folks want it backported we can do it. It is only a change to properly do modulus for numeric. Well, from my point of view it's an absolute mathematical error - i'd backport it. I can't see anyone relying on it :) Doesn't this patch break the basic theorem that a = trunc(a / b) * b + (a mod b) ? If division rounds and mod doesn't, you've got pretty serious issues. Well, this is a good question. In the equation above we assume '/' is an integer division. The problem with NUMERIC when used with zero-scale operands is that the result is already _rounded_ to the nearest hole number before it gets to trunc(), and that is why we used to get negative modulus values. I assume the big point is that we don't offer any way for users to get a NUMERIC division without rounding. With integers, we always round down to the nearest whole number on division; float doesn't offer a modulus operator, and C doesn't support it either. We round NUMERICs to the specific scale because we want to give the most accurate value: test= select 1000::numeric(24,0) / 11::numeric(24,0); ?column? 9090909090909090909091 The actual values is: -- 9090909090909090909090.90 But the problem is that the equation at the top assumes the division is not rounded. Should we supply a NUMERIC division operator that doesn't round? integer doesn't need it, and float doesn't have the accurate precision needed for modulus operators. The user could supply some digits in the division: test= select 1000::numeric(30,6) / 11::numeric(24,0); ?column? --- 9090909090909090909090.909091 (1 row) but there really is no _right_ value to prevent rounding (think 0.999). A non-rounding NUMERIC division would require duplicating numeric_div() but with a false for 'round', and adding operators. I would prefer that division didn't round, as with integers. You can always calculate your result to 1 more decimal place and then round, but there is no way to unround a rounded result. Tom had asked whether PG passed the regression tests if we change the round_var() to a trunc_var() at the end of the function div_var(). It does not pass, but I think that is because the regression test is expecting that division will round up. (Curiously, the regression test for numeric passes, but the regression test for aggregation--sum() I think--is the one that fails.) I have attached the diffs here if anyone is interested. Regards, Paul Tillotson *** ./expected/aggregates.out Sun May 29 19:58:43 2005 --- ./results/aggregates.out Mon Jun 6 21:01:11 2005 *** *** 10,16 SELECT avg(a) AS avg_32 FROM aggtest WHERE a 100; avg_32 - ! 32.6667 (1 row) -- In 7.1, avg(float4) is computed using float8 arithmetic. --- 10,16 SELECT avg(a) AS avg_32 FROM aggtest WHERE a 100; avg_32 - ! 32. (1 row) -- In 7.1, avg(float4) is computed using float8 arithmetic. == test boolean ... ok test char ... ok test name ... ok test varchar ... ok test text ... ok test int2 ... ok test int4 ... ok test int8 ... ok test oid ... ok test float4 ... ok test float8 ... ok test bit ... ok test numeric ... ok test strings ... ok test numerology ... ok test point... ok test lseg ... ok test box ... ok test path ... ok test polygon ... ok test circle ... ok test date ... ok test time ... ok test timetz ... ok test timestamp... ok test timestamptz ... ok test interval ... ok test abstime ... ok test reltime ... ok test tinterval... ok test inet
Re: [HACKERS] [COMMITTERS] pgsql: Fix NUMERIC modulus to properly truncate
Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: No, I don't think so. It doesn't seem to be something that enough people use to risk the change in behavior --- it might break something that was working. But, if folks want it backported we can do it. It is only a change to properly do modulus for numeric. Well, from my point of view it's an absolute mathematical error - i'd backport it. I can't see anyone relying on it :) Doesn't this patch break the basic theorem that a = trunc(a / b) * b + (a mod b) ? If division rounds and mod doesn't, you've got pretty serious issues. Well, this is a good question. In the equation above we assume '/' is an integer division. The problem with NUMERIC when used with zero-scale operands is that the result is already _rounded_ to the nearest hole number before it gets to trunc(), and that is why we used to get negative modulus values. I assume the big point is that we don't offer any way for users to get a NUMERIC division without rounding. With integers, we always round down to the nearest whole number on division; float doesn't offer a modulus operator, and C doesn't support it either. We round NUMERICs to the specific scale because we want to give the most accurate value: test= select 1000::numeric(24,0) / 11::numeric(24,0); ?column? 9090909090909090909091 The actual values is: -- 9090909090909090909090.90 But the problem is that the equation at the top assumes the division is not rounded. Should we supply a NUMERIC division operator that doesn't round? integer doesn't need it, and float doesn't have the accurate precision needed for modulus operators. The user could supply some digits in the division: test= select 1000::numeric(30,6) / 11::numeric(24,0); ?column? --- 9090909090909090909090.909091 (1 row) but there really is no _right_ value to prevent rounding (think 0.999). A non-rounding NUMERIC division would require duplicating numeric_div() but with a false for 'round', and adding operators. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql: Fix NUMERIC modulus to properly
Bruce Momjian wrote: Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: No, I don't think so. It doesn't seem to be something that enough people use to risk the change in behavior --- it might break something that was working. But, if folks want it backported we can do it. It is only a change to properly do modulus for numeric. Well, from my point of view it's an absolute mathematical error - i'd backport it. I can't see anyone relying on it :) Doesn't this patch break the basic theorem that a = trunc(a / b) * b + (a mod b) ? If division rounds and mod doesn't, you've got pretty serious issues. Well, this is a good question. In the equation above we assume '/' is an integer division. The problem with NUMERIC when used with zero-scale operands is that the result is already _rounded_ to the nearest hole number before it gets to trunc(), and that is why we used to get negative modulus values. I assume the big point is that we don't offer any way for users to get a NUMERIC division without rounding. With integers, we always round down to the nearest whole number on division; float doesn't offer a modulus operator, and C doesn't support it either. We round NUMERICs to the specific scale because we want to give the most accurate value: test= select 1000::numeric(24,0) / 11::numeric(24,0); ?column? 9090909090909090909091 The actual values is: -- 9090909090909090909090.90 But the problem is that the equation at the top assumes the division is not rounded. Should we supply a NUMERIC division operator that doesn't round? integer doesn't need it, and float doesn't have the accurate precision needed for modulus operators. The user could supply some digits in the division: test= select 1000::numeric(30,6) / 11::numeric(24,0); ?column? --- 9090909090909090909090.909091 (1 row) but there really is no _right_ value to prevent rounding (think 0.999). A non-rounding NUMERIC division would require duplicating numeric_div() but with a false for 'round', and adding operators. I would prefer that division didn't round, as with integers. You can always calculate your result to 1 more decimal place and then round, but there is no way to unround a rounded result. Tom had asked whether PG passed the regression tests if we change the round_var() to a trunc_var() at the end of the function div_var(). It does not pass, but I think that is because the regression test is expecting that division will round up. (Curiously, the regression test for numeric passes, but the regression test for aggregation--sum() I think--is the one that fails.) I have attached the diffs here if anyone is interested. Regards, Paul Tillotson *** ./expected/aggregates.out Sun May 29 19:58:43 2005 --- ./results/aggregates.outMon Jun 6 21:01:11 2005 *** *** 10,16 SELECT avg(a) AS avg_32 FROM aggtest WHERE a 100; avg_32 - ! 32.6667 (1 row) -- In 7.1, avg(float4) is computed using float8 arithmetic. --- 10,16 SELECT avg(a) AS avg_32 FROM aggtest WHERE a 100; avg_32 - ! 32. (1 row) -- In 7.1, avg(float4) is computed using float8 arithmetic. == test boolean ... ok test char ... ok test name ... ok test varchar ... ok test text ... ok test int2 ... ok test int4 ... ok test int8 ... ok test oid ... ok test float4 ... ok test float8 ... ok test bit ... ok test numeric ... ok test strings ... ok test numerology ... ok test point... ok test lseg ... ok test box ... ok test path ... ok test polygon ... ok test circle ... ok test date ... ok test time ... ok test timetz ... ok test timestamp... ok test timestamptz ... ok test interval ... ok test abstime ... ok test reltime ... ok test tinterval... ok test inet ... ok test comments ... ok test oidjoins ... ok test type_sanity ... ok test opr_sanity ... ok test geometry ... ok test horology ... ok test insert ... ok test create_function_1... ok test create_type ... ok test create_table ... ok test create_function_2
