[SQOOP-585] Bug when sqoop a join of two tables with the same column name with mysql backend

• Type: Bug
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.4.4
• Fix Version/s: 1.4.5
• Component/s: connectors/sqlserver
• Labels: patch

Environment

• Mysql server backend (Postgres is fine)
• using a --query argument to join over two tables with identical column names. Here is an example of my sqoop command:

JAVA_HOME=/usr ./bin/sqoop import \
--query 'SELECT events.*, square_applications.name as square_application, user_agents.name as user_agent from events LEFT JOIN square_applications on events.square_application_id=square_applications.id LEFT JOIN user_agents on events.user_agent_id=user_agents.id where $CONDITIONS' \
--boundary-query "SELECT MIN(id), MAX(id) from events"\
--split-by events.id \
--target-dir /tmp/sqoop/events \
--connect jdbc:mysql://localhost/development_database

• Assignee: Amit Sela
• Reporter: Vida Ha
• Created: 23/Aug/12 17:19
• Updated: 26/Aug/14 07:02
• Resolved: 13/Jul/14 17:32

Description

I get this error when I run the command:

INFO: Executing SQL statement: SELECT events.*, square_applications.name as square_application, user_agents.name as user_agent from events LEFT JOIN square_applications on events.square_application_id=square_applications.id LEFT JOIN user_agents on events.user_agent_id=user_agents.id where (1 = 0)
Aug 23, 2012 10:10:56 AM org.apache.sqoop.tool.ImportTool run
SEVERE: Imported Failed: Duplicate Column identifier specified: 'name'

Calling "getColumnLabel" instead of "getColumnName" in SqlManager.java fixes this problem and this still works in postgres as well as mysql. Here is my patch:

Index: src/java/org/apache/sqoop/manager/SqlManager.java
===================================================================
— src/java/org/apache/sqoop/manager/SqlManager.java (revision 1375124)
+++ src/java/org/apache/sqoop/manager/SqlManager.java (working copy)
@@ -135,9 +135,9 @@
ArrayList<String> columns = new ArrayList<String>();
ResultSetMetaData metadata = results.getMetaData();
for (int i = 1; i < cols + 1; i++) {

• String colName = metadata.getColumnName;
  + String colName = metadata.getColumnLabel;
  if (colName == null || colName.equals("")) {
• colName = metadata.getColumnLabel;
  + colName = metadata.getColumnName;
  if (null == colName) { colName = "_RESULT_" + i; }

  @@ -208,9 +208,9 @@
  typeId = Types.BIGINT;
  }

• String colName = metadata.getColumnName;
  + String colName = metadata.getColumnLabel;
  if (colName == null || colName.equals("")) { - colName = metadata.getColumnLabel(i); + colName = metadata.getColumnName(i); }

colTypes.put(colName, Integer.valueOf(typeId));

Issue Links

• is duplicated by: SQOOP-1204 Issue with Sqoop column aliases