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

Inconsistent domain's constraint validation in PSQL [CORE3545] #3901

Closed
firebird-automations opened this issue Jul 3, 2011 · 10 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @asfernandes

Is related to QA567
Relate to CORE3947

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!

Commits: 046355b d8a30e8 FirebirdSQL/fbt-repository@09988b3

====== Test Details ======

Tested OK 24.08.2015 ( checked block of statements after message http://sourceforge.net/p/firebird/code/62113 )

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA567 [ QA567 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Reopened [ 4 ] => Reopened [ 4 ]

QA Status: Done with caveats

Test Details: TODO: re-implement after resolving issue related to 'NUMERIC-SORT=1'

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue relate to CORE3947 [ CORE3947 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC 1 [ 10584 ]

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done with caveats => Done successfully

Test Details: TODO: re-implement after resolving issue related to 'NUMERIC-SORT=1' => Tested OK 24.08.2015 ( checked block of statements after message http://sourceforge.net/p/firebird/code/62113 )

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

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