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

ResultSetMetaData.getColumnName(int) and getColumnLabel(int) not JDBC 4.0 compliant [JDBC162] #205

Closed
firebird-issue-importer opened this issue Aug 24, 2010 · 28 comments

Comments

@firebird-issue-importer

Submitted by: @mrotteveel

Relate to JDBC258
Relate to JDBC260
Relate to JDBC278
Relate to JDBC298
Relate to JDBC356

Attachments:
ColumnMetaDataTest.java
Teste22_mark.zip

The current behavior of ResultSetMetaData.getColumnName(int) and ResultSetMetaData.getColumnLabel(int) are not JDBC 4.0 compliant

Example query:
SELECT CUST_NO, CUSTOMER AS CST_NAME, 'A', 'B' AS FOURTH_COL FROM CUSTOMER

Expected:
getColumnName(1) => "CUST_NO"
getColumnLabel(1) => "CUST_NO"
getColumnName(2) => "CUSTOMER"
getColumnLabel(2) => "CST_NAME"
getColumnName(3) => ? (unspecified) ?
getColumnLabel(3) => ? (unspecified) ?
getColumnName(4) => ? (unspecified) ?
getColumnLabel(4) => "FOURTH_COL"

Actual behavior:
getColumnName(1) => "CUST_NO"
getColumnLabel(1) => "CUST_NO"
getColumnName(2) => "CST_NAME" <= INCORRECT
getColumnLabel(2) => "CST_NAME"
getColumnName(3) => "CONSTANT"
getColumnLabel(3) => "CONSTANT"
getColumnName(4) => "FOURTH_COL" <= INCORRECT
getColumnLabel(4) => "FOURTH_COL"

Unspecified behavior: the spec is unclear about the behavior for literal assignments without alias (column 3), or for literal assignment with alias (column 4)). In the case of getColumnName(4) it would probably need to be consistent with getColumnName(3), returning "FOURTH_COL" is clearly wrong. Some sources seem to suggest that the column name for literals should be emptry string (""), as well for columnLabel if the alias is not specified. The value currently returned for column 3 is set by the server (at least for the label).

Relevant documentation:

JDBC 4.0 API doc for ResultSetMetaData.getColumnLabel(int):
{quote}
Gets the designated column's suggested title for use in printouts and displays. The suggested title is usually specified by the SQL AS clause. If a SQL AS is not specified, the value returned from getColumnLabel will be the same as the value returned by the getColumnName method.
{quote}

JDBC 4.0 API doc for ResultSetMetaData.getColumnName(int):
{quote}
Get the designated column's name.
{quote}

Additional sources:
https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.apdv.java.doc/doc/c0052593.html

Commits: c689bf9

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 24, 2010

Commented by: @mrotteveel

Test for getColumnName and getColumnLabel

Currently failing:
* getColumnNameWithAlias

Also failing; cases for unspecified behavior (my expectation: "" (or empty string))
* getColumnLabelForLiteralNoAlias
* getColumnNameForLiteralNoAlias
* getColumnLabelForLiteralWithAlias

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 24, 2010

Modified by: @mrotteveel

Attachment: ColumnMetaDataTest.java [ 11721 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 24, 2010

Modified by: @mrotteveel

description: The current behavior of ResultSetMetaData.getColumnName(int) and ResultSetMetaData.getColumnLabel(int) are not JDBC 4.0 compliant

Example query:
SELECT CUST_NO, CUSTOMER AS CST_NAME, 'A', 'B' AS FOURTH_COL FROM CUSTOMER

Expected:
getColumnName(1) => "CUST_NO"
getColumnLabel(1) => "CUST_NO"
getColumnName(2) => "CUSTOMER"
getColumnLabel(2) => "CST_NAME"
getColumnName(3) => ? (unspecified) ?
getColumnLabel(3) => ? (unspecified) ?
getColumnName(4) => ? (unspecified) ?
getColumnLabel(4) => "FOURTH_COL"

Actual behavior:
getColumnName(1) => "CUST_NO"
getColumnLabel(1) => "CUST_NO"
getColumnName(2) => "CST_NAME" <= INCORRECT
getColumnLabel(2) => "CST_NAME"
getColumnName(3) => "CONSTANT"
getColumnLabel(3) => "CONSTANT"
getColumnName(4) => "FOURTH_COL" <= INCORRECT
getColumnLabel(4) => "FOURTH_COL"

Unspecified behavior: the spec is unclear about the behavior for literal assignments without alias (column 3), or for literal assignment with alias (column 4)). In the case of getColumnName(4) it would probably need to be consistent with getColumnName(3), returning "FOURTH_COL" is clearly wrong. Some sources seem to suggest that the column name for literals should be emptry string (""), as well for columnLabel if the alias is not specified. The value currently returned for column 3 is set by the server (at least for the labe).

Relevant documentation:

JDBC 4.0 API doc for ResultSetMetaData.getColumnLabel(int):
{quote}
Gets the designated column's suggested title for use in printouts and displays. The suggested title is usually specified by the SQL AS clause. If a SQL AS is not specified, the value returned from getColumnLabel will be the same as the value returned by the getColumnName method.
{quote}

JDBC 4.0 API doc for ResultSetMetaData.getColumnName(int):
{quote}
Get the designated column's name.
{quote}

Additional sources:
https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.apdv.java.doc/doc/c0052593.html

=>

The current behavior of ResultSetMetaData.getColumnName(int) and ResultSetMetaData.getColumnLabel(int) are not JDBC 4.0 compliant

Example query:
SELECT CUST_NO, CUSTOMER AS CST_NAME, 'A', 'B' AS FOURTH_COL FROM CUSTOMER

Expected:
getColumnName(1) => "CUST_NO"
getColumnLabel(1) => "CUST_NO"
getColumnName(2) => "CUSTOMER"
getColumnLabel(2) => "CST_NAME"
getColumnName(3) => ? (unspecified) ?
getColumnLabel(3) => ? (unspecified) ?
getColumnName(4) => ? (unspecified) ?
getColumnLabel(4) => "FOURTH_COL"

Actual behavior:
getColumnName(1) => "CUST_NO"
getColumnLabel(1) => "CUST_NO"
getColumnName(2) => "CST_NAME" <= INCORRECT
getColumnLabel(2) => "CST_NAME"
getColumnName(3) => "CONSTANT"
getColumnLabel(3) => "CONSTANT"
getColumnName(4) => "FOURTH_COL" <= INCORRECT
getColumnLabel(4) => "FOURTH_COL"

Unspecified behavior: the spec is unclear about the behavior for literal assignments without alias (column 3), or for literal assignment with alias (column 4)). In the case of getColumnName(4) it would probably need to be consistent with getColumnName(3), returning "FOURTH_COL" is clearly wrong. Some sources seem to suggest that the column name for literals should be emptry string (""), as well for columnLabel if the alias is not specified. The value currently returned for column 3 is set by the server (at least for the label).

Relevant documentation:

JDBC 4.0 API doc for ResultSetMetaData.getColumnLabel(int):
{quote}
Gets the designated column's suggested title for use in printouts and displays. The suggested title is usually specified by the SQL AS clause. If a SQL AS is not specified, the value returned from getColumnLabel will be the same as the value returned by the getColumnName method.
{quote}

JDBC 4.0 API doc for ResultSetMetaData.getColumnName(int):
{quote}
Get the designated column's name.
{quote}

Additional sources:
https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.apdv.java.doc/doc/c0052593.html

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 24, 2010

Commented by: @mrotteveel

This bug is questionable, as several sources claim that getColumnName should also return the alias if specified. Unfortunately the JDBC 4.0 spec is not clear what getColumnName() should actually return.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 24, 2010

Modified by: @mrotteveel

priority: Major [ 3 ] => Minor [ 4 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 1, 2010

Commented by: Roman Rokytskyy (rrokytskyy)

On the beginning Jaybird returned XSQLVAR.sqlname for getColumnName and XSQLVAR.aliasname/XSQLVAR.sqlname for getColumnLabel depending on whether alias was specified or not. The issue was that some (if not many) GUIs used getColumnName to display the name of the column, and produced NPE for the cases where the constant was added as a field.

The fix I made that time was incomplete. Currently getColumnName will return XSQLVAR.sqlname if it is not null or getColumnLabel if it is null. We could think about adding the "dummy" alias for the cases when neither name or alias were specified (like with constant 'A'). But that will happen when a ticket is reopened.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 1, 2010

Modified by: Roman Rokytskyy (rrokytskyy)

timeestimate: 0 [ 0 ]

timeoriginalestimate: 0 [ 0 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 1, 2010

Modified by: Roman Rokytskyy (rrokytskyy)

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2011

Commented by: @mrotteveel

Apparently this change broke the getXxx(String) methods of com.sun.rowset.CachedRowSetImpl (the RI for CachedRowSet). Selects with aliased columns can only be retrieved by their original columnname and not by their alias, this works with Jaybird 2.1.6.

It looks like CachedRowSetImpl only uses the value returned by ResultSetMetaData.getColumnName() and ignores the getColumnLabel() value (even though it is retrieved when populating the rowset). Either I am misinterpreting the JDBC spec on this, or the implementors of CachedRowSetImpl misinterpreted it.

I am going to check the behavior for Oracle, PostgreSQL and MySQL JDBC and see if the sources of CachedRowSetImpl is available. This change may need to be reverted.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2011

Modified by: @mrotteveel

assignee: Roman Rokytskyy [ rrokytskyy ] => Mark Rotteveel [ avalanche1979 ]

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

resolution: Fixed [ 1 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2011

Modified by: @mrotteveel

Fix Version: Jaybird 2.2 [ 10053 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2011

Commented by: @mrotteveel

Similar bug reported against MySQL: http://bugs.mysql.com/bug.php?id=49516
Looks like the discussion there also seems to point to this being a bug in the CachedRowSetImpl

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2011

Modified by: @mrotteveel

summary: ResultSetMetaData.getColumnName(int) and getColumnLabel(int) not JDBC 4.0 complient => ResultSetMetaData.getColumnName(int) and getColumnLabel(int) not JDBC 4.0 compliant

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2011

Commented by: @mrotteveel

I am considering a connection property to fallback to the old incorrect implementation where getColumnName returns the label for people using the CachedRowSetImpl RI.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2011

Commented by: @mrotteveel

Testcase provided by Fabiano Bonin attached

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2011

Modified by: @mrotteveel

Attachment: Teste22_mark.zip [ 12060 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 8, 2011

Commented by: @mrotteveel

Fabiano replied to my suggestion to add an additional property to revert to the old behavior:
"Thanks to follow this issue. I suggest you to keep the new way, even
if it breaks current CRS impl.
Since you found the problem, now anyone can get the CRS code and
recompile it to get the expected behavior.
Maybe it is enough to mention this inconsistence in CRS impl in
Jaybird release notes."

So for now I will simply add a notice in the releasenotes. I will also try to file a bug with Oracle.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 25, 2011

Commented by: @mrotteveel

Added entry to releasenotes

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 25, 2011

Commented by: @mrotteveel

This bug is already known at Oracle and considered to be low importance: http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=7046875

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 25, 2011

Modified by: @mrotteveel

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 19, 2012

Modified by: @mrotteveel

Link: This issue relate to JDBC258 [ JDBC258 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 18, 2012

Modified by: @mrotteveel

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 23, 2012

Modified by: @mrotteveel

Link: This issue block progress on JDBC260 [ JDBC260 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 23, 2012

Modified by: @mrotteveel

Link: This issue block progress on JDBC260 [ JDBC260 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 23, 2012

Modified by: @mrotteveel

Link: This issue relate to JDBC278 [ JDBC278 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 15, 2012

Modified by: @mrotteveel

Link: This issue relate to JDBC260 [ JDBC260 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 9, 2013

Modified by: @mrotteveel

Link: This issue relate to JDBC298 [ JDBC298 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 3, 2014

Modified by: @mrotteveel

Link: This issue relate to JDBC356 [ JDBC356 ]

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