Salesforce: UPDATE fails for BOOLEAN and DATE fields ---------------------------------------------------- Key: TEIID-5923 URL: https://issues.redhat.com/browse/TEIID-5923 Project: Teiid Issue Type: Bug Components: Salesforce Connector Affects Versions: 13.1 Reporter: Dmitrii Pogorelov Assignee: Steven Hawkins Priority: Critical Fix For: 14.0, 13.1.1 1. Running the following queries: {code:sql} --1.1 Update some BOOLEAN field and check if it's value changed (it should). --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: bo: value not of required type: FALSE update sf34.test_type__c set bo__c = false where str__c = 'test_updates' ;; --1.2 Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: bo: value not of required type: FALSE update sf34.test_type__c set bo__c = false ;; {code} will lead to the following error: {code} 2020-03-26 16:49:42,926 WARN [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue15) 1yWWIDJQwoJo Connector worker process failed for atomic-request=1yWWIDJQwoJo.3.0.3: org.teiid.translator.TranslatorException: bo: value not of required type: FALSE at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.analyzeResult(SalesforceConnectionImpl.java:431) at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.update(SalesforceConnectionImpl.java:425) at org.teiid.translator.salesforce.execution.UpdateExecutionImpl.processIds(UpdateExecutionImpl.java:74) at org.teiid.translator.salesforce.execution.AbstractUpdateExecution.execute(AbstractUpdateExecution.java:124) at org.teiid.translator.salesforce.execution.UpdateExecutionImpl.execute(UpdateExecutionImpl.java:51) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:443) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402) 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:228) at com.sun.proxy.$Proxy44.execute(Unknown Source) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124) at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212) 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) {code} {code:sql} --1.3 DEFECT(value has not changed) select OwnerId, bo__c, str__c from sf34.test_type__c where str__c = 'test_updates' ;; {code} 2. Running the following queries: {code:sql} --2.1 Insert some value into BOOLEAN field and check if it's there (it should be). --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: bo: value not of required type: true select w.* from (call sf34.native('create;type=test_type__c;attributes=OwnerId, bo__c, str__c', '005i0000002dtHTAAY', true, 'test_updates')) as w ;; {code} will lead to the following error: {code} 2020-03-26 16:53:31,525 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue29) 1yWWIDJQwoJo Connector worker process failed for atomic-request=1yWWIDJQwoJo.7.3.7: org.teiid.translator.TranslatorException: bo: value not of required type: true at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.analyzeResult(SalesforceConnectionImpl.java:431) at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.create(SalesforceConnectionImpl.java:396) at org.teiid.translator.salesforce.execution.DirectQueryExecution.doInsert(DirectQueryExecution.java:133) at org.teiid.translator.salesforce.execution.DirectQueryExecution.execute(DirectQueryExecution.java:97) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402) 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:228) at com.sun.proxy.$Proxy44.execute(Unknown Source) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124) at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212) 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) {code} {code:sql} --2.2 DEFECT(value has not changed) select OwnerId, bo__c, str__c from sf34.test_type__c where str__c = 'test_updates' ;; {code} 3. Running the following queries: {code:sql} --3.1 Update some DATE field and check if it's value changed (it should). --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type: {d '2002-11-11'} update sf34.Contact set Birthdate = '2002-11-11' where LastName = 'test_updates' ;; --3.2 Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type: {d '2002-11-11'} update sf34.Contact set Birthdate = cast('2002-11-11' as date) where LastName = 'test_updates' ;; {code} will lead to the following error: {code} 2020-03-26 16:56:03,868 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue42) 1yWWIDJQwoJo Connector worker process failed for atomic-request=1yWWIDJQwoJo.11.0.11: org.teiid.translator.TranslatorException: Birthdate: value not of required type: {d '2002-11-11'} at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.analyzeResult(SalesforceConnectionImpl.java:431) at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.update(SalesforceConnectionImpl.java:425) at org.teiid.translator.salesforce.execution.UpdateExecutionImpl.processIds(UpdateExecutionImpl.java:74) at org.teiid.translator.salesforce.execution.AbstractUpdateExecution.execute(AbstractUpdateExecution.java:124) at org.teiid.translator.salesforce.execution.UpdateExecutionImpl.execute(UpdateExecutionImpl.java:51) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:443) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402) 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:228) at com.sun.proxy.$Proxy44.execute(Unknown Source) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124) at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212) 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) {code} {code:sql} --3.3 DEFECT(value has not changed) select OwnerId, LastName, Birthdate from sf34.Contact where LastName = 'test_updates' ;; {code} 4. Running the following queries: {code:sql} --4.1 Insert some value into DATE field and check if it's there (it should be). --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type: 2020-03-22 select w.* from (call sf34.native('create;type=Contact;attributes=OwnerId, LastName, Birthdate', '005i0000002dtHTAAY', 'test_updates', '2020-03-22')) as w ;; --4.2 Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type: 2020-03-22 select w.* from (call sf34.native('create;type=Contact;attributes=OwnerId, LastName, Birthdate', '005i0000002dtHTAAY', 'test_updates', cast('2020-03-22' as date)) ) as w ;; {code} will lead to the following error: {code} 2020-03-26 16:57:49,333 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue51) 1yWWIDJQwoJo Connector worker process failed for atomic-request=1yWWIDJQwoJo.14.3.14: org.teiid.translator.TranslatorException: Birthdate: value not of required type: 2020-03-22 at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.analyzeResult(SalesforceConnectionImpl.java:431) at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.create(SalesforceConnectionImpl.java:396) at org.teiid.translator.salesforce.execution.DirectQueryExecution.doInsert(DirectQueryExecution.java:133) at org.teiid.translator.salesforce.execution.DirectQueryExecution.execute(DirectQueryExecution.java:97) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402) 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:228) at com.sun.proxy.$Proxy44.execute(Unknown Source) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124) at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212) 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) {code} {code:sql} --4.3 DEFECT(value has not changed) select OwnerId, LastName, Birthdate from sf34.Contact where LastName = 'test_updates' ;; {code}