[DERBY-2371] Setting a default value for a VARCHAR column fails when column contains data

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.2.1.6, 10.2.2.0
• Fix Version/s: 10.2.2.1, 10.3.1.4
• Component/s: SQL
• Environment: This probably affects all platforms.
• Assignee: Bryan Pendleton
• Reporter: Tim Dudgeon
• Created: 23/Feb/07 16:13
• Updated: 21/Jan/11 17:49
• Resolved: 27/Feb/07 17:41

Description

I'm seeing a problem updating the default value for a VARCHAR column when the column already contains data. I'm, using:

alter table TABLE_NAME alter column COL_NAME DEFAULT 'new value'

and with some VARCHAR columns I get an error like this:

Invalid character string format for type long.; nested exception is java.sql.SQLException: Invalid character string format for type long.
Caused by: ERROR 22018: Invalid character string format for type long.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.iapi.types.SQLChar.getLong(Unknown Source)
at org.apache.derby.impl.sql.execute.AlterTableConstantAction.getColumnMax(Unknown Source)
at org.apache.derby.impl.sql.execute.AlterTableConstantAction.modifyColumnDefault(Unknown Source)
at org.apache.derby.impl.sql.execute.AlterTableConstantAction.execGuts(Unknown Source)
at org.apache.derby.impl.sql.execute.AlterTableConstantAction.executeConstantAction(Unknown Source)
at org.apache.derby.impl.sql.execute.MiscResultSet.open(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)

This only happens on VARCHAR columns containing data. Columns that are entirely NULL update fine.

This was discussed on users mailing list:
http://www.nabble.com/problem-with-ALTER-COLUMN-DEFAULT-on-VARCHAR-column-tf3274046.html
and the conclusion was that it is related to this statement being run internally as part of the updata process:
SELECT MAX(COL_NAME) FROM TABLE_NAME

As reported in that discussion:

-bash-2.05b$ java org.apache.derby.tools.ij
ij version 10.3
ij> connect 'jdbc:derby:brydb';
ij> create table t (a varchar(10));
0 rows inserted/updated/deleted
ij> alter table t alter column a default 'my val';
0 rows inserted/updated/deleted
ij> insert into t (a) values ('hi');
1 row inserted/updated/deleted
ij> alter table t alter column a default 'another val';
ERROR 22018: Invalid character string format for type long.