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

trigger on view with union receive nulls [CORE188] #515

Closed
firebird-automations opened this issue Dec 6, 2001 · 6 comments
Closed

trigger on view with union receive nulls [CORE188] #515

firebird-automations opened this issue Dec 6, 2001 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @ibaseru

Attachments:
core-0188_sql_and_logs_in_FB_25_vs_30.7z

SFID: 489762#⁠
Submitted By: kdv

Vlad Filippov in russian IB conference reported interesting bug
with trigger on view with union.
Let's show script, you will find what is happening as a comments to it:

/* begin script */

CREATE TABLE A (ID INT);
CREATE TABLE B (ID INT);

COMMIT;

CREATE VIEW VW_C(ID, INCLUDED_IN_B)
AS
SELECT ID, CAST(0 AS INT)
FROM A
WHERE NOT EXISTS (SELECT ID FROM B WHERE ID = http://A.ID)

UNION ALL

SELECT ID, CAST(1 AS INT)
FROM B;

COMMIT;

SET TERM ^ ;

CREATE TRIGGER VW_C_BU FOR VW_C
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
IF (NEW.INCLUDED_IN_B = 1) THEN
BEGIN
IF (OLD.INCLUDED_IN_B = 0) THEN
INSERT INTO B(ID)
VALUES(http://NEW.ID);
END
ELSE
DELETE FROM B
WHERE ID = http://OLD.ID;
END^

SET TERM ; ^

COMMIT;

INSERT INTO A(ID) VALUES(1);
COMMIT;

/* SELECT * FROM VW_C; */
/* will return one record based on data in table A */

/* this update in theory will insert record into A and delete same
record from B, according to trigger source*/

UPDATE VW_C SET INCLUDED_IN_B = 1 WHERE ID = 1;

COMMIT;

/*END SCRIPT*/

Last update statement must insert record into table B. But this does not happen.
I've traced trigger with debug udf, and found that

1. trigger is being called (it can be checked by inserting exception as a first line
in trigger body)
2. all new. and old. values are NULLs. This was checked with udf Check_Point from
udfDemo, which output all passed values to console

All was tested with Firebird 1.0.0 builds 555 (RC1), 608 and 641
for Windows, W2K SP2, Dialect 3 database.

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

Passed on: WI-V3.0.0.32487, WI-T4.0.0.141 -- works fine.
On WI-V2.5.6.27001 issues wrong result thus min_version (for now) is 3.0.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10212 ] => Firebird [ 14417 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Attachment: core-0188_sql_and_logs_in_FB_25_vs_30.7z [ 12920 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Consider script from attach (" core-0188_sql_and_logs_in_FB_25_vs_30.7z " ): we have a trouble in WI-V2.5.6.26980.

Following is the source of trigger defined for updatable view, plus debug RDB$SET_CONTEXT-statements for displaying values after statement will finish:

create trigger v\_test\_bu for v\_test active before update position 0 as
  declare o int;
begin
  rdb$set\_context\('USER\_SESSION','trigger\_sees\_old\_id', <http://old.id>\);
  rdb$set\_context\('USER\_SESSION','trigger\_sees\_old\_of\_b1', old\.row\_from\_table\_b\);
  rdb$set\_context\('USER\_SESSION','trigger\_sees\_new\_id', <http://new.id>\);
  rdb$set\_context\('USER\_SESSION','trigger\_sees\_new\_of\_b', new\.row\_from\_table\_b\);
  if \(new\.row\_from\_table\_b = 1\) then
      begin
          rdb$set\_context\('USER\_SESSION','trigger\_sees\_old\_of\_b2', old\.row\_from\_table\_b\);
          if \(old\.row\_from\_table\_b = 0\) then
          begin
              rdb$set\_context\('USER\_SESSION','trigger\_sees\_old\_of\_b3', old\.row\_from\_table\_b\);
              execute statement \( 'insert into test\_b\(id\) values\(?\)'\) \(<http://new.id>\);
              execute statement \( 'delete from test\_a where id = ?' \) \(<http://new.id>\);
              rdb$set\_context\('USER\_SESSION','trigger\_DID\_its\_job', <http://new.id>\);
          end
      end
  else
      delete from test\_b
      where id = <http://old.id>;
end

===

If query to view returns:
ID ROW_FROM_TABLE_B
=== ================
1 0

-- then it means that tables test_a and test_b are:

select \* from test\_a;

Records affected: 0

select \* from test\_b;

      ID 

============
1

Records affected: 1

Further, when trigger "v_test_bu" fires before statement:

update v_test set row_from_table_b = 1 where id = 1;

-- it must get old.row_from_table_b = 0.

But one may see that in 2.5 value of old.row_from_table_b:
1) is WRONG when control fall inside this trigger but not yet processes old.row_from_table_b in conditional statement ("IF ...")
2) is changed inside this trigger in this statement: "if (old.row_from_table_b = 0) then" (!!)

This is debug info that will be produced when run script on 2.5:

CTX_NAME CTX_VAL
============================== ==========
trigger_DID_its_job 1
trigger_sees_new_id 1
trigger_sees_new_of_b 1
trigger_sees_old_id 1
trigger_sees_old_of_b1 1 <<< ???
trigger_sees_old_of_b2 1 <<< ???
trigger_sees_old_of_b3 0 <<< ?!?!?!

Compare with result in 3.0:

CTX_NAME CTX_VAL
============================== ==========
trigger_DID_its_job 1
trigger_sees_new_id 1
trigger_sees_new_of_b 1
trigger_sees_old_id 1
trigger_sees_old_of_b1 0 -- OK, here and after
trigger_sees_old_of_b2 0
trigger_sees_old_of_b3 0

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Deferred

Test Details: Waiting for issue 16/Mar/16 10:10 AM

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Deferred => Done successfully

Test Details: Waiting for issue 16/Mar/16 10:10 AM => Passed on: WI-V3.0.0.32487, WI-T4.0.0.141 -- works fine.
On WI-V2.5.6.27001 issues wrong result thus min_version (for now) is 3.0.

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

No branches or pull requests

2 participants