Re: [UDD] debversion type does not work with MAX aggregate function

[Roger Leigh <rleigh@codelibre.net>]

On Sat, May 16, 2009 at 06:45:38PM +0200, Andreas Tille wrote:
> Hi,
> 
> if I try for instance
> 
>  SELECT  MAX(version) FROM packages WHERE package = 'gcc' GROUP BY version order by version desc;
>     max     
> ------------
>  4:4.3.3-5
>  4:4.3.3-3
>  4:4.3.2-2
>  4:4.2.4-5
>  4:4.1.1-15
> (5 rows)
> 
> I get several maximum versions of the package gcc.  Am I missing something
> or is there a problem in the debversion type implementation?

It's possible that there's a bug in the implementation; min/max have not
been tested extensively (UDD is AFAIK the only user).  These are the
functions used for aggregation:

  Datum
  debversion_smaller(PG_FUNCTION_ARGS)
  {
    text *left  = PG_GETARG_TEXT_PP(0);
    text *right = PG_GETARG_TEXT_PP(1);
    text *result;

    result = debversioncmp(left, right) < 0 ? left : right;

    PG_RETURN_TEXT_P(result);
  }

  PG_FUNCTION_INFO_V1(debversion_larger);

  Datum
  debversion_larger(PG_FUNCTION_ARGS)
  {
    text *left  = PG_GETARG_TEXT_PP(0);
    text *right = PG_GETARG_TEXT_PP(1);
    text *result;

    result = debversioncmp(left, right) > 0 ? left : right;

    PG_RETURN_TEXT_P(result);
  }
}

The libapt_dpkg version comparison function is used for all comparisons;
that part is certainly correct.

And this is the actual function in SQL:


CREATE OR REPLACE FUNCTION debversion_smaller(version1 debversion,
					      version2 debversion)
  RETURNS debversion
  AS 'MODULE_PATHNAME'
  LANGUAGE 'C'
  IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION debversion_larger(version1 debversion,
					     version2 debversion)
  RETURNS debversion
  AS 'MODULE_PATHNAME'
  LANGUAGE 'C'
  IMMUTABLE STRICT;

CREATE AGGREGATE min(debversion)  (
  SFUNC = debversion_smaller,
  STYPE = debversion,
  SORTOP = <
);

CREATE AGGREGATE max(debversion)  (
  SFUNC = debversion_larger,
  STYPE = debversion,
  SORTOP = >
);

The actual aggregate function can only return a single value.
max() always returns the largest value, and min() the smallest.

Is it possible that the GROUP BY is making the max() function
operate on the versions within each group (which will be
identical due to being grouped), therefore returning
multiple group results?  In this query I think the GROUP BY
(and the ORDER BY) are superfluous.  If you remove them, do you
then see a different (single) result?


Regards,
Roger


-- 
  .''`.  Roger Leigh
 : :' :  Debian GNU/Linux             http://people.debian.org/~rleigh/
 `. `'   Printing on GNU/Linux?       http://gutenprint.sourceforge.net/
   `-    GPG Public Key: 0x25BFB848   Please GPG sign your mail.