[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 week, 4 days
[JBoss JIRA] (TEIID-5242) XML/JSON issues
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5242?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5242.
-----------------------------------
Resolution: Done
Corrected the json attribute logic and captured the event iterators as reusable lists.
> XML/JSON issues
> ---------------
>
> Key: TEIID-5242
> URL: https://issues.jboss.org/browse/TEIID-5242
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 10.2, 10.0.3, 9.3.7, 10.1.1
>
>
> TEIID-5233 highlighted a couple of issues with xml and json:
> XMLEventStreamReader is very inefficient in dealing with a large number of attributes/namespaces as the iterator was traversed on each access.
> JsonToXmlContentHandler was not strictly correct in the events that it emitted. Namespace and attribute events should be associated with their respective start/end element events. While the JRE libraries are tolerant to emitting the events separately, Saxon was not. To prevent issues with embedded consumers reading directly from the stax event stream this should be corrected.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5241) Problem with OData proxy configuration
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5241?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-5241.
---------------------------------
Resolution: Done
Thanks [~dvallabh_doi]
> Problem with OData proxy configuration
> --------------------------------------
>
> Key: TEIID-5241
> URL: https://issues.jboss.org/browse/TEIID-5241
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Reporter: Divyesh Vallabh
> Assignee: Steven Hawkins
> Fix For: 10.2
>
>
> When configuring the proxy-base-uri parameter in the web.xml in the teiid-olingo-odata4.war located in the <jbosshome>\modules\system\layers\dv\org\jboss\teiid\main\deployments folder the following error is generated when trying to access a vdb via the OData protocol
> 2018-01-26 11:50:22,396 WARN [org.teiid.ODATA] (default task-2) TEIID16052 Unable to process odata request due to: The URI is malformed.: Unexpected start of resource-path segment.
> at org.apache.olingo.server.core.uri.parser.ResourcePathParser.leadingResourcePathSegment(ResourcePathParser.java:236)
> at org.apache.olingo.server.core.uri.parser.ResourcePathParser.parsePathSegment(ResourcePathParser.java:88)
> at org.apache.olingo.server.core.uri.parser.Parser.parseUri(Parser.java:234)
> at org.apache.olingo.server.core.ServiceDispatcher.execute(ServiceDispatcher.java:93)
> at org.apache.olingo.server.core.OData4HttpHandler.process(OData4HttpHandler.java:67)
> at org.teiid.olingo.web.ODataServlet.service(ODataServlet.java:39)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5241) Problem with OData proxy configuration
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5241?page=com.atlassian.jira.plugin... ]
Ramesh Reddy updated TEIID-5241:
--------------------------------
Fix Version/s: 10.2
> Problem with OData proxy configuration
> --------------------------------------
>
> Key: TEIID-5241
> URL: https://issues.jboss.org/browse/TEIID-5241
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Reporter: Divyesh Vallabh
> Assignee: Steven Hawkins
> Fix For: 10.2
>
>
> When configuring the proxy-base-uri parameter in the web.xml in the teiid-olingo-odata4.war located in the <jbosshome>\modules\system\layers\dv\org\jboss\teiid\main\deployments folder the following error is generated when trying to access a vdb via the OData protocol
> 2018-01-26 11:50:22,396 WARN [org.teiid.ODATA] (default task-2) TEIID16052 Unable to process odata request due to: The URI is malformed.: Unexpected start of resource-path segment.
> at org.apache.olingo.server.core.uri.parser.ResourcePathParser.leadingResourcePathSegment(ResourcePathParser.java:236)
> at org.apache.olingo.server.core.uri.parser.ResourcePathParser.parsePathSegment(ResourcePathParser.java:88)
> at org.apache.olingo.server.core.uri.parser.Parser.parseUri(Parser.java:234)
> at org.apache.olingo.server.core.ServiceDispatcher.execute(ServiceDispatcher.java:93)
> at org.apache.olingo.server.core.OData4HttpHandler.process(OData4HttpHandler.java:67)
> at org.teiid.olingo.web.ODataServlet.service(ODataServlet.java:39)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5241) Problem with OData proxy configuration
by Divyesh Vallabh (JIRA)
[ https://issues.jboss.org/browse/TEIID-5241?page=com.atlassian.jira.plugin... ]
Divyesh Vallabh updated TEIID-5241:
-----------------------------------
Git Pull Request: https://github.com/teiid/teiid/pull/1059
> Problem with OData proxy configuration
> --------------------------------------
>
> Key: TEIID-5241
> URL: https://issues.jboss.org/browse/TEIID-5241
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Reporter: Divyesh Vallabh
> Assignee: Steven Hawkins
>
> When configuring the proxy-base-uri parameter in the web.xml in the teiid-olingo-odata4.war located in the <jbosshome>\modules\system\layers\dv\org\jboss\teiid\main\deployments folder the following error is generated when trying to access a vdb via the OData protocol
> 2018-01-26 11:50:22,396 WARN [org.teiid.ODATA] (default task-2) TEIID16052 Unable to process odata request due to: The URI is malformed.: Unexpected start of resource-path segment.
> at org.apache.olingo.server.core.uri.parser.ResourcePathParser.leadingResourcePathSegment(ResourcePathParser.java:236)
> at org.apache.olingo.server.core.uri.parser.ResourcePathParser.parsePathSegment(ResourcePathParser.java:88)
> at org.apache.olingo.server.core.uri.parser.Parser.parseUri(Parser.java:234)
> at org.apache.olingo.server.core.ServiceDispatcher.execute(ServiceDispatcher.java:93)
> at org.apache.olingo.server.core.OData4HttpHandler.process(OData4HttpHandler.java:67)
> at org.teiid.olingo.web.ODataServlet.service(ODataServlet.java:39)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-4988) Oracle - table alias is not used in whole query
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4988?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4988:
------------------------------------------------
Maximilián Košiarčik <mkosiarc(a)redhat.com> changed the Status of [bug 1469946|https://bugzilla.redhat.com/show_bug.cgi?id=1469946] from ON_QA to VERIFIED
> Oracle - table alias is not used in whole query
> -----------------------------------------------
>
> Key: TEIID-4988
> URL: https://issues.jboss.org/browse/TEIID-4988
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.x-6.4
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
> Fix For: 10.0, 8.12.x-6.4, 9.3.2
>
>
> When I run query like \[1\] Teiid translates it to query which does not use one of table aliases in whole query. Oracle (11g 11.2.0.2.0) seems to be sensitive to aliasing table and then not using it. Query fails. Source specific query (problematic one) \[2\].
> Sample query which fails if running directly against Oracle database \[3\]
> {code:sql|title=\[1\] Query}
> select
> s_name,
> s_address
> from
> tpch1.soaeds.supplier,
> tpch1.soaeds.nation
> where
> s_supplierkey in (
> select
> ps_supplierkey
> from
> tpch2.soaeds.partsupp
> where
> ps_partkey in (
> select
> p_partkey
> from
> tpch2.soaeds.part
> where
> p_name like 'powder%'
> )
> and ps_availqty > (
> select
> 0.5 * sum(l_quantity)
> from
> tpch2.soaeds.lineitem
> where
> l_partkey = ps_partkey
> and l_supplierkey = ps_supplierkey
> and l_shipdate >= '1994-01-01'
> and l_shipdate < TIMESTAMPADD(SQL_TSI_YEAR,'1', '1994-01-01')
> )
> )
> and s_nationkey = n_nationkey
> and n_name = 'BRAZIL'
> order by
> s_name;
> {code}
> {code:sql|title=\[2\] Source-specific query}
> SELECT g_0."PS_SUPPLIERKEY" AS c_0 FROM "SOAEDS"."PARTSUPP" g_0 WHERE g_0."PS_PARTKEY" IN (SELECT g_1."P_PARTKEY" FROM "SOAEDS"."PART" g_1 WHERE g_1."P_NAME" LIKE 'powder%') AND g_0."PS_AVAILQTY" > (SELECT (0.5 * SUM(g_2."L_QUANTITY")) FROM "SOAEDS"."LINEITEM" g_2 WHERE g_2."L_PARTKEY" = "SOAEDS"."PARTSUPP"."PS_PARTKEY" AND g_2."L_SUPPLIERKEY" = "SOAEDS"."PARTSUPP"."PS_SUPPLIERKEY" AND g_2."L_SHIPDATE" >= to_date('1994-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND g_2."L_SHIPDATE" < ?) ORDER BY c_0
> {code}
> {code:sql|title=\[3\] Oracle query}
> SELECT smalla.intkey FROM smalla a
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5242) XML/JSON issues
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5242:
-------------------------------------
Summary: XML/JSON issues
Key: TEIID-5242
URL: https://issues.jboss.org/browse/TEIID-5242
Project: Teiid
Issue Type: Quality Risk
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 10.2, 10.0.3, 9.3.7, 10.1.1
TEIID-5233 highlighted a couple of issues with xml and json:
XMLEventStreamReader is very inefficient in dealing with a large number of attributes/namespaces as the iterator was traversed on each access.
JsonToXmlContentHandler was not strictly correct in the events that it emitted. Namespace and attribute events should be associated with their respective start/end element events. While the JRE libraries are tolerant to emitting the events separately, Saxon was not. To prevent issues with embedded consumers reading directly from the stax event stream this should be corrected.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5241) Problem with OData proxy configuration
by Divyesh Vallabh (JIRA)
[ https://issues.jboss.org/browse/TEIID-5241?page=com.atlassian.jira.plugin... ]
Divyesh Vallabh updated TEIID-5241:
-----------------------------------
Forum Reference: https://developer.jboss.org/thread/277101 (was: https://developer.jboss.org/message/979983?et=watches.email.thread#979983)
> Problem with OData proxy configuration
> --------------------------------------
>
> Key: TEIID-5241
> URL: https://issues.jboss.org/browse/TEIID-5241
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Reporter: Divyesh Vallabh
> Assignee: Steven Hawkins
>
> When configuring the proxy-base-uri parameter in the web.xml in the teiid-olingo-odata4.war located in the <jbosshome>\modules\system\layers\dv\org\jboss\teiid\main\deployments folder the following error is generated when trying to access a vdb via the OData protocol
> 2018-01-26 11:50:22,396 WARN [org.teiid.ODATA] (default task-2) TEIID16052 Unable to process odata request due to: The URI is malformed.: Unexpected start of resource-path segment.
> at org.apache.olingo.server.core.uri.parser.ResourcePathParser.leadingResourcePathSegment(ResourcePathParser.java:236)
> at org.apache.olingo.server.core.uri.parser.ResourcePathParser.parsePathSegment(ResourcePathParser.java:88)
> at org.apache.olingo.server.core.uri.parser.Parser.parseUri(Parser.java:234)
> at org.apache.olingo.server.core.ServiceDispatcher.execute(ServiceDispatcher.java:93)
> at org.apache.olingo.server.core.OData4HttpHandler.process(OData4HttpHandler.java:67)
> at org.teiid.olingo.web.ODataServlet.service(ODataServlet.java:39)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months