Details
-
Bug
-
Status: Open
-
Minor
-
Resolution: Unresolved
-
10.11.1.1
-
None
-
None
-
Low
-
Repro attached
-
Deviation from standard, Seen in production
Description
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:
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;
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...
COALESCE( v1, v2 )
...is supposed to be equivalent to...
CASE v1 IS NOT NULL THEN v1 ELSE v2 END
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.
Attachments
Issue Links
- relates to
-
DERBY-712 Support for sequences
- Closed