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

INSERT into updatable view without explicit field list failed [CORE5454] #5725

Closed
firebird-issue-importer opened this issue Jan 16, 2017 · 7 comments

Comments

@firebird-issue-importer

Submitted by: @romansimakov

CREATE DATABASE 'upd_view.fdb' PAGE_SIZE 16384;

CREATE TABLE DOC1 (ID INTEGER NOT NULL,
NAME VARCHAR(1024),
DATA VARCHAR(1024),
PRIMARY KEY (ID));

CREATE TABLE DOC2 (ID INTEGER NOT NULL,
NAME VARCHAR(1024),
DATA VARCHAR(1024),
PRIMARY KEY (ID));

CREATE VIEW V (ID, NAME, DATA)
AS
SELECT * FROM DOC1
UNION ALL
SELECT * FROM DOC2;

SET TERM ^;

CREATE TRIGGER V_INS FOR V BEFORE INSERT
AS
DECLARE VARIABLE I INTEGER;
BEGIN
I = MOD(http://NEW.ID, 2);
if (I = 0) THEN
INSERT INTO DOC1 VALUES (http://NEW.ID, http://NEW.NAME, NEW.DATA);
else if (I = 1) THEN
INSERT INTO DOC2 VALUES (http://NEW.ID, http://NEW.NAME, NEW.DATA);
END^

SET TERM ;^

COMMIT WORK;

INSERT INTO V (ID, NAME, DATA) VALUES (0, '0', '0');

INSERT INTO DOC1 VALUES (1, '1', '1');

INSERT INTO V VALUES (2, '2', '2'); -- << failed

Commits: 9b228fb

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 17, 2017

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 17, 2017

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 20, 2017

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 20, 2017

Modified by: @pavel-zotov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 4, 2017

Commented by: @dyemanov

Now we have a regression:

SQL> recreate table test(x int, y int);
SQL> recreate view v_test as select t.x, t.y, t.x+t.y as z from test t;
SQL> insert into v_test values(1,1);
Statement failed, SQLSTATE = 21S01
Dynamic SQL Error
-SQL error code = -804
-Count of read-write columns does not equal count of values

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 5, 2017

Commented by: @asfernandes

I'll check it.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 6, 2017

Commented by: @asfernandes

Dmitry, of course, fix for this bug would cause this behaviour (with is described as the bug itself).

In my opinion, usage of INSERT without field list is ad-hoc command, so I think we can live with it. It's a reason this was not backported.

Also, AFAIU absense of computed fields in implicit field/value list is a non-standard behavior.

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

Successfully merging a pull request may close this issue.

None yet
2 participants