Rick Hillegas created DERBY-6494:
------------------------------------
Summary: NEXT VALUE FOR should be illegal inside a COALESCE
expression
Key: DERBY-6494
URL: https://issues.apache.org/jira/browse/DERBY-6494
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.11.0.0
Reporter: Rick Hillegas
Priority: Minor
It should be illegal to use a NEXT VALUE FOR expression as one of the values in
a COALESCE expression. That is because the result of a NEXT VALUE FOR clause is
non-deterministic. However, Derby allows this forbidden behavior, as the
following script shows:
{noformat}
connect 'jdbc:derby:memory:db;create=true';
create sequence seq_cpd_code start with 0;
-- a COALESCE expression can invoke a sequence generator...
values coalesce( 99, next value for seq_cpd_code );
-- ...but a CASE expression cannot...
values case when 99 is not null then 99 else next value for seq_cpd_code end;
{noformat}
Here is how I reason that NEXT VALUE FOR should be forbidden in COALESCE
expressions:
1) According to the 2011 SQL Standard, part 2, section 6.12 (case expression),
syntax rule 1d...
{noformat}
COALESCE( v1, v2 )
{noformat}
...is supposed to be equivalent to...
{noformat}
CASE v1 IS NOT NULL
THEN v1
ELSE v2
END
{noformat}
2) However, a NEXT VALUE FOR expression may not appear in a CASE expression,
according to part 2, section 6.14 (next value expression), syntax rule 2.
It appears that people are using NEXT VALUE FOR expressions inside COALESCE
expressions (see
http://apache-database.10148.n7.nabble.com/Unnecessary-increment-of-sequence-td136260.html).
For that reason, I am not inclined to fix this defect.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
