Differences in field metadata descriptions between Firebird 2.5 and Firebird 4

[medi6]

Hi everyone,

We're working on a big soft in Java, and we want to migrate from FB2.5 to FB4.0.
We are using our self home made ORM to create some beans.
This ORM is using resultset metadata to match a class with a table name.
The problem is that we've got some differencies in metadata when we're using sub queries.

The query on employee.db :
SELECT * FROM EMPLOYEE LEFT OUTER JOIN (SELECT MAX(SALARY_HISTORY.NEW_SALARY) as SALARY,SALARY_HISTORY.EMP_NO FROM SALARY_HISTORY GROUP BY SALARY_HISTORY.EMP_NO ) E ON E.EMP_NO=EMPLOYEE.EMP_NO

The result on FB 2.5 is ok :
Field #1: EMPLOYEE.EMP_NO Alias:EMP_NO Type:SMALLINT
Field #2: EMPLOYEE.FIRST_NAME Alias:FIRST_NAME Type:STRING(15)
Field #3: EMPLOYEE.LAST_NAME Alias:LAST_NAME Type:STRING(20)
Field #4: EMPLOYEE.PHONE_EXT Alias:PHONE_EXT Type:STRING(4)
Field #5: EMPLOYEE.HIRE_DATE Alias:HIRE_DATE Type:TIMESTAMP
Field #6: EMPLOYEE.DEPT_NO Alias:DEPT_NO Type:STRING(3)
Field #7: EMPLOYEE.JOB_CODE Alias:JOB_CODE Type:STRING(5)
Field #8: EMPLOYEE.JOB_GRADE Alias:JOB_GRADE Type:SMALLINT
Field #9: EMPLOYEE.JOB_COUNTRY Alias:JOB_COUNTRY Type:STRING(15)
Field #10: EMPLOYEE.SALARY Alias:SALARY Type:NUMERIC(18,2)
Field #11: EMPLOYEE.FULL_NAME Alias:FULL_NAME Type:STRING(37)
Field #12: .MAX Alias:SALARY Type:DOUBLE PRECISION
Field #13: SALARY_HISTORY.EMP_NO Alias:EMP_NO Type:SMALLINT

On FB4.0 :
Field #1: EMPLOYEE.EMP_NO Alias:EMP_NO Type:SMALLINT
Field #2: EMPLOYEE.FIRST_NAME Alias:FIRST_NAME Type:STRING(15)
Field #3: EMPLOYEE.LAST_NAME Alias:LAST_NAME Type:STRING(20)
Field #4: EMPLOYEE.PHONE_EXT Alias:PHONE_EXT Type:STRING(4)
Field #5: EMPLOYEE.HIRE_DATE Alias:HIRE_DATE Type:TIMESTAMP
Field #6: EMPLOYEE.DEPT_NO Alias:DEPT_NO Type:STRING(3)
Field #7: EMPLOYEE.JOB_CODE Alias:JOB_CODE Type:STRING(5)
Field #8: EMPLOYEE.JOB_GRADE Alias:JOB_GRADE Type:SMALLINT
Field #9: EMPLOYEE.JOB_COUNTRY Alias:JOB_COUNTRY Type:STRING(15)
Field #10: EMPLOYEE.SALARY Alias:SALARY Type:NUMERIC(18,2)
Field #11: EMPLOYEE.FULL_NAME Alias:FULL_NAME Type:STRING(37)
Field #12: . Alias:SALARY Type:DOUBLE PRECISION
Field #13: . Alias:EMP_NO Type:SMALLINT

Thank you very much,

[reevespaul]

It looks as if this change was introduced in Firebird 3.0

Is it a bug? I think so.

A simple left join produces this:

Preparing statement: SELECT EMPLOYEE.*, SALARY_HISTORY.NEW_SALARY, SALARY_HISTORY.EMP_NO 
FROM EMPLOYEE
LEFT OUTER JOIN SALARY_HISTORY ON SALARY_HISTORY.EMP_NO=EMPLOYEE.EMP_NO
Statement prepared (elapsed time: 0.000s).
Field #01: EMPLOYEE.EMP_NO Alias:EMP_NO Type:SMALLINT
Field #02: EMPLOYEE.FIRST_NAME Alias:FIRST_NAME Type:STRING(15)
Field #03: EMPLOYEE.LAST_NAME Alias:LAST_NAME Type:STRING(20)
Field #04: EMPLOYEE.PHONE_EXT Alias:PHONE_EXT Type:STRING(4)
Field #05: EMPLOYEE.HIRE_DATE Alias:HIRE_DATE Type:TIMESTAMP
Field #06: EMPLOYEE.DEPT_NO Alias:DEPT_NO Type:STRING(3)
Field #07: EMPLOYEE.JOB_CODE Alias:JOB_CODE Type:STRING(5)
Field #08: EMPLOYEE.JOB_GRADE Alias:JOB_GRADE Type:SMALLINT
Field #09: EMPLOYEE.JOB_COUNTRY Alias:JOB_COUNTRY Type:STRING(15)
Field #10: EMPLOYEE.SALARY Alias:SALARY Type:NUMERIC(18,2)
Field #11: EMPLOYEE.FULL_NAME Alias:FULL_NAME Type:STRING(37)
Field #12: SALARY_HISTORY.NEW_SALARY Alias:NEW_SALARY Type:DOUBLE PRECISION
Field #13: SALARY_HISTORY.EMP_NO Alias:EMP_NO Type:SMALLINT
PLAN JOIN (EMPLOYEE NATURAL, SALARY_HISTORY INDEX (RDB$FOREIGN21))

and we can see that both table names are visible in the xsqlda.

So it is logical to conclude that the table name of the sub query should also appear.

[medi6]

Up

[aafemt]

@medi6 Feel free to prepare a Pull Request with fix.

[dyemanov]

Suggested patch is attached:
gh-7461.patch.txt

[medi6]

@dyemanov thank you very much.
My first tests with your patch are Ok, it works. I'll try more and come back to confirm this to you.
Do you know if it will be included in next Firebird 4 release ? I've seen that there is a lot of bugs fixes in v4 release branch.

Thank you so much, it will unlock our migration project.

[dyemanov]

I'd like someone else to review it before committing. And of course wider testing would be useful.

[asfernandes]

I'm not sure it's correct to have field N with NAME = 'CONSTANT' in this case:

select * from rdb$database r1 left join (select r2.*, 1 n from rdb$database r2) on 1 = 1;

[dyemanov]

I'm not sure either. But it's the same naming as it was just a plain select (without a derived table), so why not?

[dyemanov]

What I'm also not sure is whether we need to dig into the DsqlAliasNode like genBlr() does:

	ValueExprNode* val = value;

	while (nodeIs<DsqlAliasNode>(val))
		val = nodeAs<DsqlAliasNode>(val)->value;

[medi6]

Hi, after a few weeks of use with our own patched version, it seems to be Ok. Thanks for all.