Skip to content
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

Non-flexible dependance of procedures using TYPE OF COLUMN [CORE3505] #3863

Open
firebird-automations opened this issue May 31, 2011 · 12 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Michal Dohnal (mixerito)

All dependant procedures using TYPE OF COLUMN, where column is defined by domain, need to be manually recompiled, when the definition of domain is changed.

Example:

-- Let's have 2 different domains
CREATE DOMAIN D_VARCHAR10 AS
VARCHAR(10) CHARACTER SET WIN1250
COLLATE PXW_CSY ;

CREATE DOMAIN D_VARCHAR40 AS
VARCHAR(40) CHARACTER SET WIN1250
COLLATE PXW_CSY ;

-- In time of creating table, we presume 10 characters should be enough
CREATE TABLE NEW_TABLE (
ID INTEGER,
FIELD D_VARCHAR10);

-- We create some read procedure
CREATE OR ALTER PROCEDURE NEW_PROC
RETURNS(
FIELD TYPE OF COLUMN NEW_TABLE.FIELD)
AS
BEGIN
FOR SELECT FIELD
FROM NEW_TABLE
INTO :FIELD
DO
SUSPEND;
END;

-- Now, we need more space
ALTER TABLE NEW_TABLE ALTER FIELD TYPE D_VARCHAR40;

-- Let's insert some long record
INSERT INTO NEW_TABLE
(ID,FIELD) VALUES (1,'I am longer than 10 characters');

-- And then we try to read all records of the table using the read procedure
SELECT * FROM NEW_PROC;

-- So we get this exception
/*Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
At procedure 'NEW_PROC' line: 6, col: 3.*/

I can mention that, Backup and Restore doesn't help.

I am aware of that, some software like IB Expert offer to recompile all depenadant procedures, but it gets complicated in some situations like differential updating on customers databases.

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

Changing definition of domain must be denied if this domain is used anywhere (including via TYPE OF).

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Does this issue exist for TYPE OF COLUMN *input* parameters?

@firebird-automations
Copy link
Collaborator Author

Commented by: Michal Dohnal (mixerito)

Dimitry S:
I've maybe expressed badly.
If you see the example, i'm not changing the definition of domain itself. I ment using another one.

Dmitry Y:
I've just tried and TYPE OF COLUMN *input* parameters cause the same problem.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

I'm sure that all you need is to reconnect, assuming you run DDL in single attachment as required by IB6 docs.
I.e. it is the same old known behavior of metadata cache.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: All dependant procedures using TYPE OF COLUMN, where column is defined by domain, need to be manually recompiled, when the definition of domain is changed.

Example:

-- Let's have 2 different domains

CREATE DOMAIN D_VARCHAR10 AS
VARCHAR(10) CHARACTER SET WIN1250
COLLATE PXW_CSY ;

CREATE DOMAIN D_VARCHAR40 AS
VARCHAR(40) CHARACTER SET WIN1250
COLLATE PXW_CSY ;

-- In time of creating table, we presume 10 characters should be enough

CREATE TABLE NEW_TABLE (
ID INTEGER,
FIELD D_VARCHAR10);

-- We create some read procedure

CREATE OR ALTER PROCEDURE NEW_PROC
RETURNS(
FIELD TYPE OF COLUMN NEW_TABLE.FIELD)
AS
BEGIN
FOR SELECT FIELD
FROM NEW_TABLE
INTO :FIELD
DO
SUSPEND;
END;

-- Now, we need more space

ALTER TABLE NEW_TABLE ALTER FIELD TYPE D_VARCHAR40;

-- Let's insert some long record

INSERT INTO NEW_TABLE
(ID,FIELD) VALUES (1,'I am longer than 10 characters');

-- And then we try to read all records of the table using the read procedure

SELECT * FROM NEW_PROC;

-- So we get this exception

/*Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
At procedure 'NEW_PROC' line: 6, col: 3.*/

I can mention that, Backup and Restore doesn't help.

I am aware of that, some software like IB Expert offer to recompile all depenadant procedures,
but it gets complicated in some situations like differential updating on customers databases.

=>

All dependant procedures using TYPE OF COLUMN, where column is defined by domain, need to be manually recompiled, when the definition of domain is changed.

Example:

-- Let's have 2 different domains
CREATE DOMAIN D_VARCHAR10 AS
VARCHAR(10) CHARACTER SET WIN1250
COLLATE PXW_CSY ;

CREATE DOMAIN D_VARCHAR40 AS
VARCHAR(40) CHARACTER SET WIN1250
COLLATE PXW_CSY ;

-- In time of creating table, we presume 10 characters should be enough
CREATE TABLE NEW_TABLE (
ID INTEGER,
FIELD D_VARCHAR10);

-- We create some read procedure
CREATE OR ALTER PROCEDURE NEW_PROC
RETURNS(
FIELD TYPE OF COLUMN NEW_TABLE.FIELD)
AS
BEGIN
FOR SELECT FIELD
FROM NEW_TABLE
INTO :FIELD
DO
SUSPEND;
END;

-- Now, we need more space
ALTER TABLE NEW_TABLE ALTER FIELD TYPE D_VARCHAR40;

-- Let's insert some long record
INSERT INTO NEW_TABLE
(ID,FIELD) VALUES (1,'I am longer than 10 characters');

-- And then we try to read all records of the table using the read procedure
SELECT * FROM NEW_PROC;

-- So we get this exception
/*Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
At procedure 'NEW_PROC' line: 6, col: 3.*/

I can mention that, Backup and Restore doesn't help.

I am aware of that, some software like IB Expert offer to recompile all depenadant procedures, but it gets complicated in some situations like differential updating on customers databases.

@firebird-automations
Copy link
Collaborator Author

Commented by: Michal Dohnal (mixerito)

Reconnecting was the first desperate step, followed by the Backup and Restore, I had tried before i published this issue.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

> Reconnecting was the first desperate step

The issue is not reproducible by me.

@firebird-automations
Copy link
Collaborator Author

Commented by: Michal Dohnal (mixerito)

It's strange. Has anybody else tried to reproduce this issue ?

I and co-workers can raise it repeatedly.

Would you need some another information necessary to resolve ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

I've just tried the provided test case. The issue is reproducible if everything runs in the same connection. If you reconnect after getting the mentioned error, the problem disappears.

I was testing with Classic Server.

@firebird-automations
Copy link
Collaborator Author

Commented by: Michal Dohnal (mixerito)

Ok, sorry,
you are right.

I've simplified the example.

In the mentioned situation, where the reconnect and Backup and Restore doesn't help, was a little change.

Instead of using

ALTER TABLE NEW_TABLE ALTER FIELD TYPE D_VARCHAR40;

i really used direct change in RDB table (as provided by IB Expert during manual change):

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = 'D_VARCHAR40'
where (RDB$FIELD_NAME = 'FIELD') and
(RDB$RELATION_NAME = 'NEW_TABLE');

So this is the exact problem.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

I'd say that we shouldn't care about direct system table updates ;-)

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

No branches or pull requests

2 participants