[teiid-issues] [JBoss JIRA] (TEIID-3141) Bad result of query with GROUB BY clause (underlying sybase15 datasource)

[Juraj Duráni (JIRA)]

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

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

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


> Bad result of query with GROUB BY clause (underlying sybase15 datasource)
> -------------------------------------------------------------------------
>
>                 Key: TEIID-3141
>                 URL: https://issues.jboss.org/browse/TEIID-3141
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 8.7.1
>         Environment: OS: fedora20
> arch: x86_64
> java: sun 1.7
>            Reporter: Juraj Duráni
>            Assignee: Steven Hawkins
>
> Description:
> There is sybase15 database with table named SmallA and table named SmallA in VDB which is mapped to sybase table (see tables definition below).
> I am trying to run query against VDB:
> SELECT 
> 	INTKEY, STRINGKEY, INTNUM, STRINGNUM, FLOATNUM, LONGNUM, DOUBLENUM, BYTENUM, DATEVALUE, TIMEVALUE, TIMESTAMPVALUE, BOOLEANVALUE, CHARVALUE, SHORTVALUE, BIGINTEGERVALUE, BIGDECIMALVALUE
> FROM 
> 	BQT1.SMALLA 
> GROUP BY 
> 	INTKEY, STRINGKEY, INTNUM, STRINGNUM, FLOATNUM, LONGNUM, DOUBLENUM, BYTENUM, DATEVALUE, TIMEVALUE, TIMESTAMPVALUE, BOOLEANVALUE, CHARVALUE, SHORTVALUE, BIGINTEGERVALUE, BIGDECIMALVALUE
> Result is table which misses some values (the other values are OK):
> FloatNum: always 0
> ByteNum: always 0
> DateValue: always 1900-01-01
> TimeValue: always 00:00:00
> BooleanValue: always 'false'
> CharValue: always empty character
> ShortValue: always 0
> BigIntegerValue: always 0
> BigDecimalValue: always 0
> After removing 'INTKEY' and 'STRINGKEY' from the query is result OK (sybase15 has indices only for these two columns).
> ///////////////////
> Table definition
> ///////////////////
> SmallA (sybase) has these columns (name:type):
> IntKey:int -> PRIMARY KEY, HAS INDEX
> StringKey:varchar -> HAS INDEX
> IntNum:int
> StringNum:varchar
> FloatNum:float
> LongNum:numeric
> DoubleNum:float
> ByteNum:real
> DateValue:datetime
> TimeValue:datetime
> TimestampValue:datetime
> BooleanValue:tinyint
> CharValue:char
> ShortValue:numeric
> BigIntegerValue:numeric
> BigDecimalValue:numeric
> ObjectValue:text
> SmallA (VDB) has these columns (name:type):
> IntKey:integer
> StringKey:string
> IntNum:integer
> StringNum:string
> FloatNum:float
> LongNum:long
> DoubleNum:double
> ByteNum:byte
> DateValue:date
> TimeValue:time
> TimestampValue:timestamp
> BooleanValue:boolean
> CharValue:char
> ShortValue:short
> BigIntegerValue:biginteger
> BigDecimalValue:bigdecimal
> ObjectValue:object



--
This message was sent by Atlassian JIRA
(v6.3.1#6329)