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

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-3319?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins closed TEIID-3319.
---------------------------------


> Union with different datatypes gives error in mysql
> ---------------------------------------------------
>
>                 Key: TEIID-3319
>                 URL: https://issues.jboss.org/browse/TEIID-3319
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>            Reporter: Mark Tawk
>            Assignee: Steven Hawkins
>             Fix For: 8.10
>
>
> 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)