New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Regression: Unable to ALTER column between BLOB SUB_TYPE TEXT and VARCHAR [CORE6052] #6302

Closed

firebird-automations opened this issue Apr 18, 2019 · 11 comments

Labels

affect-version: 3.0.4 component: engine priority: major resolution: wontfix type: bug

Collaborator

firebird-automations commented Apr 18, 2019

Submitted by: Arioch (arioch)

Expanding columns from VarChar(nn) to BLOB SUB_TYPE TEXT (or shrinking it back) is working in Firebird 2.1.7 and 2.5.8 but it fails in Firebird 3.0.4

P.S. I know there is a workaround for this regression: creating extra column and copying data there. Still, it is just a workaround, and slow one on large tables.
https://stackoverflow.com/questions/34368282

------------

CREATE DOMAIN T_TEXT_BLOB AS
BLOB SUB_TYPE 1;

CREATE DOMAIN T_TEXT_VC AS
VARCHAR(20);

CREATE TABLE ALTER_TEXT (
TARGET T_TEXT_VC);

INSERT INTO ALTER_TEXT(TARGET) VALUES('aaa');
INSERT INTO ALTER_TEXT(TARGET) VALUES('bbb');
INSERT INTO ALTER_TEXT(TARGET) VALUES('ccc');

COMMIT;

-- now we want to change the column WITHOUT copying data

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = 'T_TEXT_BLOB'
where (RDB$FIELD_NAME = 'TARGET') and
(RDB$RELATION_NAME = 'ALTER_TEXT')

/* works like a charm in FB 2.1 and FB 2.5, but fails in FB 3.0.4.

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
UPDATE operation is not allowed for system table RDB$RELATION_FIELDS. */

ALTER TABLE ALTER_TEXT ALTER COLUMN TARGET TYPE T_TEXT_BLOB;

/* Fails with internal errors on both FB 2.1, FB 2.5, FB 3.0.4

This operation is not defined for system tables.
unsuccessful metadata update.
ALTER TABLE ALTER_TEXT failed.
Cannot change datatype for column TARGET. Changing datatype is not supported for BLOB or ARRAY columns. */

So, the operation that worked (albeit in an "obsolete" way) on Firebird 2.1 and 2.5 is no more working in 3.x

This regression is twice sad since "BLOB and array data types are not supported for move operation" was reportedly fixed in CORE3446

Collaborator Author

firebird-automations commented Apr 18, 2019

Modified by: Arioch (arioch)

description: Expanding columns from VarChar(nn) to BLOB SUB_TYPE TEXT (or shrinking it back) is working in Firebird 2.1.7 and 2.5.8 but it fails in Firebird 3.0.4

P.S. I know there is a workaround for this regression: creating extra column and copying data there. Still, it is just a workaround, and slow one on large tables.
https://stackoverflow.com/questions/34368282

------------

CREATE DOMAIN T_TEXT_BLOB AS
BLOB SUB_TYPE 1;

CREATE DOMAIN T_TEXT_VC AS
VARCHAR(20);

CREATE TABLE ALTER_TEXT (
TARGET T_TEXT_VC);

INSERT INTO ALTER_TEXT(TARGET) VALUES('aaa');
INSERT INTO ALTER_TEXT(TARGET) VALUES('bbb');
INSERT INTO ALTER_TEXT(TARGET) VALUES('ccc');

COMMIT;

-- now we want to change the column WITHOUT copying data

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = 'T_TEXT_BLOB'
where (RDB$FIELD_NAME = 'TARGET') and
(RDB$RELATION_NAME = 'ALTER_TEXT')

/* works like a charm in FB 2.1 and FB 2.5, but fails in FB 3.0.4.

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
UPDATE operation is not allowed for system table RDB$RELATION_FIELDS. */

/* ALTER TABLE ALTER_TEXT ALTER COLUMN TARGET TYPE T_TEXT_BLOB;

This operation is not defined for system tables.
unsuccessful metadata update.
ALTER TABLE ALTER_TEXT failed.
Cannot change datatype for column TARGET. Changing datatype is not supported for BLOB or ARRAY columns. */

Fails with internal errors on both FB 2.1, FB 2.5, FB 3.0.4

So, the operation that worked (albeit in an "obsolete" way) on Firebird 2.1 and 2.5 is no more working in 3.x

This regression is twice sad since "BLOB and array data types are not supported for move operation" was reportedly fixed in BLOB and array data types are not supported for move operation

=>

Expanding columns from VarChar(nn) to BLOB SUB_TYPE TEXT (or shrinking it back) is working in Firebird 2.1.7 and 2.5.8 but it fails in Firebird 3.0.4

P.S. I know there is a workaround for this regression: creating extra column and copying data there. Still, it is just a workaround, and slow one on large tables.
https://stackoverflow.com/questions/34368282

------------

CREATE DOMAIN T_TEXT_BLOB AS
BLOB SUB_TYPE 1;

CREATE DOMAIN T_TEXT_VC AS
VARCHAR(20);

CREATE TABLE ALTER_TEXT (
TARGET T_TEXT_VC);

INSERT INTO ALTER_TEXT(TARGET) VALUES('aaa');
INSERT INTO ALTER_TEXT(TARGET) VALUES('bbb');
INSERT INTO ALTER_TEXT(TARGET) VALUES('ccc');

COMMIT;

-- now we want to change the column WITHOUT copying data

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = 'T_TEXT_BLOB'
where (RDB$FIELD_NAME = 'TARGET') and
(RDB$RELATION_NAME = 'ALTER_TEXT')

/* works like a charm in FB 2.1 and FB 2.5, but fails in FB 3.0.4.

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
UPDATE operation is not allowed for system table RDB$RELATION_FIELDS. */

ALTER TABLE ALTER_TEXT ALTER COLUMN TARGET TYPE T_TEXT_BLOB;

/* Fails with internal errors on both FB 2.1, FB 2.5, FB 3.0.4

This operation is not defined for system tables.
unsuccessful metadata update.
ALTER TABLE ALTER_TEXT failed.
Cannot change datatype for column TARGET. Changing datatype is not supported for BLOB or ARRAY columns. */

So, the operation that worked (albeit in an "obsolete" way) on Firebird 2.1 and 2.5 is no more working in 3.x

This regression is twice sad since "BLOB and array data types are not supported for move operation" was reportedly fixed in BLOB and array data types are not supported for move operation

Collaborator Author

firebird-automations commented Apr 18, 2019

Modified by: Arioch (arioch)

description: Expanding columns from VarChar(nn) to BLOB SUB_TYPE TEXT (or shrinking it back) is working in Firebird 2.1.7 and 2.5.8 but it fails in Firebird 3.0.4

P.S. I know there is a workaround for this regression: creating extra column and copying data there. Still, it is just a workaround, and slow one on large tables.
https://stackoverflow.com/questions/34368282

------------

CREATE DOMAIN T_TEXT_BLOB AS
BLOB SUB_TYPE 1;

CREATE DOMAIN T_TEXT_VC AS
VARCHAR(20);

CREATE TABLE ALTER_TEXT (
TARGET T_TEXT_VC);

INSERT INTO ALTER_TEXT(TARGET) VALUES('aaa');
INSERT INTO ALTER_TEXT(TARGET) VALUES('bbb');
INSERT INTO ALTER_TEXT(TARGET) VALUES('ccc');

COMMIT;

-- now we want to change the column WITHOUT copying data

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = 'T_TEXT_BLOB'
where (RDB$FIELD_NAME = 'TARGET') and
(RDB$RELATION_NAME = 'ALTER_TEXT')

/* works like a charm in FB 2.1 and FB 2.5, but fails in FB 3.0.4.

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
UPDATE operation is not allowed for system table RDB$RELATION_FIELDS. */

ALTER TABLE ALTER_TEXT ALTER COLUMN TARGET TYPE T_TEXT_BLOB;

/* Fails with internal errors on both FB 2.1, FB 2.5, FB 3.0.4

This operation is not defined for system tables.
unsuccessful metadata update.
ALTER TABLE ALTER_TEXT failed.
Cannot change datatype for column TARGET. Changing datatype is not supported for BLOB or ARRAY columns. */

So, the operation that worked (albeit in an "obsolete" way) on Firebird 2.1 and 2.5 is no more working in 3.x

This regression is twice sad since "BLOB and array data types are not supported for move operation" was reportedly fixed in BLOB and array data types are not supported for move operation

=>

Expanding columns from VarChar(nn) to BLOB SUB_TYPE TEXT (or shrinking it back) is working in Firebird 2.1.7 and 2.5.8 but it fails in Firebird 3.0.4

P.S. I know there is a workaround for this regression: creating extra column and copying data there. Still, it is just a workaround, and slow one on large tables.
https://stackoverflow.com/questions/34368282

------------

CREATE DOMAIN T_TEXT_BLOB AS
BLOB SUB_TYPE 1;

CREATE DOMAIN T_TEXT_VC AS
VARCHAR(20);

CREATE TABLE ALTER_TEXT (
TARGET T_TEXT_VC);

INSERT INTO ALTER_TEXT(TARGET) VALUES('aaa');
INSERT INTO ALTER_TEXT(TARGET) VALUES('bbb');
INSERT INTO ALTER_TEXT(TARGET) VALUES('ccc');

COMMIT;

-- now we want to change the column WITHOUT copying data

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = 'T_TEXT_BLOB'
where (RDB$FIELD_NAME = 'TARGET') and
(RDB$RELATION_NAME = 'ALTER_TEXT')

/* works like a charm in FB 2.1 and FB 2.5, but fails in FB 3.0.4.

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
UPDATE operation is not allowed for system table RDB$RELATION_FIELDS. */

ALTER TABLE ALTER_TEXT ALTER COLUMN TARGET TYPE T_TEXT_BLOB;

/* Fails with internal errors on both FB 2.1, FB 2.5, FB 3.0.4

This operation is not defined for system tables.
unsuccessful metadata update.
ALTER TABLE ALTER_TEXT failed.
Cannot change datatype for column TARGET. Changing datatype is not supported for BLOB or ARRAY columns. */

So, the operation that worked (albeit in an "obsolete" way) on Firebird 2.1 and 2.5 is no more working in 3.x

This regression is twice sad since "BLOB and array data types are not supported for move operation" was reportedly fixed in CORE3446

Collaborator Author

firebird-automations commented Apr 18, 2019

Modified by: Arioch (arioch)

description: Expanding columns from VarChar(nn) to BLOB SUB_TYPE TEXT (or shrinking it back) is working in Firebird 2.1.7 and 2.5.8 but it fails in Firebird 3.0.4

P.S. I know there is a workaround for this regression: creating extra column and copying data there. Still, it is just a workaround, and slow one on large tables.
https://stackoverflow.com/questions/34368282

------------

CREATE DOMAIN T_TEXT_BLOB AS
BLOB SUB_TYPE 1;

CREATE DOMAIN T_TEXT_VC AS
VARCHAR(20);

CREATE TABLE ALTER_TEXT (
TARGET T_TEXT_VC);

INSERT INTO ALTER_TEXT(TARGET) VALUES('aaa');
INSERT INTO ALTER_TEXT(TARGET) VALUES('bbb');
INSERT INTO ALTER_TEXT(TARGET) VALUES('ccc');

COMMIT;

-- now we want to change the column WITHOUT copying data

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = 'T_TEXT_BLOB'
where (RDB$FIELD_NAME = 'TARGET') and
(RDB$RELATION_NAME = 'ALTER_TEXT')

/* works like a charm in FB 2.1 and FB 2.5, but fails in FB 3.0.4.

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
UPDATE operation is not allowed for system table RDB$RELATION_FIELDS. */

ALTER TABLE ALTER_TEXT ALTER COLUMN TARGET TYPE T_TEXT_BLOB;

/* Fails with internal errors on both FB 2.1, FB 2.5, FB 3.0.4

This operation is not defined for system tables.
unsuccessful metadata update.
ALTER TABLE ALTER_TEXT failed.
Cannot change datatype for column TARGET. Changing datatype is not supported for BLOB or ARRAY columns. */

So, the operation that worked (albeit in an "obsolete" way) on Firebird 2.1 and 2.5 is no more working in 3.x

This regression is twice sad since "BLOB and array data types are not supported for move operation" was reportedly fixed in CORE3446

=>

Expanding columns from VarChar(nn) to BLOB SUB_TYPE TEXT (or shrinking it back) is working in Firebird 2.1.7 and 2.5.8 but it fails in Firebird 3.0.4

P.S. I know there is a workaround for this regression: creating extra column and copying data there. Still, it is just a workaround, and slow one on large tables.
https://stackoverflow.com/questions/34368282

------------

CREATE DOMAIN T_TEXT_BLOB AS
BLOB SUB_TYPE 1;

CREATE DOMAIN T_TEXT_VC AS
VARCHAR(20);

CREATE TABLE ALTER_TEXT (
TARGET T_TEXT_VC);

INSERT INTO ALTER_TEXT(TARGET) VALUES('aaa');
INSERT INTO ALTER_TEXT(TARGET) VALUES('bbb');
INSERT INTO ALTER_TEXT(TARGET) VALUES('ccc');

COMMIT;

-- now we want to change the column WITHOUT copying data

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = 'T_TEXT_BLOB'
where (RDB$FIELD_NAME = 'TARGET') and
(RDB$RELATION_NAME = 'ALTER_TEXT')

/* works like a charm in FB 2.1 and FB 2.5, but fails in FB 3.0.4.

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
UPDATE operation is not allowed for system table RDB$RELATION_FIELDS. */

ALTER TABLE ALTER_TEXT ALTER COLUMN TARGET TYPE T_TEXT_BLOB;

/* Fails with internal errors on both FB 2.1, FB 2.5, FB 3.0.4

This operation is not defined for system tables.
unsuccessful metadata update.
ALTER TABLE ALTER_TEXT failed.
Cannot change datatype for column TARGET. Changing datatype is not supported for BLOB or ARRAY columns. */

So, the operation that worked (albeit in an "obsolete" way) on Firebird 2.1 and 2.5 is no more working in 3.x

This regression is twice sad since "BLOB and array data types are not supported for move operation" was reportedly fixed in CORE3446

Collaborator Author

firebird-automations commented Apr 18, 2019

Modified by: Sean Leyne (seanleyne)

summary: Regression: no more can change column from BLOB to VARCHAR or back => Regression: Unable to ALTER column between BLOB SUB_TYPE TEXT and VARCHAR

Collaborator Author

firebird-automations commented Apr 21, 2019

Commented by: Omacht András (aomacht)

"works like a charm in FB 2.1 and FB 2.5..."

Yes, it works, but after that when you want to read the data from the field, sometimes you get "invalid blob id" and you lose data unless you can restore the original field informations in the system tables.

Collaborator Author

firebird-automations commented Apr 22, 2019

Commented by: @asfernandes

That type of change never worked properly and is now disallowed. Not a bug.

Collaborator Author

firebird-automations commented Apr 22, 2019

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

firebird-automations closed this as completed

Collaborator Author

firebird-automations commented Apr 22, 2019

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

Collaborator Author

firebird-automations commented Jul 10, 2019

Commented by: Arioch (arioch)

Is there example when it was not working? it is just a yet another table format, with auto-conversion from varchar to blob on fetching.

Also, if this conversion risks loosing data, then the fix is not blocking it, but in making it work without loosing data.

Collaborator Author

firebird-automations commented Aug 8, 2019

Commented by: Arioch (arioch)

Crickets....

OK, I managed to trigger the infamous invalid BLOB ID Firebird error caused by yet another manifestation of reference counting being broken w.r.t. BLOB payloads.

Which, however, shows the way to prevent the Firebird bug hinted above.

To trigger the bug:

1. update RDB$RELATION_FIELDS ....
2. update ALTER_TEXT set TARGET = TARGET -- trying to force data conversion, but instead forcing Firebird reference counting bug
3. select cast( TARGET as VarChar(20) ) from ALTER_TEXT

Then to preempt the bug we must prevent Firebird from (broken) reference counting. So, we must do a fake update, invoking expression evaluator, so that Firebird optimizer would loose track of value sources and would fail to realize the data was not really changed.

1f. update RDB$RELATION_FIELDS ....
2f. update ALTER_TEXT set TARGET = '' || TARGET || '' -- invoking expression evaluator to actually create new blob bodies
3f. select cast( TARGET as VarChar(20) ) from ALTER_TEXT

I understand that reference counting might be tricky. Still it is sad to see 2f being also rocket science that Firebird core can not issue it when executing ALTER TABLE varchar-to-blob conversion.

Collaborator Author

firebird-automations commented Aug 8, 2019

Commented by: Arioch (arioch)

The above stands for Firebird 2.1.7 win32. I did not manage to trigger "invalid BLOB id" with Firebird 2.5.8 Win64 - it "just worked".
At least using the single-connection schema update script, which is anyway the intended way to do database upgrades.
Maybe if I would do schema upgrades while simultaneously there would be users actively working - the FB 2.5 would get broken too, don't know.

firebird-automations added affect-version: 3.0.4 resolution: wontfix priority: major component: engine type: bug labels

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment