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

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-3141:
---------------------------------------

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

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