Problems on NUMERIC

Hi,

sometimes it's good not to spend too much efford implementing
the final solution first. So for the NUMERIC.

First I wonder why the can_coerce... stuff is #if'd out of
parse_relation.c? For the NUMERIC type the
numeric(num,typmod) must be called if someone does an

INSERT INTO ... SELECT * FROM ...

But it isn't. It is only called when there are calculations
done on the columns. I also checked that for BPCHAR type and
it simply throws an ERROR if the target's length doesn't
match.

This might be easy to fix, but the other problem I have is a
bit more difficult.

When binary operators (add, sub, mul, div) are called, the
required precision of the result isn't known. And the coerce
function numeric(num,typmod) will only be called for the
final result. Now have the following situation:

CREATE TABLE t1 (id int4, annual_val numeric(20,4));
CREATE TABLE t2 (id int4, monthly_val numeric(24,8));

INSERT INTO t2 SELECT id, annual_val / '12' FROM t1;

A multiplication would have a maximum number of digits that
can appear after the decimal point. It is the sum of number
of digits present in the two operators. But not so for a
division.

If we want to implement NUMERIC with a real high precision
(maybe 4000 or more digits), there would currently be no
other chance than to do the division with the full ever
possible precision and then throw away most of the digits
when the result is assigned to the target column. Wasted
efford and more important MUCH WASTED CPU.

I can think of something like this:

On add/subtract the results precision after the decimal point
is the higher of the two operands.

On multiply the results precision after the decimal point is
the sum of the precisions of the two operands.

On divide the results precision after the decimal point is
like for mult or the double of the higher precision of the
two operands. Any other suggestions?

On the other hand it is possible to do it as

INSERT INTO t2 SELECT id, ROUND(annual_val,8) / '12' FROM t1;

How do other databases handle this problem. How is the
precision of a numeric result defined?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #