Filip Elias <felias@redhat.com> changed the Status of bug 1146501 from VERIFIED to CLOSED

Juraj Duráni <jdurani@redhat.com> changed the Status of bug 1146501 from ON_QA to VERIFIED

Van Halbert <vhalbert@redhat.com> changed the Status of bug 1146501 from MODIFIED to ON_QA

Van Halbert <vhalbert@redhat.com> changed the Status of bug 1146501 from NEW to MODIFIED

Will look at again if there is a similar customer issue such that there is a grouping over an index and there are secondary grouping columns with functions.

TEIID-3160 resolves the issue I was mentioned, which ensures that we won't inappropriately narrow.

> Only rows with IntNum=1 and IntNum=-1 are correct. Same result for IntKey column.

Actually for the types involved, what sybase/Teiid is returning is the correct answer. You are performing integer division which results in most values being acos(0).

> I have tried oracle12 (VDB and original) and result has been OK

More than likely the source is performing decimal division. That is the intnum type is likely not a true integral type.

> Result is for every value in the table 1.5708 (same for ShortValue). In this case original sybase returns correct result.

Same as above. It matters what the source type is. From the Teiid perspective with the query as written you are performing integer division and thus getting acos(0).

An issue I do see is that if you issue 1.0/int Teiid should not evaluate using integer division, and should produce the wider decimal result.

I have found these:

SELECT intnum, ACOS(1 / intnum) FROM BQT1.SmallA WHERE intnum <> 0 ORDER BY intnum

OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(0) output=[Source.SmallA.IntNum, ACOS(convert((1 / Source.SmallA.IntNum), double))] SELECT g_0.IntNum AS c_0, ACOS(convert((1 / g_0.IntNum), double)) AS c_1 FROM Source.SmallA AS g_0 WHERE g_0.IntNum <> 0 ORDER BY c_0 LIMIT 100

Return for almost every value 1.5708 (=ACOS(0)). Only rows with IntNum=1 and IntNum=-1 are correct. Same result for IntKey column.
But same result returns original sybaseDB (maybe bug in sybase jdbc). I have tried oracle12 (VDB and original) and result has been OK.

----------------------------------------------------------------------------------------------------
SELECT longnum, ACOS(1 / longnum) FROM BQT1.SmallA WHERE longnum <> 0 ORDER BY longnum

OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(0) output=[Source.SmallA.LongNum, ACOS(convert((1 / Source.SmallA.LongNum), bigdecimal))] SELECT g_0.LongNum AS c_0, ACOS(convert((1 / g_0.LongNum), bigdecimal)) AS c_1 FROM Source.SmallA AS g_0 WHERE g_0.LongNum <> 0 ORDER BY c_0 LIMIT 100

Result is correct (same for FloatNum, DoubleNum, BigIntegerValue, BigDecimalValue). IntNum and LongNum columns have same values so results should be same.

-------------------------------------------------------------------------------------------------------
SELECT bytenum, ACOS(1 / bytenum) FROM BQT1.SmallA WHERE bytenum <> 0 ORDER BY bytenum

Result is for every value in the table 1.5708 (same for ShortValue). In this case original sybase returns correct result.
-------------------------------------------------------------------------------------------------------
SELECT AVG(shortvalue) FROM BQT1.SmallA
SELECT AVG(bytenum) FROM BQT1.SmallA

AVG returns integer (-103/ByteNum, -32,743/ShortValue) but should be real (-103.2766/ByteNum, -32,743.65957/ShortValue).
Original sybase DB returns correct result in this case.

AVG with DoubleNum, LongNum, BigIntegerNum, BigDecimalNum, IntKey, IntNum, FloatNum returns correct result.
------------------------------------------------------------------------------------------------------

I can double check that aggregate scenario if you provide the details of the query.

One more note: there is similar problem with functions AVG, ASIN, ACOS,... (returns always integer instead real).