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

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 Attachments: QT_Syb125_Push.vdb, QT_Sybase125_Push-ds.xml 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 1 CharValue 0 However, the following is being returned: IntKey 1 CharValue 0 IntKey 10 CharValue <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>