db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-6009) Need stricter checking of ORDER BY clause in VALUES expressions
Date Thu, 27 Dec 2012 06:16:12 GMT

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

Dag H. Wanvik edited comment on DERBY-6009 at 12/27/12 6:15 AM:
----------------------------------------------------------------

For "values 1,2 order by 1+2", the code the throws the exception was added as part of DERBY-2459
"Ordering on a CASE-expression casues a NullPointerException when using a UNION". That patch
may or may not have considered the union node's rôle in the VALUES clause, I'll investigate
a bit.

[Update:] This seems not to have been the case; the existing tests checks for the fact that
added pulled up order by columns (as seen in the presence of expressions), would get removed
when the set operator's RCL is constructed (cf Bryan's analysis in DERBY-2459), i.e. resultCol
would be null:

			if (resultCol == null)
				throw StandardException.newException(SQLState.LANG_UNION_ORDER_BY);

I think I'll avoid touching this mechanism for the VALUES checking. The text of the error
message is also a bit misleading: the fact that table value constructors are implemented using
UNION would be unknown to most users.
                
      was (Author: dagw):
    For "values 1,2 order by 1+2", the code the throws the exception was added as part of
DERBY-2459 "Ordering on a CASE-expression casues a NullPointerException when using a UNION".
That patch may or may not have considered the union node's rôle in the VALUES clause, I'll
investigate a bit.
                  
> Need stricter checking of ORDER BY clause in VALUES expressions
> ---------------------------------------------------------------
>
>                 Key: DERBY-6009
>                 URL: https://issues.apache.org/jira/browse/DERBY-6009
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.9.1.0
>            Reporter: Knut Anders Hatlen
>            Priority: Minor
>
> We only support column numbers in ORDER BY clauses in VALUES expression, as seen by this
error message:
> ij> values 1,2 order by 1+2;
> ERROR 42878: The ORDER BY clause of a SELECT UNION statement only supports unqualified
column references and column position numbers. Other expressions are not currently supported.
(errorCode = 30000)
> However, the checks let some unsupported expressions through and produce strange results.
For example:
> ij> values 1 order by 1+2;
> 1          |2          
> -----------------------
> 1          |3          
> 1 row selected
> It should probably have raised the same exception as the first query. And if not, the
result should only have had one column.
> And the next example should probably have raised a syntax error too, instead of a NullPointerException:
> ij> values 1 order by int(1);
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'. (errorCode = 0)

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message