This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

Bug 132647 - column alias handled incorrectly in MySQL using 5.1.5 driver
Summary: column alias handled incorrectly in MySQL using 5.1.5 driver
Status: RESOLVED DUPLICATE of bug 129330
Alias: None
Product: obsolete
Classification: Unclassified
Component: visualweb (show other bugs)
Version: 6.x
Hardware: PC All
: P3 blocker (vote)
Assignee: John Baker
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-04-11 18:15 UTC by jeff06si
Modified: 2008-04-25 13:12 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Exception Reporter:


Attachments
Exception from application server log file. (25.93 KB, text/plain)
2008-04-16 10:37 UTC, Roman Mostyka
Details

Note You need to log in before you can comment on or make changes to this bug.
Description jeff06si 2008-04-11 18:15:15 UTC
Using Netbeans 6.1 RC1 Build: 200804100130

Using a mysql database, any time that a column alias comes into play, while the visual query tool will display columns 
correctly, the query results return the actual column name.  And, when binding to a cachedrowset whose definition uses 
a column alias, only actual column names are available for selection.  If the column alias is forced, the project will 
not build.  This is also true when a database view which contains a column alias is used.  The view's column names 
will appear correctly in the query designer.  However, when you run the query, the actual table column name will 
appear in the column header.

Unfortunately, there's no work-around because, if you pick the real column name when binding to data in Netbeans, when 
you load the application in glassfish, it's working with the column alias instead, and can't find the actual column 
name which is referenced in the binding.

This was discovered when moving a project from 6.0.1 to 6.1.  The behavior does not exist in 6.0.1
Comment 1 Winston Prakash 2008-04-15 22:46:57 UTC
As far as I know nothing has been changed between 6.0.1 & 6.1 in this area. So, first I checked this with well tested
bundled derby databaes 

I created an alias for the derby database table column (PRODUCT.POSTAL_CODE as PRODUCT.p_code) and then bind it to the
JSF table. It correctly displayed the aliased name. So the logic to show the alias is not broken in the CachedRowset
data provider.

Howerver, the names displayed by the table is obtained after executing the SQL and then via the method
CachedRowset.getMetadat()a (which uses the JDBC driver). I'm not sure if this is a problem with the JDBC driver.

Also, we introduced Caching of meta-adat in NB 6.1. Not sure, if this has any effect when Cached metadata is fetched
after changing a column alias.

I tried it with derby after closing and opening the project (so that cached metadata will come in to play), but it seems
to work fine when I aliased a column.
Comment 2 Jayashri Visvanathan 2008-04-16 05:32:15 UTC
I was wondering about this as well. As far as I know, not much has changed in 6.1 in cached rowset/query editor. Anyway,
Roman can you please help reproduce this issue with MySQL ?

thanks
Comment 3 John Baker 2008-04-16 08:26:33 UTC
Using 6.0 and setting an alias for a column is working in 6.0. The Query Editor executes the SQL and the alias appears
in place of the column name.  Also the dataprovider uses the alias instead of the column.

So, this looks like a regression in 6.1.

The Query Editor, at design-time uses a live connection to obtain table information, not the cached resultset.
So, this doesn't seem to be related to the new feature in 6.1 for cached resultset.

There were some changes in Query Editor and CachedRowSetDataProvider for 6.1, but I'll have to identify the changes made.

Comment 4 Roman Mostyka 2008-04-16 09:17:13 UTC
I've tried with Derby and MySQL.
For Derby everything was fine, as Winston wrote.
But with MySQL for me alias doesn't work. But in contrast to reporter I didn't see column alias even in visual query editor.
Applications (with Derby and with MySQL) both were successfully deployed and loaded in browser. Just for Derby shows
column alias, but for MySQL actual column name.
Comment 5 John Baker 2008-04-16 09:20:45 UTC
I didn't see any changes that could have caused this, so I tried debugging a local build of my nearly up-to-date local
workspace.
The alias worked fine for MySQL and Java DB for the Query Editor and dataprovider.

AFAIK no recent changes have been made by me or David in the Query Editor or Dataprovider.
I'm still investigating
Comment 6 Roman Mostyka 2008-04-16 09:26:45 UTC
I tried also with MS SQL Server and result is the same as for Derby: everything is fine.
As I see there is a difference between query for MySQL and Derby, MS SQL Server. MySQL column names are marked out with
'`' sign, but for Derby and MS SQL Server there is no such mark. Can this be a reason?
Comment 7 John Baker 2008-04-16 10:01:47 UTC
I recompiled the db.sql.visualeditor module in my local workspace (the one where I wasn't seeing the problem) and can
now also reproduce using MySQL.  

However, this is not reproducible for Derby

It looks like a MySQL driver issue.
In 6.1 we moved to MySQL driver 5.1.5, whereas in 6.0 we used 5.0.7

Instead of using MySQL driver 5.1.5, I used 5.0.7 and aliases are now working fine.

Although there appears to be a workaround, we might want to consider moving back to driver 5.0.7

Roman please confirm using the 5.0.7 MySQL driver

Comment 8 John Baker 2008-04-16 10:06:59 UTC
Perhaps the 5.0.8 driver works also.
http://dev.mysql.com/downloads/connector/j/5.0.html
Comment 9 Roman Mostyka 2008-04-16 10:31:20 UTC
Yes, John, with 5.0.7 MySQL driver everything works fine.
Comment 10 Roman Mostyka 2008-04-16 10:36:38 UTC
Not everything. In visual SQL editor and after binding with Table JSF component column alias is displayed correctly, but
after deployment I've got exception (in attach).
Comment 11 Roman Mostyka 2008-04-16 10:37:37 UTC
Created attachment 60257 [details]
Exception from application server log file.
Comment 12 David Vancouvering 2008-04-16 15:53:37 UTC
This is a known "issue" - MySQL does not consider this a bug.  See http://bugs.mysql.com/bug.php?id=21596

Our problem is we are using ResultSetMetaData.getColumnName() when we *should* be using
ResuletSetMetaData.getColumnLabel().  In the old driver MySQL had a bug where they returned the alias name (the label)
even for getColumnName(), which properly should return the underlying name regardless of the alias.

I apologize I didn't catch this sooner - I see I'm on the cc list but I don't think I paid attention.

The proper fix is for us to change our code to use getColumnLabel() instead of getColumName().  I fixed this in the
query editor already, but it looks like there are other places we need to fix this.

As a workaround, the user can add the following property to their connection string: "useOldAliasMetadataBehavior".

For example: "jdbc:mysql://localhost:3306/sample?useOldAliasMetadataBehavior=true"
Comment 13 John Baker 2008-04-16 19:09:01 UTC
David's correct.  A change could be made in dataprovider but I thought it was a risky fix at the time.

However, regarding the use of the 5.0.7 driver, runtime works for me. 

For Roman in desc11, probably the newer driver had been deployed to glassfish previously and reused instead of 5.0.7 so
I would expect runtime to fail
Comment 14 John Baker 2008-04-16 23:08:16 UTC
will fix in the trunk for the next patch

might want to add an FAQ for 6.1
Comment 15 John Baker 2008-04-21 20:18:59 UTC
fix integrated in main.  I'll request for inclusion in the 6.1 patch

*** This issue has been marked as a duplicate of 129330 ***