Odd behavior with overloaded function in Postgres 13.3

user_11757024 0
Viewed 116

I've added a pair of overloaded functions to handle safe vision, optionally with rounding, in PG 13.3. I've run some simple example cases through the routines and, in one case, the output varies unexpectedly. I'm hoping that someone can shed some light on what might be causing this inconsistency. First off, here is the code for the div_safe (anycompatible, anycompatible) : real and div_safe (anycompatible, anycompatible, integer) : real functions. (I tried replacing integer with anycompatible in that third parameter, it made no difference.)

------------------------------
-- No rounding
------------------------------
CREATE OR REPLACE FUNCTION tools.div_safe(
    numerator   anycompatible,
    denominator anycompatible)

RETURNS real

AS $BODY$

SELECT numerator/NULLIF(denominator,0)::real

$BODY$
  LANGUAGE SQL;

COMMENT ON FUNCTION tools.div_safe (anycompatible, anycompatible) IS
'Pass in any two values that are, or can be coerced into, numbers, and get a safe division real result.';

------------------------------
-- Rounding
------------------------------
CREATE OR REPLACE FUNCTION tools.div_safe(
    numerator    anycompatible,
    denominator  anycompatible,
    rounding_in  integer)

RETURNS real

AS $BODY$

SELECT ROUND(numerator/NULLIF(denominator,0)::numeric, rounding_in)::real

$BODY$
  LANGUAGE sql;

COMMENT ON FUNCTION tools.div_safe (anycompatible, anycompatible, integer) IS
'Pass in any two values that are, or can be coerced into, numbers, the number of rounding digits, and get back a rounded, safe division real result.';

I threw together these checks, as I was working out the code:

-- (real, int))
select '5.1/nullif(null,0)', 5.1/nullif(null,0)    as result union all 
select 'div_safe(5.1,0)', div_safe(5.1, 0)         as result union all

-- (0, 0)
select '0/nullif(0,0)', 5.1/nullif(null,0)         as result union all 
select 'div_safe(0, 0)', div_safe(0, 0)            as result union all

-- (int, int)
select '5/nullif(8,0)::real', 5/nullif(8,0)::real  as result union all 
select 'div_safe(5,8)', div_safe(5, 8)             as result union all

-- (string, int)
select 'div_safe(''5'',8)', div_safe('5', 8)       as result union all
select 'div_safe(''8'',5)', div_safe('8', 5)       as result union all

-- Rounding: Have to convert real result to numeric to pass it into ROUND (numeric, integer)
select 'round(div_safe(10,3)::numeric, 2)', 
        round(div_safe(10,3)::numeric, 2)           as result union all
        
-- Pass a third parameter to specify rounding:
select 'div_safe(20,13,2)', div_safe(20, 13, 2)     as result


+-----------------------------------+--------------------+
| ?column?                          | result             |
+-----------------------------------+--------------------+
| 5.1/nullif(null,0)                | NULL               |
| div_safe(5.1,0)                   | NULL               |
| 0/nullif(0,0)                     | NULL               |
| div_safe(0, 0)                    | NULL               |
| 5/nullif(8,0)::real               | 0.625              |
| div_safe(5,8)                     | 0.625              |
| div_safe('5',8)                   | 0.625              |
| div_safe('8',5)                   | 1.600000023841858  |
| round(div_safe(10,3)::numeric, 2) | 3.33               |
| div_safe(20,13,2)                 | 1.5399999618530273 |
+-----------------------------------+--------------------+

The last line looks wrong to me, it should be rounded to 1.54. I've discovered that I get this behavior in the presence of one of the other tests. Specifically:

select '5/nullif(8,0)::real', 5/nullif(8,0)::real  as result union all

Without that, the final line returns 1.54, as expected.

Can anyone shed some light on what's going on? Is it something to do with the combination of anycompatible with UNION ALL? Something incredibly simple that I'm missing?

And, if anyone knows, is there a chance that anynum might be added as a pseudo-type in the future?

Follow-up regarding inconsistent output

I've already gotten a helpful answer to my original question (thanks!), and am following up on a follow-on point. Namely, why does my function round data before returning it, and then the value is changed in the final result. It think that there's something fundamental I'm missing here, and it's not obvious. I figured that I needed to confirm that the right version of the function is being called, and RAISE NOTIFICATION to get at the values, as seen inside the method. This new version is div_safe_p (anycompatible, anycompatible, integer) : real, and is written in PL/PgSQL:

------------------------------
-- Rounding
------------------------------
drop function if exists tools.div_safe_p(anycompatible,anycompatible,integer);

CREATE OR REPLACE FUNCTION tools.div_safe_p(
    numerator    anycompatible,
    denominator  anycompatible,
    rounding_in  integer)

RETURNS real

AS $BODY$

DECLARE
  result_r    real := 0;

BEGIN

   SELECT ROUND(numerator/NULLIF(denominator,0)::numeric, rounding_in)::real INTO result_r;

   RAISE NOTICE 'Calling div_safe_p(%, %, %) : %', numerator, denominator, rounding_in, result_r;

   RETURN result_r;

END
$BODY$
  LANGUAGE plpgsql;

COMMENT ON FUNCTION tools.div_safe_p (anycompatible, anycompatible, integer) IS
'Pass in any two values that are, or can be coerced into, numbers, the number of roudning digits, and get back a rounded, safe division real result.';

Here's a sample call, and output:

select  5/nullif(8,0)::real union all
select div_safe_p(10,3, 2)::real

+--------------------+
| ?column?           |
+--------------------+
| 0.625              |
| 3.3299999237060547 |
+--------------------+

The result of div_safe_p appears to be converted to a double, not a real. Check the RAISE NOTICE console output, the function returned 3.33:

NOTICE:  Calling div_safe_p(10, 3, 2) : 3.33

Yes, this 3.33 is shown as 3.3299999237060547. I'm not clear why the value is modified from how it's returned from the function. I also can't reproduce the transformation by converting the value by hand. Both select 3.33::real and select 3.33::double precision return 3.33.

Another variant, the same as the original except without the ::real castings:

select  5/nullif(8,0) union all
select div_safe_p(10,3, 2)

+----------+
| ?column? |
+----------+
| 0        |
| 3.33     |
+----------+

It certainly looks like the first value encountered is guiding the column typing, as answered already. However, I'm stumped as to why this changes the behavior of the function itself. Or, at least changes how the output is interpreted.

If this sounds like a fine point...maybe it is. When I run into peculiarities that I can't explain, I hope to figure out what's going on so that I can predict and troubleshoot more complex examples in the future.

Thanks for any illumination!

overloading postgresql stored-procedures
1 Answers

This is as expected on account of the type resolution rules for UNION:

Select the first non-unknown input type as the candidate type, then consider each other non-unknown input type, left to right.

Now the first non-NULL data type is double precision (see the type resolution rules for operators), so all results get cast to double precision resulting in the imprecision being visible. Without that test, the result is of type real, so PostgreSQL shows few enough digits to hide the imprecision.

It is useful to use the pg_typeof function to show the data type, that will clear things up:

SELECT pg_typeof(v)
FROM (SELECT NULL
      UNION ALL
      SELECT 2::real / 3::real
      UNION ALL
      SELECT pi()) AS t(v);

    pg_typeof     
══════════════════
 double precision
 double precision
 double precision
(3 rows)
user_64643080
Related