RE: Re[2]: [Firebird-devel] My TODO

[Dmitry Y. <di...@us...>]

Nickolay et al,

> > SELECT INTO doesn't touch the variables if no record is processed.
> > FOR SELECT INTO doesn't touch the variables if the loop
> > doesn't happen
> > because no records match the conditions.
> > I vote against this change.
>
> Firebird behaviour is not consistent and it goes against SQL standard.
> SQL committee had hard to decide what to do - set values to nulls or
> throw exception when no record is found in SELECT INTO statement. They
> decided to go the latter way because it helps to detect bugs earlier.
> They didn't even consider Firebird-like behaviour as it tends to hide
> bugs in SQL code and make them extremely difficult to trace out.
>
> I have nothing against SQL standard-compliant way as it also allows to
> detect problems. But it forces to write a little bit more code when
> you expect query to return 0..1 rows.

The current behaviour is quite logical/understandable, but it doesn't allow
to check reliably whether the query returned anything or not. Your
suggestion is more intuitive from user's POV, but it has exactly the same
problem. Both of them are IMO consistent, but both aren't 100% reliable and
go against the SQL specification. We can throw an error (as suggested by the
SQL specification), but it requires us to write more code (nested BEGIN..END
blocks) to handle this case, so practically it's not a good solution either.
Checking ROW_COUNT is another possible solution, but it requires to write an
additional IF-block (although it's much better than writing exception
handlers here and there).

My conclusion is that we should use ROW_COUNT when it's necessary, as it
provides a reliable solution with minimum programming/execution overhead and
preserves the engine's behaviour. Probably, we might consider having a
config switch that's intended to force assigning NULLs for "no data"
conditions (to avoid using ROW_COUNT in simplest cases), but it has been
mentioned that the engine will become inconsistent with the SQLDA stuff in
this case. I don't think we're going to change the API behaviour too. But
even if we are (I really doubt that), then a default behaviour must be kept
unchanged.

And finally, we have another possible solution. We can implement a SQL-level
option to handle this, e.g.:

SELECT ... INTO <var_list> [DEFAULT NULL];

or even

SELECT ... INTO <var_list> [DEFAULT <null_or_value>];


Dmitry