db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4371) Non-selected columns for SELECT DISTINCT allowed in ORDER BY clause if ordered by expression
Date Thu, 29 Apr 2010 18:52:55 GMT

    [ 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:
>            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.

View raw message