On 23-8-2010 14:29, Steffen Heil wrote:

> Hi
>
> I *wanted* to write the following code:
> select a, b, c, d from some_table
>
> And used in java:
> a = resultSet.getString( "a" );
> b = resultSet.getInt( "b" );
> c = resultSet.getString ( "c" );
> d = resultSet.getString ( "d" );
>
> This worked fine, as I did not check (or programmatically use) the value of
> c at all.
> When I started using c I discovered that c was usually "0", which was never
> written to the database at all.
>
> Trying to build a testcase, I discovered that my actual query was:
> select a, b c, d from some_table
> Now, it seems to have handled "c" as some alias for b, even though there was
> no "as".
>
> I verified this behavior using direct sql:
> select c from ( select a, b c, d from some_table )
> Which indeed yielded the value of column 'b'.
>
> Trying the following was rejected by the firebird engine:
> select b from ( select a, b c, d from some_table )
> The server replied with "unknown column 'b'".
>
> Now my questions:
> 1. Is "as" optional? Is the code above even sql conformant?

Yes it is, SQL 92 7.9 (query specification) says:

Specify a table derived from the result of a <table expression>.

Format

<query specification> ::=
SELECT [ <set quantifier> ] <select list> <table expression>

<select list> ::=
<asterisk>
| <select sublist> [ { <comma> <select sublist> }... ]

<select sublist> ::=
<derived column>
| <qualifier> <period> <asterisk>

<derived column> ::= <value expression> [ <as clause> ]

<as clause> ::= [ AS ] <column name>

> 2. Is jaybird correct to allow selecting b in the resultset?

I am not sure, if I look at the jdbc API doc for getXxx methods, I would
say it is not:
"columnLabel - the label for the column specified with the SQL AS
clause. If the SQL AS clause was not specified, then the label is the
name of the column"

As we do have an AS clause, the column should be selected based on its
AS clause name, and not on its (original) column name.

> 3. Is firebird correct to deny selecting b in the resultset?

Yes, because after applying the AS clause the name for that column has
changed.

>
> Note, that I think that 2. and 3. are mutually exclusive. So there might be
> a bug in either firebird or jaybird...

I'd say a bug in Jaybird.

Mark

--
Mark Rotteveel