Incorrect number of affected rows for UPDATE against VIEW created WITH CHECK OPTION [CORE4870]

[firebird-automations]

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;

      ID            X

============ ============
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

[firebird-automations]

Modified by: @dyemanov

Link: This issue is related to CORE578 [ CORE578 ]

[firebird-automations]

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.

[firebird-automations]

Commented by: @dyemanov

Sorry, CHECK OPTION surely creates BEFORE triggers, not AFTER.

[firebird-automations]

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

[firebird-automations]

Modified by: @asfernandes

Version: 3.0 RC 1 [ 10584 ] =>

[firebird-automations]

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC 1 [ 10584 ]

[firebird-automations]

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

[firebird-automations]

Modified by: @pavel-zotov

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

[firebird-automations]

Commented by: @dyemanov

Backported to v2.5.6.

[firebird-automations]

Modified by: @dyemanov

Fix Version: 2.5.6 [ 10721 ]

[firebird-automations]

Modified by: @dyemanov

Component: 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 ] =>