[teiid-issues] [JBoss JIRA] (TEIID-3319) Union with different datatypes gives error in mysql

[Mark Tawk (JIRA)]

Mark Tawk created TEIID-3319:
--------------------------------

             Summary: Union with different datatypes gives error in mysql
                 Key: TEIID-3319
                 URL: https://issues.jboss.org/browse/TEIID-3319
             Project: Teiid
          Issue Type: Feature Request
            Reporter: Mark Tawk
            Assignee: Steven Hawkins


Hello,

I'm using Teiid 8.9.0 Alpha2 with mysql5.7 and mysql5 translator.

I have a union query with fields of different types, int and varchar.
Teiid is adding dynamically the cast as char to the int field but the jdbc query is giving an error on mysql level (Illegal mix of collations for operation 'UNION')
I edited the generated jdbc query and added cast as char to both fields int and varchar, mysql executes the jdbc query without a problem.
I added convert to String to both union fields in Teiid sql query, but the jdbc query generated by teiid is not adding the cast to the string field.

Teiid sql query:
select "ITRITPTicketsView"."ITRITPTicketsView_jiraissue_" as "ITRITPTicketsView_jiraissue_" 
from  ((select convert("ITRITPTicketsView_sub"."jiraissue_ID", string) as "ITRITPTicketsView_jiraissue_" from  "implifyBusinessModel"."ITRITPTicketsView" "ITRITPTicketsView_sub") 
		union 
	(select convert("SDView_sub"."incidentsm1_INCIDENT_ID", string) as "SDView_incidentsm1_INCIDENT_" from  "implifyBusinessModel"."SDView" "SDView_sub")
) "ITRITPTicketsView" 
LIMIT 0 , 10

The generated error:
org.teiid.translator.jdbc.JDBCExecutionException: 1271 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: (SELECT cast(g_1.`jiraissue_ID` AS char) AS c_0 FROM `implify_view`.`ITRITPTicketsView` AS g_1) UNION (SELECT g_0.`incidentsm1_INCIDENT_ID` AS c_0 FROM `implify_view`.`SDView` AS g_0) LIMIT 10]
	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:151)
	at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:326)
	at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
	at java.util.concurrent.FutureTask.run(FutureTask.java:262)
	at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
	at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274)
	at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
	at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:744)
Caused by: java.sql.SQLException: Illegal mix of collations for operation 'UNION'
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2212)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:141)
	... 12 more

Feb 03, 2015 8:43:32 AM org.teiid.logging.JavaLogger log
WARNING: TEIID30020 Processing exception for request AxQ9XTXrAYkm.0 'TEIID30504 implify_viewModelVDB: 1271 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: (SELECT cast(g_1.`jiraissue_ID` AS char) AS c_0 FROM `implify_view`.`ITRITPTicketsView` AS g_1) UNION (SELECT g_0.`incidentsm1_INCIDENT_ID` AS c_0 FROM `implify_view`.`SDView` AS g_0) LIMIT 10]'. Originally TeiidProcessingException 'Illegal mix of collations for operation 'UNION'' SQLError.java:1084. Enable more detailed logging to see the entire stacktrace.




--
This message was sent by Atlassian JIRA
(v6.3.11#6341)