Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Thu, 27 Dec 2012 08:30:12 +0000 (UTC)
From: "Knut Anders Hatlen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12622888.1354875323102.54198.1356597012808@arcas>
In-Reply-To: <JIRA.12622888.1354875323102@arcas>
References: <JIRA.12622888.1354875323102@arcas>
Subject: [jira] [Commented] (DERBY-6009) Need stricter checking of ORDER BY
 clause in VALUES expressions
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Knut Anders Hatlen commented on DERBY-6009:
-------------------------------------------

Thanks for investigating, Dag.

> For the record, ordering by column numbers is non-standard, it was last
> allowed in SQL-92. Since newer versions of the standard allow expressions,
> there is a semantic ambiguity when a single integer is used: Derby considers
> it to be a column number *only* if it is a a single integer literal,
> otherwise the expression is treated as a post-92 value expression.

So in the case of ORDER BY 1+2, the sort key is the expression 1+2. That is,
the value 3. Not column #1 + column #2, and not column #3. And since the sort
key evaluates to the exact same value for every row, any ordering is OK.

I think this means the SELECT statements listed above are OK.

As to the VALUES statements, we have:

1) VALUES 1,2 ORDER BY 1+2: Currently throws a syntax error. It could continue
to do that, as it's not particularly useful to order results by a constant, or
we could allow it and return the values 1 and 2 in no particular order.

2) VALUES 1 ORDER BY 1+2: Currently returns one row with two columns. If we
allow (1), this query should return a single row with a single
column. Otherwise, it should raise the same error as (1).

3) VALUES 1 ORDER BY INT(1): Currently throws NullPointerException. If (1) and
(2) raise syntax errors, so should this query. Otherwise, it should return a
single row with a single column.
                
> 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
>         Attachments: derby-6009.diff, derby-6009.stat
>
>
> 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