> 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 be necessary 
	select char6col = '123456789';	conversion looses precision --> return false for eq
	select 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 :-(

Mon, 16 Sep 2002 15:35:05 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Proposal for resolving casting issues
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> 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)

The runtime checks are there already, eg

regression=# select 123456789::int4::int2;
ERROR:  i4toi2: '123456789' causes int2 overflow

However this does not help us much; the critical point is that if we
want function overloading to work in a sane fashion, we have to prefer
up-conversions to down-conversions *at parse time*, at least for the
operands of functions and operators (which is what I meant by "in
expressions").  Runtime checks are irrelevant to this problem.

> Regarding the "isExplicit": I think a closer match would be an output
> argument "PrecisionInfo" enum(ok, precision loss, [conversion failed
> ?]).

I'm not planning to add output arguments to fix this problem ;-)

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

Actually, I had been toying with the notion of doing the following:

1. A numeric literal is initially typed as the smallest type that will
hold it in the series int2, int4, int8, numeric (notice NOT float8).

2. Allow implicit up-coercion int2->int4->int8->numeric->float4->float8,
but down-coercions aren't implicit except for assignment.

3. Eliminate most or all of the cross-numeric-type operators (eg, there
is no reason to support int2+int4 as a separate operator).

With this approach, an expression like "int4var = 42" would be initially
typed as int4 and int2, but then the constant would be coerced to int4
because int4=int4 is the closest-match operator.  (int2=int2 would not
be considered because down-coercion isn't implicitly invokable.)  Also
we get more nearly SQL-standard behavior in expressions that combine
numeric with float4/float8: the preferred type will be float, which
accords with the spec's notions of exact numeric vs. approximate numeric.

I think this solves most or all of our problems with poor type choices
for numeric literals, but it still needs thought --- I'm not suggesting
we shoehorn it into 7.3.  In particular, I'm not sure whether the
current preferred-type arrangement (namely, numeric and float8 are both
preferred types for the numeric category) would need to change.

> There are also tons of "unsigned char vs signed char" warnings in
> current mb sources :-( 

Yeah, I know :-( ... I see 'em too when using HPUX' vendor compiler.
We ought to clean that up someday.

			regards, tom lane

Mon, 16 Sep 2002 10:01:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal for resolving casting issues 
Tom Lane wrote:
> "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> > 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)
> 
> The runtime checks are there already, eg
> 
> regression=# select 123456789::int4::int2;
> ERROR:  i4toi2: '123456789' causes int2 overflow
> 
> However this does not help us much; the critical point is that if we
> want function overloading to work in a sane fashion, we have to prefer
> up-conversions to down-conversions *at parse time*, at least for the
> operands of functions and operators (which is what I meant by "in
> expressions").  Runtime checks are irrelevant to this problem.

I think there is some confusion here.  The runtime checks Andreas was
talking about was allowing a double of 64.0 to cast to an int4 while
disallowing 64.1 from being cast to an int4 because it is not a hole
number.  

I am not sure doubles have enough precision to make such comparisons
functional (NUMERIC certainly does) but that was his proposal, and he
stated he thought the standard required it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 17 Sep 2002 02:30:05 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Proposal for resolving casting issues
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I think there is some confusion here.  The runtime checks Andreas was
> talking about was allowing a double of 64.0 to cast to an int4 while
> disallowing 64.1 from being cast to an int4 because it is not a hole
> number.  

> I am not sure doubles have enough precision to make such comparisons
> functional (NUMERIC certainly does) but that was his proposal, and he
> stated he thought the standard required it.

It seems clear to me that the standard requires us NOT to reject that.

In the explicit-cast case, SQL92 6.10 <cast specification> saith:

         3) If TD is exact numeric, then

            Case:

            a) If SD is exact numeric or approximate numeric, then

              Case:

              i) If there is a representation of SV in the data type TD
                 that does not lose any leading significant digits after
                 rounding or truncating if necessary, then TV is that rep-
                 resentation. The choice of whether to round or truncate is
                 implementation-defined.

             ii) Otherwise, an exception condition is raised: data exception-
                 numeric value out of range.

So we are *only* allowed to throw an error for overflow; having to round
is not an error condition.

In the implicit-cast case, section 9.2 Store assignment has

            k) If the data type of T is numeric and there is an approxi-
              mation obtained by rounding or truncation of the numerical
              value of V for the data type of T, then the value of T is set
              to such an approximation.

              If there is no such approximation, then an exception condi-
              tion is raised: data exception-numeric value out of range.

              If the data type of T is exact numeric, then it is implementation-
              defined whether the approximation is obtained by rounding or
              by truncation.

which is different wording but seems to boil down to the same thing: the
only error condition is out-of-range.

			regards, tom lane

Tue, 17 Sep 2002 02:36:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal for resolving casting issues 
Zeugswetter Andreas SB SD writes:

> PS: pg snapshot 09/11 does not compile on AIX (large files (don't want
> _LARGE_FILES),

Please provide details.

> and mb conversions (pg_ascii2mic and pg_mic2ascii not
> found in the postmaster and not included from elsewhere)

And details here as well.

-- 
Peter Eisentraut   peter_e@gmx.net


Wed, 18 Sep 2002 22:11:08 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
AIX compilation problems (was Re: Proposal ...)