Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1336867196.179661265811868046.JavaMail.jira@brutus.apache.org>
Date: Wed, 10 Feb 2010 14:24:28 +0000 (UTC)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-4513) Forbid NEXT VALUE FOR clause in
 certain contexts
In-Reply-To: <348044516.187021263321354557.JavaMail.jira@brutus.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-4513?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-4513:
---------------------------------

    Attachment: derby-4513_01-ab-illegalContexts.diff

Thanks for looking at the patch, Knut. I don't know what's going on. I did notice merge errors when I resynced this patch client with the trunk. I've brought the patch up-to-date with the head of the trunk and am attaching a merged revision: derby-4513_01-ab-illegalContexts.diff

Here's what I see with this patch when I run your experiment:

ij version 10.6
ij> connect 'jdbc:derby:memory:dummy;create=true';
ij> create sequence s;
0 rows inserted/updated/deleted
ij> select * from sysibm.sysdummy1 where (next value for s) < 4;
ERROR 42XAH: A NEXT VALUE FOR expression may not appear in many contexts, including WHERE, ON, HAVING, ORDER BY, DISTINCT, CASE, GENERATION, and AGGREGATE clauses as well as WINDOW functions and CHECK constraints.

Let me know if this still doesn't fix the problem. Thanks.

> Forbid NEXT VALUE FOR clause in certain contexts
> ------------------------------------------------
>
>                 Key: DERBY-4513
>                 URL: https://issues.apache.org/jira/browse/DERBY-4513
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.6.0.0
>            Reporter: Rick Hillegas
>            Assignee: Rick Hillegas
>         Attachments: derby-4513_01-aa-illegalContexts.diff, derby-4513_01-ab-illegalContexts.diff
>
>
> This is part of the work needed to implement ANSI/ISO sequences. The functional spec attached to DERBY-712 lists various situations in which the NEXT VALUE FOR clause is illegal. These include:
>     * CASE expression
>     * WHERE clause
>     * ORDER BY clause
>     * AGGREGATE expression
>     * WINDOW function
>     * DISTINCT select list
> In addition, I propose that we make it illegal for a statement to have more than one NEXT VALUE FOR clause on the same sequence generator. This is a tighter restriction than the ANSI/ISO standard calls for. The standard requires that if two columns in a row are populated by NEXT VALUE FOR clauses on the same sequence, then the values should be the same. I don't feel confident that I could track down all of the cases which could give rise to this situation--so I propose to limit the number of NEXT VALUE FOR clauses on a given sequence generator to just 1.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.