[teiid-issues] [JBoss JIRA] (TEIID-3813) Informix translator - convert function in definition of view is not pushed down

[Juraj Duráni (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3813?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13126853#comment-13126853 ] 

Juraj Duráni commented on TEIID-3813:
-------------------------------------

Another examples:
1. '+'/'-'/'*'/'/' operations are not allowed  on boolean values
- Query
{code:sql}
SELECT BooleanValue, (convert(BooleanValue, float)+2) AS BooleanValuePlus2, (convert(BooleanValue, float) - 2) AS BooleanValueMinus2, (convert(BooleanValue, float)*2) AS BooleanValueTimes2, (convert(BooleanValue, float)/2) AS BooleanValueDiv2 FROM BQT1.SmallA WHERE IntKey >=0 AND IntKey <=5 ORDER BY BooleanValue
{code}
- Source-specific command
{code:sql}
SELECT g_0.booleanvalue AS c_0, (g_0.booleanvalue + 2.0) AS c_1, (g_0.booleanvalue - 2.0) AS c_2, (g_0.booleanvalue * 2.0) AS c_3, (g_0.booleanvalue / 2.0) AS c_4 FROM smalla AS g_0 WHERE g_0.intkey >= 0 AND g_0.intkey <= 5 ORDER BY c_0
{code}

However, infromix doe not support conversion from boolean to numeric type. But in my opinion it would be better to get exception concerning casting rather than "Routine (equal/plus/minut/...) can not be resolved."

> Informix translator - convert function in definition of view is not pushed down
> -------------------------------------------------------------------------------
>
>                 Key: TEIID-3813
>                 URL: https://issues.jboss.org/browse/TEIID-3813
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 8.7.1.6_2
>            Reporter: Juraj Duráni
>            Assignee: Steven Hawkins
>
> I defined a view (see definition below). When I try to select all rows from the view, teiid/informix jdbc driver throws an exception.
> Teiid DDL:
> {code:sql}
> CREATE VIEW U6 (StringCol string,
>     IntCol integer)
>   AS
>     SELECT CONVERT(BQT1.SmallA.IntNum, string) AS StringCol, BQT1.SmallA.IntNum AS IntCol
>     FROM BQT1.SmallA
>     UNION ALL
>     SELECT BQT1.SmallB.StringNum, CONVERT(BQT1.SmallB.StringNum, integer)
>     FROM BQT1.SmallB;
> }
> {code}
> Query:
> {code:sql}
> SELECT * FROM VQT.U6
> {code}
> Source-specific query
> {code:sql}
> SELECT g_1.intnum AS c_0, g_1.intnum AS c_1 FROM smalla AS g_1 UNION ALL SELECT g_0.stringnum AS c_0, g_0.stringnum AS c_1 FROM smallb AS g_0
> {code}
> Actual exception:
> {code:text}
> Caused by: java.sql.SQLException: The statement failed because corresponding column data types must be compatible for each UNION, INTERSECT, or MINUS query.
> 	at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
> 	at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3178)
> ...
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)