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
Incorrect number of affected rows for UPDATE against VIEW created WITH CHECK OPTION [CORE4870] #5166
Comments
Commented by: @dyemanov AFAIU, this issue should affect all views having triggers. CHECK OPTION creates triggers for AFTER INSERT and AFTER UPDATE, this is why DELETE is not affected. |
Commented by: @dyemanov Sorry, CHECK OPTION surely creates BEFORE triggers, not AFTER. |
Modified by: @asfernandesassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Modified by: @asfernandesVersion: 3.0 RC 1 [ 10584 ] => |
Modified by: @asfernandesstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 RC 1 [ 10584 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: No test => Done successfully |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Commented by: @dyemanov Backported to v2.5.6. |
Modified by: @dyemanovFix Version: 2.5.6 [ 10721 ] |
Modified by: @dyemanovComponent: Engine [ 10000 ] summary: SET COUNT ON: issuing UPDATE on view which was created WITH CHECK OPTION produces wrong number of affected rows (doubled ?) => Incorrect number of affected rows for UPDATE against VIEW created WITH CHECK OPTION Component: ISQL [ 10003 ] => |
Submitted by: @pavel-zotov
Is related to CORE578
DDL:
####
C:\MIX\firebird\QA\fbt-repo\tmp>isql /3333:e30
Database: /3333:e30
SQL> recreate table test(id int, x int); commit;
SQL> recreate sequence g1; commit;
SQL> recreate sequence g2; commit;
SQL> insert into test select gen_id(g1, 1), gen_id(g2,1)*10 from rdb$types rows 10; commit;
SQL> select * from test;
============ ============
1 10
2 20
3 30
. . .
10 100
SQL> commit;
TEST.
#####
First, create view WITHOUT check option in order to compare further results:
SQL> recreate view v_test as select * from test where mod(id, 3)=0;
SQL> commit;
SQL> set count on;
SQL> update test set x=-x where id=3;
Records affected: 1
SQL> update v_test set x=-x where id=3;
Records affected: 1 -- OK
Second, change definition of VIEW by adding 'with check option' clause and repeat:
SQL> recreate view v_test as select * from test where mod(id, 3)=0 with check option;
SQL> commit;
SQL> update test set x=-x where id=3;
Records affected: 1
SQL> update v_test set x=-x where id=3;
Records affected: 2 -- <<<<<<<<<<<<<<<<<<<<<<< ?? <<<<<<<<<<<<<<<
-PS. No such effect for DELETE statement.
Commits: 9d0e4cb 35397f8 FirebirdSQL/fbt-repository@74e11fe
The text was updated successfully, but these errors were encountered: