[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)
6 days, 20 hours
[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-6072) Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-6072?page=com.atlassian.jira.plugi... ]
Steven Hawkins resolved TEIID-6072.
-----------------------------------
Resolution: Won't Fix
This is a bug with Olingo's ODataXmlSerializer.writeEntity - they expect a non null MediaContentType. This is not the case in the JSON serializer.
Given the turnaround for this is likely months, we're marking as Won't Fix.
> Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
> --------------------------------------------------------------------------
>
> Key: TEIID-6072
> URL: https://issues.redhat.com/browse/TEIID-6072
> Project: Teiid
> Issue Type: Bug
> Components: OData, spring-boot, XML Planner/Processor
> Affects Versions: 15.0.1
> Reporter: Marc Kusters
> Assignee: Ramesh Reddy
> Priority: Major
> Fix For: 17.0
>
> Attachments: json_example, odata_xml_medialink_null_pointer_top1
>
>
> When serving a view with an streamable object (a blob) which is in our case a document through OData we get an error when the XML format is used. The JSON format gives us as expected a working media edit link.
> This is with Spring boot starter 1.6.0. I'm not quite sure what components are responsible for OData or XML.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 8 months
[teiid-issues] [Red Hat JIRA] (TEIID-6072) Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
by Marc Kusters (Jira)
[ https://issues.redhat.com/browse/TEIID-6072?page=com.atlassian.jira.plugi... ]
Marc Kusters commented on TEIID-6072:
-------------------------------------
We discussed it, we don't need the fix. I rather see you could spent some time on TEIID-6068 which we do need.
> Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
> --------------------------------------------------------------------------
>
> Key: TEIID-6072
> URL: https://issues.redhat.com/browse/TEIID-6072
> Project: Teiid
> Issue Type: Bug
> Components: OData, spring-boot, XML Planner/Processor
> Affects Versions: 15.0.1
> Reporter: Marc Kusters
> Assignee: Ramesh Reddy
> Priority: Major
> Fix For: 17.0
>
> Attachments: json_example, odata_xml_medialink_null_pointer_top1
>
>
> When serving a view with an streamable object (a blob) which is in our case a document through OData we get an error when the XML format is used. The JSON format gives us as expected a working media edit link.
> This is with Spring boot starter 1.6.0. I'm not quite sure what components are responsible for OData or XML.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 8 months
[teiid-issues] [Red Hat JIRA] (TEIID-6072) Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
by Ramesh Reddy (Jira)
[ https://issues.redhat.com/browse/TEIID-6072?page=com.atlassian.jira.plugi... ]
Ramesh Reddy commented on TEIID-6072:
-------------------------------------
we can look into Olingo fix, but that release may not be for another month or two. The Teiid needs to pick that up for the fix and do a release. Unless you can put in a patched version of the Olingo library yourself now and make it work for your project.
> Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
> --------------------------------------------------------------------------
>
> Key: TEIID-6072
> URL: https://issues.redhat.com/browse/TEIID-6072
> Project: Teiid
> Issue Type: Bug
> Components: OData, spring-boot, XML Planner/Processor
> Affects Versions: 15.0.1
> Reporter: Marc Kusters
> Assignee: Ramesh Reddy
> Priority: Major
> Fix For: 17.0
>
> Attachments: json_example, odata_xml_medialink_null_pointer_top1
>
>
> When serving a view with an streamable object (a blob) which is in our case a document through OData we get an error when the XML format is used. The JSON format gives us as expected a working media edit link.
> This is with Spring boot starter 1.6.0. I'm not quite sure what components are responsible for OData or XML.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 8 months
[teiid-issues] [Red Hat JIRA] (TEIID-6072) Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
by Marc Kusters (Jira)
[ https://issues.redhat.com/browse/TEIID-6072?page=com.atlassian.jira.plugi... ]
Marc Kusters commented on TEIID-6072:
-------------------------------------
Thanks for looking into it. I will discuss it tomorrow morning at our standup (10:30 UTC +1). Will the Olingo version with a fix become automatically available in Teiid or is there a possibility that it won't get added since the stop on active Teiid development work (apart from the bugfixes)?
> Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
> --------------------------------------------------------------------------
>
> Key: TEIID-6072
> URL: https://issues.redhat.com/browse/TEIID-6072
> Project: Teiid
> Issue Type: Bug
> Components: OData, spring-boot, XML Planner/Processor
> Affects Versions: 15.0.1
> Reporter: Marc Kusters
> Assignee: Ramesh Reddy
> Priority: Major
> Fix For: 17.0
>
> Attachments: json_example, odata_xml_medialink_null_pointer_top1
>
>
> When serving a view with an streamable object (a blob) which is in our case a document through OData we get an error when the XML format is used. The JSON format gives us as expected a working media edit link.
> This is with Spring boot starter 1.6.0. I'm not quite sure what components are responsible for OData or XML.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 8 months
[teiid-issues] [Red Hat JIRA] (TEIID-6072) Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
by Ramesh Reddy (Jira)
[ https://issues.redhat.com/browse/TEIID-6072?page=com.atlassian.jira.plugi... ]
Ramesh Reddy commented on TEIID-6072:
-------------------------------------
[~marc.kusters] as this is Olingo based fix, this may take a while as we have to wait for Olingo release to pick up changes in Teiid before this can be released. If it is not critical for your organization to have XML based responses then we can close as "Won't Fix"
> Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
> --------------------------------------------------------------------------
>
> Key: TEIID-6072
> URL: https://issues.redhat.com/browse/TEIID-6072
> Project: Teiid
> Issue Type: Bug
> Components: OData, spring-boot, XML Planner/Processor
> Affects Versions: 15.0.1
> Reporter: Marc Kusters
> Assignee: Ramesh Reddy
> Priority: Major
> Fix For: 17.0
>
> Attachments: json_example, odata_xml_medialink_null_pointer_top1
>
>
> When serving a view with an streamable object (a blob) which is in our case a document through OData we get an error when the XML format is used. The JSON format gives us as expected a working media edit link.
> This is with Spring boot starter 1.6.0. I'm not quite sure what components are responsible for OData or XML.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 8 months
[teiid-issues] [Red Hat JIRA] (TEIID-6072) Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-6072?page=com.atlassian.jira.plugi... ]
Steven Hawkins edited comment on TEIID-6072 at 2/17/21 2:13 PM:
----------------------------------------------------------------
As best as I can determine this is an olingo bug. In the JSON serialization the don't expect to have entity.getMediaContentType set, but they do in the XML serialization. I don't think it should be required. To go further Teiid is not setting a content type regardless, and that would be a little difficult given that an entity could be projecting multiple stream columns - with different content types.
was (Author: shawkins):
As best as I can determine this is an olingo bug. In the JSON serialization the don't expect to have entity.getMediaContentType set, but they do in the XML serialization. I don't think it should be required.
> Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
> --------------------------------------------------------------------------
>
> Key: TEIID-6072
> URL: https://issues.redhat.com/browse/TEIID-6072
> Project: Teiid
> Issue Type: Bug
> Components: OData, spring-boot, XML Planner/Processor
> Affects Versions: 15.0.1
> Reporter: Marc Kusters
> Assignee: Ramesh Reddy
> Priority: Major
> Fix For: 17.0
>
> Attachments: json_example, odata_xml_medialink_null_pointer_top1
>
>
> When serving a view with an streamable object (a blob) which is in our case a document through OData we get an error when the XML format is used. The JSON format gives us as expected a working media edit link.
> This is with Spring boot starter 1.6.0. I'm not quite sure what components are responsible for OData or XML.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 8 months
[teiid-issues] [Red Hat JIRA] (TEIID-6072) Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-6072?page=com.atlassian.jira.plugi... ]
Steven Hawkins commented on TEIID-6072:
---------------------------------------
As best as I can determine this is an olingo bug. In the JSON serialization the don't expect to have entity.getMediaContentType set, but they do in the XML serialization. I don't think it should be required.
> Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
> --------------------------------------------------------------------------
>
> Key: TEIID-6072
> URL: https://issues.redhat.com/browse/TEIID-6072
> Project: Teiid
> Issue Type: Bug
> Components: OData, spring-boot, XML Planner/Processor
> Affects Versions: 15.0.1
> Reporter: Marc Kusters
> Assignee: Ramesh Reddy
> Priority: Major
> Fix For: 17.0
>
> Attachments: json_example, odata_xml_medialink_null_pointer_top1
>
>
> When serving a view with an streamable object (a blob) which is in our case a document through OData we get an error when the XML format is used. The JSON format gives us as expected a working media edit link.
> This is with Spring boot starter 1.6.0. I'm not quite sure what components are responsible for OData or XML.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 8 months
[teiid-issues] [Red Hat JIRA] (TEIID-6072) Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
by Ramesh Reddy (Jira)
[ https://issues.redhat.com/browse/TEIID-6072?page=com.atlassian.jira.plugi... ]
Ramesh Reddy commented on TEIID-6072:
-------------------------------------
[~marc.kusters] have you tried CLOB instead of BLOB, BLOB is treated as binary and any "toString" conversions are not available. You can change it in your definition if needs to be. Also I am not able dicepher which column from JSON result is the blob content?
> Teiid Springboot OData 4 Media Link not working for XML, but does for JSON
> --------------------------------------------------------------------------
>
> Key: TEIID-6072
> URL: https://issues.redhat.com/browse/TEIID-6072
> Project: Teiid
> Issue Type: Bug
> Components: OData, spring-boot, XML Planner/Processor
> Affects Versions: 15.0.1
> Reporter: Marc Kusters
> Assignee: Ramesh Reddy
> Priority: Major
> Fix For: 17.0
>
> Attachments: json_example, odata_xml_medialink_null_pointer_top1
>
>
> When serving a view with an streamable object (a blob) which is in our case a document through OData we get an error when the XML format is used. The JSON format gives us as expected a working media edit link.
> This is with Spring boot starter 1.6.0. I'm not quite sure what components are responsible for OData or XML.
--
This message was sent by Atlassian Jira
(v8.13.1#813001)
5 years, 8 months