[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 day, 5 hours
[JBoss JIRA] (TEIID-5190) Postgresql translator does not support expressions in group by
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5190:
-------------------------------------
Summary: Postgresql translator does not support expressions in group by
Key: TEIID-5190
URL: https://issues.jboss.org/browse/TEIID-5190
Project: Teiid
Issue Type: Quality Risk
Components: JDBC Connector
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 10.1
Inline views are being added in situations involving expressions in the group by clause because the translator is not marked as supporting that.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5183) NPE in Swagger translator
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5183?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5183:
---------------------------------------
A new set of fixes is in. There is also a doc note that has been added about using named parameters as there is no specific order provided by the swagger library.
> NPE in Swagger translator
> -------------------------
>
> Key: TEIID-5183
> URL: https://issues.jboss.org/browse/TEIID-5183
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 9.3.4
> Environment: teiid-9.3.4 on WildFly Full 10.0.0.Final (WildFly Core 2.0.10.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Fix For: 10.1, 9.3.6, 10.0.2
>
>
> When using the http://api.apis.guru/v2/specs/azure.com/redis/2017-10-01/ link as an endpoint for swagger resource adapter Teiid throws up the following error message (in bootstrapping phase), though with http://petstore.swagger.io/v2 endpoint according to the https://teiid.gitbooks.io/documents/reference/Swagger_Translator.html documentation the translator works:
> {code:noformat}
> 2017-12-18 16:14:43,975 WARN [org.teiid.RUNTIME] (Worker1_async-teiid-threads1) TEIID50036 VDB test.1 model "mswagger" metadata failed to load. Reason:java.lang.NullPointerExceptio
> n: java.lang.NullPointerException
> at org.teiid.metadata.MetadataFactory.setUUID(MetadataFactory.java:155)
> at org.teiid.metadata.MetadataFactory.addProcedureParameter(MetadataFactory.java:421)
> at org.teiid.translator.swagger.SwaggerMetadataProcessor.addProcedureParameters(SwaggerMetadataProcessor.java:560)
> at org.teiid.translator.swagger.SwaggerMetadataProcessor.addProcedure(SwaggerMetadataProcessor.java:261)
> at org.teiid.translator.swagger.SwaggerMetadataProcessor.process(SwaggerMetadataProcessor.java:206)
> at org.teiid.translator.swagger.SwaggerMetadataProcessor.process(SwaggerMetadataProcessor.java:74)
> at org.teiid.translator.ExecutionFactory.getMetadata(ExecutionFactory.java:961)
> at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:96)
> at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:62)
> at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55)
> at org.teiid.jboss.VDBService$6.run(VDBService.java:341)
> at org.teiid.jboss.VDBService$7.run(VDBService.java:392)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> {code}
> The http://api.apis.guru/v2/specs/azure.com/redis/2017-10-01/ endpoint supports swagger API according to the https://apis.guru/browse-apis/ link and I can see json output when requesting the http://api.apis.guru/v2/specs/azure.com/redis/2017-10-01/swagger.json link. Probably the NPE appears because of RefParameter type which is not handled in SwaggerMetadataProcessor.addProcedureParameters method which leads to name = null.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5189) Empty XmlElement throws an NPE when passed for the non-nullable parameter
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5189?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5189.
-----------------------------------
Fix Version/s: 10.1
9.3.6
10.0.2
Resolution: Done
The current logic doesn't ensure that only checked exceptions will be thrown when the evaluator is called during validation, so the exception handling was broadened. If there is a validity issue it will be caught later during execution.
> Empty XmlElement throws an NPE when passed for the non-nullable parameter
> -------------------------------------------------------------------------
>
> Key: TEIID-5189
> URL: https://issues.jboss.org/browse/TEIID-5189
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 10.0.1
> Environment: teiid-10.0.1 on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 10.1, 9.3.6, 10.0.2
>
>
> Empty XmlElement throws an NPE when passed for the non-nullable parameter.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5189) Empty XmlElement throws an NPE when passed for the non-nullable parameter
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-5189?page=com.atlassian.jira.plugin... ]
dalex dalex updated TEIID-5189:
-------------------------------
Steps to Reproduce:
1. create the following proc:
{code:xml}
<model visible = "true" type = "VIRTUAL" name = "views">
<metadata type = "DDL"><![CDATA[
create virtual procedure v0(IN soapBody xml not null)
returns (response xml) as
begin
select XmlElement(root, 'Hi!');
end
]]>
</metadata>
</model>
{code}
2. when running the following query:
{code:sql}
call views.v0(
soapBody => XmlElement(root)
);;
{code}
teiid throws out the following stacktrace:
{code:noformat}
2017-12-27 17:19:12,864 ERROR [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue5) l9O5D2rjxOO3 TEIID30019 Unexpected exception for request l9O5D2rjxOO3.0: java.lang.NullPointerExcep
tion
at org.teiid.query.function.source.XMLSystemFunctions.xmlElement(XMLSystemFunctions.java:500)
at org.teiid.query.eval.Evaluator.evaluateXMLElement(Evaluator.java:1130)
at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:769)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:151)
at org.teiid.query.validator.ValidationVisitor.visit(ValidationVisitor.java:341)
at org.teiid.query.sql.lang.StoredProcedure.acceptVisitor(StoredProcedure.java:202)
at org.teiid.query.sql.navigator.AbstractNavigator.visitVisitor(AbstractNavigator.java:50)
at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.preVisitVisitor(PreOrPostOrderNavigator.java:51)
at org.teiid.query.validator.Validator$1.preVisitVisitor(Validator.java:80)
at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:358)
at org.teiid.query.sql.lang.StoredProcedure.acceptVisitor(StoredProcedure.java:202)
at org.teiid.query.validator.Validator.executeValidation(Validator.java:90)
at org.teiid.query.validator.Validator.validate(Validator.java:44)
at org.teiid.dqp.internal.process.Request.validateWithVisitor(Request.java:338)
at org.teiid.dqp.internal.process.Request.validateQuery(Request.java:290)
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:434)
at org.teiid.dqp.internal.process.Request.processRequest(Request.java:486)
at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:660)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:339)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:276)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:280)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
{code}
was:
1. create the following view and proc:
{code:xml}
<model visible = "true" type = "VIRTUAL" name = "views">
<metadata type = "DDL"><![CDATA[
create virtual procedure v0(IN soapBody xml not null)
returns (response xml) as
begin
select XmlElement(root, 'Hi!');
end
]]>
</metadata>
</model>
{code}
2. when running the following query:
{code:sql}
call views.v0(
soapBody => XmlElement(root)
);;
{code}
teiid throws out the following stacktrace:
{code:noformat}
2017-12-27 17:19:12,864 ERROR [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue5) l9O5D2rjxOO3 TEIID30019 Unexpected exception for request l9O5D2rjxOO3.0: java.lang.NullPointerExcep
tion
at org.teiid.query.function.source.XMLSystemFunctions.xmlElement(XMLSystemFunctions.java:500)
at org.teiid.query.eval.Evaluator.evaluateXMLElement(Evaluator.java:1130)
at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:769)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:151)
at org.teiid.query.validator.ValidationVisitor.visit(ValidationVisitor.java:341)
at org.teiid.query.sql.lang.StoredProcedure.acceptVisitor(StoredProcedure.java:202)
at org.teiid.query.sql.navigator.AbstractNavigator.visitVisitor(AbstractNavigator.java:50)
at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.preVisitVisitor(PreOrPostOrderNavigator.java:51)
at org.teiid.query.validator.Validator$1.preVisitVisitor(Validator.java:80)
at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:358)
at org.teiid.query.sql.lang.StoredProcedure.acceptVisitor(StoredProcedure.java:202)
at org.teiid.query.validator.Validator.executeValidation(Validator.java:90)
at org.teiid.query.validator.Validator.validate(Validator.java:44)
at org.teiid.dqp.internal.process.Request.validateWithVisitor(Request.java:338)
at org.teiid.dqp.internal.process.Request.validateQuery(Request.java:290)
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:434)
at org.teiid.dqp.internal.process.Request.processRequest(Request.java:486)
at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:660)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:339)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:276)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:280)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
{code}
> Empty XmlElement throws an NPE when passed for the non-nullable parameter
> -------------------------------------------------------------------------
>
> Key: TEIID-5189
> URL: https://issues.jboss.org/browse/TEIID-5189
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 10.0.1
> Environment: teiid-10.0.1 on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Critical
>
> Empty XmlElement throws an NPE when passed for the non-nullable parameter.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5188) Update swagger libraries
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5188:
-------------------------------------
Summary: Update swagger libraries
Key: TEIID-5188
URL: https://issues.jboss.org/browse/TEIID-5188
Project: Teiid
Issue Type: Quality Risk
Components: Misc. Connectors
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 10.1
>From TEIID-5183, we should upgrade to the latest swagger library versions.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5187) Swagger translator does not handle null procedure parameters
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5187:
-------------------------------------
Summary: Swagger translator does not handle null procedure parameters
Key: TEIID-5187
URL: https://issues.jboss.org/browse/TEIID-5187
Project: Teiid
Issue Type: Bug
Components: Misc. Connectors
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 10.1
If null is passed as a parameter value for anything other than a body parameter, the swagger translator will throw a null pointer exception.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 10 months