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

Union with different datatypes gives error in mysql --------------------------------------------------- Key: TEIID-3319 URL: https://issues.jboss.org/browse/TEIID-3319 Project: Teiid Issue Type: Bug 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.