Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Kevin Grittner <kgrittn@ymail.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