You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Satheesh Bandaram (JIRA)" <de...@db.apache.org> on 2006/01/27 08:22:34 UTC
[jira] Commented: (DERBY-882) Increasing size of varchar type using
ALTER TABLE can implicitly change the column from NOT NULL to NULLable.
[ 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.
> Increasing size of varchar type using ALTER TABLE can implicitly change the column from NOT NULL to NULLable.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-882
> URL: http://issues.apache.org/jira/browse/DERBY-882
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.1.2.2, 10.2.0.0
> Environment: generic
> Reporter: Satheesh Bandaram
> Assignee: Satheesh Bandaram
> Fix For: 10.2.0.0, 10.1.3.0
>
> Altering size of a varchar column using ALTER TABLE command can change the column from not nullable to nullable.
> ij version 10.1
> ij> connect 'jdbc:derby:bdb;create=true';
> ij> 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> insert into a values (2, null);
> ERROR 23502: Column 'NAME' cannot accept a NULL value. <==== Initially doesn't accept nulls
> ij> alter table a alter name set data type varchar(50); <==== Change size of varchar column
> 0 rows inserted/updated/deleted
> ij> insert into a values (3, 'hijk');
> 1 row inserted/updated/deleted
> ij> insert into a values (4, null); <==== Now NULLs are accepted
> 1 row inserted/updated/deleted
> ij> select * from a;
> ID |NAME
> --------------------------------------------------------------
> 1 |abc
> 3 |hijk
> 4 |NULL
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-882) Increasing size of varchar type
using ALTER TABLE can implicitly change the column from NOT NULL to NULLable.
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
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>