March 2016 - teiid-issues - Jboss List Archives

[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 month, 2 weeks

2
1
0 / 0

[JBoss JIRA] (TEIID-3911) Nearly all odata4 errors reported as internal server errors

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-3911?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-3911: ------------------------------------- Can we make this resolved Steve? > Nearly all odata4 errors reported as internal server errors > ----------------------------------------------------------- > > Key: TEIID-3911 > URL: https://issues.jboss.org/browse/TEIID-3911 > Project: Teiid > Issue Type: Sub-task > Components: OData > Affects Versions: 8.12 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 9.0, 8.12.5 > > > From the TeiidServiceHandler we generally rethrow our exceptions as ODataApplicationExceptions that have a status code of 500 - however even using a different status code still results in a 500 error as the Olingo framework expects exception subclasses to be used (see ErrorHandler.handleException - a general application exception is always a 500 error). -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4053) Provide Kerberos authentication on OData service

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-4053?page=com.atlassian.jira.plugin... ] Ramesh Reddy resolved TEIID-4053. --------------------------------- Resolution: Done Added the template and some documentation > Provide Kerberos authentication on OData service > ------------------------------------------------ > > Key: TEIID-4053 > URL: https://issues.jboss.org/browse/TEIID-4053 > Project: Teiid > Issue Type: Task > Components: OData > Affects Versions: 8.12.4 > Reporter: Ramesh Reddy > Assignee: Ramesh Reddy > Fix For: 8.12.5 > > > Provide template to generate OData web service that can participate in a Kerberos based authentication, similar to that of the template of OData. Provide documentation for the same. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4112) ORA-32039: recursive WITH clause must have column alias list

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-4112?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration updated TEIID-4112: ------------------------------------------- Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1322935 Bugzilla Update: Perform > ORA-32039: recursive WITH clause must have column alias list > ------------------------------------------------------------ > > Key: TEIID-4112 > URL: https://issues.jboss.org/browse/TEIID-4112 > Project: Teiid > Issue Type: Bug > Affects Versions: 8.7.2.6_2 > Reporter: Debbie Steigner > Assignee: Steven Hawkins > > If running a WITH table AS(...) query to Oracle and the query schema name is the same as the subquery name i.e. EWI. you receive the Oracle error: > ORA-32039: recursive WITH clause must have column alias list > If you modify the query to > WITH EWI1 AS .... > then it works. > Query: > ====================================================== > with EWI AS ( > SELECT > A.cis_code, > B.ewi_id, > B.Action_Code, > B.Action_Comments, > B.Action_Date, > VP.LONG_NAME AS "PERSON_LONG_NAME", > B.Action_Taken_By_ECD_ID, > D.Notes, > D.Notes_Date, > C.EWI_Sub_Type_Code, > C.Narrative, > C.Last_Update_Date, > C.Last_Updated_By > FROM > "V_PARTY_STATUS" A inner join v_ewi_action B On A.cis_code = B.cis_code inner join v_ewi C ON B.ewi_id = C.ewi_id INNER JOIN V_PERSON VP ON B.Action_Taken_By_ECD_ID = VP.ECD_ID > AND B.ACTION_TAKEN_BY_RACFID = VP.WINDOWS_LOGON > AND B.ACTION_TAKEN_BY_DOMAIN = VP.DOMAIN left outer join v_ewi_notes D ON C.ewi_id = D.ewi_id > where > A.agreed_monitoring_code='ENHANCED_MONITORING' > AND A.party_status_code not in ('INAC', > 'GARB') > AND B.Action_Code = 'ENHANCED_MONITORING' > AND B.task_status_code = 'FINISHED' > ) select * from EWI > ====================================================== -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4112) ORA-32039: recursive WITH clause must have column alias list

by Debbie Steigner (JIRA)

Debbie Steigner created TEIID-4112: -------------------------------------- Summary: ORA-32039: recursive WITH clause must have column alias list Key: TEIID-4112 URL: https://issues.jboss.org/browse/TEIID-4112 Project: Teiid Issue Type: Bug Affects Versions: 8.7.2.6_2 Reporter: Debbie Steigner Assignee: Steven Hawkins If running a WITH table AS(...) query to Oracle and the query schema name is the same as the subquery name i.e. EWI. you receive the Oracle error: ORA-32039: recursive WITH clause must have column alias list If you modify the query to WITH EWI1 AS .... then it works. Query: ====================================================== with EWI AS ( SELECT A.cis_code, B.ewi_id, B.Action_Code, B.Action_Comments, B.Action_Date, VP.LONG_NAME AS "PERSON_LONG_NAME", B.Action_Taken_By_ECD_ID, D.Notes, D.Notes_Date, C.EWI_Sub_Type_Code, C.Narrative, C.Last_Update_Date, C.Last_Updated_By FROM "V_PARTY_STATUS" A inner join v_ewi_action B On A.cis_code = B.cis_code inner join v_ewi C ON B.ewi_id = C.ewi_id INNER JOIN V_PERSON VP ON B.Action_Taken_By_ECD_ID = VP.ECD_ID AND B.ACTION_TAKEN_BY_RACFID = VP.WINDOWS_LOGON AND B.ACTION_TAKEN_BY_DOMAIN = VP.DOMAIN left outer join v_ewi_notes D ON C.ewi_id = D.ewi_id where A.agreed_monitoring_code='ENHANCED_MONITORING' AND A.party_status_code not in ('INAC', 'GARB') AND B.Action_Code = 'ENHANCED_MONITORING' AND B.task_status_code = 'FINISHED' ) select * from EWI ====================================================== -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4111) Impala translator - add all date pushdown functions

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4111?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-4111. ----------------------------------- Resolution: Done Merged the pull request with a few minor changes. > Impala translator - add all date pushdown functions > --------------------------------------------------- > > Key: TEIID-4111 > URL: https://issues.jboss.org/browse/TEIID-4111 > Project: Teiid > Issue Type: Enhancement > Components: JDBC Connector > Affects Versions: 8.13.3 > Reporter: Don Krapohl > Assignee: Steven Hawkins > Fix For: 9.0 > > > As a teiid client I need the ability to perform date-based operations in the impala data store (data functions pushed down to impala) instead of within teiid. > Please add the impala date functions from http://www.cloudera.com/documentation/archive/impala/2-x/2-0-x/topics/imp... > Version 1.0.1 function support list is at http://www.cloudera.com/documentation/archive/impala/1-x/1-0-1/Installing... > Version 1.2.4 function support list is at http://www.cloudera.com/documentation/archive/impala/1-x/1-2-4/Installing... -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4111) Impala translator - add all date pushdown functions

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4111?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-4111: ---------------------------------- Issue Type: Enhancement (was: Feature Request) Fix Version/s: 9.0 > Impala translator - add all date pushdown functions > --------------------------------------------------- > > Key: TEIID-4111 > URL: https://issues.jboss.org/browse/TEIID-4111 > Project: Teiid > Issue Type: Enhancement > Components: JDBC Connector > Affects Versions: 8.13.3 > Reporter: Don Krapohl > Assignee: Steven Hawkins > Fix For: 9.0 > > > As a teiid client I need the ability to perform date-based operations in the impala data store (data functions pushed down to impala) instead of within teiid. > Please add the impala date functions from http://www.cloudera.com/documentation/archive/impala/2-x/2-0-x/topics/imp... > Version 1.0.1 function support list is at http://www.cloudera.com/documentation/archive/impala/1-x/1-0-1/Installing... > Version 1.2.4 function support list is at http://www.cloudera.com/documentation/archive/impala/1-x/1-2-4/Installing... -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4017) EXECUTE IMMEDIATE in anonymous procedure ignores permissions

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4017?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-4017: --------------------------------------- Yes that is not a valid expectation. > EXECUTE IMMEDIATE in anonymous procedure ignores permissions > ------------------------------------------------------------ > > Key: TEIID-4017 > URL: https://issues.jboss.org/browse/TEIID-4017 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 8.12 > Environment: teiid-8.12-Beta1 on Red Hat JBoss Enterprise Application Platform - Version 6.3.0.GA > Reporter: dalex dalex > Assignee: Steven Hawkins > Priority: Blocker > Fix For: 9.0, 8.12.5, 8.13.2, 8.7.5.6_2 > > > I've found a bug when executing a query in the EXECUTE IMMEDIATE clause. It ignores permissions. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4017) EXECUTE IMMEDIATE in anonymous procedure ignores permissions

by Juraj Duráni (JIRA)

[ https://issues.jboss.org/browse/TEIID-4017?page=com.atlassian.jira.plugin... ] Juraj Duráni commented on TEIID-4017: ------------------------------------- Thanks for clarification. So I expect that \[1\] is not true and you just ignored it. I will set related BZ to VERIFY. \[1\] {quote} I'll get 1 as value that is also wrong because we have no permissions to execute testSelect() procedure even from another procedure. {quote} > EXECUTE IMMEDIATE in anonymous procedure ignores permissions > ------------------------------------------------------------ > > Key: TEIID-4017 > URL: https://issues.jboss.org/browse/TEIID-4017 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 8.12 > Environment: teiid-8.12-Beta1 on Red Hat JBoss Enterprise Application Platform - Version 6.3.0.GA > Reporter: dalex dalex > Assignee: Steven Hawkins > Priority: Blocker > Fix For: 9.0, 8.12.5, 8.13.2, 8.7.5.6_2 > > > I've found a bug when executing a query in the EXECUTE IMMEDIATE clause. It ignores permissions. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4110) Odata - date functions does't accept date string as a parameter

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-4110?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration commented on TEIID-4110: ------------------------------------------------ Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1322536|https://bugzilla.redhat.com/show_bug.cgi?id=1322536] from NEW to MODIFIED > Odata - date functions does't accept date string as a parameter > --------------------------------------------------------------- > > Key: TEIID-4110 > URL: https://issues.jboss.org/browse/TEIID-4110 > Project: Teiid > Issue Type: Sub-task > Components: OData > Reporter: Michal Sanitrak > Assignee: Ramesh Reddy > Fix For: 9.0, 8.12.5 > > > Date functions year, month, day, hour, minute and second doesn't accept date string(DateTimeOffset) as a parameter. > Reference service which supports OData V4: > *ULR:* http://services.odata.org/V4/Northwind/Northwind.svc/Employees/?$filter=m... eq EmployeeID > *Result:* Employee with ID 1 > *URL:* http://localhost:8080/odata4/olingo_basic/Source/SimpleDataTable/?$filter... eq intkey > *Result:* {"error": {"code": null, "message": "TEIID30069 The function 'MONTH(?)' has more than one possible signature." }} > *Expected result:* Entity with intkey 1 -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 8 months

1
0
0 / 0

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues March 2016