[
https://issues.jboss.org/browse/TEIID-3319?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-3319:
---------------------------------------
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')
It's not that there's a mismatch between char and varchar - those are normally
comparable. The issue is that the connections default encoding/collation is not
compatible with the incidentsm1_INCIDENT_ID column.
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.
That would effectively give them the same encoding, which would resolve the issue.
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.
The cast would be seen as unnecessary and removed.
A couple of resolutions would be to change the collation in use for your connection,
change the collation in use for the column, or you could use the teiid_rel:native-query
metadata property on the SDView_sub source table to manually insert a cast/convert on the
incidentsm1_INCIDENT_ID column -
https://docs.jboss.org/author/display/TEIID/JDBC+Translator
> 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)