|
Not able to attach the patch to JIRA... Seems to be timing out for
me... so I am attaching here. Would like to submit this to both 10.1
and trunk. Satheesh Satheesh Bandaram (JIRA) wrote: [ http://issues.apache.org/jira/browse/DERBY-882?page=comments#action_12364191 ]Satheesh Bandaram commented on DERBY-882: ----------------------------------------- I have a patch for this problem. Running tests currently. Will post the patch tomorrow. I would like to submit this patch to both 10.1 branch and trunk. |
Index: java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
(revision 370230)
+++ java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
(working copy)
@@ -35,6 +35,7 @@
import org.apache.derby.iapi.sql.dictionary.ConstraintDescriptor;
import org.apache.derby.iapi.types.TypeId;
+import org.apache.derby.iapi.types.DataTypeDescriptor;
import org.apache.derby.iapi.reference.SQLState;
@@ -93,7 +94,7 @@
{
ColumnDescriptor cd;
TypeDescriptor oldType;
- TypeDescriptor newType = dataTypeServices;
+ DataTypeDescriptor newType = dataTypeServices;
TypeId oldTypeId;
TypeId newTypeId;
@@ -110,6 +111,7 @@
oldType = cd.getType();
oldTypeId = cd.getType().getTypeId();
newTypeId = dataTypeServices.getTypeId();
+ newType.setNullability(oldType.isNullable());
// can't change types yet.
if (!(oldTypeId.equals(newTypeId)))
Index:
java/testing/org/apache/derbyTesting/functionTests/tests/lang/modifyColumn.sql
===================================================================
---
java/testing/org/apache/derbyTesting/functionTests/tests/lang/modifyColumn.sql
(revision 370230)
+++
java/testing/org/apache/derbyTesting/functionTests/tests/lang/modifyColumn.sql
(working copy)
@@ -91,3 +91,31 @@
-- clean up
drop table t1;
+
+-- DERBY-882
+-- ALTER TABLE to increase size of varchar could convert a non-null column to
nullable
+-- before fix for DERBY-882
+
+create table a (id integer not null, name varchar(20) not null, primary
key(name));
+insert into a values (1, 'abc');
+-- Should fail
+insert into a values (2, null);
+alter table a alter name set data type varchar(50);
+insert into a values (3, 'hijk');
+-- Used to pass before the fix
+insert into a values (4, null);
+select * from a;
+
+drop table a;
+
+-- Now test the otherway, nullable column to start with
+create table a (id integer not null, name varchar(20));
+insert into a values (1, 'abc');
+insert into a values (2, null);
+alter table a alter name set data type varchar(50);
+insert into a values (3, 'hijk');
+insert into a values (4, null);
+select * from a;
+
+drop table a;
+
Index:
java/testing/org/apache/derbyTesting/functionTests/master/modifyColumn.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/modifyColumn.out
(revision 370230)
+++ java/testing/org/apache/derbyTesting/functionTests/master/modifyColumn.out
(working copy)
@@ -126,32 +126,78 @@
ERROR 23505: The statement was aborted because it would have caused a
duplicate key value in a unique or primary key constraint or unique index
identified by 'UQ' defined on 'T1'.
ij> -- do some selects to ensure consistency of data.
select * from t1 where vc='pe';
-VC |NVC |BV
---------------
-pe |p |01
-pe |pe |01
-pe |pe |1000
+VC|N&|BV
+----------
+pe|p |01
+pe|pe|01
+pe|pe|1000
ij> select * from t1 where vc='pe';
-VC |NVC |BV
---------------
-pe |p |01
-pe |pe |01
-pe |pe |1000
+VC|N&|BV
+----------
+pe|p |01
+pe|pe|01
+pe|pe|1000
ij> alter table t1 alter vc set data type varchar(3);
0 rows inserted/updated/deleted
ij> select * from t1 where vc='pe';
-VC |NVC |BV
---------------
-pe |p |01
-pe |pe |01
-pe |pe |1000
+VC |N&|BV
+-----------
+pe |p |01
+pe |pe|01
+pe |pe|1000
ij> select * from t1 where vc='pe';
-VC |NVC |BV
---------------
-pe |p |01
-pe |pe |01
-pe |pe |1000
+VC |N&|BV
+-----------
+pe |p |01
+pe |pe|01
+pe |pe|1000
ij> -- clean up
drop table t1;
0 rows inserted/updated/deleted
+ij> -- DERBY-882
+-- ALTER TABLE to increase size of varchar could convert a non-null column to
nullable
+-- before fix for DERBY-882
+create table a (id integer not null, name varchar(20) not null, primary
key(name));
+0 rows inserted/updated/deleted
+ij> insert into a values (1, 'abc');
+1 row inserted/updated/deleted
+ij> -- Should fail
+insert into a values (2, null);
+ERROR 23502: Column 'NAME' cannot accept a NULL value.
+ij> alter table a alter name set data type varchar(50);
+0 rows inserted/updated/deleted
+ij> insert into a values (3, 'hijk');
+1 row inserted/updated/deleted
+ij> -- Used to pass before the fix
+insert into a values (4, null);
+ERROR 23502: Column 'NAME' cannot accept a NULL value.
+ij> select * from a;
+ID |NAME
+--------------------------------------------------------------
+1 |abc
+3 |hijk
+ij> drop table a;
+0 rows inserted/updated/deleted
+ij> -- Now test the otherway, nullable column to start with
+create table a (id integer not null, name varchar(20));
+0 rows inserted/updated/deleted
+ij> insert into a values (1, 'abc');
+1 row inserted/updated/deleted
+ij> insert into a values (2, null);
+1 row inserted/updated/deleted
+ij> alter table a alter name set data type varchar(50);
+0 rows inserted/updated/deleted
+ij> insert into a values (3, 'hijk');
+1 row inserted/updated/deleted
+ij> insert into a values (4, null);
+1 row inserted/updated/deleted
+ij> select * from a;
+ID |NAME
+--------------------------------------------------------------
+1 |abc
+2 |NULL
+3 |hijk
+4 |NULL
+ij> drop table a;
+0 rows inserted/updated/deleted
ij>
