At 11:58 AM 31/05/2004 +0000, you wrote:

>--- In Firebird-Java@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
> > Yes it does - but it still returns a singleton result, containing
>NULL in
> > rsname.
> >
> > This guy is really hard to convince that, if you ask a question in
>SQL that
> > returns a result, you'll get a result. Here and in support, he
>insists that
> > there should be NO result if the answer is null.
>
>Not really what I'm saying - and remember I started this thread by
>describing what goes on in the Java/JDBC realm, not purely SQL - the
>key here is not that 'the answer is null' but that we are working with
>zero rows, so if we are getting a Java ResultSet back, should this not
>also have zero rows?

From a select on columns in the table, yes. From a select that returns
the RESULT of an expression, no. Select max(something) from ATable must
return a result. The max() of an empty table is unknown. In SQL "unknown"
is represented by NULL. So the result returned is a singleton with NULL in
the result.

>Do appreciate your comments, I'm just trying to deduce what is the
>'correct' behaviour. :-)
>If an SQL query should produce zero rows, surely the ResultSet should
>also contain zero rows (which means rs.next() should return false when
>called the first time).

You're still not grokking the difference between selecting a set of rows
and selecting the result of an expression.

>1) Query "select PK_SN from MYTABLE order by desc" produces zero rows
>and it appears the ResultSet is empty because rs.next() returns false
>(correct).

Correct. This query says "return the value of PK_SN from every row in the
table in some sort of order" (you have an error in this syntax). There are
no rows, so no rows are returned.

>2) The result of "select max(PK_SN) from MYTABLE" is messy without any
>rows - yes if running such a query 'interactively' the result should
>be null.

What does "interactively" mean? All queries are submitted by clients -
even those in stored procedures, indirectly. There really is no such thing
as "an interactive query". All DSQL queries pass through the API, one way
or the other.

>But I'm not comfortable (when done via JDBC/Java) with the
>ResultSet 'having a row' which has this null in it - if an empty
>ResultSet is not returned then maybe it'd be better if an Exception
>was thrown?

Really? Break the valid, expected behaviour to make it fit with what you
thought you had learned from using SQL Server?

[..]

SQL is a standard and JDBC is a standard. Consistency will lie in the
accord between the two; not in trying to force Microsoft's idea of SQL and
Java onto standard implementations. Mainline for the standard and branch
for the Microsoft stuff.

Helen