[teiid-issues] [JBoss JIRA] Created: (TEIID-1158) Sybase CTC queries - Unexpected results when <null> involved

[Warren Gibson (JIRA)]

Sybase CTC queries - Unexpected results when <null> involved
------------------------------------------------------------

                 Key: TEIID-1158
                 URL: https://jira.jboss.org/browse/TEIID-1158
             Project: Teiid
          Issue Type: Bug
          Components: Query Engine
    Affects Versions: 7.1
         Environment: Teiid 7.1 and Sybase 12.5 as a source using pushdown capabilities
Sybase URL:  jdbc:sybase:Tds:slntds17.mm.atl2.redhat.com:5000/bqt2

            Reporter: Warren Gibson
            Assignee: Steve Hawkins


It appears we are handling <null> differently in Sybase 12.5.  Results are being returned when <null> is involved and apparently in the past these were not returned.  

The following queries are not returning the expected results in query testing:   

Query 1
SELECT intkey, charvalue FROM BQT1.SmallA WHERE concat(stringkey, charvalue) = '10' ORDER BY intkey

The expected results is:
IntKey  and CharValue 
     1                   0

However, the following is being returned:
IntKey	CharValue
1	0
10	<null>

Query 2
SELECT INTKEY, TIMEVALUE FROM BQT1.SmallA WHERE LEFT(TIMEVALUE, 1) = 0 ORDER BY INTKEY

The expected results is:
21 rows

Actual results returned:
24 rows

As you can see from the following there are 3 nulls which would make the 24 (21+3).
TimeValue
1900-01-01 00:00:00.0
1900-01-01 00:00:00.0
1900-01-01 00:00:00.0
1900-01-01 01:00:00.0
1900-01-01 01:00:00.0
1900-01-01 01:00:00.0
1900-01-01 02:00:00.0
1900-01-01 02:00:00.0
1900-01-01 03:00:00.0
1900-01-01 03:00:00.0
1900-01-01 04:00:00.0
1900-01-01 04:00:00.0
1900-01-01 05:00:00.0
1900-01-01 05:00:00.0
1900-01-01 06:00:00.0
1900-01-01 06:00:00.0
1900-01-01 07:00:00.0
1900-01-01 08:00:00.0
1900-01-01 08:00:00.0
1900-01-01 09:00:00.0
1900-01-01 09:00:00.0
1900-01-01 10:00:00.0
1900-01-01 10:00:00.0
1900-01-01 11:00:00.0
1900-01-01 11:00:00.0
1900-01-01 12:00:00.0
1900-01-01 12:00:00.0
1900-01-01 13:00:00.0
1900-01-01 13:00:00.0
1900-01-01 14:00:00.0
1900-01-01 14:00:00.0
1900-01-01 15:00:00.0
1900-01-01 16:00:00.0
1900-01-01 16:00:00.0
1900-01-01 17:00:00.0
1900-01-01 17:00:00.0
1900-01-01 18:00:00.0
1900-01-01 18:00:00.0
1900-01-01 19:00:00.0
1900-01-01 19:00:00.0
1900-01-01 20:00:00.0
1900-01-01 20:00:00.0
1900-01-01 21:00:00.0
1900-01-01 21:00:00.0
1900-01-01 22:00:00.0
1900-01-01 22:00:00.0
1900-01-01 23:00:00.0
<null>
<null>
<null>

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira