[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:all-tabpanel ]

Andrej Šmigala updated TEIID-5196:
----------------------------------
    Description: 
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.

  was:
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.

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.



> 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: Steven Hawkins
>
> 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)