[teiid-issues] [JBoss JIRA] (TEIID-6030) OFFSET LIMIT is not work in multiple joins SOQLs
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIID-6030?page=com.atlassian.jira.plugi... ]
Renat Eskenin edited comment on TEIID-6030 at 9/9/20 7:34 AM:
--------------------------------------------------------------
If you just remove task.Request_Type__c as \"{Task}/Request_Type__c\", and task.Type = :type you get same behavior
was (Author: i3draven):
If you just remove Type and task.Type = :type you get same behavior
> OFFSET LIMIT is not work in multiple joins SOQLs
> ------------------------------------------------
>
> Key: TEIID-6030
> URL: https://issues.redhat.com/browse/TEIID-6030
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
>
> In simple teiid spring boot applicatio we call SQL
> {code}
> select
> task.Id as \"{Task}/Id\",
> task.createddate as \"{Task}/CreatedDate\",
> owner.Name as \"{Owner}/Name\",
> contact.Name as \"{Contact}/Name\",
> lead.Name as \"{Lead}/Name\",
> task.ActivityDate as \"{Task}/ActivityDate\",
> task.Request_Type__c as \"{Task}/Request_Type__c\",
> task.subject as \"{Task}/Subject\",
> task.description as \"{Task}/Description\"
> from task
> LEFT OUTER JOIN /*+ MAKEDEP */ \"user\" AS creator ON task.CreatedById=creator.Id
> LEFT OUTER JOIN /*+ MAKEDEP */ \"user\" AS owner ON task.OwnerId=owner.Id
> LEFT OUTER JOIN /*+ MAKEDEP */ Contact AS contact ON task.whoid=contact.Id
> LEFT OUTER JOIN /*+ MAKEDEP */ Lead AS lead ON task.whoid=lead.Id
> where creator.Name = :name
> and task.Type = :type
> LIMIT 20 OFFSET 3000
> {code}
> And get Exception
> {code}
> org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select task.Id as \"{Task}/Id\", task.createddate as \"{Task}/CreatedDate\", owner.Name as \"{Owner}/Name\", contact.Name as \"{Contact}/Name\", lead.Name as \"{Lead}/Name\", task.ActivityDate as \"{Task}/ActivityDate\", task.Request_Type__c as \"{Task}/Request_Type__c\", task.subject as \"{Task}/Subject\", task.description as \"{Task}/Description\" from task LEFT OUTER JOIN /*+ MAKEDEP */ \"user\" AS creator ON task.CreatedById=creator.Id LEFT OUTER JOIN /*+ MAKEDEP */ \"user\" AS owner ON task.OwnerId=owner.Id LEFT OUTER JOIN /*+ MAKEDEP */ Contact AS contact ON task.whoid=contact.Id LEFT OUTER JOIN /*+ MAKEDEP */ Lead AS lead ON task.whoid=lead.Id where creator.Name = ? and task.Type = ? LIMIT 20 OFFSET 3000 ]; SQL state [50000]; error code [30504]; TEIID30504 salesforce: null; nested exception is org.teiid.jdbc.TeiidSQLException: TEIID30504 salesforce: null
> {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
1 week, 5 days
[JBoss JIRA] (TEIID-2605) Optimization substitutes wrong column in where clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2605?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2605.
---------------------------------
> Optimization substitutes wrong column in where clause
> -----------------------------------------------------
>
> Key: TEIID-2605
> URL: https://issues.jboss.org/browse/TEIID-2605
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.1
> Environment: z/OS
> Reporter: Jeff Hayes
> Assignee: Steven Hawkins
> Attachments: query_plan.txt, views.xml
>
>
> Optimization results in a query with a different column in the WHERE clause producing an empty result set.
> Full query plan is attached but the beginning and ending queries are shown below. Note that the subject column of the IN clause is SCOPEID but optimization changes it to AUTHID for some reason.
> USER COMMAND:
> SELECT * FROM SECURITY.SCPXREF AS CHORUS_B WHERE (CHORUS_B.SYSID = 'DE29') AND ((CHORUS_B.SCOPEID IN (SELECT SN5.SCOPEID FROM SECURI
> TY.SCPNEXT AS SN5 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN4.SCOPEID FROM SECURITY.SCPNEXT AS SN4 WHERE (SYSID = 'DE29') AND
> (NEXTREC IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURI
> TY.SCPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))))))) OR (CHORUS_B.SCOPEID IN (SELECT SN4.SCOPEID FROM SECURIT
> Y.SCPNEXT AS SN4 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND
> (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURITY.SCPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))))) OR (CHORUS_B.SC
> OPEID IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURITY.S
> CPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))) OR (CHORUS_B.SCOPEID IN (SELECT SN2.SCOPEID FROM SECURITY.SCPNEX
> T AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))) OR (CHORUS_B.SCOPEID = 'CHRDEPT1'))
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> AccessNode(10) output=[x.sysid AS sysid, x.scopeid AS authid, x.authid AS scopeid, x.authtype AS authtype] SELECT g_0.SYSID, g_0.SCO
> PEID, g_0.AUTHID, g_0.AUTHTYPE FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPXREF AS g_0 WHERE (g_0.SYSID = 'DE29') AND ((g_0.AUTHID IN
> (SELECT g_1.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_1 WHERE (g_1.SYSID = 'DE29') AND (g_1.NEXTREC IN (SELECT g_2
> .SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_2 WHERE (g_2.SYSID = 'DE29') AND (g_2.NEXTREC IN (SELECT g_3.SCOPEID FR
> OM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_3 WHERE (g_3.SYSID = 'DE29') AND (g_3.NEXTREC IN (SELECT g_4.SCOPEID FROM SECURITY
> _CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_4 WHERE (g_4.SYSID = 'DE29') AND (g_4.NEXTREC = 'CHRDEPT1'))))))))) OR (g_0.AUTHID IN (SELECT
> g_5.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_5 WHERE (g_5.SYSID = 'DE29') AND (g_5.NEXTREC IN (SELECT g_6.SCOPEI
> D FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_6 WHERE (g_6.SYSID = 'DE29') AND (g_6.NEXTREC IN (SELECT g_7.SCOPEID FROM SECU
> RITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_7 WHERE (g_7.SYSID = 'DE29') AND (g_7.NEXTREC = 'CHRDEPT1'))))))) OR (g_0.AUTHID IN (SELE
> CT g_8.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_8 WHERE (g_8.SYSID = 'DE29') AND (g_8.NEXTREC IN (SELECT g_9.SCOP
> EID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_9 WHERE (g_9.SYSID = 'DE29') AND (g_9.NEXTREC = 'CHRDEPT1'))))) OR (g_0.AUTH
> ID IN (SELECT g_10.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_10 WHERE (g_10.SYSID = 'DE29') AND (g_10.NEXTREC = 'C
> HRDEPT1'))) OR (g_0.AUTHID = 'CHRDEPT1'))
> The view definitions are shown below:
> <view name="SCPNEXT">
> <columns>
> <column name="sysid" type="varchar"/>
> <column name="scopeid" type="varchar"/>
> <column name="nextrec" type="varchar"/>
> </columns>
> <definition>
> #if ($db.count("select count(*) from sys.tables where Name='config' and SchemaName = 'security_db'") > 0)
> #set ($count = 0)
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> #if ($db.count("select count(*) from sys.tables where SchemaName = 'SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}'") == 0)
> #set ($count = $count + 1)
> #end
> #end
> #if ($count == 0)
> SELECT t.sysid, t.scopeid, t.nextrec
> FROM (
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> SELECT n.sysid, n.scopeid, n.nextrec
> FROM SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}.SCPNEXT n
> #if( $velocityHasNext ) UNION #end
> #end
> ) AS t
> #end
> #end
> </definition>
> </view>
> <view name="SCPXREF">
> <columns>
> <column name="sysid" type="varchar"/>
> <column name="authid" type="varchar"/>
> <column name="scopeid" type="varchar"/>
> <column name="authtype" type="varchar"/>
> </columns>
> <definition>
> #if ($db.count("select count(*) from sys.tables where Name='config' and SchemaName = 'security_db'") > 0)
> #set ($count = 0)
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> #if ($db.count("select count(*) from sys.tables where SchemaName = 'SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}'") == 0)
> #set ($count = $count + 1)
> #end
> #end
> #if ($count == 0)
> SELECT sysid, scopeid, authid, authtype
> FROM (
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> SELECT x.sysid, x.scopeid, x.authid, x.authtype
> FROM SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}.SCPXREF x
> #if( $velocityHasNext ) UNION #end
> #end
> ) AS t
> #end
> #end
> </definition>
> </view>
> I guess the best way to test this is to define these views and run the input query with SHOWPLAN=DEBUG and see if the AUTHID substitution is occurring.
> Thanks!
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
1 year
[teiid-issues] [Red Hat JIRA] (TEIID-6069) Malformed SOQL for ORDER BY in salesforce connector
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIID-6069?page=com.atlassian.jira.plugi... ]
Renat Eskenin commented on TEIID-6069:
--------------------------------------
Thank you.
> Malformed SOQL for ORDER BY in salesforce connector
> ----------------------------------------------------
>
> Key: TEIID-6069
> URL: https://issues.redhat.com/browse/TEIID-6069
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 17.0
>
>
> We have simple request
> {code}
> SELECT
> AccountId,
> Account.Name
> FROM Contact
> left OUTER JOIN /*+ MAKEDEP */
> Account ON
> Account.Id = Contact.AccountId
> WHERE Email = 'test(a)test.com'
> ORDER BY Contact.CreatedDate DESC NULLS LAST
> LIMIT 1
> {code}
> And for this sql generated plan by teiid is:
> {code}
> PLAN:
> ProjectNode
> + Relational Node ID:0
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> LimitNode
> + Relational Node ID:1
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> SortNode
> + Relational Node ID:2
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> AccessNode
> + Relational Node ID:3
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> 2: CreatedDate (timestamp)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Query:SELECT g_0.AccountId, g_1.Name, g_0.CreatedDate FROM salesforce.Contact AS g_0 LEFT OUTER JOIN salesforce.Account AS g_1 ON g_1.Id = g_0.AccountId WHERE g_0.Email = 'test(a)test.com'
> + Model Name:salesforce
> + Sort Columns:[salesforce.Contact.CreatedDate DESC NULLS LAST]
> + Sort FrameMode:SORT
> + Row Offset:null
> + Row Limit:1
> + Select Columns:
> 0: salesforce.Contact.AccountId
> 1: salesforce.Account.Name
> + Data Bytes Sent:7
> + Planning Time:1
> END PLAN
> DEBUG LOG :
> nullEND DEBUG LOG
> {code}
> And we got exception:
> {code}
> Caused by: com.sforce.soap.partner.fault.MalformedQueryFault: null
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
> at java.base/java.lang.Class.newInstance(Class.java:584)
> at com.sforce.ws.bind.TypeMapper.readSingle(TypeMapper.java:673)
> at com.sforce.ws.bind.TypeMapper.readObject(TypeMapper.java:556)
> at com.sforce.ws.transport.SoapConnection.parseDetail(SoapConnection.java:236)
> at com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:210)
> at com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:156)
> at com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:99)
> at com.sforce.soap.partner.PartnerConnection.query(PartnerConnection.java:1197)
> at org.teiid.salesforce.BaseSalesforceConnection.query(BaseSalesforceConnection.java:220)
> {code}
> But if we switch to supportsOrderBy false
> then plan generated well.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 9 months
[teiid-issues] [Red Hat JIRA] (TEIID-6069) Malformed SOQL for ORDER BY in salesforce connector
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-6069?page=com.atlassian.jira.plugi... ]
Steven Hawkins resolved TEIID-6069.
-----------------------------------
Fix Version/s: 17.0
Resolution: Done
Merged [~i3draven] fix into master.
> Malformed SOQL for ORDER BY in salesforce connector
> ----------------------------------------------------
>
> Key: TEIID-6069
> URL: https://issues.redhat.com/browse/TEIID-6069
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 17.0
>
>
> We have simple request
> {code}
> SELECT
> AccountId,
> Account.Name
> FROM Contact
> left OUTER JOIN /*+ MAKEDEP */
> Account ON
> Account.Id = Contact.AccountId
> WHERE Email = 'test(a)test.com'
> ORDER BY Contact.CreatedDate DESC NULLS LAST
> LIMIT 1
> {code}
> And for this sql generated plan by teiid is:
> {code}
> PLAN:
> ProjectNode
> + Relational Node ID:0
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> LimitNode
> + Relational Node ID:1
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> SortNode
> + Relational Node ID:2
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> AccessNode
> + Relational Node ID:3
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> 2: CreatedDate (timestamp)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Query:SELECT g_0.AccountId, g_1.Name, g_0.CreatedDate FROM salesforce.Contact AS g_0 LEFT OUTER JOIN salesforce.Account AS g_1 ON g_1.Id = g_0.AccountId WHERE g_0.Email = 'test(a)test.com'
> + Model Name:salesforce
> + Sort Columns:[salesforce.Contact.CreatedDate DESC NULLS LAST]
> + Sort FrameMode:SORT
> + Row Offset:null
> + Row Limit:1
> + Select Columns:
> 0: salesforce.Contact.AccountId
> 1: salesforce.Account.Name
> + Data Bytes Sent:7
> + Planning Time:1
> END PLAN
> DEBUG LOG :
> nullEND DEBUG LOG
> {code}
> And we got exception:
> {code}
> Caused by: com.sforce.soap.partner.fault.MalformedQueryFault: null
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
> at java.base/java.lang.Class.newInstance(Class.java:584)
> at com.sforce.ws.bind.TypeMapper.readSingle(TypeMapper.java:673)
> at com.sforce.ws.bind.TypeMapper.readObject(TypeMapper.java:556)
> at com.sforce.ws.transport.SoapConnection.parseDetail(SoapConnection.java:236)
> at com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:210)
> at com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:156)
> at com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:99)
> at com.sforce.soap.partner.PartnerConnection.query(PartnerConnection.java:1197)
> at org.teiid.salesforce.BaseSalesforceConnection.query(BaseSalesforceConnection.java:220)
> {code}
> But if we switch to supportsOrderBy false
> then plan generated well.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 9 months
[teiid-issues] [Red Hat JIRA] (TEIID-6069) Malformed SOQL for ORDER BY in salesforce connector
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-6069?page=com.atlassian.jira.plugi... ]
Work on TEIID-6069 started by Steven Hawkins.
---------------------------------------------
> Malformed SOQL for ORDER BY in salesforce connector
> ----------------------------------------------------
>
> Key: TEIID-6069
> URL: https://issues.redhat.com/browse/TEIID-6069
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
>
> We have simple request
> {code}
> SELECT
> AccountId,
> Account.Name
> FROM Contact
> left OUTER JOIN /*+ MAKEDEP */
> Account ON
> Account.Id = Contact.AccountId
> WHERE Email = 'test(a)test.com'
> ORDER BY Contact.CreatedDate DESC NULLS LAST
> LIMIT 1
> {code}
> And for this sql generated plan by teiid is:
> {code}
> PLAN:
> ProjectNode
> + Relational Node ID:0
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> LimitNode
> + Relational Node ID:1
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> SortNode
> + Relational Node ID:2
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> AccessNode
> + Relational Node ID:3
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> 2: CreatedDate (timestamp)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Query:SELECT g_0.AccountId, g_1.Name, g_0.CreatedDate FROM salesforce.Contact AS g_0 LEFT OUTER JOIN salesforce.Account AS g_1 ON g_1.Id = g_0.AccountId WHERE g_0.Email = 'test(a)test.com'
> + Model Name:salesforce
> + Sort Columns:[salesforce.Contact.CreatedDate DESC NULLS LAST]
> + Sort FrameMode:SORT
> + Row Offset:null
> + Row Limit:1
> + Select Columns:
> 0: salesforce.Contact.AccountId
> 1: salesforce.Account.Name
> + Data Bytes Sent:7
> + Planning Time:1
> END PLAN
> DEBUG LOG :
> nullEND DEBUG LOG
> {code}
> And we got exception:
> {code}
> Caused by: com.sforce.soap.partner.fault.MalformedQueryFault: null
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
> at java.base/java.lang.Class.newInstance(Class.java:584)
> at com.sforce.ws.bind.TypeMapper.readSingle(TypeMapper.java:673)
> at com.sforce.ws.bind.TypeMapper.readObject(TypeMapper.java:556)
> at com.sforce.ws.transport.SoapConnection.parseDetail(SoapConnection.java:236)
> at com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:210)
> at com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:156)
> at com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:99)
> at com.sforce.soap.partner.PartnerConnection.query(PartnerConnection.java:1197)
> at org.teiid.salesforce.BaseSalesforceConnection.query(BaseSalesforceConnection.java:220)
> {code}
> But if we switch to supportsOrderBy false
> then plan generated well.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 9 months
[teiid-issues] [Red Hat JIRA] (TEIID-6069) Malformed SOQL for ORDER BY in salesforce connector
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIID-6069?page=com.atlassian.jira.plugi... ]
Renat Eskenin commented on TEIID-6069:
--------------------------------------
I made pull request with fix
https://github.com/teiid/teiid/pull/1339
> Malformed SOQL for ORDER BY in salesforce connector
> ----------------------------------------------------
>
> Key: TEIID-6069
> URL: https://issues.redhat.com/browse/TEIID-6069
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
>
> We have simple request
> {code}
> SELECT
> AccountId,
> Account.Name
> FROM Contact
> left OUTER JOIN /*+ MAKEDEP */
> Account ON
> Account.Id = Contact.AccountId
> WHERE Email = 'test(a)test.com'
> ORDER BY Contact.CreatedDate DESC NULLS LAST
> LIMIT 1
> {code}
> And for this sql generated plan by teiid is:
> {code}
> PLAN:
> ProjectNode
> + Relational Node ID:0
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> LimitNode
> + Relational Node ID:1
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> SortNode
> + Relational Node ID:2
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> AccessNode
> + Relational Node ID:3
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> 2: CreatedDate (timestamp)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Query:SELECT g_0.AccountId, g_1.Name, g_0.CreatedDate FROM salesforce.Contact AS g_0 LEFT OUTER JOIN salesforce.Account AS g_1 ON g_1.Id = g_0.AccountId WHERE g_0.Email = 'test(a)test.com'
> + Model Name:salesforce
> + Sort Columns:[salesforce.Contact.CreatedDate DESC NULLS LAST]
> + Sort FrameMode:SORT
> + Row Offset:null
> + Row Limit:1
> + Select Columns:
> 0: salesforce.Contact.AccountId
> 1: salesforce.Account.Name
> + Data Bytes Sent:7
> + Planning Time:1
> END PLAN
> DEBUG LOG :
> nullEND DEBUG LOG
> {code}
> And we got exception:
> {code}
> Caused by: com.sforce.soap.partner.fault.MalformedQueryFault: null
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
> at java.base/java.lang.Class.newInstance(Class.java:584)
> at com.sforce.ws.bind.TypeMapper.readSingle(TypeMapper.java:673)
> at com.sforce.ws.bind.TypeMapper.readObject(TypeMapper.java:556)
> at com.sforce.ws.transport.SoapConnection.parseDetail(SoapConnection.java:236)
> at com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:210)
> at com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:156)
> at com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:99)
> at com.sforce.soap.partner.PartnerConnection.query(PartnerConnection.java:1197)
> at org.teiid.salesforce.BaseSalesforceConnection.query(BaseSalesforceConnection.java:220)
> {code}
> But if we switch to supportsOrderBy false
> then plan generated well.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 9 months
[teiid-issues] [Red Hat JIRA] (TEIID-6069) Malformed SOQL for ORDER BY in salesforce connector
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIID-6069?page=com.atlassian.jira.plugi... ]
Renat Eskenin edited comment on TEIID-6069 at 1/29/21 10:16 AM:
----------------------------------------------------------------
Generated SOQL for this SQL is
{code}
SELECT AccountId, Account.Name, CreatedDate FROM Contact WHERE Email = 'test(a)test.com' ORDER BY CreatedDate DESC NULLS LASTLIMIT 1
{code}
Note: look at "LASTLIMIT 1"
was (Author: i3draven):
Generated SOQL for this SQL is
{code}
SELECT AccountId, Account.Name, CreatedDate FROM Contact WHERE Email = 'test(a)test.com ORDER BY CreatedDate DESC NULLS LASTLIMIT 1
{code}
Note: look at "LASTLIMIT 1" and "'test(a)test.com" without quota
> Malformed SOQL for ORDER BY in salesforce connector
> ----------------------------------------------------
>
> Key: TEIID-6069
> URL: https://issues.redhat.com/browse/TEIID-6069
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
>
> We have simple request
> {code}
> SELECT
> AccountId,
> Account.Name
> FROM Contact
> left OUTER JOIN /*+ MAKEDEP */
> Account ON
> Account.Id = Contact.AccountId
> WHERE Email = 'test(a)test.com'
> ORDER BY Contact.CreatedDate DESC NULLS LAST
> LIMIT 1
> {code}
> And for this sql generated plan by teiid is:
> {code}
> PLAN:
> ProjectNode
> + Relational Node ID:0
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> LimitNode
> + Relational Node ID:1
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> SortNode
> + Relational Node ID:2
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> AccessNode
> + Relational Node ID:3
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> 2: CreatedDate (timestamp)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Query:SELECT g_0.AccountId, g_1.Name, g_0.CreatedDate FROM salesforce.Contact AS g_0 LEFT OUTER JOIN salesforce.Account AS g_1 ON g_1.Id = g_0.AccountId WHERE g_0.Email = 'test(a)test.com'
> + Model Name:salesforce
> + Sort Columns:[salesforce.Contact.CreatedDate DESC NULLS LAST]
> + Sort FrameMode:SORT
> + Row Offset:null
> + Row Limit:1
> + Select Columns:
> 0: salesforce.Contact.AccountId
> 1: salesforce.Account.Name
> + Data Bytes Sent:7
> + Planning Time:1
> END PLAN
> DEBUG LOG :
> nullEND DEBUG LOG
> {code}
> And we got exception:
> {code}
> Caused by: com.sforce.soap.partner.fault.MalformedQueryFault: null
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
> at java.base/java.lang.Class.newInstance(Class.java:584)
> at com.sforce.ws.bind.TypeMapper.readSingle(TypeMapper.java:673)
> at com.sforce.ws.bind.TypeMapper.readObject(TypeMapper.java:556)
> at com.sforce.ws.transport.SoapConnection.parseDetail(SoapConnection.java:236)
> at com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:210)
> at com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:156)
> at com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:99)
> at com.sforce.soap.partner.PartnerConnection.query(PartnerConnection.java:1197)
> at org.teiid.salesforce.BaseSalesforceConnection.query(BaseSalesforceConnection.java:220)
> {code}
> But if we switch to supportsOrderBy false
> then plan generated well.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 9 months
[teiid-issues] [Red Hat JIRA] (TEIID-6069) Malformed SOQL for ORDER BY in salesforce connector
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIID-6069?page=com.atlassian.jira.plugi... ]
Renat Eskenin edited comment on TEIID-6069 at 1/29/21 10:02 AM:
----------------------------------------------------------------
Generated SOQL for this SQL is
{code}
SELECT AccountId, Account.Name, CreatedDate FROM Contact WHERE Email = 'test(a)test.com ORDER BY CreatedDate DESC NULLS LASTLIMIT 1
{code}
Note: look at "LASTLIMIT 1" and "'test(a)test.com" without quota
was (Author: i3draven):
Generated SOQL for this SQL is
{code}
SELECT AccountId, Account.Name, CreatedDate FROM Contact WHERE Email = 'test(a)test.com ORDER BY CreatedDate DESC NULLS LASTLIMIT 1
{code}
Note: look at "LASTLIMIT 1"
> Malformed SOQL for ORDER BY in salesforce connector
> ----------------------------------------------------
>
> Key: TEIID-6069
> URL: https://issues.redhat.com/browse/TEIID-6069
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
>
> We have simple request
> {code}
> SELECT
> AccountId,
> Account.Name
> FROM Contact
> left OUTER JOIN /*+ MAKEDEP */
> Account ON
> Account.Id = Contact.AccountId
> WHERE Email = 'test(a)test.com'
> ORDER BY Contact.CreatedDate DESC NULLS LAST
> LIMIT 1
> {code}
> And for this sql generated plan by teiid is:
> {code}
> PLAN:
> ProjectNode
> + Relational Node ID:0
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> LimitNode
> + Relational Node ID:1
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> SortNode
> + Relational Node ID:2
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> AccessNode
> + Relational Node ID:3
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> 2: CreatedDate (timestamp)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Query:SELECT g_0.AccountId, g_1.Name, g_0.CreatedDate FROM salesforce.Contact AS g_0 LEFT OUTER JOIN salesforce.Account AS g_1 ON g_1.Id = g_0.AccountId WHERE g_0.Email = 'test(a)test.com'
> + Model Name:salesforce
> + Sort Columns:[salesforce.Contact.CreatedDate DESC NULLS LAST]
> + Sort FrameMode:SORT
> + Row Offset:null
> + Row Limit:1
> + Select Columns:
> 0: salesforce.Contact.AccountId
> 1: salesforce.Account.Name
> + Data Bytes Sent:7
> + Planning Time:1
> END PLAN
> DEBUG LOG :
> nullEND DEBUG LOG
> {code}
> And we got exception:
> {code}
> Caused by: com.sforce.soap.partner.fault.MalformedQueryFault: null
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
> at java.base/java.lang.Class.newInstance(Class.java:584)
> at com.sforce.ws.bind.TypeMapper.readSingle(TypeMapper.java:673)
> at com.sforce.ws.bind.TypeMapper.readObject(TypeMapper.java:556)
> at com.sforce.ws.transport.SoapConnection.parseDetail(SoapConnection.java:236)
> at com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:210)
> at com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:156)
> at com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:99)
> at com.sforce.soap.partner.PartnerConnection.query(PartnerConnection.java:1197)
> at org.teiid.salesforce.BaseSalesforceConnection.query(BaseSalesforceConnection.java:220)
> {code}
> But if we switch to supportsOrderBy false
> then plan generated well.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 9 months
[teiid-issues] [Red Hat JIRA] (TEIID-6069) Malformed SOQL for ORDER BY in salesforce connector
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIID-6069?page=com.atlassian.jira.plugi... ]
Renat Eskenin updated TEIID-6069:
---------------------------------
Summary: Malformed SOQL for ORDER BY in salesforce connector (was: Wrong plan for ORDER BY in salesforce connector )
> Malformed SOQL for ORDER BY in salesforce connector
> ----------------------------------------------------
>
> Key: TEIID-6069
> URL: https://issues.redhat.com/browse/TEIID-6069
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
>
> We have simple request
> {code}
> SELECT
> AccountId,
> Account.Name
> FROM Contact
> left OUTER JOIN /*+ MAKEDEP */
> Account ON
> Account.Id = Contact.AccountId
> WHERE Email = 'test(a)test.com'
> ORDER BY Contact.CreatedDate DESC NULLS LAST
> LIMIT 1
> {code}
> And for this sql generated plan by teiid is:
> {code}
> PLAN:
> ProjectNode
> + Relational Node ID:0
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> LimitNode
> + Relational Node ID:1
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> SortNode
> + Relational Node ID:2
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> AccessNode
> + Relational Node ID:3
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> 2: CreatedDate (timestamp)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Query:SELECT g_0.AccountId, g_1.Name, g_0.CreatedDate FROM salesforce.Contact AS g_0 LEFT OUTER JOIN salesforce.Account AS g_1 ON g_1.Id = g_0.AccountId WHERE g_0.Email = 'test(a)test.com'
> + Model Name:salesforce
> + Sort Columns:[salesforce.Contact.CreatedDate DESC NULLS LAST]
> + Sort FrameMode:SORT
> + Row Offset:null
> + Row Limit:1
> + Select Columns:
> 0: salesforce.Contact.AccountId
> 1: salesforce.Account.Name
> + Data Bytes Sent:7
> + Planning Time:1
> END PLAN
> DEBUG LOG :
> nullEND DEBUG LOG
> {code}
> And we got exception:
> {code}
> Caused by: com.sforce.soap.partner.fault.MalformedQueryFault: null
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
> at java.base/java.lang.Class.newInstance(Class.java:584)
> at com.sforce.ws.bind.TypeMapper.readSingle(TypeMapper.java:673)
> at com.sforce.ws.bind.TypeMapper.readObject(TypeMapper.java:556)
> at com.sforce.ws.transport.SoapConnection.parseDetail(SoapConnection.java:236)
> at com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:210)
> at com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:156)
> at com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:99)
> at com.sforce.soap.partner.PartnerConnection.query(PartnerConnection.java:1197)
> at org.teiid.salesforce.BaseSalesforceConnection.query(BaseSalesforceConnection.java:220)
> {code}
> But if we switch to supportsOrderBy false
> then plan generated well.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 9 months
[teiid-issues] [Red Hat JIRA] (TEIID-6069) Wrong plan for ORDER BY in salesforce connector
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIID-6069?page=com.atlassian.jira.plugi... ]
Renat Eskenin edited comment on TEIID-6069 at 1/29/21 8:39 AM:
---------------------------------------------------------------
Generated SOQL for this SQL is
{code}
SELECT AccountId, Account.Name, CreatedDate FROM Contact WHERE Email = 'test(a)test.com ORDER BY CreatedDate DESC NULLS LASTLIMIT 1
{code}
Note: look at "LASTLIMIT 1"
was (Author: i3draven):
Generated SOQL for this SQL is
SELECT AccountId, Account.Name, CreatedDate FROM Contact WHERE Email = 'test(a)test.com ORDER BY CreatedDate DESC NULLS LASTLIMIT 1
Note: look at "LASTLIMIT 1"
> Wrong plan for ORDER BY in salesforce connector
> ------------------------------------------------
>
> Key: TEIID-6069
> URL: https://issues.redhat.com/browse/TEIID-6069
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
>
> We have simple request
> {code}
> SELECT
> AccountId,
> Account.Name
> FROM Contact
> left OUTER JOIN /*+ MAKEDEP */
> Account ON
> Account.Id = Contact.AccountId
> WHERE Email = 'test(a)test.com'
> ORDER BY Contact.CreatedDate DESC NULLS LAST
> LIMIT 1
> {code}
> And for this sql generated plan by teiid is:
> {code}
> PLAN:
> ProjectNode
> + Relational Node ID:0
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> LimitNode
> + Relational Node ID:1
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: 1.0
> + Child 0:
> SortNode
> + Relational Node ID:2
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> AccessNode
> + Relational Node ID:3
> + Output Columns:
> 0: AccountId (string)
> 1: Name (string)
> 2: CreatedDate (timestamp)
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Query:SELECT g_0.AccountId, g_1.Name, g_0.CreatedDate FROM salesforce.Contact AS g_0 LEFT OUTER JOIN salesforce.Account AS g_1 ON g_1.Id = g_0.AccountId WHERE g_0.Email = 'test(a)test.com'
> + Model Name:salesforce
> + Sort Columns:[salesforce.Contact.CreatedDate DESC NULLS LAST]
> + Sort FrameMode:SORT
> + Row Offset:null
> + Row Limit:1
> + Select Columns:
> 0: salesforce.Contact.AccountId
> 1: salesforce.Account.Name
> + Data Bytes Sent:7
> + Planning Time:1
> END PLAN
> DEBUG LOG :
> nullEND DEBUG LOG
> {code}
> And we got exception:
> {code}
> Caused by: com.sforce.soap.partner.fault.MalformedQueryFault: null
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
> at java.base/java.lang.Class.newInstance(Class.java:584)
> at com.sforce.ws.bind.TypeMapper.readSingle(TypeMapper.java:673)
> at com.sforce.ws.bind.TypeMapper.readObject(TypeMapper.java:556)
> at com.sforce.ws.transport.SoapConnection.parseDetail(SoapConnection.java:236)
> at com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:210)
> at com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:156)
> at com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:99)
> at com.sforce.soap.partner.PartnerConnection.query(PartnerConnection.java:1197)
> at org.teiid.salesforce.BaseSalesforceConnection.query(BaseSalesforceConnection.java:220)
> {code}
> But if we switch to supportsOrderBy false
> then plan generated well.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 9 months