Re: Proposal for resolving casting issues - Mailing list pgsql-hackers

> I think we must extend pg_cast's castimplicit column to a three-way value:
>     * okay as implicit cast in expression (or in assignment)
>     * okay as implicit cast in assignment only
>     * okay only as explicit cast

> In summary: I haven't yet gone through the existing casts in detail, but
> I propose the following general rules for deciding how to mark casts:
>
> * Casts across datatype categories should be explicit-only, with the
> exception of casts to text, which we will allow implicitly for backward
> compatibility's sake.
>
> * Within a category, "up" (lossless) conversions are implicit, "down"
> (potentially lossy) conversions should be assignment-only.

First of all, thank you for taking this on !

I think the following three states may enable a closer match to an actually
desired (Peter said mandated by SQL99) behavior.

1. okay as implicit cast in expression or assignment
2. okay as implicit cast in expression or assignment but needs runtime check(precision loss possible)
3. okay only as explicit cast (precision loss possible)

Now, since we prbbly can't do this all in beta I think it would be okay to
interpret my state 2 with yours for this release, and add expressions with
runtime checks later.

Regarding the "isExplicit": I think a closer match would be an output argument
"PrecisionInfo" enum(ok, precision loss, [conversion failed ?]). With this,
the caller can differentiate whether to raise a warning (note that char truncation
is actually supposed to raise a warning in sqlca.sqlwarn).
Maybe make it in/out so you can tell the function to not abort on conversion error,
since what I think we need for constants is a "try convert" that does not even abort
on wrong input.

For numbers there is probably only the solution to invent an "anynumber" generic type.

Examples that should imho succeed (and do succeed in other db's):select int2col = 1000000;    conversion fails (looses
precision?) --> return false                    this case could probably behave better if it where defined,
      that is_a_number but doesn't convert is a precision loss,                    maybe with this anynumeric would not
benecessary select char6col = '123456789';    conversion looses precision --> return false for eqselect int2col = 10.0;
      conversion ok --> use index on int2col (same for '10', '10.0') 

Andreas

PS: pg snapshot 09/11 does not compile on AIX (large files (don't want _LARGE_FILES), and mb conversions
(pg_ascii2mic and pg_mic2ascii not found in the postmaster and not included from elsewhere)
are the culprit) (make check hangs on without_oid's vacuum when removing conversions from Makefile and
undef _LARGE_FILES to make it compile)
There are also tons of "unsigned char vs signed char" warnings in current mb sources :-(