[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
[JBoss JIRA] (TEIID-4961) External Materialized View With State Loaded but 0 Cardinality
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4961?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4961.
-----------------------------------
Resolution: Done
Corrected the bug introduced by the planning change to move when the mvstatus function is checked. When a full insert with a query expression was pushed, the status check was not associated with the query.
> External Materialized View With State Loaded but 0 Cardinality
> --------------------------------------------------------------
>
> Key: TEIID-4961
> URL: https://issues.jboss.org/browse/TEIID-4961
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.3
> Environment: * Teiid 9.3.0
> * MySQL 5.7.18
> * Wildfly 10
> Reporter: Pedro Inácio
> Assignee: Steven Hawkins
> Fix For: 10.0, 9.3.1
>
> Attachments: server.log
>
>
> *Scenario*: Having several views that depend on others (ex: V1-> V2->V3, where V3 depends on V2 and V2 depends on V1), all with external materialization.
> Although all views LoadState is *LOADED *even after failures due to the dependent ones being loaded, in the end some views have zero (0) of cardinality, when there are actually records. Running the queries by hand return records.
> Log attached.
> Example:
> {panel}
> 2017-06-14 17:14:39,521 INFO [org.teiid.MATVIEWS] (Worker63_QueryProcessorQueue19799) WDpoyHmXlkvS Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 0 Load Number = 1
> {panel}
> After forcing the view update:
> {panel}
> 2017-06-14 17:23:38,042 INFO [org.teiid.MATVIEWS] (Worker65_QueryProcessorQueue27597) +IbRaNRtrZ1K Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 1816 Load Number = 2
> {panel}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 4 months
[JBoss JIRA] (TEIID-4961) External Materialized View With State Loaded but 0 Cardinality
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4961?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-4961:
----------------------------------
Component/s: Query Engine
Fix Version/s: 10.0
9.3.1
I can reproduce this behavior as well - only after subsequent reloads do the dependent mat views load correctly. I'll have a fix shortly.
> External Materialized View With State Loaded but 0 Cardinality
> --------------------------------------------------------------
>
> Key: TEIID-4961
> URL: https://issues.jboss.org/browse/TEIID-4961
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.3
> Environment: * Teiid 9.3.0
> * MySQL 5.7.18
> * Wildfly 10
> Reporter: Pedro Inácio
> Assignee: Steven Hawkins
> Fix For: 10.0, 9.3.1
>
> Attachments: server.log
>
>
> *Scenario*: Having several views that depend on others (ex: V1-> V2->V3, where V3 depends on V2 and V2 depends on V1), all with external materialization.
> Although all views LoadState is *LOADED *even after failures due to the dependent ones being loaded, in the end some views have zero (0) of cardinality, when there are actually records. Running the queries by hand return records.
> Log attached.
> Example:
> {panel}
> 2017-06-14 17:14:39,521 INFO [org.teiid.MATVIEWS] (Worker63_QueryProcessorQueue19799) WDpoyHmXlkvS Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 0 Load Number = 1
> {panel}
> After forcing the view update:
> {panel}
> 2017-06-14 17:23:38,042 INFO [org.teiid.MATVIEWS] (Worker65_QueryProcessorQueue27597) +IbRaNRtrZ1K Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 1816 Load Number = 2
> {panel}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 4 months
[JBoss JIRA] (TEIID-4976) criteria duplicated when criteria includes the same columns as dependent join criteria
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4976?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4976.
-----------------------------------
Fix Version/s: 10.0
Resolution: Done
Added a small update to the dependent criteria processor to dedup. This is not quite as good as a general rewrite, but that would have required further tweaks.
> criteria duplicated when criteria includes the same columns as dependent join criteria
> --------------------------------------------------------------------------------------
>
> Key: TEIID-4976
> URL: https://issues.jboss.org/browse/TEIID-4976
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7.11.6_2
> Reporter: Marc Shirley
> Assignee: Steven Hawkins
> Fix For: 10.0
>
>
> Submitting a query that uses the same column for the join condition and criteria results in the criteria being duplicated on the dependent side of the join.
> For example:
> SELECT * FROM A, B
> WHERE B.id = A.id AND A.id IN ('1','2','3') OPTION MAKEDEP B;
> Results in the dependent side query looking like:
> SELECT ... FROM B WHERE B.id IN ('1','2','3') AND B.id IN ('1','2','3');
> This looks to have already been resolved in later versions, but I was not able to find the original JIRA that would have addressed this issue.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 4 months
[JBoss JIRA] (TEIID-4976) criteria duplicated when criteria includes the same columns as dependent join criteria
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4976?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-4976:
----------------------------------
Component/s: Query Engine
> criteria duplicated when criteria includes the same columns as dependent join criteria
> --------------------------------------------------------------------------------------
>
> Key: TEIID-4976
> URL: https://issues.jboss.org/browse/TEIID-4976
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7.11.6_2
> Reporter: Marc Shirley
> Assignee: Steven Hawkins
>
> Submitting a query that uses the same column for the join condition and criteria results in the criteria being duplicated on the dependent side of the join.
> For example:
> SELECT * FROM A, B
> WHERE B.id = A.id AND A.id IN ('1','2','3') OPTION MAKEDEP B;
> Results in the dependent side query looking like:
> SELECT ... FROM B WHERE B.id IN ('1','2','3') AND B.id IN ('1','2','3');
> This looks to have already been resolved in later versions, but I was not able to find the original JIRA that would have addressed this issue.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 4 months
[JBoss JIRA] (TEIID-4976) criteria duplicated when criteria includes the same columns as dependent join criteria
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4976?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4976:
---------------------------------------
>From what I can see this still occurs on master. This should only occur when there is a makedep hint present.
> criteria duplicated when criteria includes the same columns as dependent join criteria
> --------------------------------------------------------------------------------------
>
> Key: TEIID-4976
> URL: https://issues.jboss.org/browse/TEIID-4976
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.11.6_2
> Reporter: Marc Shirley
> Assignee: Steven Hawkins
>
> Submitting a query that uses the same column for the join condition and criteria results in the criteria being duplicated on the dependent side of the join.
> For example:
> SELECT * FROM A, B
> WHERE B.id = A.id AND A.id IN ('1','2','3') OPTION MAKEDEP B;
> Results in the dependent side query looking like:
> SELECT ... FROM B WHERE B.id IN ('1','2','3') AND B.id IN ('1','2','3');
> This looks to have already been resolved in later versions, but I was not able to find the original JIRA that would have addressed this issue.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 4 months
[JBoss JIRA] (TEIID-4976) criteria duplicated when criteria includes the same columns as dependent join criteria
by Marc Shirley (JIRA)
[ https://issues.jboss.org/browse/TEIID-4976?page=com.atlassian.jira.plugin... ]
Marc Shirley commented on TEIID-4976:
-------------------------------------
This is to look into pulling back a fix to 6.2.12. So, to an effect both. My comments towards not being able to find an original previous report for this were towards indicating that this is resolved in 8.12 versions, but still present in 8.7 versions. So, the intention is to identify the original issue if it exists, or explore what change fixed the issue if a previous report for this scenario does not exist.
> criteria duplicated when criteria includes the same columns as dependent join criteria
> --------------------------------------------------------------------------------------
>
> Key: TEIID-4976
> URL: https://issues.jboss.org/browse/TEIID-4976
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.11.6_2
> Reporter: Marc Shirley
> Assignee: Steven Hawkins
>
> Submitting a query that uses the same column for the join condition and criteria results in the criteria being duplicated on the dependent side of the join.
> For example:
> SELECT * FROM A, B
> WHERE B.id = A.id AND A.id IN ('1','2','3') OPTION MAKEDEP B;
> Results in the dependent side query looking like:
> SELECT ... FROM B WHERE B.id IN ('1','2','3') AND B.id IN ('1','2','3');
> This looks to have already been resolved in later versions, but I was not able to find the original JIRA that would have addressed this issue.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 4 months
[JBoss JIRA] (TEIID-4978) Document issue with SQLAlchemy/Superset and table names containing .
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-4978:
-------------------------------------
Summary: Document issue with SQLAlchemy/Superset and table names containing .
Key: TEIID-4978
URL: https://issues.jboss.org/browse/TEIID-4978
Project: Teiid
Issue Type: Quality Risk
Components: Documentation
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 10.0
Superset/SQLAlchemy cannot find column metadata for tables that contain . in their name - this occurs directly against Postgresql, so this isn't specifically a Teiid issue. However Teiid is likely to create foreign tables that contain . in their names unless the importer is told to not schema qualify.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 4 months
[JBoss JIRA] (TEIID-4977) Support materialization as the 8.12.x version did
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-4977?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-4977:
------------------------------------
Can the 8.12.x logic, that Steve implemented, be used in this case, where 3 caches are used to accomplish this scenario?
> Support materialization as the 8.12.x version did
> -------------------------------------------------
>
> Key: TEIID-4977
> URL: https://issues.jboss.org/browse/TEIID-4977
> Project: Teiid
> Issue Type: Feature Request
> Components: Infinispan
> Affects Versions: 8.12.x-6.4
> Reporter: Van Halbert
> Assignee: Ramesh Reddy
> Priority: Critical
>
> Need to enable the materialization process to load the cache "offline" and not impact access to current cache and data. This would be similar to how the 8.12.x version was implemented and similar to how RDBMS materialization is done.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 4 months
[JBoss JIRA] (TEIID-4977) Support materialization as the 8.12.x version did
by Van Halbert (JIRA)
Van Halbert created TEIID-4977:
----------------------------------
Summary: Support materialization as the 8.12.x version did
Key: TEIID-4977
URL: https://issues.jboss.org/browse/TEIID-4977
Project: Teiid
Issue Type: Feature Request
Components: Infinispan
Affects Versions: 8.12.x-6.4
Reporter: Van Halbert
Assignee: Ramesh Reddy
Priority: Critical
Need to enable the materialization process to load the cache "offline" and not impact access to current cache and data. This would be similar to how the 8.12.x version was implemented and similar to how RDBMS materialization is done.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 4 months