[teiid-issues] [JBoss JIRA] (TEIID-5196) Osisoft Translator - Wrong data returned for some JOIN queries when integer and float columns are compared

[Andrej Šmigala (JIRA)]

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

Andrej Šmigala commented on TEIID-5196:
---------------------------------------

[~rareddy] another option would be removing the cast altogether.

> Osisoft Translator - Wrong data returned for some JOIN queries when integer and float columns are compared
> ----------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-5196
>                 URL: https://issues.jboss.org/browse/TEIID-5196
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 8.12.x-6.4
>            Reporter: Andrej Šmigala
>            Assignee: Ramesh Reddy
>
> When using equality comparison between an integer and float columns in a WHERE or ON clause, Teiid pushes it to Osisoft PI with both columns cast as double.
> There seems to be a bug in Osisoft PI which causes the non-positive float values in the result for such a query to be returned as NULL (or not at all).
> E.g.
> {code:sql|title=Teiid query}
> SELECT BQT1.MediumA.IntNum, BQT1.MediumB.FloatNum 
> 	FROM BQT1.MediumA 
> 	FULL JOIN BQT1.MediumB 
> 		ON BQT1.MediumA.IntNum = BQT1.MediumB.FloatNum 
> 	WHERE 
> 		BQT1.MediumA.IntNum >= -10 
> 		AND BQT1.MediumA.IntNum < 5 
> 		AND (BQT1.MediumB.FloatNum >= -10 
> 		AND BQT1.MediumB.FloatNum < 5)
> {code}
> is translated to 
> {code:sql|title=Pushed Osisoft PI query}
> SELECT g_0.IntNum, g_1.FloatNum
> 	FROM dvqe..MediumA AS g_0, dvqe..MediumB AS g_1 
> 	WHERE 
> 		cast(g_0.IntNum AS Double) = cast(g_1.FloatNum AS Double)
> 		AND g_0.IntNum < 5 
> 		AND g_1.FloatNum >= -10.0
> 		AND g_1.FloatNum < 5.0
> 		AND g_0.IntNum >= -10
> {code}
> which seems to be correct, but returns the wrong result:
> ||IntNum||FloatNum||
> |1|1|
> |2|2|
> |3|3|
> |4|4|
> Note that the only the positive values are returned, even though the criteria match also negative values (which are present in the source table).
> If the cast to double in the source query is removed (or replaced with cast to single), the query returns the expected results.



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)