Forbid NEXT VALUE FOR clause in certain contexts

Details

Description

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.

I'm fine with extra restrictions in the initial implementation, but I'm afraid I didn't understand exactly what limitation you're suggesting. Are you suggesting to limit the number of NEXT VALUE FOR clauses per row or per statement?

Knut Anders Hatlen
added a comment - 13/Jan/10 10:02 Hi Rick,
I'm fine with extra restrictions in the initial implementation, but I'm afraid I didn't understand exactly what limitation you're suggesting. Are you suggesting to limit the number of NEXT VALUE FOR clauses per row or per statement?

So both of these (presumably SQL compliant) statements will be rejected?

SELECT NEXT VALUE FOR S, NEXT VALUE FOR S FROM T – two clauses per row

VALUES (NEXT VALUE FOR S), (NEXT VALUE FOR S) – two clauses in the statement, but only one per row

The VALUES case is probably more useful than the SELECT case, especially as part of an INSERT statement. The restriction would basically prevent us from using a sequence generator in a multi-row insert. That's probably fine in the initial implementation, though, and anyone could scratch that itch later if they want to.

Knut Anders Hatlen
added a comment - 13/Jan/10 13:17 Thanks for clarifying, Rick.
So both of these (presumably SQL compliant) statements will be rejected?
SELECT NEXT VALUE FOR S, NEXT VALUE FOR S FROM T – two clauses per row
VALUES (NEXT VALUE FOR S), (NEXT VALUE FOR S) – two clauses in the statement, but only one per row
The VALUES case is probably more useful than the SELECT case, especially as part of an INSERT statement. The restriction would basically prevent us from using a sequence generator in a multi-row insert. That's probably fine in the initial implementation, though, and anyone could scratch that itch later if they want to.

Yes, that's an accurate summary of the restriction I'm proposing. Note that there is an ungainly workaround for the insert case you mention. Instead of a values clause, you can use a subquery which selects from sysibm.sysdummy1:

connect 'jdbc:derby:memory:dummy;create=true';

create table t( a int, b int );
create sequence seq1;

insert into t( a, b )
select s.c, s.c
from ( select next value for seq1 as c from sysibm.sysdummy1 ) as s;

Attaching derby-4513_01-aa-illegalContexts.diff. This patch prevents you from using a NEXT VALUE FOR clause in the following situations:

o WHERE/HAVING/ON clauses

o Aggregates and GROUP BY expressions

o DISTINCT and ORDER BY expressions

o CASE expressions

o CHECK constraints and generation clauses

The limitations imposed by this patch are more restrictive than what the SQL Standard allows:

o Only one NEXT VALUE FOR expression is allowed per sequence per statement.

o NEXT VALUE FOR is not allowed in any statement which has a DISTINCT or ORDER BY expression.

When these limitations are checked in, I will create another JIRA to track the fact that Derby is more restrictive than the Standard allows. We can consider relaxing limitations which users find onerous.

Rick Hillegas
added a comment - 07/Feb/10 02:08 Attaching derby-4513_01-aa-illegalContexts.diff. This patch prevents you from using a NEXT VALUE FOR clause in the following situations:
o WHERE/HAVING/ON clauses
o Aggregates and GROUP BY expressions
o DISTINCT and ORDER BY expressions
o CASE expressions
o CHECK constraints and generation clauses
The limitations imposed by this patch are more restrictive than what the SQL Standard allows:
o Only one NEXT VALUE FOR expression is allowed per sequence per statement.
o NEXT VALUE FOR is not allowed in any statement which has a DISTINCT or ORDER BY expression.
When these limitations are checked in, I will create another JIRA to track the fact that Derby is more restrictive than the Standard allows. We can consider relaxing limitations which users find onerous.
Touches the following files
-------------
M java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
M java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
The compiler context now tracks which sequences are mentioned in the statement.
-------------
M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
Statements which have DISTINCT and ORDER BY expressions are marked so that the bind() logic can reject NEXT VALUE FOR clauses.
-------------
M java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
M java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java
M java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java
M java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
M java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
M java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
Bind() logic to reject NEXT VALUE FOR in illegal situations.
-------------
M java/engine/org/apache/derby/loc/messages.xml
M java/shared/org/apache/derby/shared/common/reference/SQLState.java
New error messages.
-------------
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java
Regression tests.
-------------

Knut Anders Hatlen
added a comment - 10/Feb/10 13:37 I downloaded and applied the patch, but I still seem to be able to use NEXT VALUE FOR in WHERE clauses:
ij version 10.6
ij> connect 'jdbc:derby:seqdb;create=true';
ij> create sequence s;
0 rows inserted/updated/deleted
ij> select * from sysibm.sysdummy1 where (next value for s) < 4;
IBM&
Y
1 row selected
What am I missing?

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.

Rick Hillegas
added a comment - 10/Feb/10 14:22 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.