[teiid-issues] [JBoss JIRA] (TEIID-1909) Teiid Salesforce Connector with date clause not returning correct data

[Graeme Gillies (JIRA)]

Graeme Gillies created TEIID-1909:
-------------------------------------

             Summary: Teiid Salesforce Connector with date clause not returning correct data
                 Key: TEIID-1909
                 URL: https://issues.jboss.org/browse/TEIID-1909
             Project: Teiid
          Issue Type: Bug
          Components: Salesforce Connector
    Affects Versions: 7.5
         Environment: java version "1.6.0_20"
OpenJDK Runtime Environment (IcedTea6 1.9.10) (rhel-1.23.1.9.10.el5_7-x86_64)
OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode)
on
Red Hat Enterprise Linux Server release 5.7 (Tikanga) 
            Reporter: Graeme Gillies
            Assignee: Steven Hawkins
            Priority: Minor


We have a user using the salesforce connector within teiid, and when they execute a query as follows

{noformat}
SELECT
SFDC.salesforce.Case_.Origin
,SFDC.salesforce.Case_.CreatedDate
,SFDC.salesforce.Case_.CaseNumber
FROM
SFDC.salesforce.Case_
WHERE
Product__c = 'Red Hat Enterprise Linux'
AND CreatedDate >= '2009-03-01 00:00:00'
AND CreatedDate < '2009-03-02 00:00:00'
AND Origin IN (
'Web'
,'Phone'
) order by createddate;
{noformat}

They get data like follows (casenumber removed)

{noformat}
 origin |      createddate      | casenumber 
--------+-----------------------+------------
 Web    | 2009-03-01 06:10:12.0 | 
 Web    | 2009-03-01 09:47:35.0 | 
 Web    | 2009-03-01 09:55:45.0 | 
 Web    | 2009-03-01 10:04:07.0 | 
 Web    | 2009-03-01 10:25:06.0 | 
 Web    | 2009-03-01 11:20:18.0 | 
 Web    | 2009-03-01 11:25:11.0 | 
 Web    | 2009-03-01 13:23:36.0 | 
 Web    | 2009-03-01 13:43:22.0 | 
 Phone  | 2009-03-01 15:03:10.0 | 
 Web    | 2009-03-01 15:06:35.0 | 
 Web    | 2009-03-01 15:35:18.0 | 
 Web    | 2009-03-01 17:42:50.0 | 
 Phone  | 2009-03-01 18:15:42.0 | 
 Phone  | 2009-03-01 19:15:34.0 | 
 Phone  | 2009-03-01 19:44:43.0 | 
 Web    | 2009-03-01 20:09:12.0 | 
 Web    | 2009-03-01 20:25:24.0 | 
 Web    | 2009-03-01 20:38:08.0 | 
 Web    | 2009-03-01 22:50:45.0 | 
 Web    | 2009-03-01 23:32:53.0 | 
 Web    | 2009-03-01 23:58:01.0 | 
 Web    | 2009-03-02 00:11:43.0 | 
 Web    | 2009-03-02 00:30:52.0 | 
 Web    | 2009-03-02 00:35:19.0 | 
 Web    | 2009-03-02 00:37:49.0 | 
 Web    | 2009-03-02 00:44:57.0 | 
 Web    | 2009-03-02 01:21:17.0 | 
 Web    | 2009-03-02 01:24:20.0 | 
 Phone  | 2009-03-02 01:32:14.0 | 
 Phone  | 2009-03-02 01:54:19.0 | 
 Phone  | 2009-03-02 01:58:18.0 | 
 Phone  | 2009-03-02 02:17:56.0 | 
 Web    | 2009-03-02 02:32:55.0 | 
 Phone  | 2009-03-02 02:34:35.0 | 
 Web    | 2009-03-02 02:35:21.0 | 
 Phone  | 2009-03-02 02:37:16.0 | 
 Phone  | 2009-03-02 02:42:21.0 | 
 Phone  | 2009-03-02 02:46:03.0 | 
 Phone  | 2009-03-02 02:50:16.0 | 
 Web    | 2009-03-02 02:50:48.0 | 
 Phone  | 2009-03-02 02:54:29.0 | 
 Phone  | 2009-03-02 02:55:22.0 | 
 Phone  | 2009-03-02 02:57:11.0 | 
 Phone  | 2009-03-02 02:59:54.0 | 
 Phone  | 2009-03-02 03:00:54.0 | 
 Phone  | 2009-03-02 03:04:01.0 | 
 Phone  | 2009-03-02 03:06:38.0 | 
 Phone  | 2009-03-02 03:10:07.0 | 
 Phone  | 2009-03-02 03:12:11.0 | 
 Phone  | 2009-03-02 03:14:36.0 | 
 Web    | 2009-03-02 03:56:52.0 | 
 Phone  | 2009-03-02 04:07:16.0 | 
 Web    | 2009-03-02 04:12:25.0 | 
 Web    | 2009-03-02 04:19:47.0 | 
 Web    | 2009-03-02 04:27:17.0 | 
 Web    | 2009-03-02 04:36:03.0 | 
 Web    | 2009-03-02 04:36:10.0 | 
{noformat}

As you can see, we are missing a bunch of data between 2009-03-01 00:00:00.0 and 2009-03-01 00:06:00.0 as well as getting data after 2009-03-02 00:00:00.0 which according to the where clause we shouldn't be getting.

I'm assuming this is some sort of timezone conversion problem, but I'm not sure how to go about fixing it.

Regards,

Graeme

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira