Issue Details (XML | Word | Printable)

Key: CORE-6195
Type: Improvement Improvement
Status: Open Open
Priority: Minor Minor
Assignee: Unassigned
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Provide ablity to change SUBTYPE of blob field

Created: 22/Nov/19 10:28 PM   Updated: 23/Nov/19 06:21 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None

QA Status: No test


 Description  « Hide
Suppose we have two tables, both with BLOB field.
First table was created with undefined blob sub-type, second - with blob that we know forecast that it will store only text data.
Let's create and add multi-line content to these tables:
=======
set blob all;
commit;

recreate table b_undefined_type(b blob);
insert into b_undefined_type values(
'line1
line2
line3'
);
commit;
------------------------------------------

recreate table b_known_as_text(b blob sub_type text);
insert into b_known_as_text values(
'line1
line2
line3'
);
commit;
=======

If we try to extract BLOBs without any casting then all will be fine.
But if we cast blob to varchar (with providing enough length to accomodate its content) then blob from "b_undefined_type" will be extracted with loosing CRLF characters:

=======
set list on;
set echo on;

select b from b_known_as_text;
select cast(b as varchar(20)) as blob_to_varchar from b_known_as_text;

select b from b_undefined_type;
select cast(b as varchar(20)) as blob_to_varchar from b_undefined_type;
=======

Output (compare data in lines [1] and [2]):
=======
select b from b_known_as_text;

B 91:0
line1
line2
line3

select cast(b as varchar(20)) as blob_to_varchar from b_known_as_text;

BLOB_TO_VARCHAR line1 <<<<<<<<<<<<<<<<<<<<<<< [ 1 ]
line2
line3

select b from b_undefined_type;

B 90:0
line1
line2
line3

select cast(b as varchar(20)) as blob_to_varchar from b_undefined_type;

BLOB_TO_VARCHAR line1line2line3 <<<<<<<<<<<<< [ 2 ]

=======

Attempt to change blob sub_type to text will FAIL with message:
=======
SQL> alter table b_undefined_type alter b type blob sub_type text;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-ALTER TABLE B_UNDEFINED_TYPE failed
-Cannot change datatype for column B. Changing datatype is not supported for BLOB or ARRAY columns.
=======

If this table ('b_undefined_type') has many dependencies then it will be difficult to drop it and recreate again with proper blob sub_type.

Please consider ability to change blob sub-type by ALTER TABLE ALTER <COLUMN> statement.



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Karol Bieniaszewski added a comment - 23/Nov/19 06:13 AM - edited
Sub_type is equivalent to file extension and should be possible to change it. But i do not know if this can not have side effects when it is used in expression like e.g. substring and is used already in index? My concern is blob filter here..