I think you will find that the handling of random() is fairly tricky,
because I think it is a Derby-specific behavior in which the SQL
statement gets compiled into something which is able to call
java.lang.Math.random() at run time, via some special Derby-Java
I think that the object in question may be a "UserType", which is
a class that knows how to invoke user-defined Java code that
has been defined as an extension data type, which is what random() is.
I suspect it may be easier to get an understanding of the data
structures if you first have a close look at simpler situations,
as you have been doing so far.
In particular, I think you could probably work up to the problem like this:
1) select x from t order by x
2) select x, x*2 from t order by x*2
3) select random() from t order by random()
The goal in all 3 cases, I believe, is to enhance Derby so that it
can recognize that the OrderByColumn is referencing an expression
which is equivalent to a ResultColumn that is already present in
the SELECT, and so we don't need to duplicate the processing
for the OrderByColumn at runtime, but can rather arrange to have
the OrderByColumn directly reference the ResultColumn.
Once we have arranged for the OrderByColumn to directly reference
the ResultColumn, the original script (select random() order by random())
will be solved, because at runtime there won't be two random()s,
However, note that sometimes we can't have the OrderByColumn
directly reference the ResultColumn, because we will see legitimate
select name from employee order by id
In such a case, the two columns are distinct and have to be processed separately.