Issue Details (XML | Word | Printable)

Key: CORE-2118
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Adriano dos Santos Fernandes
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

UPDATE OR INSERT with subquery used in the MATCHING part doesn't insert record

Created: 11/Oct/08 12:36 PM   Updated: 23/Feb/11 10:55 AM
Component/s: None
Affects Version/s: 2.1.0, 2.1.1
Fix Version/s: 2.1.2

Time Tracking:
Not Specified

Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
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 CORE-2117.

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 (NEW.ID IS NULL) THEN
    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);


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Philippe Makowski added a comment - 10/Jun/09 09:57 AM
Q/A test ok