Issue Details (XML | Word | Printable)

Key: CORE-3505
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Michal Dohnal
Votes: 0
Watchers: 3
Operations

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

Non-flexible dependance of procedures using TYPE OF COLUMN

Created: 31/May/11 04:45 PM   Updated: 02/Jun/11 02:27 PM
Component/s: None
Affects Version/s: 2.5.0
Fix Version/s: None

Time Tracking:
Not Specified

Planning Status: Unspecified


 Description  « Hide
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   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dimitry Sibiryakov added a comment - 31/May/11 04:51 PM
Changing definition of domain must be denied if this domain is used anywhere (including via TYPE OF).

Dmitry Yemanov added a comment - 31/May/11 04:59 PM
Does this issue exist for TYPE OF COLUMN *input* parameters?

Michal Dohnal added a comment - 31/May/11 05:13 PM
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.

Vlad Khorsun added a comment - 31/May/11 05:40 PM
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.

Michal Dohnal added a comment - 31/May/11 08:40 PM
Reconnecting was the first desperate step, followed by the Backup and Restore, I had tried before i published this issue.

Adriano dos Santos Fernandes added a comment - 02/Jun/11 12:31 AM
> Reconnecting was the first desperate step

The issue is not reproducible by me.

Michal Dohnal added a comment - 02/Jun/11 05:56 AM
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 ?

Dmitry Yemanov added a comment - 02/Jun/11 06:12 AM - edited
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.

Michal Dohnal added a comment - 02/Jun/11 10:49 AM
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.

Dmitry Yemanov added a comment - 02/Jun/11 02:27 PM
I'd say that we shouldn't care about direct system table updates ;-)