Increase default BLOB/CLOB length to maximum supported (2G?)

• Key: DERBY-1384
• Type: Improvement
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Bernt M. Johnsen
• Reporter: Bernt M. Johnsen
• Component/s: SQL
• Fix Version/s: 10.2.1.6

Description

Default BLOB/CLOB length should be the maximum length supported by Derby (2G?)

Comments

[Bernt M. Johnsen]

The maximum value supported by Derby is not 2G, but 2G-1, which the user have to write as 2147483647, which again is pretty inconvenient.

[Daniel John Debrunner]

I thought 2G can be written (and means 2G -1).

Changing the default can change the behaviour of exisitng applications thus needs to be considered carefully.

An application that used BLOB will current reject values greater than 1M, changing the default means the application will now silently accept those values.

[Bernt M. Johnsen]

2G is 2G, not 2G-1. See ISO/IEC 9075-2:2003 (E) 6.1 <data type>:

If <large object length> immediately contains <large object length token> or immediately contains
<unsigned integer> and <multiplier>, then let D be the value of the specified <unsigned integer> or
the numeric value of the sequence of <digit>s of <large object length token> interpreted as an <unsigned
integer>. The numeric value of <large object length> is the numeric value resulting from the multiplication
of D and MS, then MS is:
i) If <multiplier> is K, then 1,024.
ii) If <multiplier> is M, then 1,048,576.
iii) If <multiplier> is G, then 1,073,741,824.

I agree that this might change the behaviour of existing applications, but that the increased usability is worth the change.

[Andreas Korneliussen]

Maybe the Release Note Required checkbox should be checked for this change.

My understanding is that after an upgrade (from a version without this improvement), an application which continues to use the schemas/tables as they were initially created, will not be affected by the change. An application which recreates the schemas/tables after an upgrade, may be affected if it depends on Derby rejecting Blobs with sizes bigger than 1M.

[Daniel John Debrunner]

Bernt wrote:
    2G is 2G, not 2G-1. See ISO/IEC 9075-2:2003 (E) 6.1 <data type>:

In Derby, at the moment, BLOB(2G) does mean BLOB(2147483647). Does not agree with the standard but it is the current behaviour.

[Bernt M. Johnsen]

I admit that to interpret 2G as 2147483647 is user-friendly, but that should be reflected in the docs which currently says "one of the suffixes K, M, or G is given, relating to the multiples of 1024, 1024*1024, 1024*1024*1024 respectively" without describing the exception for 2G (or 2048M and 1048576K frmo which Derby today subtracts 1).

[Satheesh Bandaram]

While I still don't know if this change is good or not for Derby at this time, change in max length, if applied, should probably be prevented in soft-upgrade mode. When Derby is running in soft-upgrade mode in 10.2, it is best to avoid increasing default max length, as it could cause problems after any downgrade.

[Andreas Korneliussen]

I do not see how the improvement would cause any problems after an downgrade, since it is about changing the default size for Blob columns within the range of what is supported in 10.1. If it was about changing the maximum size of a blob column, I agree that this could cause a downgrade problem.

[Sunitha Kambhampati]

Please note: that our interfaces like DataTypeDescriptor.getMaximumWidth() etc currently return an int . Any change to the clob/blob to be actually 2G (ie 2147483648) means we exceed the max value possible for int and it might be necessary to change the interfaces to take long instead of int.

[Satheesh Bandaram]

Here is one situation where changing default max length would cause problem. In soft upgrade, if some one creates a default length BLOB/CLOB, if the default is changed, they could successfully create larger than 1MB blob/clob. On downgrade, statements like this would fail:

INSERT INTO TAB SELECT * FROM BLOBTAB;

where all blob/clob instances are created using default size, just some are created on soft-upgrade to 10.2.

Soft upgrade checks are cheap and are designed to maintain same behavior across versions.

[Daniel John Debrunner]

I would say strictly speaking that this is not a concern for soft-upgrade. The data written to disk (if the default for BLOB was changed) does not result in a on-disk format that is mis-understood by 10.0 or 10.1. The column would be processed correctly and have the correct meta data.

This is like the previous change where the max identifier lengths for certain objects was increased in 10.1.
The change didn't need soft-upgrade checks as any larger identifiers were understood by 10.0.

Satheesh, I'm not sure what you are trying to say with your SELECT statement that would fail. In what exact situations would that fail?

[Satheesh Bandaram]

This is the case I was thinking about. I have coded my application without specifying lengths for BLOB/CLOB. I would normally expect this to work:

INSERT INTO tblob select * from tblob; OR
INSERT INTO tblob select * from myblobTab;

My concern was on soft-update, it would be possible to insert larger than 1MB and later on downgrade, same operations like above will not work as some instances of BLOB could now be larger than 1MB. (and hence above operations could fail on 10.1)

May be this is extreme case and may not be worth protecting... but one could look at 10.1 BLOB (without size) datatype to be between 1-1MB and is being changed to 1-2GB.

> This is the case I was thinking about. I have coded my application without specifying lengths for BLOB/CLOB. I would normally expect this to work:
>
> INSERT INTO tblob select * from tblob; OR
> INSERT INTO tblob select * from myblobTab;
>
> My concern was on soft-update, it would be possible to insert larger than 1MB and later on downgrade, same operations like above will not work as some instances of BLOB could now be larger than 1MB. (and hence above operations could fail on 10.1)
>

I do not see how this would be a problem on downgrade. If the operations worked in soft-upgrade, downgrade will not redefine the size of the blob-columns of existing tables. This issue is about changing the default max size of columns when doing "CREATE TABLE ..", so unless the scenario contains a create table somewhere, it should not be an issue.

> May be this is extreme case and may not be worth protecting... but one could look at 10.1 BLOB (without size) datatype to be between 1-1MB and is being changed to 1-2GB.
>

An extreme case is that the user applications recreates some of its tables, and not other tables during soft-upgrade. This means that some of the tables may allow 1MB blob columns and other tables 2GB blob columns. This should then only be a problem if the application suddenly starts using blobs with size > 1MB, and do similar queries as you gave. These will now fail. If the applications had been running only on 10.1 it would have failed once it started inserting blobs with size >1M into the tables.
I do not think this extreme case is a soft-upgrade problem, as the same problem could be constructed in a hard-upgrade scenario. Therefore, I do not think it is worth adding any logic in soft-upgrade mode.

[Bernt M. Johnsen]

Committed revision 415328.

[Bernt M. Johnsen]

Docs Committed revision 415329.