Setting a default value for a VARCHAR column fails when column contains data

• Key: DERBY-2371
• Type: Bug
• Status: Resolved
• Resolution: Fixed
• Priority: Major
• Assignee: Bryan Pendleton
• Reporter: Tim Dudgeon
• Component/s: SQL
• Affects Version/s: 10.2.1.6, 10.2.2.0
• Fix Version/s: 10.2.3.0, 10.3.1.4

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.

Comments

[Bryan Pendleton]

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.

I've got a diff ready, and will post it after running the regression tests.

[Bryan Pendleton]

All the regression tests passed (once I remembered to up the memory for the
JUnit suite). Attached is modifyValue.diff, a proposed patch for this issue.

The patch adds a new action code to the ModifyColumnNode to distinguish
the case where the column modification is for changing the default value,
and does not involve any generated identity column.

A new test case is added to altertable.sql, based directly on the repro script.

Please let me know your comments and suggestions.

[Saurabh Vyas]

Hi Bryan, I did some quick tests on your patch & it works perfectly fine.

+1 to commit.

Saurabh

[Bryan Pendleton]

Thanks Saurabh!

I intend to commit this patch to the trunk in the next day or so, and then merge the change to 10.2.

[Bryan Pendleton]

Committed to the trunk as revision 512019. Will merge to 10.2 now.

[Bryan Pendleton]

Merged the trunk change to the 10.2 branch. The altertable.sql file
needed hand-resolution of the merge, but the rest of the merging
was straightforward. 10.2 regression tests passed without error.

Committed the change to the 10.2 branch as revision 512334.