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

Extending varchar domain leaves old smaller size in PSQL BLR [CORE5812] #6074

Open
firebird-automations opened this issue May 3, 2018 · 0 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pcisar

Consistently reproducible test case:

create domain dmn varchar(1);
commit;
create table tbl(fld dmn);
commit;

set term ^;
create procedure sp
returns (fld dmn)
as begin
for select case when 1=1 then fld else 'x' end from tbl into :fld
do begin
suspend;
end
end
^
set term ;^
commit;

alter domain dmn type varchar(2);
commit;

insert into tbl (fld) values ('12');
commit;

set term ^;
alter procedure sp
returns (fld dmn)
as begin
select fld from tbl into :fld; /* select field uses new domain type varchar(2) */
suspend;
end
^
set term ;^
commit;

select * from sp; /*no exception*/
commit;

set term ^;
alter procedure sp
returns (fld dmn)
as begin
select case when 1=1 then fld else 'x' end from tbl into :fld; /* case when fld still uses old domain type varchar(1) in BLR */
suspend;
end
^
set term ;^
commit;

select * from sp; /*string truncation*/
commit;

/*reconnect*/

select * from sp; /*still string truncation*/
commit;

set term ^;
alter procedure sp
returns (fld dmn)
as begin
select case when 1=1 then fld else 'x' end from tbl into :fld; /* after reconnect 'case when fld' starts to use new domain type varchar(2) in BLR */
suspend;
end
^
set term ;^
commit;

select * from sp; /*no more exception*/
commit;

drop procedure sp;
drop table tbl;
drop domain dmn;
commit;

--- Initial comment from Vlad Khorsun:

The issue is related with metadata caching (it was obvious). There is no cache of domains, but there is cache of relations and cached relations
(of course) have fields with data types. When domain definition changed, relations in cache are not invalidated. Thus, SQL parser uses old data
type (not domain based, just raw data type) when handle procedure text. In particular, it CAST result of CASE expression to the VARCHAR(1) data
type. You may see in generated BLR something like

...
blr_cast, blr_varying2, 0,0, 1,0,
blr_value_if,
...

Later, when engine executed query, it knows real data type for the relation
field (VARCHAR(2)) but should CAST it to the VARCHAR(1) - here it raised
"string right truncation" error.

So far i see no quick way to fix it, sorry

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

1 participant