[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)
9 years, 4 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)
9 years, 4 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)
9 years, 4 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)
9 years, 4 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)
9 years, 4 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)
9 years, 4 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)
9 years, 4 months
[JBoss JIRA] (TEIID-3301) Add GEOMETRY_COLUMNS view
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3301?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3301:
---------------------------------------
> I think it would simplify things if we didn't allow such functions to be pushed down.
That would be any of the st_geomfrom* functions, udfs, st_setsrid, and any of the other construction/manipulation functions from the spec that get added. Ideally that should all be allowed as pushdown.
> We might want to be careful using MySQL internal format because I cannot find any documentation specifying it that it is what it appears to be.
No, there doesn't seem to be an official statement on the internal storage. Just other sites confirming the srid bytes + wkb format.
> WKB-based format is preferable IMO because it's more compact. ... Oracle does not support EWKT natively.
I'm only suggesting reading oracle values as ewkt since it's easy to do clob concatenation in the source query. We'd then yes have to convert to wkb for internal which would make getting the srid for oracle doubly expensive - unless the geometrytype also tracked its format.
> JTS IO classes do not fully support the "extended" formats.
It does directly support the srid / three dimensional aspects of ewkb, which would be useful for reading values from postgis.
I will be adding a validation to the current logic though to prohibit reading ewkb values so that it's fully compatible with the expectations of the st_geomfromwkb function.
> Add GEOMETRY_COLUMNS view
> -------------------------
>
> Key: TEIID-3301
> URL: https://issues.jboss.org/browse/TEIID-3301
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Tom Arnold
> Assignee: Steven Hawkins
> Labels: spatial
> Fix For: 8.10
>
>
> Another OGC Simple Features implementation detail. Basically this is like a specialized version of SYS.COLUMNS, and would be used to support GIS software (like GeoServer).
> http://www.carocoops.org/resources/doc/postgis/x321.html
> {code}
> CREATE TABLE GEOMETRY_COLUMNS (
> F_TABLE_CATALOG VARCHAR(256) NOT NULL,
> F_TABLE_SCHEMA VARCHAR(256) NOT NULL,
> F_TABLE_NAME VARCHAR(256) NOT NULL,
> F_GEOMETRY_COLUMN VARCHAR(256) NOT NULL,
> COORD_DIMENSION INTEGER NOT NULL,
> SRID INTEGER NOT NULL,
> TYPE VARCHAR(30) NOT NULL
> )
> {code}
> I think we could implement this by storing SRID & friends as column properties, and then creating a view on SYS.COLUMNS & SYS.PROPERTIES.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months