[
https://issues.apache.org/jira/browse/DERBY-4406?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12851471#action_12851471
]
C.S. Nirmal J. Fernando commented on DERBY-4406:
------------------------------------------------
Hi,
After went through the source code and after debugging, I found some
interesting points.
==================================================
ij> select random() from t order by random();
ascending; true
addedColumnOffset: -1
columnPosition: -1
expression instanceof ColumnReference = false //random() is not a column
expression.isConstantExpression() = false //random() is not a constant
expression eg: x*2
ascending; true
addedColumnOffset: 0
columnPosition: 2
obc.getResultColumn().getExpression() instanceof ColumnReference = false
//Ordering on an expression
1
----------------------
0.7460335991462618
0.22942514814484716
0.04022635294210675
0.39009678758326427
0.9438511615474983
0.803614720479578
0.7563735003656935
7 rows selected
===========================================
In OrderByColumn class at bindOrderByColumn(ResultSetNode target, OrderByList
oblist) method, Derby doesn't check for an expression (eg: random() ) in an
order by clause. Therefore it pulled up a column.
I have few questions:
1) How derby resolves random() function?
2) Does derby consider random() as a expression or else other (it can't be a
column reference)?
> Wrong order when using ORDER BY on non-deterministic function
> -------------------------------------------------------------
>
> Key: DERBY-4406
> URL: https://issues.apache.org/jira/browse/DERBY-4406
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.3.0, 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: C.S. Nirmal J. Fernando
>
> If I read the SQL standard correctly, a statement such as "select random() as
> r from t order by random()" should be treated as "select random() as r from t
> order by r". Derby does however generate a second, hidden random() column by
> which the rows are ordered.
> ij> create table t(x int);
> 0 rows inserted/updated/deleted
> ij> insert into t values 1,2,3,4,5;
> 5 rows inserted/updated/deleted
> ij> -- wrong result, not ordered by r
> ij> select random() as r from t order by random();
> R
> ----------------------
> 0.1285512465366495
> 0.5116860880915798
> 0.21060042130229073
> 0.2506706923680875
> 0.6378857329935494
> 5 rows selected
> ij> -- correct result, ordered by r
> ij> select random() as r from t order by r;
> R
> ----------------------
> 0.0749025910679918
> 0.07694931688380491
> 0.1724114605785414
> 0.2268758969382877
> 0.31900450349277965
> 5 rows selected
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
