Re: Re: BUG #5490: INSERT doesn't force cast from text to timestamp

On 5/06/2010 9:39 PM, Farid Zidan wrote:
> Craig
>
> I am not asking you to re-write my sql so the bug will not show. I am
> presenting you with sql that fails and shows the bug.

Before writing this off completely, please read on. I suspect I may've
misunderstood your argument, and on further examination think there's
something that bears design consideration here. It's not a bug, as it's
working exactly as designed, but it _is_ something where the design
might benefit from a tweak.

In the mean time, you have a system that doesn't work how you want it do
- design choice or bug, workaround or bug-hiding, the effect is the
same. You must already be handing differing names of timestamp/datetime
types in your DDL, so why not use the same mapping in your DML?

As for the root of the issue: Here's why implicitly casting those
literals to timestamps would be a bad idea for solid, technical reasons:

- The semantics of a nested/sub query should not be affected by the
calling context, ie the surrounding query. To have a function affected
by what the caller is doing would be bizarre and confusing; so it is
with a subquery. You cannot test functional units or rely on any kind of
consistent behaviour if calling context changes callee behaviour.

- A SELECT as a value-supplier to an INSERT is a type of subquery

- If your SELECT ran differently in the context of the INSERT to how it
ran standalone, that would not only be incredibly confusing but also
clearly a bug.

... so we clearly can't use type information from the surrounding INSERT
to determine the data type of the literals used in the SELECT. With no
type information to the contrary they must be interpreted as text. So,
after SELECT evaluation your query goes from:

insert into test_insert
(col1, col2)
select distinct
'b',
'2010-04-30 00:00:00';

to effectively:

insert into test_insert
(col1, col2)
values ('b'::text, '2010-04-30 00:00:00'::text);

Now, when executed with Pg both those queries result in:

ERROR: column "col2" is of type timestamp without time zone but
expression is of type text

showing that your issue isn't actually with DISTINCT at all, but with
Pg's unwillingness to *implicitly* cast a value of explict text type to
another type.

This is the real core of your complaint. You want Pg to cast from 'text'
to 'timestamp' on INSERT. Pg doesn't. In the general case (not specific
to INSERT) there are some pretty good reasons for that, but for
INSERTs... should it? In the context of an INSERT, where the desired
data type is explicitly specified and obvious, I'm not sure, but I
suspect that it should.

Anyone familiar with the details of the implicit-text-cast bugs want to
pipe up with whether it'd be safe to force a cast in the context of an
INSERT?

( As for why I adjusted your example: Your example query was
unrealistically simplistic and clearly couldn't be what you were using
in your app. Using 'DISTINCT' with one value is pointless. So, my
example added some dummy values to illustrate why it might be unsafe to
use it how you're trying to. As it turns out, in your app's case you can
guarantee input formatting consistency, so it's safe for you so long as
you stick very closely to timestamp formatting specifics, but I'm sure
you can see that a database's behaviour depending on the formatting of
timestamps is probably not something that most people with most apps
would be happy with. Even if INSERT did convert the results of the
SELECT DISTINCT subquery to timestamps, it'd still be pretty unsafe.)

--
Craig Ringer