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

UPDATE OR INSERT with subquery used in the MATCHING part doesn't insert record [CORE2118] #2550

Closed
firebird-automations opened this issue Oct 11, 2008 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @asfernandes

Is related to QA270

I'm creating this ticket just for documentation purposes. It doesn't require any code change, because fix for it is the same one of CORE2117.

Reported on a Russian forum:

SET SQL DIALECT 3;

SET NAMES WIN1251;

CREATE DATABASE 'localhost:C:\Firebird2.1\Base\TEST.FDB'
USER 'SYSDBA' PASSWORD '1'
PAGE_SIZE 16384
DEFAULT CHARACTER SET WIN1251;

CREATE GENERATOR GEN_MACRO_ID;

CREATE TABLE MACRO (
ID INTEGER NOT NULL,
T1 INTEGER,
CODE VARCHAR(50)
);

CREATE TABLE PARAM (
ID INTEGER NOT NULL,
P1 INTEGER
);

ALTER TABLE MACRO ADD CONSTRAINT PK_MACRO PRIMARY KEY (ID);
ALTER TABLE PARAM ADD CONSTRAINT PK_PARAM PRIMARY KEY (ID);

ALTER TABLE MACRO ADD CONSTRAINT FK_MACRO_1 FOREIGN KEY (T1) REFERENCES PARAM (ID);

SET TERM ^ ;

/* Trigger: MACRO_BI */
CREATE TRIGGER MACRO_BI FOR MACRO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (http://NEW.ID IS NULL) THEN
http://NEW.ID = GEN_ID(GEN_MACRO_ID,1);
END
^

SET TERM ; ^

INSERT INTO PARAM (ID, P1) VALUES (2, 11);

-- This command doesn't insert a record, but should
update or insert into MACRO (T1, CODE)
values ( (select ID from PARAM where P1 = 11), 'fsdfdsf')
matching (T1);

Commits: 2223e6a

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 2.1.2 [ 10270 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

Q/A test ok

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA270 [ QA270 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test => Done successfully

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