[jira] [Comment Edited] (NIFI-3064) Executesql convert to avrò fails when a mysql query returns two columns with the same name even with different aliases

["Matt Burgess (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/NIFI-3064?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15675817#comment-15675817 ] 

Matt Burgess edited comment on NIFI-3064 at 11/18/16 5:20 AM:
--------------------------------------------------------------

The MySQL driver returns the original column name (without table prefix) for getColumnName(), and the alias for getColumnLabel(). The logic in JdbcCommon currently checks for name first, then defaults to the value of label if name is not present:

String nameOrLabel = StringUtils.isNotEmpty(meta.getColumnName( i )) ? meta.getColumnName( i ) : meta.getColumnLabel( i );
            
At first glance, it appears the logic should be reversed to use the label if present, or default to the name otherwise. However this should be tested on a few databases to ensure the behavior does not change (except to fix this bug for whichever DBs it affects).


was (Author: mattyb149):
The MySQL driver returns the original column name (without table prefix) for getColumnName(), and the alias for getColumnLabel(). The logic in JdbcCommon currently checks for name first, then defaults to the value of label if name is not present:

String nameOrLabel = StringUtils.isNotEmpty(meta.getColumnName(i)) ? meta.getColumnName(i) : meta.getColumnLabel(i);
            
At first glance, it appears the logic should be reversed to use the label if present, or default to the name otherwise. However this should be tested on a few databases to ensure the behavior does not change (except to fix this bug for whichever DBs it affects).

> Executesql convert to avrò fails when a mysql query returns two columns with the same name even with different aliases
> ----------------------------------------------------------------------------------------------------------------------
>
>                 Key: NIFI-3064
>                 URL: https://issues.apache.org/jira/browse/NIFI-3064
>             Project: Apache NiFi
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 0.7.0, 0.6.1
>            Reporter: Karthik Narayanan
>            Assignee: Matt Burgess
>
> Customer came with some issues in executing query using executesql. He thought the error was due to biting field in the query. But, upon research i was able to reproduce the error when i tried this particular query
> select A.host as host1,B.host as host from `hosts` A  join host2 B on B.hostid=A.hostid;
> in the above query A.host and B.host are columns that have same name, but come from different tables, and have a different alias name. But looks like avro conversion is ignoring the alias and just looking at the column name. This causes the query to fail with this error
> Duplicate Field Name in any.data.hosts ; name type UNION pos:1 and name type UNION pos:0.
> I have attached a screenshot for the same. 



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)