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

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-1909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12661036#comment-12661036 ] 

Steven Hawkins commented on TEIID-1909:
---------------------------------------

This may have already been addressed by TEIID-1852, which corrected several sf issues including date formats and time zone handling.  Can you see if the issue persists after patching?
                
> 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