Re: float8 strtod weirdness

On Wed, Jan 07, 2009 at 09:56:48AM -0500, Tom Lane wrote:
> "Nikhil Sontakke" <nikhil(dot)sontakke(at)enterprisedb(dot)com> writes:
> > Consider the following with latest CVS sources:
>
> > postgres=# create table temp(val float4);
> > CREATE TABLE
> > postgres=# insert into temp values (415.1);
> > INSERT 0 1
> > postgres=# select * from temp where val = 415.1;
>
> Anybody who works with float arithmetic can tell you that exact equality
> tests are usually a bad idea.

This example does seem to be confounded by PG's somewhat eccentric type
system. Things would "just work" (in this case, and there have been
other cases recently[1]) if type decisions could be delayed slightly.

Both of these cases are handled very nicely in Haskell; the expedient
feature being parametric polymorphism. Parametric polymorphism allows
the type system to say that the field "val" above and the constant
"415.1" should have the same type. The lexer would interpret "415.1" as
a literal representing a "real number" but wouldn't know which type to
give it (this unknown type would typically be labeled as "alpha"). When
it had made its way through the parser and through to type checking we
would get to the equality operator and realize that we had to give the
literal a concrete type. At this point we'd realize that if the LHS
of the equality was of type float4 then the RHS should be as well and
everything would just work.

In example[1] the lexer would interpret the literal "" as of unknown
type (and assign the type parameter alpha again) and everything would
pass through until type checking at which point the two sides of the
UNION would be forced to unify. As normal in type theory, the two type
parameters would unify and you then just have to decide which concrete
type to give them. This could either be an error, or you could do as PG
does at the moment and default to some arbitrary type. Another example
would be:

SELECT '1' UNION SELECT '1' UNION SELECT 1;

In PG 8.3 this gives "UNION types text and integer cannot be matched",
again because types are being checked too early. Type parameters also
allow the more accurate specification of functions, compare:

subscript(anyarray, integer) returns anyelement

with (better syntax is needed than just using a type name of alpha):

subscript(alpha[], integer) returns alpha

arbitrary numbers of independent type parameters are normally supported.
One classic example is the fold function, which makes its appearance
in database systems as an aggregation operator. In PG we define a
aggregation by specify the following:

the input type: alpha
the state type: beta
the final type: delta
a transition function: function(beta,alpha) returns beta
a finalization function: function(beta) returns delta

for example, AVG(INT) would be:

CREATE TYPE avg_numeric AS (
num INTEGER,
sum NUMERIC
);

CREATE FUNCTION avg_trans(avg_numeric,INT) RETURNS avg_numeric
LANGUAGE SQL AS $$
SELECT (($2).num+1,($2).sum+$1) $$;

CREATE FUNCTION avg_final(avg_numeric) RETURNS NUMERIC
LANGUAGE SQL AS $$
SELECT ($1).sum / ($1).num; $$;

that's all the definitions out the way, we're now OK to specify the
aggregation:

(INT,avg_numeric,NUMERIC,avg_trans,avg_final)

PG can then ensure that all the type parameters match up OK and
every thing's set to go.

Parametric polymorphism would be quite an invasive change, but would be
a laudable goal. I'd be personally interested in helping with this but
because of the size of the change it'd probably not be a good task for
me unless I had some guidance.

Sam

[1] http://archives.postgresql.org/pgsql-general/2009-01/msg00065.php