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 Mon, 03 May 2010 15:40:56 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4371?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12863382#action_12863382

Knut Anders Hatlen commented on DERBY-4371:

Hi Nirmal,

After a quick look at the patch, I have these comments:

I don't think the CollectNodesVisitor should be used on the expressions in the select list,
as that would allow too many queries to be executed. For example, the #4 patch will allow
this statement:

    select distinct i*j from t1 order by i/j

This statement should be rejected because none of the columns I or J, or the expression I/J
are contained directly in the select list (both I and J are in an expression in the select
list, but not on the top level, so there's no functional dependency between the columns in
the select list and the expression in the order by clause).

I'm not sure if it's enough to check the name of the columns to see if it's the same column,
as they may refer to columns in different tables. Perhaps ColumnReference.isEquivalent() would
work better?

I don't think column names should be compared with String.equalsIgnoreCase(), as column names
are case sensitive. For example, I think this select statement is supposed to fail because
"I" is not the same column as "i":

ij> create table t2("I" int , "i" int);
0 rows inserted/updated/deleted
ij> select distinct "I"+1 from t2 order by "i"+1;

0 rows selected

> 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-4.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