[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-5682) Raise aggregation to allow for join pushdown
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5682?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5682:
---------------------------------------
This seems a little involved for the problem that it is solving. With an incoming query such as:
SELECT g0.e1, g0.e2, g0.e3, /*+ MJ */ (SELECT ARRAY_AGG((g1.e1, g1.e2) ORDER BY g1.e1) FROM PM1.G2 AS g1 WHERE g0.e2 = g1.e2) FROM PM1.G1 AS g0 ORDER BY g0.e2
SELECT g0.e1, g0.e2, g0.e3, X__1.expr1 AS expr4
FROM PM1.G1 AS g0 LEFT OUTER JOIN
(SELECT ARRAY_AGG((g1.e1, g1.e2) ORDER BY g1.e1) AS expr1, g1.e2 FROM PM1.G2 AS g1 GROUP BY g1.e2) AS X__1 ON g0.e2 = X__1.e2
ORDER BY g0.e2
We need to get to:
SELECT g0.e1, g0.e2, g0.e3, ARRAY_AGG((X__1.e1, X__1.e2) ORDER BY X__1.e1) ) FILTER (WHERE X__1.e1 IS NOT NULL) AS expr4
FROM PM1.G1 AS g0 LEFT OUTER JOIN
(SELECT g1.e1, g1.e2 FROM PM1.G2 AS g1) AS X__1 ON g0.e2 = X__1.e2
GROUP BY g0.e1, g0.e2, g0.e3 ORDER BY g0.e2
Note that all projected g0 columns must be sortable - this is not always the case. It also requires attaching a filter to aggregates that can be null dependent when there is an outer join. And finally it can't have any intervening usage of aggregate values or even complex projection between the logical subquery grouping root and it join parent - this will generally be the case for expand, but can't generally be assumed.
A simpler approach would either be to either already aggregate the other top level columns: select g0.e2, array_agg(g0.e1 ...)
Or not use the nested aggregation and process the expand like the older style - but that only makes sense for a single expand.
> Raise aggregation to allow for join pushdown
> --------------------------------------------
>
> Key: TEIID-5682
> URL: https://issues.jboss.org/browse/TEIID-5682
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 12.2
>
>
> In some plans, for example those created by odata expand see TEIID-5680, the resulting query has the form of:
> select tbl1-cols from tbl1 left outer join (select cols, aggregates ... from tbl2 group by cols) on (some non-aggregate predicate)
> If the aggregates or other intervening constructs are not able to be pushed the result will be a federated join.
> In most instances (where the tbl1 columns are sortable) it's possible to change this to:
> select tbl1-cols aggregates from tbl1 left outer join tbl1 on (...) group by tbl1-cols, cols
> which can allow the pushdown of the join to proceed.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
6 years, 6 months
[JBoss JIRA] (TEIID-5406) Allow usage of long datatype as the increment parameter in TimestampAdd function
by Dmitrii Pogorelov (Jira)
[ https://issues.jboss.org/browse/TEIID-5406?page=com.atlassian.jira.plugin... ]
Dmitrii Pogorelov commented on TEIID-5406:
------------------------------------------
Hi [~shawkins], the issue was resolved previous year but just a question: do I understand correctly that some of DB still can treat the fractional second parameter as Integer type? That's why you added the check on Integer range?
> Allow usage of long datatype as the increment parameter in TimestampAdd function
> --------------------------------------------------------------------------------
>
> Key: TEIID-5406
> URL: https://issues.jboss.org/browse/TEIID-5406
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 10.2
> Environment: teiid-10.2.0 on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 11.1
>
>
> Currently the TIMESTAMPADD(interval, count, timestamp) expects the count parameter to be integer. This is inconsistent with the return value of the TIMSTAMPDIFF function that is long. Thus we cannot take the fraction of the count returned by TIMSTAMPDIFF and submit it to TIMESTAMPADD. Since the SQL_TSI_FRAC_SECOND difference is too large for most human values (and it's the only way to retrieve millis, even though millions of millis), we need a way to submit this data to TIMSTAMPADD.
> 1. There is an inconsistency when adding 1 billion fractions of a second. Please execute the script below, one adds one billion minus one, the next one exactly 1bln fractions which results in two values, one of which is 1 fraction larger than expected:
> {code:sql}
> Select TimestampAdd(SQL_TSI_FRAC_SECOND, 999999999, Cast(CurDate() as timestamp))
> Union All
> Select TimestampAdd(SQL_TSI_FRAC_SECOND, 999999999 + 1, Cast(CurDate() as timestamp));;
> {code}
> 2. Another example:
> {code:sql}
> Begin
> Declare timestamp initialDate = CurDate();
> Declare long diff = TimestampDiff(SQL_TSI_FRAC_SECOND, initialDate, TimestampAdd(SQL_TSI_DAY, 1, initialDate));
> Declare timestamp back = TimestampAdd(SQL_TSI_FRAC_SECOND, diff, initialDate);
> select initialDate, back;
> End ;;
> {code}
> which leads to the following error message:
> {code:noformat}
> 2018-07-03 10:10:33,942 WARN [org.teiid.PROCESSOR] (Worker7_QueryProcessorQueue47) 1RBg/ooz5vx7 TEIID30020 Processing exception for request 1RBg/ooz5vx7.27 'TEIID30070 The function
> 'TimestampAdd(SQL_TSI_FRAC_SECOND, VARIABLES.diff, VARIABLES.initialDate)' is a valid function form, but the arguments do not match a known type signature and cannot be converted usi
> ng implicit type conversions.'. Originally QueryResolverException ResolverVisitor.java:757.: org.teiid.api.exception.query.QueryResolverException: TEIID30070 The function 'TimestampA
> dd(SQL_TSI_FRAC_SECOND, VARIABLES.diff, VARIABLES.initialDate)' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit
> type conversions.
> at org.teiid.query.resolver.util.ResolverVisitor.resolveFunction(ResolverVisitor.java:757)
> at org.teiid.query.resolver.util.ResolverVisitor.visit(ResolverVisitor.java:392)
> at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:169)
> at org.teiid.query.sql.navigator.AbstractNavigator.visitVisitor(AbstractNavigator.java:50)
> at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.postVisitVisitor(PreOrPostOrderNavigator.java:57)
> at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:194)
> at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:169)
> at org.teiid.query.resolver.util.ResolverVisitor.resolveLanguageObject(ResolverVisitor.java:1456)
> at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveStatement(UpdateProcedureResolver.java:234)
> at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveBlock(UpdateProcedureResolver.java:127)
> at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveCommand(UpdateProcedureResolver.java:110)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:282)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:128)
> at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:282)
> at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:418)
> 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:277)
> 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}
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
6 years, 6 months
[JBoss JIRA] (TEIID-5682) Raise aggregation to allow for join pushdown
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5682?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5682:
---------------------------------------
With cardinalities set this issue is somewhat mitigated as the join will be planned as a dependent join. The rationale for raising the aggregation is if the cardinalties aren't set and the join can be pushed. The grouping and order by operations can then be combined.
> Raise aggregation to allow for join pushdown
> --------------------------------------------
>
> Key: TEIID-5682
> URL: https://issues.jboss.org/browse/TEIID-5682
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 12.2
>
>
> In some plans, for example those created by odata expand see TEIID-5680, the resulting query has the form of:
> select tbl1-cols from tbl1 left outer join (select cols, aggregates ... from tbl2 group by cols) on (some non-aggregate predicate)
> If the aggregates or other intervening constructs are not able to be pushed the result will be a federated join.
> In most instances (where the tbl1 columns are sortable) it's possible to change this to:
> select tbl1-cols aggregates from tbl1 left outer join tbl1 on (...) group by tbl1-cols, cols
> which can allow the pushdown of the join to proceed.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
6 years, 7 months
[JBoss JIRA] (TEIID-5682) Raise aggregation to allow for join pushdown
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5682?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5682:
----------------------------------
Fix Version/s: 12.2
(was: 12.x)
> Raise aggregation to allow for join pushdown
> --------------------------------------------
>
> Key: TEIID-5682
> URL: https://issues.jboss.org/browse/TEIID-5682
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 12.2
>
>
> In some plans, for example those created by odata expand see TEIID-5680, the resulting query has the form of:
> select tbl1-cols from tbl1 left outer join (select cols, aggregates ... from tbl2 group by cols) on (some non-aggregate predicate)
> If the aggregates or other intervening constructs are not able to be pushed the result will be a federated join.
> In most instances (where the tbl1 columns are sortable) it's possible to change this to:
> select tbl1-cols aggregates from tbl1 left outer join tbl1 on (...) group by tbl1-cols, cols
> which can allow the pushdown of the join to proceed.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
6 years, 7 months
[JBoss JIRA] (TEIID-5682) Raise aggregation to allow for join pushdown
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5682?page=com.atlassian.jira.plugin... ]
Work on TEIID-5682 started by Steven Hawkins.
---------------------------------------------
> Raise aggregation to allow for join pushdown
> --------------------------------------------
>
> Key: TEIID-5682
> URL: https://issues.jboss.org/browse/TEIID-5682
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 12.x
>
>
> In some plans, for example those created by odata expand see TEIID-5680, the resulting query has the form of:
> select tbl1-cols from tbl1 left outer join (select cols, aggregates ... from tbl2 group by cols) on (some non-aggregate predicate)
> If the aggregates or other intervening constructs are not able to be pushed the result will be a federated join.
> In most instances (where the tbl1 columns are sortable) it's possible to change this to:
> select tbl1-cols aggregates from tbl1 left outer join tbl1 on (...) group by tbl1-cols, cols
> which can allow the pushdown of the join to proceed.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
6 years, 7 months