[teiid-issues] [JBoss JIRA] Updated: (TEIID-1158) Sybase CTC queries - Unexpected results when <null> involved
Warren Gibson (JIRA)
jira-events at lists.jboss.org
Wed Jul 14 18:20:58 EDT 2010
[ https://jira.jboss.org/browse/TEIID-1158?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Warren Gibson updated TEIID-1158:
---------------------------------
Description:
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>
was:
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>
> 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>
--
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
More information about the teiid-issues
mailing list