You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ba...@apache.org on 2006/01/28 03:15:51 UTC
svn commit: r373066 - in /db/derby/code/branches/10.1/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: bandaram
Date: Fri Jan 27 18:15:41 2006
New Revision: 373066
URL: http://svn.apache.org/viewcvs?rev=373066&view=rev
Log:
DERBY-882: Set Nullability of new typeDescriptor from previous typeDescriptor following changing of varchar column length using ALTER TABLE.
Submitted by Satheesh Bandaram (satheesh@sourcery.org)
Modified:
db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/modifyColumn.out
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/modifyColumn.sql
Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java?rev=373066&r1=373065&r2=373066&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java (original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java Fri Jan 27 18:15:41 2006
@@ -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)))
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/modifyColumn.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/modifyColumn.out?rev=373066&r1=373065&r2=373066&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/modifyColumn.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/modifyColumn.out Fri Jan 27 18:15:41 2006
@@ -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>
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/modifyColumn.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/modifyColumn.sql?rev=373066&r1=373065&r2=373066&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/modifyColumn.sql (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/modifyColumn.sql Fri Jan 27 18:15:41 2006
@@ -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;
+