[
https://issues.apache.org/jira/browse/DERBY-4371?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12862345#action_12862345
]
Knut Anders Hatlen commented on DERBY-4371:
-------------------------------------------
Hi Nirmal,
If I understand correctly, the patch only addresses the case with a binary
operator with one numeric constant operand and one result column operand.
Unfortunately, that's just one out of an infinite number of possible shapes the
ORDER BY clause could take, and we cannot address each one of those cases
individually, so I think we should aim for a more general approach.
Perhaps this would work:
When DISTINCT is specified, do this for each OrderByColumn:
1) Check if there's a column in the target's result column list with an
equivalent expression (can be checked with the isEquivalent() method in
ValueNode). If there is one, this OrderByColumn is OK.
2) Otherwise, collect all columns referenced by the expression in the
OrderByColumn (CollectNodesVisitor could probably do this for you), and check
if all the columns are also in the target's result column list. If they all
are, the OrderByColumn is OK. If not, throw an exception.
> Non-selected columns for SELECT DISTINCT allowed in ORDER BY clause if
> ordered by expression
> --------------------------------------------------------------------------------------------
>
> Key: DERBY-4371
> URL: https://issues.apache.org/jira/browse/DERBY-4371
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.1
> Reporter: Bernt M. Johnsen
> Assignee: C.S. Nirmal J. Fernando
> Priority: Critical
> Attachments: DERBY-4371-2.diff, DERBY-4371-3.diff, DERBY-4371.diff
>
>
> How to repeat:
> ij> create table t (i integer, j integer);;
> 0 rows inserted/updated/deleted
> ij> insert into t values (1,2),(1,3);
> 2 rows inserted/updated/deleted
> ij> select distinct i from t order by j;
> ERROR 42879: The ORDER BY clause may not contain column 'J', since the query
> specifies DISTINCT and that column does not appear in the query result.
> ij> select distinct i from t order by j*2;
> I
> -----------
> 1
> 1
> 2 rows selected
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
