[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
13 hours, 8 minutes
[JBoss JIRA] (TEIID-4992) Add OSDQ set functions
by vivek singh (JIRA)
[ https://issues.jboss.org/browse/TEIID-4992?page=com.atlassian.jira.plugin... ]
vivek singh commented on TEIID-4992:
------------------------------------
osDQ core version 6.2.2 will have jar to support following API
/**
* @author vivek singh
*
*/
import java.util.Arrays;
import java.util.Vector;
import org.arrah.framework.analytics.SetAnalysis;
/**
* @author viveksingh
*
*/
public class SetUtil {
/**
* @param seta
* the input set A where memebers can be duplicate
* @param setb
* the input set B where memebers can be duplicate
* @return
* Union of Set A and set B where memebers can not be duplicate
*/
Object[] unionSet(Object[] seta, Object[] setb) {
if (seta == null ) return setb;
if (setb == null ) return setb;
SetAnalysis sa = new SetAnalysis(new Vector<Object>(Arrays.asList(seta)),
new Vector<Object>(Arrays.asList(setb)));
return sa.getUnion().toArray();
}
/**
* @param seta
* the input set A where memebers can be duplicate
* @param setb
* the input set B where memebers can be duplicate
* @param fuzzyIndex
* this is the fuzzy distance which will match similar words
* 0.0 - will take all ( no match) and > 1.0 will match exact
* @return
* Union of Set A and set B where memebers can not be duplicate
*/
Object[] unionFuzzy(Object[] seta, Object[] setb, float fuzzyIndex) {
if (seta == null ) return setb;
if (setb == null ) return setb;
SetAnalysis sa = new SetAnalysis(new Vector<Object>(Arrays.asList(seta)),
new Vector<Object>(Arrays.asList(setb)));
return sa.getUnion(fuzzyIndex).toArray();
}
/**
* @param seta
* the input set A where memebers can be duplicate
* @param setb
* the input set B where memebers can be duplicate
* @return
* Intersection of Set A and set B where memebers can not be duplicate
*/
Object[] intersectionSet(Object[] seta, Object[] setb) {
if (seta == null || setb == null) return null;
SetAnalysis sa = new SetAnalysis(new Vector<Object>(Arrays.asList(seta)),
new Vector<Object>(Arrays.asList(setb)));
return sa.getIntersection().toArray();
}
/**
* @param seta
* the input set A where memebers can be duplicate
* @param setb
* the input set B where memebers can be duplicate
* @param fuzzyIndex
* this is the fuzzy distance which will match similar words
* 0.0 - will take all ( no match) and > 1.0 will match exact
* @return
* Intersection of Set A and set B where memebers can not be duplicate
*/
Object[] intersectionFuzzy(Object[] seta, Object[] setb, float fuzzyIndex) {
if (seta == null || setb == null) return null;
SetAnalysis sa = new SetAnalysis(new Vector<Object>(Arrays.asList(seta)),
new Vector<Object>(Arrays.asList(setb)));
return sa.getIntersection(fuzzyIndex).toArray();
}
/**
* @param seta
* the input set A where memebers can be duplicate
* @param setb
* the input set B where memebers can be duplicate
* @return
* Difference of Set A and set B where memebers can not be duplicate
* Elements which are there in set A but not in set B
*/
Object[] minusSet(Object[] seta, Object[] setb) {
if (seta == null ) return null;
if (setb == null ) return seta;
SetAnalysis sa = new SetAnalysis();
return sa.getDifference(new Vector<Object>(Arrays.asList(seta)),
new Vector<Object>(Arrays.asList(setb))).toArray();
}
/**
* @param seta
* the input set A where memebers can be duplicate
* @param setb
* the input set B where memebers can be duplicate
* @param fuzzyIndex
* this is the fuzzy distance which will match similar words
* 0.0 - will take all ( no match) and > 1.0 will match exact
* @return
* Difference of Set A and set B where memebers can not be duplicate
* Elements which are there in set A but not in set B
*/
Object[] minusFuzzy(Object[] seta, Object[] setb, float fuzzyIndex) {
if (seta == null ) return null;
if (setb == null ) return seta;
SetAnalysis sa = new SetAnalysis();
return sa.getDifference(new Vector<Object>(Arrays.asList(seta)),
new Vector<Object>(Arrays.asList(setb)),fuzzyIndex).toArray();
}
// for testing
public static void main(String[] args) {
Object[] first = new Object[]{"vivek","vivek16","abcd","vivek44"};
Object[] second = new Object[]{"vivek36","vivekkk","abcd","vivek445"};
SetUtil setu = new SetUtil();
//Object[] finaltse = setu.unionSet(first, second);
//Object[] finaltse = setu.unionFuzzy(first, second, 1.1f);
//Object[] finaltse = setu.intersectionSet(first, second);
//Object[] finaltse = setu.intersectionFuzzy(first, second,1.1f);
//Object[] finaltse = setu.minusSet(first, second);
Object[] finaltse = setu.minusFuzzy(first, second,1.1f);
for (Object a:finaltse)
System.out.println(a.toString());
}
}
> Add OSDQ set functions
> ----------------------
>
> Key: TEIID-4992
> URL: https://issues.jboss.org/browse/TEIID-4992
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 10.0
>
> Attachments: SetUtil.java
>
>
> There will be a new osdq core library drop in maven central soon containing additional functions for Teiid to add.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 2 months
[JBoss JIRA] (TEIID-5010) Swarm usage should be more configurable
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5010:
-------------------------------------
Summary: Swarm usage should be more configurable
Key: TEIID-5010
URL: https://issues.jboss.org/browse/TEIID-5010
Project: Teiid
Issue Type: Sub-task
Components: WildFly Swarm
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 10.0
When building a swarm uber jar the wildfly/swarm functionality remains the same regardless of what is needed. The wildfly fractions/subsystems need to be configurable so that we can drop systems, such as the web server, selectively.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 2 months
[JBoss JIRA] (TEIID-4918) Enhance infinispan support for muti-threaded puts or using putAll
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4918?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-4918:
-------------------------------------
Right now I am considering this only used in 6.4 with old style of before and after scripts with truncate/rename. The latter one uses the upsert mechanism which never really starts with empty table other than very first time. So, IMO that may be little less useful there. We need to really solve the UDF issue on the Infinispan side for correct behavior. So, let's come back to it at a later point, hopefully, by then Infinispan will solve the issue on their side.
> Enhance infinispan support for muti-threaded puts or using putAll
> -----------------------------------------------------------------
>
> Key: TEIID-4918
> URL: https://issues.jboss.org/browse/TEIID-4918
> Project: Teiid
> Issue Type: Enhancement
> Components: Infinispan
> Affects Versions: 9.3, 8.12.x-6.4
> Reporter: Van Halbert
> Assignee: Ramesh Reddy
> Fix For: 10.0
>
>
> For performance reasons, it was requested that the infinispan connector/translator be enhanced to be multi-threaded when doing batch inserts or try using the putAll option.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 2 months
[JBoss JIRA] (TEIID-4918) Enhance infinispan support for muti-threaded puts or using putAll
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4918?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4918:
---------------------------------------
As this relates to materialization, do you want to have the system procedures add a general hint, e.g. 'bulk-load', that the translator would look for as well?
> Enhance infinispan support for muti-threaded puts or using putAll
> -----------------------------------------------------------------
>
> Key: TEIID-4918
> URL: https://issues.jboss.org/browse/TEIID-4918
> Project: Teiid
> Issue Type: Enhancement
> Components: Infinispan
> Affects Versions: 9.3, 8.12.x-6.4
> Reporter: Van Halbert
> Assignee: Ramesh Reddy
> Fix For: 10.0
>
>
> For performance reasons, it was requested that the infinispan connector/translator be enhanced to be multi-threaded when doing batch inserts or try using the putAll option.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 2 months
[JBoss JIRA] (TEIID-5009) Add license files to JDBC Driver
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5009?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5009:
---------------------------------------
Keep in mind that the original reason for TEIID-4094 was moot with later releases. We also had issues with bringing TEIID-4094 forward, so introducing new projects should be done with caution. If it's possible to just augment the existing assembly logic that seems a lot simpler.
> Add license files to JDBC Driver
> --------------------------------
>
> Key: TEIID-5009
> URL: https://issues.jboss.org/browse/TEIID-5009
> Project: Teiid
> Issue Type: Task
> Components: JDBC Driver
> Affects Versions: 8.12.x-6.4, 10.x
> Reporter: Van Halbert
> Assignee: Van Halbert
> Priority: Blocker
>
> Adding the license.xml and license.html files to the jdbc driver.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 2 months