[jira] [Comment Edited] (DERBY-6009) Need stricter checking of ORDER BY clause in VALUES expressions

["Dag H. Wanvik (JIRA)" <ji...@apache.org>]

    [ 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