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
