[JBoss JIRA] (TEIID-3319) Union with different datatypes gives error in mysql
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3319?page=com.atlassian.jira.plugin... ]
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)
11 years, 2 months
[JBoss JIRA] (TEIID-3319) Union with different datatypes gives error in mysql
by Mark Tawk (JIRA)
[ https://issues.jboss.org/browse/TEIID-3319?page=com.atlassian.jira.plugin... ]
Mark Tawk commented on TEIID-3319:
----------------------------------
you mean that teiid won't remove the cast on the field of type string?
If yes, how can i remove the pushdown?
> 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)
11 years, 2 months
[JBoss JIRA] (TEIID-3325) Subquery hints in row based conditions not honored
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-3325:
-------------------------------------
Summary: Subquery hints in row based conditions not honored
Key: TEIID-3325
URL: https://issues.jboss.org/browse/TEIID-3325
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 8.7
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 8.10
Some of the subquery hints, such as DJ take affect during rewrite - which happens before security planning.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
11 years, 2 months
[JBoss JIRA] (TEIID-3319) Union with different datatypes gives error in mysql
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3319?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3319:
---------------------------------------
Another resolution could be a code change to remove the pushdown conversion at the source layer and let mysql perform the implicit conversion.
> 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)
11 years, 2 months
[JBoss JIRA] (TEIID-3324) Boolean fields with null are evaluated to false
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3324?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration updated TEIID-3324:
-------------------------------------------
Bugzilla Update: Perform
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1189484
> Boolean fields with null are evaluated to false
> -----------------------------------------------
>
> Key: TEIID-3324
> URL: https://issues.jboss.org/browse/TEIID-3324
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 7.1
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.10
>
>
> I have a source table defined as follows (e.g. in PostgreSQL):
> {code:sql}
> CREATE TABLE public.test
> (
> id integer,
> iscustomer boolean
> )
> {code}
> and the table contains these rows:
> {code:sql}
> insert into public.test values(1, null);
> insert into public.test values(2, true);
> insert into public.test values(3, false);
> {code}
>
> When I try to get data from this table in Teiid, the "null" value is evaluated as false:
> ||id||iscustomer||
> |1|false|
> |2|true|
> |3|false|
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
11 years, 2 months
[JBoss JIRA] (TEIID-3324) Boolean fields with null are evaluated to false
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3324?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3324:
----------------------------------
Fix Version/s: 8.10
Affects Version/s: 7.1
Component/s: JDBC Connector
> Boolean fields with null are evaluated to false
> -----------------------------------------------
>
> Key: TEIID-3324
> URL: https://issues.jboss.org/browse/TEIID-3324
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 7.1
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.10
>
>
> I have a source table defined as follows (e.g. in PostgreSQL):
> {code:sql}
> CREATE TABLE public.test
> (
> id integer,
> iscustomer boolean
> )
> {code}
> and the table contains these rows:
> {code:sql}
> insert into public.test values(1, null);
> insert into public.test values(2, true);
> insert into public.test values(3, false);
> {code}
>
> When I try to get data from this table in Teiid, the "null" value is evaluated as false:
> ||id||iscustomer||
> |1|false|
> |2|true|
> |3|false|
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
11 years, 2 months
[JBoss JIRA] (TEIID-3324) Boolean fields with null are evaluated to false
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3324?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3324.
-----------------------------------
Resolution: Done
Updated the retrieval methods to check was null.
> Boolean fields with null are evaluated to false
> -----------------------------------------------
>
> Key: TEIID-3324
> URL: https://issues.jboss.org/browse/TEIID-3324
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 7.1
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.10
>
>
> I have a source table defined as follows (e.g. in PostgreSQL):
> {code:sql}
> CREATE TABLE public.test
> (
> id integer,
> iscustomer boolean
> )
> {code}
> and the table contains these rows:
> {code:sql}
> insert into public.test values(1, null);
> insert into public.test values(2, true);
> insert into public.test values(3, false);
> {code}
>
> When I try to get data from this table in Teiid, the "null" value is evaluated as false:
> ||id||iscustomer||
> |1|false|
> |2|true|
> |3|false|
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
11 years, 2 months
[JBoss JIRA] (TEIID-3324) Boolean fields with null are evaluated to false
by Salvatore R (JIRA)
Salvatore R created TEIID-3324:
----------------------------------
Summary: Boolean fields with null are evaluated to false
Key: TEIID-3324
URL: https://issues.jboss.org/browse/TEIID-3324
Project: Teiid
Issue Type: Bug
Reporter: Salvatore R
Assignee: Steven Hawkins
I have a source table defined as follows (e.g. in PostgreSQL):
{code:sql}
CREATE TABLE public.test
(
id integer,
iscustomer boolean
)
{code}
and the table contains these rows:
{code:sql}
insert into public.test values(1, null);
insert into public.test values(2, true);
insert into public.test values(3, false);
{code}
When I try to get data from this table in Teiid, the "null" value is evaluated as false:
||id||iscustomer||
|1|false|
|2|true|
|3|false|
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
11 years, 2 months