Re: Failure to coerce unknown type to specific type

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Kevin Grittner <kgrittn(at)ymail(dot)com> writes:

>> If we
>> *are* going to allow it, it would be pretty confusing to have it
>> behave differently that what I previously outlined (regarding the
>> equivalent long form CASE clause).
>
> AFAICT, we do treat them the same; can you provide an example where
> we don't?

No, I was just arguing that if we change the CASE clause to return
untyped NULL, then the COALESCE clause should follow suit.

> It would be interesting to try variants of the
>
> select u+i from (select '1' as u, '2'::int as i) s where u<'foo'::text;
>
> example to see what they do if the column has to be converted to two
> mutually inconsistent types, assuming you can find candidate types
> in each system.

I finagled a syntax that was accepted by all servers on SQL Fiddle
and got a row with a 3 without the WHERE clause from all products
except PostgreSQL:

-- Build Schema
create table onerow (n int not null);
insert into onerow values (1);
-- Run SQL
select u+i from (select cast('1' as char) as u, 2 as i from onerow) s;

Then I added that WHERE clause.

select u+i from (select cast('1' as char) as u, 2 as i from onerow) s
where u<cast('foo' as char);

Much to my amazement, all of them *still* return a row with the
value 3, without error. I'm still picking my jaw up from the floor.

I'm OK with being in the minority on that!

> Another idea would be to try things like
>
> select u+i from (select 'bar' as u, '2'::int as i) s where u<'foo'::text;
>
> and see exactly what error gets thrown.

I changed '1' to 'bar' in the above code.

MySQL and SQL Lite return a row with a 2.

Oracle throws an error: ORA-01722: invalid number

MS SQL Server throws an error: Conversion failed when converting the varchar value 'bar ' to data type int.

Yes, my literal was three characters and the error message added a space.

>> To restate it, this hardly seems like the most important issue to
>> address; I just don't think the standard gives us much cover here.
>
> I stand by my opinion that the cases that are controversial here
> are all illegal per spec.

With that last bit you pointed out, I now agree.

> We may well want to allow them on usability
> grounds, but what the spec does *not* provide any cover for is claiming
> that the spec requires some particular non-error interpretation.

ok

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company