[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=13006066#comment-13006066 ] 

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

I think yes. I hope this will help:
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(1) output=[anon_grp0.gcol0 AS IntKey, anon_grp0.gcol1 AS StringKey, anon_grp0.gcol2 AS IntNum, anon_grp0.gcol3 AS StringNum, anon_grp0.gcol4 AS FloatNum, anon_grp0.gcol5 AS LongNum, anon_grp0.gcol6 AS DoubleNum, anon_grp0.gcol7 AS ByteNum, anon_grp0.gcol8 AS DateValue, anon_grp0.gcol9 AS TimeValue, anon_grp0.gcol10 AS TimestampValue, anon_grp0.gcol11 AS BooleanValue, anon_grp0.gcol12 AS CharValue, anon_grp0.gcol13 AS ShortValue, anon_grp0.gcol14 AS BigIntegerValue, anon_grp0.gcol15 AS BigDecimalValue] SELECT g_0.IntKey AS c_0, g_0.StringKey AS c_1, g_0.IntNum AS c_2, g_0.StringNum AS c_3, convert(g_0.FloatNum, float) AS c_4, g_0.LongNum AS c_5, g_0.DoubleNum AS c_6, convert(g_0.ByteNum, byte) AS c_7, convert(g_0.DateValue, date) AS c_8, convert(g_0.TimeValue, time) AS c_9, g_0.TimestampValue AS c_10, convert(g_0.BooleanValue, boolean) AS c_11, convert(g_0.CharValue, char) AS c_12, convert(g_0.ShortValue, short) AS c_13, convert(g_0.BigIntegerValue, biginteger) AS c_14, convert(g_0.BigDecimalValue, bigdecimal) AS c_15 FROM Source.SmallA AS g_0 GROUP BY g_0.IntKey, g_0.StringKey, g_0.IntNum, g_0.StringNum, convert(g_0.FloatNum, float), g_0.LongNum, g_0.DoubleNum, convert(g_0.ByteNum, byte), convert(g_0.DateValue, date), convert(g_0.TimeValue, time), g_0.TimestampValue, convert(g_0.BooleanValue, boolean), convert(g_0.CharValue, char), convert(g_0.ShortValue, short), convert(g_0.BigIntegerValue, biginteger), convert(g_0.BigDecimalValue, bigdecimal) LIMIT 100

I tried query against VDB with oracle10 and mysql55 datasource and there was no problem. Original sybase15 DB returns correct result too.

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