April 2019 - 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

2 months, 2 weeks

2
1
0 / 0

[JBoss JIRA] (TEIID-5731) Recursive Query Common Table Expressions (CTE) PostgreSQL

by Kolmar Vasconcelos (Jira)

[ https://issues.jboss.org/browse/TEIID-5731?page=com.atlassian.jira.plugin... ] Kolmar Vasconcelos closed TEIID-5731. ------------------------------------- Fix Version/s: 12.1.1 Resolution: Done My bad. I just cast the literal null and it worked. > Recursive Query Common Table Expressions (CTE) PostgreSQL > --------------------------------------------------------- > > Key: TEIID-5731 > URL: https://issues.jboss.org/browse/TEIID-5731 > Project: Teiid > Issue Type: Bug > Components: JDBC Driver > Affects Versions: 12.1.1 > Environment: Ubuntu Linux 18.04 > DBeaver 5.3.3 > TEIID JDBC 12.1.1 > Reporter: Kolmar Vasconcelos > Assignee: Steven Hawkins > Priority: Critical > Fix For: 12.1.1 > > > I'm trying to a recursive Common Table Expressions on my organization's VBL. I know it runs on top of PostgreSQL. I'm not responsible for the implementation, I'm just trying to access the data for Data Analysis. When I try to create the Recursive Query it gives me the error: Remote org.postgresql.util.PSQLException: ERROR: recursive query "cte" column 6 has type character varying(255) in non-recursive term but type character varying overall. > The problem is with the Null value. I can't CAST Null to VARCHAR. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5731) Recursive Query Common Table Expressions (CTE) PostgreSQL

by Kolmar Vasconcelos (Jira)

[ https://issues.jboss.org/browse/TEIID-5731?page=com.atlassian.jira.plugin... ] Kolmar Vasconcelos updated TEIID-5731: -------------------------------------- Description: I'm trying to a recursive Common Table Expressions on my organization's VBL. I know it runs on top of PostgreSQL. I'm not responsible for the implementation, I'm just trying to access the data for Data Analysis. When I try to create the Recursive Query it gives me the error: Remote org.postgresql.util.PSQLException: ERROR: recursive query "cte" column 6 has type character varying(255) in non-recursive term but type character varying overall. The problem is with the Null value. I can't CAST Null to VARCHAR. was: I'm trying to a recursive Common Table Expressions on my organization's VBL. I know it runs on top of PostgreSQL. I'm not responsible for the implementation, I'm just trying to access the data for Data Analysis. When I try to create the Recursive Query it gives me the error: Remote org.postgresql.util.PSQLException: ERROR: recursive query "cte" column 6 has type character varying(255) in non-recursive term but type character varying overall. The problem is with the Null value. I just can't CAST Null to VARCHAR. > Recursive Query Common Table Expressions (CTE) PostgreSQL > --------------------------------------------------------- > > Key: TEIID-5731 > URL: https://issues.jboss.org/browse/TEIID-5731 > Project: Teiid > Issue Type: Bug > Components: JDBC Driver > Affects Versions: 12.1.1 > Environment: Ubuntu Linux 18.04 > DBeaver 5.3.3 > TEIID JDBC 12.1.1 > Reporter: Kolmar Vasconcelos > Assignee: Steven Hawkins > Priority: Critical > > I'm trying to a recursive Common Table Expressions on my organization's VBL. I know it runs on top of PostgreSQL. I'm not responsible for the implementation, I'm just trying to access the data for Data Analysis. When I try to create the Recursive Query it gives me the error: Remote org.postgresql.util.PSQLException: ERROR: recursive query "cte" column 6 has type character varying(255) in non-recursive term but type character varying overall. > The problem is with the Null value. I can't CAST Null to VARCHAR. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5731) Recursive Query Common Table Expressions (CTE) PostgreSQL

by Kolmar Vasconcelos (Jira)

Kolmar Vasconcelos created TEIID-5731: ----------------------------------------- Summary: Recursive Query Common Table Expressions (CTE) PostgreSQL Key: TEIID-5731 URL: https://issues.jboss.org/browse/TEIID-5731 Project: Teiid Issue Type: Bug Components: JDBC Driver Affects Versions: 12.1.1 Environment: Ubuntu Linux 18.04 DBeaver 5.3.3 TEIID JDBC 12.1.1 Reporter: Kolmar Vasconcelos Assignee: Steven Hawkins I'm trying to a recursive Common Table Expressions on my organization's VBL. I know it runs on top of PostgreSQL. I'm not responsible for the implementation, I'm just trying to access the data for Data Analysis. When I try to create the Recursive Query it gives me the error: Remote org.postgresql.util.PSQLException: ERROR: recursive query "cte" column 6 has type character varying(255) in non-recursive term but type character varying overall. The problem is with the Null value. I just can't CAST Null to VARCHAR. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIIDSB-84) There needs to be an error page

by Steven Hawkins (Jira)

Steven Hawkins created TEIIDSB-84: ------------------------------------- Summary: There needs to be an error page Key: TEIIDSB-84 URL: https://issues.jboss.org/browse/TEIIDSB-84 Project: Teiid Spring Boot Issue Type: Quality Risk Components: OData Reporter: Steven Hawkins Fix For: 1.1.0 If you try a url, such as /foo it results in a lengthy log: 2019-04-30 15:20:14.247 ERROR 14280 --- [nio-8080-exec-2] o.a.c.c.C.[Tomcat].[localhost] : Exception Processing ErrorPage[errorCode=0, location=/error] javax.servlet.ServletException: Circular view path [error]: would dispatch back to the current handler URL [/error] again. Check your ViewResolver setup! (Hint: This may be the result of an unspecified view, due to default view name generation.) at org.springframework.web.servlet.view.InternalResourceView.prepareForRendering(InternalResourceView.java:209) ~[spring-webmvc-5.1.4.RELEASE.jar!/:5.1.4.RELEASE] at org.springframework.web.servlet.view.InternalResourceView.renderMergedOutputModel(InternalResourceView.java:147) ~[spring-webmvc-5.1.4.RELEASE.jar!/:5.1.4.RELEASE] at org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:316) ~[spring-webmvc-5.1.4.RELEASE.jar!/:5.1.4.RELEASE] at org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1370) ~[spring-webmvc-5.1.4.RELEASE.jar!/:5.1.4.RELE... -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIIDSB-44) Support JDBC/ODBC security using Keycloak

by Ramesh Reddy (Jira)

[ https://issues.jboss.org/browse/TEIIDSB-44?page=com.atlassian.jira.plugin... ] Ramesh Reddy resolved TEIIDSB-44. --------------------------------- Resolution: Duplicate Issue > Support JDBC/ODBC security using Keycloak > ----------------------------------------- > > Key: TEIIDSB-44 > URL: https://issues.jboss.org/browse/TEIIDSB-44 > Project: Teiid Spring Boot > Issue Type: Feature Request > Components: core > Reporter: Ramesh Reddy > Priority: Major > > JDBC or ODBC there is no security defined when using these protocols. Security options need to be investigated using Keycloak as the auth provider. This can be a simple user/password or Kerberos based authentication/authorization support. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIIDSB-47) Determine behavior of alter statements

by Ramesh Reddy (Jira)

[ https://issues.jboss.org/browse/TEIIDSB-47?page=com.atlassian.jira.plugin... ] Ramesh Reddy resolved TEIIDSB-47. --------------------------------- Resolution: Done Added the flag to control the allowance of allowAlter property to be set on TSB. By default, it is set to false. > Determine behavior of alter statements > -------------------------------------- > > Key: TEIIDSB-47 > URL: https://issues.jboss.org/browse/TEIIDSB-47 > Project: Teiid Spring Boot > Issue Type: Quality Risk > Components: core > Reporter: Steven Hawkins > Assignee: Ramesh Reddy > Priority: Major > Fix For: 1.1.0 > > > The engine allows alter statements to ephemerally (by default) change view definitions, turn on/off triggers, etc. For Spring Boot and OpenShift deployment in particular we should consider whether this should be allowable/optional behavior. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5545) Odata V4 Batch processing does not work as teiid/olingo rejects "Accept: multipart/mixed" request header

by Christoph John (Jira)

[ https://issues.jboss.org/browse/TEIID-5545?page=com.atlassian.jira.plugin... ] Christoph John commented on TEIID-5545: --------------------------------------- Great news :) > Odata V4 Batch processing does not work as teiid/olingo rejects "Accept: multipart/mixed" request header > -------------------------------------------------------------------------------------------------------- > > Key: TEIID-5545 > URL: https://issues.jboss.org/browse/TEIID-5545 > Project: Teiid > Issue Type: Bug > Components: OData > Affects Versions: 11.2 > Reporter: Christoph John > Assignee: Ramesh Reddy > Priority: Critical > Fix For: 12.3 > > > When using Teiid together with SAPUI5/OpenUI5 Odata V4 model, batch processing does not work as Teiid rejects the "Accept: multipart/mixed" header. The issue has been described in > https://developer.jboss.org/message/986421#986421 > It has been stated that Olingo in general does support multipart messages and it was recommended to remove the header from the request. > https://developer.jboss.org/message/986425#986425 > Unfortunately this seems to be not easily possible when using the SAPUI5 framework. I addressed the issue in the SAPUI5 forum under the following issue: > https://github.com/SAP/openui5/issues/2288 > The result of this discussion was, that the header is valid for the send content. As I am no expert in the topic I do not really have an opinion on that. However, > https://www.w3.org/Protocols/rfc1341/7_2_Multipart.html > suggests that the header is required. So I am not sure if it is correct or an error to reject the message. However, if Olingo can process the content, it would be great to have a fix for the accept header, or alternatively a config option to enable handling of the Accept header in a way that Olingo processes these data. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIIDSB-82) OData content should be served under a specific context

by Ramesh Reddy (Jira)

[ https://issues.jboss.org/browse/TEIIDSB-82?page=com.atlassian.jira.plugin... ] Ramesh Reddy resolved TEIIDSB-82. --------------------------------- Resolution: Done Moved under "odata" by default. The URL always will be`[<context>?]/odata/$metadata` > OData content should be served under a specific context > ------------------------------------------------------- > > Key: TEIIDSB-82 > URL: https://issues.jboss.org/browse/TEIIDSB-82 > Project: Teiid Spring Boot > Issue Type: Quality Risk > Components: OData > Affects Versions: 1.0.3 > Reporter: Ramesh Reddy > Assignee: Ramesh Reddy > Priority: Major > Fix For: 1.1.0 > > > Currently, OData documents are served at the root context, by default these should be limited to a context path such that collisions in the name can be avoided. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIIDSB-82) OData content should be served under a specific context

by Ramesh Reddy (Jira)

[ https://issues.jboss.org/browse/TEIIDSB-82?page=com.atlassian.jira.plugin... ] Ramesh Reddy reassigned TEIIDSB-82: ----------------------------------- Assignee: Ramesh Reddy > OData content should be served under a specific context > ------------------------------------------------------- > > Key: TEIIDSB-82 > URL: https://issues.jboss.org/browse/TEIIDSB-82 > Project: Teiid Spring Boot > Issue Type: Quality Risk > Components: OData > Affects Versions: 1.0.3 > Reporter: Ramesh Reddy > Assignee: Ramesh Reddy > Priority: Major > Fix For: 1.1.0 > > > Currently, OData documents are served at the root context, by default these should be limited to a context path such that collisions in the name can be avoided. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 8 months

1
0
0 / 0

2026

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues April 2019