Adriano dos Santos Fernandes wrote:

> Alex Peshkov wrote:
>
>>I think that before talking about implementation details it's good to
>>decide what we want from that implementation.
>>
>>An example:
>>
>>create table t(f1 int, f2 int, f3 int);
>>commit;
>>insert into t values(1, 2, 2);
>>insert into t values(1, 3, 3);
>>REPLACE INTO t(f1, f2) VALUES(1, 4) MATCHING(f1) RETURNING(f1, f2, f3);
>>
>>What will be returned in singleton case - 1,4,2 or 1,4,3 ?
>>
>
> Statement failed, SQLCODE = -811
> multiple rows in singleton select
>

This old trick when singleton is checked at runtime is very bad (it
often fails in procedures, that seemed to work for a long time, but
start to fail after specific data modification). Taking into account,
that REPLACE is supposed to be often used in database update/upgrade
scripts, I very much dislike run-time checks there, because fixing such
bugs in such scripts is specially problematic - REPLACE may pass on test
data in test environment, but fail on real user's data, in the worst
case leaving production database in unstable state.

I suggest to do as much compile-time checks as possible, and not leave
them for run-time. Specially in cases, when pass/fail may depend upon
data in tables. When we will be ready RETURNING multiple rows - OK, no
problems. Now your suggestion adds one more dangerous place to the engine.