Issue Details (XML | Word | Printable)

Key: CORE-3545
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Adriano dos Santos Fernandes
Votes: 0
Watchers: 3
Operations

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

Inconsistent domain's constraint validation in PSQL

Created: 03/Jul/11 03:07 AM   Updated: 24/Aug/15 04:38 PM
Component/s: Engine
Affects Version/s: 3.0 Initial, 2.5.0
Fix Version/s: 3.0 RC1

Issue Links:
Relate

QA Status: Done successfully
Test Details: Tested OK 24.08.2015 ( checked block of statements after message http://sourceforge.net/p/firebird/code/62113 )


 Description  « Hide
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!

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Zotov added a comment - 28/May/15 02:11 PM
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.