Author: bpendleton
Date: Mon Feb 26 13:26:55 2007
New Revision: 512019
URL: http://svn.apache.org/viewvc?view=rev&rev=512019
Log:
DERBY-2371: Altering VARCHAR default fails when column contains data
Using ALTER TABLE to modify the default value for a VARCHAR column when
the column already contains data throws ERROR 22018: Invalid character
string format for type long.
The problem arises in AlterTableConstantAction.modifyColumnDefault, which
only understands two types of modifications that can be made to a
column's default value:
- changing the INCREMENT BY value for a generated IDENTITY column
- changing the RESTART WITH value for a generated IDENTITY column
But there is a third type of modification that can be made to a column's
default value, which is simply to change the default value that is to
be used for an ordinary column if no value is provided when inserting
a new row into that table.
The fix is to add a new ColumnInfo modification type code:
MODIFY_COLUMN_DEFAULT_VALUE
and to teach ModifyColumnNode and AlterTableConstantAction what to do
for modifications of type MODIFY_COLUMN_DEFAULT_VALUE.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ColumnInfo.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java?view=diff&rev=512019&r1=512018&r2=512019
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
Mon Feb 26 13:26:55 2007
@@ -234,8 +234,11 @@
case C_NodeTypes.MODIFY_COLUMN_DEFAULT_NODE:
if (autoinc_create_or_modify_Start_Increment == ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE)
return ColumnInfo.MODIFY_COLUMN_DEFAULT_RESTART;
- else
+ else if (autoinc_create_or_modify_Start_Increment ==
+ ColumnDefinitionNode.MODIFY_AUTOINCREMENT_INC_VALUE)
return ColumnInfo.MODIFY_COLUMN_DEFAULT_INCREMENT;
+ else
+ return ColumnInfo.MODIFY_COLUMN_DEFAULT_VALUE;
case C_NodeTypes.MODIFY_COLUMN_TYPE_NODE:
return ColumnInfo.MODIFY_COLUMN_TYPE;
case C_NodeTypes.MODIFY_COLUMN_CONSTRAINT_NODE:
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?view=diff&rev=512019&r1=512018&r2=512019
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
Mon Feb 26 13:26:55 2007
@@ -370,7 +370,10 @@
}
else if (columnInfo[ix].action ==
ColumnInfo.MODIFY_COLUMN_DEFAULT_RESTART ||
- columnInfo[ix].action == ColumnInfo.MODIFY_COLUMN_DEFAULT_INCREMENT)
+ columnInfo[ix].action ==
+ ColumnInfo.MODIFY_COLUMN_DEFAULT_INCREMENT ||
+ columnInfo[ix].action ==
+ ColumnInfo.MODIFY_COLUMN_DEFAULT_VALUE)
{
modifyColumnDefault(activation, ix);
}
@@ -1104,6 +1107,7 @@
dd.setAutoincrementValue(tc, td.getUUID(), columnInfo[ix].name,
columnInfo[ix].autoincStart, false);
}
+ // else we are simply changing the default value
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ColumnInfo.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ColumnInfo.java?view=diff&rev=512019&r1=512018&r2=512019
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ColumnInfo.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ColumnInfo.java Mon
Feb 26 13:26:55 2007
@@ -92,6 +92,7 @@
public static final int MODIFY_COLUMN_DEFAULT_RESTART = 5;
//This indicates column is for ALTER TABLE to change the increment value of autoinc column
public static final int MODIFY_COLUMN_DEFAULT_INCREMENT = 6;
+ public static final int MODIFY_COLUMN_DEFAULT_VALUE = 7;
// CONSTRUCTORS
/**
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out?view=diff&rev=512019&r1=512018&r2=512019
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
Mon Feb 26 13:26:55 2007
@@ -1642,4 +1642,36 @@
USER2
|s|{0, 1}
SUPER_USER
|u|{1}
USER1
|u|{0}
+ij> -- JIRA 2371: ensure that a non-numeric, non-autogenerated column can
+-- have its default value modified:
+create table t2371 ( a varchar(10));
+0 rows inserted/updated/deleted
+ij> describe t2371;
+COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+A |VARCHAR |NULL|NULL|10 |NULL |20 |YES
+ij> alter table t2371 alter column a default 'my val';
+0 rows inserted/updated/deleted
+ij> describe t2371;
+COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+A |VARCHAR |NULL|NULL|10 |'my val' |20 |YES
+ij> insert into t2371 (a) values ('hi');
+1 row inserted/updated/deleted
+ij> insert into t2371 (a) values (default);
+1 row inserted/updated/deleted
+ij> alter table t2371 alter column a default 'another';
+0 rows inserted/updated/deleted
+ij> describe t2371;
+COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+A |VARCHAR |NULL|NULL|10 |'another' |20 |YES
+ij> insert into t2371 (a) values (default);
+1 row inserted/updated/deleted
+ij> select * from t2371;
+A
+----------
+hi
+my val
+another
ij>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql?view=diff&rev=512019&r1=512018&r2=512019
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
Mon Feb 26 13:26:55 2007
@@ -610,7 +610,6 @@
insert into atmod_1 values (default, 'forty two');
select * from atmod_1;
-
-- Tests for renaming a column. These tests are in altertable.sql because
-- renaming a column is closely linked, conseptually, to other table
-- alterations. However, the actual syntax is:
@@ -929,3 +928,15 @@
select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
where c.tableid = t.tableid and t.tablename='D1909';
+-- JIRA 2371: ensure that a non-numeric, non-autogenerated column can
+-- have its default value modified:
+create table t2371 ( a varchar(10));
+describe t2371;
+alter table t2371 alter column a default 'my val';
+describe t2371;
+insert into t2371 (a) values ('hi');
+insert into t2371 (a) values (default);
+alter table t2371 alter column a default 'another';
+describe t2371;
+insert into t2371 (a) values (default);
+select * from t2371;
|