[
http://tracker.firebirdsql.org/browse/CORE-3545?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pavel Zotov reopened CORE-3545:
-------------------------------
Following script and its output shows that when domain based on collation which
was created with attrubite 'NUMERIC-SORT=1' than some values can be pass
through limitation od domain definition:
===
set echo on;
set term ^;
execute block as
begin
begin execute statement 'drop table test'; when any do begin end end
begin execute statement 'drop domain dm_nums'; when any do begin end end
begin execute statement 'drop collation co_nums'; when any do begin end
end
end
^ set term ;^
commit;
show version;
set list on;
create collation co_nums for utf8 from unicode 'NUMERIC-SORT=1';
commit;
create domain dm_nums varchar(3) character set utf8 check (value < '50')
collate co_nums;
commit;
create table test(id int, n dm_nums);
commit;
show collation;
show domain;
show table test;
insert into test values(1, 4);
insert into test values(2, 399);
insert into test values(2, 50);
insert into test values(3, '4');
insert into test values(3,'399');
insert into test values(4, '50');
select * from test;
commit;
set term ^;
execute block returns(o1 dm_nums, o2 dm_nums, o3 dm_nums) as
begin
o1 = '4';
o2 = '399';
o3 = '50';
suspend;
end
^
execute block returns(o1 dm_nums, o2 dm_nums, o3 dm_nums) as
begin
o1 = 4;
o2 = 399;
o3 = 50;
suspend;
end
^
set term ;^
===
STDOUT + STDERR in WI-T3.0.0.31844, starting from line 'show collation':
===
show collation;
CO_NUMS, CHARACTER SET UTF8, FROM EXTERNAL ('UNICODE'), PAD SPACE,
'COLL-VERSION=58.0.6.50;NUMERIC-SORT=1'
show domain;
DM_NUMS
show table test;
ID INTEGER Nullable
N (DM_NUMS) VARCHAR(3) CHARACTER SET UTF8
Nullable
check (value < '50')
COLLATE CO_NUMS
insert into test values(1, 4);
insert into test values(2, 399);
insert into test values(2, 50);
Statement failed, SQLSTATE = 23000
validation error for column "TEST"."N", value "50"
After line 29 in file c3545.sql
insert into test values(3, '4');
insert into test values(3,'399');
insert into test values(4, '50');
Statement failed, SQLSTATE = 23000
validation error for column "TEST"."N", value "50"
After line 33 in file c3545.sql
select * from test;
ID 1
N 4
ID 2
N 399
ID 3
N 4
ID 3
N 399
commit;
set term ^;
execute block returns(o1 dm_nums, o2 dm_nums, o3 dm_nums) as
begin
o1 = '4';
o2 = '399';
o3 = '50';
suspend;
end
^
Statement failed, SQLSTATE = 42000
validation error for variable O3, value "50"
After line 41 in file c3545.sql
execute block returns(o1 dm_nums, o2 dm_nums, o3 dm_nums) as
begin
o1 = 4;
o2 = 399;
o3 = 50;
suspend;
end
^
Statement failed, SQLSTATE = 42000
validation error for variable O3, value "50"
After line 49 in file c3545.sql
set term ;^
===
Compare with WI-V2.5.5.26871:
===
show collation;
CO_NUMS, CHARACTER SET UTF8, FROM EXTERNAL ('UNICODE'), PAD SPACE,
'NUMERIC-SORT=1'
show domain;
DM_NUMS
show table test;
ID INTEGER Nullable
N (DM_NUMS) VARCHAR(3) CHARACTER SET UTF8
Nullable
check (value < '50')
COLLATE CO_NUMS
insert into test values(1, 4);
insert into test values(2, 399);
Statement failed, SQLSTATE = 23000
validation error for column "TEST"."N", value "399"
After line 28 in file c3545.sql
insert into test values(2, 50);
Statement failed, SQLSTATE = 23000
validation error for column "TEST"."N", value "50"
After line 29 in file c3545.sql
insert into test values(3, '4');
insert into test values(3,'399');
Statement failed, SQLSTATE = 23000
validation error for column "TEST"."N", value "399"
After line 32 in file c3545.sql
insert into test values(4, '50');
Statement failed, SQLSTATE = 23000
validation error for column "TEST"."N", value "50"
After line 33 in file c3545.sql
select * from test;
ID 1
N 4
ID 3
N 4
commit;
set term ^;
execute block returns(o1 dm_nums, o2 dm_nums, o3 dm_nums) as
begin
o1 = '4';
o2 = '399';
o3 = '50';
suspend;
end
^
Statement failed, SQLSTATE = 42000
validation error for variable O3, value "50"
After line 41 in file c3545.sql
execute block returns(o1 dm_nums, o2 dm_nums, o3 dm_nums) as
begin
o1 = 4;
o2 = 399;
o3 = 50;
suspend;
end
^
Statement failed, SQLSTATE = 42000
validation error for variable O2, value "399"
After line 49 in file c3545.sql
set term ;^
===
Note: value "399" was denied in 2.5 but not in 3.0.
> Inconsistent domain's constraint validation in PSQL
> ---------------------------------------------------
>
> Key: CORE-3545
> URL: http://tracker.firebirdsql.org/browse/CORE-3545
> Project: Firebird Core
> Issue Type: Bug
> Components: Engine
> Affects Versions: 3.0 Initial, 2.5.0
> Reporter: Adriano dos Santos Fernandes
> Assignee: Adriano dos Santos Fernandes
> Fix For: 3.0 Alpha 1
>
>
> The domain's check constraint validation is done using the type of the
> expression, instead of the type of the variable.
> Test case:
> create domain dx varchar(2) check (value < '5')!
> -- No error: ok
> execute block as
> declare v1 dx = '40';
> begin
> end!
> -- Error: not ok - validation error for variable V1, value "40"
> execute block as
> declare v1 dx = 40;
> begin
> end!
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
