[DERBY-4442] Evaluation of default value and identity in an INSERT result set evaluated too early.

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.5.3.0
• Fix Version/s: 10.5.3.1, 10.6.1.0
• Component/s: SQL
• Bug behavior facts: Deviation from standard
• Assignee: Knut Anders Hatlen
• Reporter: Dag H. Wanvik
• Created: 13/Nov/09 17:17
• Updated: 13/Jan/11 17:32
• Resolved: 06/Jul/10 18:57

Description

In contrast to generated column, which are evaluated when the next row from the result set to be inserted, currently default values and identity columns are generated "early", that is as part of avaluating the subquery (SELECT or VALUES as the case may be).
This does not currently cause a user visible bug in Derby, but it lies behind DERBY-3 and the effect Bryan observed in DERBY-4.
Additionally, "early" computation has given rise to much special handling and ensuing bugs, cf. DERBY-1644, DERBY-4413, DERBY-4419, DERBY-4425 and others.

DERBY-4397 requires this fix for correct behaviour with INSERT.

See also
https://issues.apache.org/jira/browse/DERBY-4413?focusedCommentId=12769532&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_12769532

Issue Links

• is duplicated by: DERBY-4433 Cannot insert from EXCEPT/INTERSECT when target table has more columns than the source
• is duplicated by: DERBY-3 Identity column not filled consecutively with "insert ... select distinct"
• is related to: DERBY-4413 INSERT from SELECT DISTINCT gives assertFailure (sane), or NPE (insane) in presence of generated columns
• is related to: DERBY-4419 NullPointerException with INSERT INTO ... from UNION and identity columns
• is related to: DERBY-4425 NullPointerException with INSERT INTO ... from UNION and columns generated from expressions
• is related to: DERBY-4433 Cannot insert from EXCEPT/INTERSECT when target table has more columns than the source
• is related to: DERBY-4457 'Column value mismatch' in 'testDistinctInsertWithGeneratedColumn(...lang.DistinctTest)' on Jvm 1.5, 1.4, phoneME.
• is related to: DERBY-4460 test_031_derby_4413 failed in nightly regression run : ComparisonFailure: expected: 1 but was: 2
• is related to: DERBY-1644 NPE when inserting values to a table that has a column declared as generated by default as identity
• is related to: DERBY-4426 With generated columns, INSERT with DEFAULT inside a VALUES clause inside a UNION fails.
• is related to: DERBY-3 Identity column not filled consecutively with "insert ... select distinct"
• is related to: DERBY-4 "order by" is not supported for "insert ... select"
• is required by: DERBY-4728 10.5 backport effort tracking issue