Re: Possible bug on insert

On Tue, 7 Oct 2003, Rick Gigger wrote:

> I am also still confused as to what the rules are that I am breaking.  For
> instance:
>
> The following will all execute successfully:
>
> CREATE TABLE bugtest ( a int2 );
> select '1' union select '2';
> insert into bugtest values('1');
> insert into bugtest values('2');

Technically speaking, I believe the above inserts would be errors in plain
SQL92 (I don't believe character(1) is assignable to an exact numeric
type). In part because of the flexible type system, we try to treat such
things as the type being assigned to when possible.

> insert into bugtest (a) select '1' union select '2';
>
> Where exactly is the illegal type conversion taking place in the last
> statement that is different from the above statements.

The union requires us to figure out what type it is before we get to
actually doing the insert. It might be possible in union all to avoid
that, but for union, you need to know what rules to use to determine
duplicates, so I really don't think there's any choice there (imagine that
the values were say '1.1' and '1.2'... Are those two distinct values or
not?)