[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:comment-tabpanel&focusedCommentId=13038102#comment-13038102 ] 

Steven Hawkins commented on TEIID-3319:
---------------------------------------

> you mean that teiid won't remove the cast on the field of type string?

The engine will insert the conversion from int to string so that the types match for the union.  It will remove a conversion from string to string as that is seen as unnecessary.  What I'm saying is that mysql seems to handle the union of int and a character string just fine without the explicit conversion, so we could just have the translator remove the explicit conversion inserted by the engine.  This would be a code change, not something that would be directly in the user query.

> 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.



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