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

Creating table with computed fields containing "SELECT FIRST" produces corrupted result [CORE4027] #4357

Closed
firebird-issue-importer opened this issue Jan 4, 2013 · 12 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Jan 4, 2013

Submitted by: Hanno Nagland (hanno)

Creating table with computed fields that are containing "SELECT FIRST" produces corrupted result:

Sample (using Firebird sample database EMPLOYEE.FDB):

CREATE TABLE TEST (
CUST_NO INTEGER NOT NULL,
FIRST_PO_NUMBER COMPUTED BY ((SELECT FIRST 1 PO_NUMBER FROM FROM SALES WHERE SALES.CUST_NO=TEST.CUST_NO ORDER BY ORDER_DATE)),
CUSTOMER_NAME COMPUTED BY ((SELECT CUSTOMER FROM CUSTOMER WHERE CUSTOMER.CUST_NO = TEST.CUST_NO))
);

Resulting table DLL (first computed column source contains all following definitions, second computed column source contains ";"):

CREATE TABLE TEST (
CUST_NO INTEGER NOT NULL,
FIRST_PO_NUMBER COMPUTED BY ((SELECT FIRST 1 PO_NUMBER FROM SALES WHERE SALES.CUST_NO=TEST.CUST_NO ORDER BY ORDER_DATE)),
CUSTOMER_NAME COMPUTED BY ((SELECT CUSTOMER FROM CUSTOMER WHERE CUSTOMER.CUST_NO = TEST.CUST_NO))
);,
CUSTOMER_NAME COMPUTED BY ;
);

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 4, 2013

Modified by: Hanno Nagland (hanno)

description: Creating table with computed fields that are containing "SELECT FIRST" produces corrupted result:

Sample (using Firebird sale database EMPLOYEE.FDB):

CREATE TABLE TEST (
CUST_NO INTEGER NOT NULL,
FIRST_ORDER COMPUTED BY ((SELECT FIRST 1 ORDER_DATE FROM SALES WHERE SALES.CUST_NO=TEST.CUST_NO ORDER BY ORDER_DATE)),
CUSTOMER_NAME COMPUTED BY ((SELECT CUSTOMER FROM CUSTOMER WHERE CUSTOMER.CUST_NO = TEST.CUST_NO))
);

Resulting table DLL (first computed column source contains all following definitions, second computed column source contains ";"):

CREATE TABLE TEST (
CUST_NO INTEGER NOT NULL,
FIRST_PO_NUMBER COMPUTED BY ((SELECT FIRST 1 PO_NUMBER FROM SALES WHERE SALES.CUST_NO=TEST.CUST_NO ORDER BY ORDER_DATE)),
CUSTOMER_NAME COMPUTED BY ((SELECT CUSTOMER FROM CUSTOMER WHERE CUSTOMER.CUST_NO = TEST.CUST_NO))
);,
CUSTOMER_NAME COMPUTED BY ;
);

=>

Creating table with computed fields that are containing "SELECT FIRST" produces corrupted result:

Sample (using Firebird sample database EMPLOYEE.FDB):

CREATE TABLE TEST (
CUST_NO INTEGER NOT NULL,
FIRST_ORDER COMPUTED BY ((SELECT FIRST 1 ORDER_DATE FROM SALES WHERE SALES.CUST_NO=TEST.CUST_NO ORDER BY ORDER_DATE)),
CUSTOMER_NAME COMPUTED BY ((SELECT CUSTOMER FROM CUSTOMER WHERE CUSTOMER.CUST_NO = TEST.CUST_NO))
);

Resulting table DLL (first computed column source contains all following definitions, second computed column source contains ";"):

CREATE TABLE TEST (
CUST_NO INTEGER NOT NULL,
FIRST_PO_NUMBER COMPUTED BY ((SELECT FIRST 1 PO_NUMBER FROM SALES WHERE SALES.CUST_NO=TEST.CUST_NO ORDER BY ORDER_DATE)),
CUSTOMER_NAME COMPUTED BY ((SELECT CUSTOMER FROM CUSTOMER WHERE CUSTOMER.CUST_NO = TEST.CUST_NO))
);,
CUSTOMER_NAME COMPUTED BY ;
);

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 4, 2013

Modified by: Hanno Nagland (hanno)

description: Creating table with computed fields that are containing "SELECT FIRST" produces corrupted result:

Sample (using Firebird sample database EMPLOYEE.FDB):

CREATE TABLE TEST (
CUST_NO INTEGER NOT NULL,
FIRST_ORDER COMPUTED BY ((SELECT FIRST 1 ORDER_DATE FROM SALES WHERE SALES.CUST_NO=TEST.CUST_NO ORDER BY ORDER_DATE)),
CUSTOMER_NAME COMPUTED BY ((SELECT CUSTOMER FROM CUSTOMER WHERE CUSTOMER.CUST_NO = TEST.CUST_NO))
);

Resulting table DLL (first computed column source contains all following definitions, second computed column source contains ";"):

CREATE TABLE TEST (
CUST_NO INTEGER NOT NULL,
FIRST_PO_NUMBER COMPUTED BY ((SELECT FIRST 1 PO_NUMBER FROM SALES WHERE SALES.CUST_NO=TEST.CUST_NO ORDER BY ORDER_DATE)),
CUSTOMER_NAME COMPUTED BY ((SELECT CUSTOMER FROM CUSTOMER WHERE CUSTOMER.CUST_NO = TEST.CUST_NO))
);,
CUSTOMER_NAME COMPUTED BY ;
);

=>

Creating table with computed fields that are containing "SELECT FIRST" produces corrupted result:

Sample (using Firebird sample database EMPLOYEE.FDB):

CREATE TABLE TEST (
CUST_NO INTEGER NOT NULL,
FIRST_PO_NUMBER COMPUTED BY ((SELECT FIRST 1 PO_NUMBER FROM FROM SALES WHERE SALES.CUST_NO=TEST.CUST_NO ORDER BY ORDER_DATE)),
CUSTOMER_NAME COMPUTED BY ((SELECT CUSTOMER FROM CUSTOMER WHERE CUSTOMER.CUST_NO = TEST.CUST_NO))
);

Resulting table DLL (first computed column source contains all following definitions, second computed column source contains ";"):

CREATE TABLE TEST (
CUST_NO INTEGER NOT NULL,
FIRST_PO_NUMBER COMPUTED BY ((SELECT FIRST 1 PO_NUMBER FROM SALES WHERE SALES.CUST_NO=TEST.CUST_NO ORDER BY ORDER_DATE)),
CUSTOMER_NAME COMPUTED BY ((SELECT CUSTOMER FROM CUSTOMER WHERE CUSTOMER.CUST_NO = TEST.CUST_NO))
);,
CUSTOMER_NAME COMPUTED BY ;
);

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 4, 2013

Commented by: Sean Leyne (seanleyne)

Did you use ISQL to apply the script to the database?

Perhaps the problem is with the tool which applied the script.

Did you try to add the second COMPUTED BY using an ALTER ADD script?

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 4, 2013

Commented by: @mrotteveel

(removed comment)

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 4, 2013

Commented by: Hanno Nagland (hanno)

Tested with IBExpert, UIB Interbase library and ISQL. Same result with all of them: RDB$FIELDS.RDB$COMPUTED_SOURCE will corrupted, but looks like RDB$COMPUTED_BLR is ok, as resulting table works as expected.

Adding both computed fields at same time also produces corrupted result:
CREATE TABLE TEST (CUST_NO INTEGER NOT NULL);
ALTER TABLE TEST add FIRST_PO_NUMBER COMPUTED BY ((SELECT FIRST 1 PO_NUMBER FROM SALES WHERE SALES.CUST_NO=TEST.CUST_NO ORDER BY ORDER_DATE)), ADD CUSTOMER_NAME COMPUTED BY ((SELECT CUSTOMER FROM CUSTOMER WHERE CUSTOMER.CUST_NO = TEST.CUST_NO));

Adding these computed fields separately works ok:
CREATE TABLE TEST (CUST_NO INTEGER NOT NULL);
ALTER TABLE TEST ADD FIRST_PO_NUMBER COMPUTED BY ((SELECT FIRST 1 PO_NUMBER FROM SALES WHERE SALES.CUST_NO=TEST.CUST_NO ORDER BY ORDER_DATE));
ALTER TABLE TEST ADD CUSTOMER_NAME COMPUTED BY ((SELECT CUSTOMER FROM CUSTOMER WHERE CUSTOMER.CUST_NO = TEST.CUST_NO));

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 4, 2013

Commented by: @asfernandes

Please test FB 3, which migh have this problem fixed.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 4, 2013

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 4, 2013

Commented by: Hanno Nagland (hanno)

Tested this case with FB 3 current snapshot: worked ok.
Is there any chance that this bug will fixed (backported) in FB 2.5.x version?

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 4, 2013

Commented by: @asfernandes

I may be wrong, but I think the fix depends on large changes in the parser, so no.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 5, 2013

Commented by: Hanno Nagland (hanno)

Interestingly resulting BLR is correct, only RDB$FIELDS.RDB$COMPUTED_SOURCE is corrupted...

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 23, 2013

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 29, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

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