You should get an error if you try to override a generated column via an INSERT driven by an inner ResultSet

• Key: DERBY-3950
• Type: Bug
• Status: Resolved
• Resolution: Fixed
• Priority: Major
• Assignee: Rick Hillegas
• Reporter: Rick Hillegas
• Component/s: SQL
• Affects Version/s: 10.5.1.1
• Fix Version/s: 10.5.1.1

Description

On DERBY-481 Dag points out the following bug:

drop table t1;
drop table t2;

create table t1( a int, b int generated always as ( -a ) );
create table t2( a int, b int );

insert into t2( a, b ) values ( 1, 100 );

-- should fail to compile. instead, it compiles but at run time the generation clause
-- overrides the value coming from the select
insert into t1 select * from t2;

-- should fail to compile. instead, it compiles but at run time the generation clause
-- overrides the value coming from the select
insert into t1 select a, 0 from t2;

-- should also fail to compile. instead, it compiles but at run time the generation clause
-- overrides the value coming from the list of literals
insert into t1 values( 2, 200 );

select * from t1;

Comments

[Rick Hillegas]

Attaching derby-3950-01-aa-forbidOverrides.diff. This prevents driving SELECTs and VALUEs lists from attempting to override generation clauses. Offending statements now fail at bind() time. Running tests now.

Generated clauses now behave like identity columns when you try to override their contents.

Touches the following files:

M java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java

Adds a new piece of state to this node so that it can track whether a DEFAULT literal was inserted into a generated column. This differs from the processing of identities. The different processing arises because generation clauses are bound later than other expressions.


M java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java

Adds a paragraph to a method which checks to see whether the user is trying to override an identity column. Renames that method because it now performs a similar check for generated columns.


M java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
M java/engine/org/apache/derby/impl/sql/compile/InsertNode.java

Calls the above method by its new name.


M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java

Tests.

[Rick Hillegas]

Tests ran cleanly for me except for the heisenbug in org.apache.derbyTesting.functionTests.tests.multi.StressMultiTest. Committed derby-3950-01-aa-forbidOverrides.diff at subversion revision 718381.