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
Comments
Modified by: @pcisarWorkflow: jira [ 10212 ] => Firebird [ 14417 ] |
Modified by: @pavel-zotovQA Status: No test |
Modified by: @pavel-zotovAttachment: core-0188_sql_and_logs_in_FB_25_vs_30.7z [ 12920 ] |
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:
=== If query to view returns: -- then it means that tables test_a and test_b are:
Records affected: 0
============ Records affected: 1Further, 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: This is debug info that will be produced when run script on 2.5: CTX_NAME CTX_VAL Compare with result in 3.0: CTX_NAME CTX_VAL |
Modified by: @pavel-zotovstatus: Closed [ 6 ] => Closed [ 6 ] QA Status: No test => Deferred Test Details: Waiting for issue 16/Mar/16 10:10 AM |
Modified by: @pavel-zotovstatus: 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. |
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.
The text was updated successfully, but these errors were encountered: