[teiid-issues] [JBoss JIRA] (TEIID-4402) Insert statement with a date field fails

Insert statement with a date field fails ---------------------------------------- Key: TEIID-4402 URL: https://issues.jboss.org/browse/TEIID-4402 Project: Teiid Issue Type: Bug Components: Salesforce Connector Affects Versions: 9.0.2 Environment: WildFly Full 9.0.2.Final (WildFly Core 1.0.2.Final) Teiid 9.0.2 jdk1.8.0_101 Salesforce sandbox Salesforce connectors / translators: salesforce and salesforce-34 Reporter: J Price Assignee: Steven Hawkins Fix For: 9.1, 9.0.4 I have a custom object created in Salesforce which has date fields. The model has the fields identified as date correctly. When specifying a date using an insert statement I receive an error back from Salesforce: _ org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30504 Sandbox: Start Date: value not of required type: 2016-03-17_ I have tried forcing it to a date using the parsedate function as well as passing it as is. I setup a proxy to try and identify the cause and found that the datatype being passed in the underlying SOAP request is xsd:string instead of xsd:date. Using fiddler I duplicated the underlying SOAP request, but changed the types to xsd:date, and resubmitted the request successfully. ---- As an example I have created a simple custom object in salesforce. The DDL for the imported object is as follows: {{ CREATE TABLE SimpleDateObjectDemo__c ( Id VARCHAR(18) DEFAULT Generated upon creation NOT NULL, OwnerId VARCHAR(18), IsDeleted BOOLEAN, Name VARCHAR(80), Start_Date__c DATE ); }} The following query produces the same error: {{ INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ('2001-01-01') }} The above query results in the following log messages: {{ 14:43:28,743 INFO [org.teiid.PLANNER] (Worker3_QueryProcessorQueue19) ffLmdSxeJfyw ============================================================================ USER COMMAND: INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}) ---------------------------------------------------------------------------- OPTIMIZE: INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}) ---------------------------------------------------------------------------- GENERATE CANONICAL: INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}) CANONICAL PLAN: Project(groups=[], props={PROJECT_COLS=[Count]}) Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})}) ============================================================================ EXECUTING PlaceAccess AFTER: Project(groups=[], props={PROJECT_COLS=[Count]}) Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e}) Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})}) ============================================================================ EXECUTING RaiseAccess AFTER: Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e}) Project(groups=[], props={PROJECT_COLS=[Count]}) Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})}) ============================================================================ EXECUTING AssignOutputElements AFTER: Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count]}) Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count]}) Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), OUTPUT_COLS=[Count]}) ============================================================================ EXECUTING CalculateCost AFTER: Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0}) Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0}) Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0}) ============================================================================ EXECUTING PlanSorts AFTER: Access(groups=[Sandbox.SimpleDateObjectDemo__c]) Project(groups=[]) Source(groups=[Sandbox.SimpleDateObjectDemo__c]) ============================================================================ EXECUTING CollapseSource AFTER: Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0, ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})}) ============================================================================ CONVERTING PLAN TREE TO PROCESS TREE PROCESS PLAN = AccessNode(0) output=[Count] INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}) ============================================================================ ---------------------------------------------------------------------------- OPTIMIZATION COMPLETE: PROCESSOR PLAN: AccessNode(0) output=[Count] INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}) ============================================================================ 14:43:30,088 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue20) ffLmdSxeJfyw Connector worker process failed for atomic-request=ffLmdSxeJfyw.0.0.0: org.teiid.translator.TranslatorException: Start Date: value not of required type: 2001-01-01 at org.teiid.translator.salesforce.execution.InsertExecutionImpl.execute(InsertExecutionImpl.java:121) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:403) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211) at com.sun.proxy.$Proxy57.execute(Unknown Source) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: javax.resource.ResourceException: Start Date: value not of required type: 2001-01-01 at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.analyzeResult(SalesforceConnectionImpl.java:403) at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.create(SalesforceConnectionImpl.java:368) at org.teiid.translator.salesforce.execution.InsertExecutionImpl.execute(InsertExecutionImpl.java:89) ... 19 more 14:43:30,093 WARN [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue21) ffLmdSxeJfyw TEIID30020 Processing exception for request ffLmdSxeJfyw.0 'TEIID30504 Sandbox: Start Date: value not of required type: 2001-01-01'. Originally TeiidProcessingException SalesforceConnectionImpl.java:403. Enable more detailed logging to see the entire stacktrace. }} The SOAP request produced for the above query is as follows: {{ <?xml version="1.0" encoding="UTF-8"?> <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <env:Header> <CallOptions xmlns="urn:partner.soap.sforce.com"> <client>RedHat/MetaMatrix/</client> <defaultNamespace xsi:nil="true" /> </CallOptions> <SessionHeader xmlns="urn:partner.soap.sforce.com"> <sessionId>REMOVED</sessionId> </SessionHeader> </env:Header> <env:Body> <m:create xmlns:m="urn:partner.soap.sforce.com" xmlns:sobj="urn:sobject.partner.soap.sforce.com"> <m:sObjects> <sobj:type xsi:type="xsd:string">SimpleDateObjectDemo__c</sobj:type> <sobj:Start_Date__c xsi:type="xsd:string">2001-01-01</sobj:Start_Date__c> </m:sObjects> </m:create> </env:Body> </env:Envelope> }} The XMI for the above table is as follows: {{ <relational:BaseTable xmi:uuid="mmuuid:878a18c9-359c-4756-b192-79ed7c91d7af" name="SimpleDateObjectDemo__c" nameInSource="SimpleDateObjectDemo__c"> <columns xmi:uuid="mmuuid:22b29abc-6d51-475e-891c-6a19a4869cee" name="Id" nameInSource="Id" nativeType="id" length="18" nullable="NO_NULLS" defaultValue="Generated upon creation" uniqueKeys="mmuuid/f1d6a7d7-cdff-4bc9-b1df-61096c31aacf"> <type href="http://www.w3.org/2001/XMLSchema#string"/> </columns> <columns xmi:uuid="mmuuid:4b64550b-057f-49d5-ac44-6f4275508a0c" name="OwnerId" nameInSource="OwnerId" nativeType="id" length="18"> <type href="http://www.w3.org/2001/XMLSchema#string"/> </columns> <columns xmi:uuid="mmuuid:cc2969bd-98fd-4918-b6e2-6c0b98407f8b" name="IsDeleted" nameInSource="IsDeleted" nativeType="boolean"> <type href="http://www.w3.org/2001/XMLSchema#boolean"/> </columns> <columns xmi:uuid="mmuuid:0c42849d-7b8c-4bc9-8472-bf976c9946db" name="Name" nameInSource="Name" nativeType="string" length="80"> <type href="http://www.w3.org/2001/XMLSchema#string"/> </columns> <columns xmi:uuid="mmuuid:c085614b-49b8-4e30-810b-13f0e8d337c5" name="Start_Date__c" nameInSource="Start_Date__c" nativeType="date"> <type href="http://www.w3.org/2001/XMLSchema#date"/> </columns> <primaryKey xmi:uuid="mmuuid:f1d6a7d7-cdff-4bc9-b1df-61096c31aacf" name="Id_PK" columns="mmuuid/22b29abc-6d51-475e-891c-6a19a4869cee"/> </relational:BaseTable> }}