db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeremy Boynes <jboy...@apache.org>
Subject Re: About improvement of DERBY-134
Date Sun, 13 Mar 2005 21:07:31 GMT
Satheesh Bandaram wrote:
> Derby actually generates two different names... 1) Unique name for
> unnamed references 2) Final exposed names for top level unnamed result
> columns. The unique names are of the form 'SQLCol<Number>' and final
> exposed names, that IJ shows, are of the form '<Number>'. The number
> part of the names could be different. (Eg: 'SQLCol5' may map to '1' as
> exposed name)
> 
> The SQLCol unique names can't be guessed correctly by end users. It may
> change depending on how the query is compiled and can change totally
> unexpectedly.(Like adding an extra union at the end of the query could
> change top level result column names) So these are definitely not
> suitable to be used by end users. It may be possible to use final
> exposed names (like '1', '2') in order by clause, but Derby currently
> doesn't allow it. It is not consistant to allow this only under some new
> code path, as a side effect. We have to consistantly change it all over.
> I don't know what the SQL standard says about this... I don't want to
> look at that huge spec on a Sunday. :-)
> 
> I am not sure we should, even if we could... Like you said, these
> numbers are implementation dependent, change from one vendor to another
> and may even change from one release to another.  Shouldn't querries
> instead use explicit aliasing to avoid all confusion, which is standards
> based and portable?
> 

Yes they should. Unfortunately that is not always possible and I think 
the case where you are using VALUES to construct a table is one of 
those. In other words, Tomohito's test is reliant on implementation 
dependent behaviour.

The spec does say that alias names are generated and so it should be 
possible to reference them even if doing so is non-portable. Personally 
I would prefer the <SQLCol1> form because it is at least a legal 
identifier; using <1> would mean having to quote the reference which 
seems inconvenient and confusing e.g. ORDER BY "1"

Whatever the outcome I do think we need to document how the names are 
generated so a user can figure it out.

--
Jeremy

Mime
View raw message