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.

Sign up for GitHub

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

Jump to bottom

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

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

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

firebird-automations opened this issue Jan 4, 2013 · 12 comments
Assignees
@asfernandes
Labels
affect-version: 2.5.1 affect-version: 2.5.2 component: engine fix-version: 3.0 Alpha 1 priority: major qa: done successfully type: bug

Comments

@firebird-automations
Copy link
Collaborator

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

(removed comment)

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Hanno Nagland (hanno)

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

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
Assignees

@asfernandes asfernandes

Labels
affect-version: 2.5.1 affect-version: 2.5.2 component: engine fix-version: 3.0 Alpha 1 priority: major qa: done successfully type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@asfernandes @firebird-automations