[JBoss JIRA] (TEIID-6010) Salesforce: an implementation restriction when querying ContentDocumentLink table
8:31 a.m.
[
https://issues.redhat.com/browse/TEIID-6010?page=com.atlassian.jira.plugi...
]
Steven Hawkins edited comment on TEIID-6010 at 8/13/20 9:30 AM:
----------------------------------------------------------------
For me using all defaults with 34 connectivity get the following imported metadata:
{code:sql}
CREATE FOREIGN TABLE ContentDocumentLink (
Id string(18) NOT NULL AUTO_INCREMENT DEFAULT 'sf default' OPTIONS (NAMEINSOURCE
'Id', UPDATABLE FALSE, NATIVE_TYPE 'id', "teiid_sf:Calculated"
'false', "teiid_sf:Custom" 'false', "teiid_sf:Defaulted on
Create" 'true'),
LinkedEntityId string(18) NOT NULL OPTIONS (NAMEINSOURCE 'LinkedEntityId',
NATIVE_TYPE 'reference', "teiid_sf:Calculated" 'false',
"teiid_sf:Custom" 'false', "teiid_sf:Defaulted on Create"
'false'),
ContentDocumentId string(18) NOT NULL OPTIONS (NAMEINSOURCE 'ContentDocumentId',
NATIVE_TYPE 'reference', "teiid_sf:Calculated" 'false',
"teiid_sf:Custom" 'false', "teiid_sf:Defaulted on Create"
'false'),
IsDeleted boolean NOT NULL DEFAULT 'sf default' OPTIONS (NAMEINSOURCE
'IsDeleted', UPDATABLE FALSE, NATIVE_TYPE '_boolean',
"teiid_sf:Calculated" 'false', "teiid_sf:Custom"
'false', "teiid_sf:Defaulted on Create" 'true'),
ShareType string(40) OPTIONS (NAMEINSOURCE 'ShareType', NATIVE_TYPE
'restrictedpicklist', "teiid_sf:Calculated" 'false',
"teiid_sf:Custom" 'false', "teiid_sf:Defaulted on Create"
'false', "teiid_sf:Picklist Values" 'V,C,I'),
Visibility string(40) OPTIONS (NAMEINSOURCE 'Visibility', NATIVE_TYPE
'restrictedpicklist', "teiid_sf:Calculated" 'false',
"teiid_sf:Custom" 'false', "teiid_sf:Defaulted on Create"
'false', "teiid_sf:Picklist Values"
'AllUsers,InternalUsers,SharedUsers'),
...
{code}
The same pushdown query/plan:
{code}
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(0) output=[SalesForceModel.ContentDocumentLink.Id,
SalesForceModel.ContentDocumentLink.LinkedEntityId,
SalesForceModel.ContentDocumentLink.ContentDocumentId,
SalesForceModel.ContentDocumentLink.IsDeleted,
SalesForceModel.ContentDocumentLink.ShareType,
SalesForceModel.ContentDocumentLink.Visibility] SELECT g_0.Id, g_0.LinkedEntityId,
g_0.ContentDocumentId, g_0.IsDeleted, g_0.ShareType, g_0.Visibility FROM
SalesForceModel.ContentDocumentLink AS g_0 WHERE g_0.ContentDocumentId =
'0691t000002aeKaAAI'
{code}
And the soql query:
{code}
SELECT Id, LinkedEntityId, ContentDocumentId, IsDeleted, ShareType, Visibility FROM
ContentDocumentLink WHERE ContentDocumentId = '0691t000002aeKaAAI'
{code}
The difference is that this is on 15/master after TEIID-5988.
On Teiid 14 before that change the soql query has qualifications - "WHERE
ContentDocumentLink.ContentDocumentId ="
Which for some reason undocumented Salesforce treats differently than the unqualified
form. Can you confirm that this is already addressed in Teiid 15 for you?
was (Author: shawkins):
For me using all defaults with 34 connectivity get the following imported metadata:
{code:sql}
CREATE FOREIGN TABLE ContentDocumentLink (
Id string(18) NOT NULL AUTO_INCREMENT DEFAULT 'sf default' OPTIONS (NAMEINSOURCE
'Id', UPDATABLE FALSE, NATIVE_TYPE 'id', "teiid_sf:Calculated"
'false', "teiid_sf:Custom" 'false', "teiid_sf:Defaulted on
Create" 'true'),
LinkedEntityId string(18) NOT NULL OPTIONS (NAMEINSOURCE 'LinkedEntityId',
NATIVE_TYPE 'reference', "teiid_sf:Calculated" 'false',
"teiid_sf:Custom" 'false', "teiid_sf:Defaulted on Create"
'false'),
ContentDocumentId string(18) NOT NULL OPTIONS (NAMEINSOURCE 'ContentDocumentId',
NATIVE_TYPE 'reference', "teiid_sf:Calculated" 'false',
"teiid_sf:Custom" 'false', "teiid_sf:Defaulted on Create"
'false'),
IsDeleted boolean NOT NULL DEFAULT 'sf default' OPTIONS (NAMEINSOURCE
'IsDeleted', UPDATABLE FALSE, NATIVE_TYPE '_boolean',
"teiid_sf:Calculated" 'false', "teiid_sf:Custom"
'false', "teiid_sf:Defaulted on Create" 'true'),
ShareType string(40) OPTIONS (NAMEINSOURCE 'ShareType', NATIVE_TYPE
'restrictedpicklist', "teiid_sf:Calculated" 'false',
"teiid_sf:Custom" 'false', "teiid_sf:Defaulted on Create"
'false', "teiid_sf:Picklist Values" 'V,C,I'),
Visibility string(40) OPTIONS (NAMEINSOURCE 'Visibility', NATIVE_TYPE
'restrictedpicklist', "teiid_sf:Calculated" 'false',
"teiid_sf:Custom" 'false', "teiid_sf:Defaulted on Create"
'false', "teiid_sf:Picklist Values"
'AllUsers,InternalUsers,SharedUsers'),
...
{code}
The same pushdown query/plan:
{code}
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(0) output=[SalesForceModel.ContentDocumentLink.Id,
SalesForceModel.ContentDocumentLink.LinkedEntityId,
SalesForceModel.ContentDocumentLink.ContentDocumentId,
SalesForceModel.ContentDocumentLink.IsDeleted,
SalesForceModel.ContentDocumentLink.ShareType,
SalesForceModel.ContentDocumentLink.Visibility] SELECT g_0.Id, g_0.LinkedEntityId,
g_0.ContentDocumentId, g_0.IsDeleted, g_0.ShareType, g_0.Visibility FROM
SalesForceModel.ContentDocumentLink AS g_0 WHERE g_0.ContentDocumentId =
'0691t000002aeKaAAI'
{code}
And the soql query:
{code}
SELECT Id, LinkedEntityId, ContentDocumentId, IsDeleted, ShareType, Visibility FROM
ContentDocumentLink WHERE ContentDocumentId = '0691t000002aeKaAAI'
{code}
The difference is that this is on 15/master after TEIID-5988.
On Teiid 14 before that change the soql query has qualifications - "WHERE
ContentDocumentLink.ContentDocumentId ="
Which for some reason undocumented reason Salesforce treats differently, then the
unqualified form. Can you confirm that this is already addressed in Teiid 15 for you?
Salesforce: an implementation restriction when querying
ContentDocumentLink table
---------------------------------------------------------------------------------
Key: TEIID-6010
URL: https://issues.redhat.com/browse/TEIID-6010
Project: Teiid
Issue Type: Bug
Components: Salesforce Connector
Affects Versions: 14.0
Reporter: Dmitrii Pogorelov
Assignee: Steven Hawkins
Priority: Major
when running the following query:
{code:sql}
SELECT "Id", "LinkedEntityId", "ContentDocumentId",
"IsDeleted", "ShareType", "Visibility"
FROM "sf34.ContentDocumentLink"
WHERE "ContentDocumentId" = '0691t000002aeKaAAI' ;; {code}
Teiid throws out the stack trace:
{code:java}
2020-08-10 14:11:56,721 WARN [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue33)
SSrs8BwlD5AI Connector worker process failed for atomic-request=SSrs8BwlD5AI.14.0.0:
org.teiid.translator.TranslatorException
at
org.teiid.salesforce.BaseSalesforceConnection.query(BaseSalesforceConnection.java:198)
at
org.teiid.translator.salesforce.execution.QueryExecutionImpl.execute(QueryExecutionImpl.java:249)
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.$Proxy46.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)
Caused by:
[MalformedQueryFault [ApiQueryFault [ApiFault exceptionCode='MALFORMED_QUERY'
exceptionMessage='Implementation restriction: ContentDocumentLink requires a filter by
a single Id on ContentDocumentId or LinkedEntityId using the equals operator or multiple
Id's using the IN operator.'
]
row='-1'
column='-1'
]
]
at
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at
java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at
java.lang.Class.newInstance(Class.java:442)
at
com.sforce.ws.bind.TypeMapper.readSingle(TypeMapper.java:652)
at
com.sforce.ws.bind.TypeMapper.readObject(TypeMapper.java:529)
at
com.sforce.ws.transport.SoapConnection.parseDetail(SoapConnection.java:230)
at
com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:204)
at
com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:150)
at
com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:99)
at
com.sforce.soap.partner.PartnerConnection.query(PartnerConnection.java:1078)
at
org.teiid.salesforce.BaseSalesforceConnection.query(BaseSalesforceConnection.java:193)
... 19 more{code}
though the native query works:
{code:sql}
select w.* from (call sf34.native('search;SELECT Id, LinkedEntityId,
ContentDocumentId, IsDeleted, ShareType, Visibility FROM ContentDocumentLink WHERE
ContentDocumentId = ''0691t000002aeKaAAI''', null)) as w ;; {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
1375
days inactive
1375
days old
0 comments
1 participants
participants (1)
-
Steven Hawkins (Jira)