[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 weeks, 4 days
[JBoss JIRA] (TEIID-5928) Allow External and Internal materialization of multi-source to fail/update individually
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5928?page=com.atlassian.jira.plugi... ]
Steven Hawkins commented on TEIID-5928:
---------------------------------------
After looking this over some more, the initial approach would be to add generic logic for an additional load strategy. That is additional teiid_rel:MATVIEW_ options to control a partitioned load. This would be something like adding:
teiid_rel:MATVIEW_PART_LOAD_COLUMN - name of the view column the load is partitioned over
teiid_rel:MATVIEW_PART_LOAD_VALUES - specifies the partitioning values, likely would default to select distinct(load_column) from matView
I'll update SYS.Schemas to include the source names. So in the case of a multisource materialization you would set the teiid_rel:MATVIEW_PART_LOAD_COLUMN to the relevant column, possibly SOURCE_NAME and teiid_rel:MATVIEW_PART_LOAD_VALUES to
select cast(col as string) from (exec sys.arrayIterate((select sources from sys.schemas where name = 'some schema'))) as a
I realize this isn't as friendly as Teiid automatically inferring those values, but it ensures that we'll handle all cases initially without the need for detection logic for the various cases multi-source vs. partitioned union, the effects of view layers, etc.
> Allow External and Internal materialization of multi-source to fail/update individually
> ---------------------------------------------------------------------------------------
>
> Key: TEIID-5928
> URL: https://issues.redhat.com/browse/TEIID-5928
> Project: Teiid
> Issue Type: Enhancement
> Components: Common
> Affects Versions: 12.2.2
> Reporter: Rafael Sampaio
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 15.0
>
> Original Estimate: 0 minutes
> Time Spent: 30 minutes
> Remaining Estimate: 0 minutes
>
> Hi, all.
> First of all thanks in advance for your attention, and efforts on such a great product.
> Since I haven't found any docs or examples regarding my current usage (please if this already exists or there's a better way of doing this, disregard this request and advice) here goes my suggestion:
> It would be nice to have a way to gracefully update/fail per source, if a view that has Materialization enabled and target a multi-source source model, because things can get, messy if I have to create a view per source and Materialize each one and only then create a unified (UNION) view.
> Using this approach also obligates me to implement custom insert/update/delete/based on the discriminator column.
> Thanks again.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 4 months
[JBoss JIRA] (TEIIDSB-213) We have strange behavior with aliases
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-213?page=com.atlassian.jira.plug... ]
Steven Hawkins commented on TEIIDSB-213:
----------------------------------------
> As a solution I think need to add property for Teiid "Generate full qualified names" or other similar solution. Why used full qualified names always?
Presumably it was done that way to prevent ambiguity in join scenarios. Since there is no documentation to indicate that some scenarios will behave differently with qualification, there was no reason to believe it isn't generally valid.
An option to no qualify names would therefore probably imply not to pushdown any joins.
> Or may be you can add SQL option as MAKEDEP to do not generate full qualified names? It is better solution because we can have more granularity of requests.
There is a feature called source hints that are a user level hint that is pushed to the source which could be used to control some of this behavior.
Another option is to only qualify in join scenarios. A simple query like the one you have here should then work, but likely wouldn't when there is a parent/child join.
> So, at now Teiid do not compatible with this Salesforce behavior
You mean we are not compatible with their qualification bug. Yes that is correct.
> We have strange behavior with aliases
> -------------------------------------
>
> Key: TEIIDSB-213
> URL: https://issues.redhat.com/browse/TEIIDSB-213
> Project: Teiid Spring Boot
> Issue Type: Bug
> Components: datasource
> Reporter: Renat Eskenin
> Priority: Major
> Attachments: Снимок экрана от 2020-06-26 12-37-40.png, Снимок экрана от 2020-06-26 12-38-04.png
>
>
> We have two SOQL
> SELECT ArticleNumber,Id,KnowledgeArticleId,Language FROM Regular_Articles__kav WHERE Language = 'it' LIMIT 5
>
> SELECT a.ArticleNumber,a.Id,a.KnowledgeArticleId,a.Language FROM Regular_Articles__kav a WHERE a.Language = 'it' LIMIT 5
> This SOQL requests get different responses from SF because in second request we have alias for table.
> It is mystics from SF (we made bug in sf)
> How we can call
> SELECT ArticleNumber,Id,KnowledgeArticleId,Language FROM Regular_Articles__kav WHERE Language = 'it' LIMIT 5
> in Teiid without aliases?
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 4 months
[JBoss JIRA] (TEIIDSB-214) doc links broken by file name changes
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-214?page=com.atlassian.jira.plug... ]
Steven Hawkins updated TEIIDSB-214:
-----------------------------------
Summary: doc links broken by file name changes (was: Incoming doc links broken by file name changes)
> doc links broken by file name changes
> -------------------------------------
>
> Key: TEIIDSB-214
> URL: https://issues.redhat.com/browse/TEIIDSB-214
> Project: Teiid Spring Boot
> Issue Type: Bug
> Components: examples
> Reporter: Steven Hawkins
> Priority: Major
> Fix For: 1.6.0
>
>
> There are quite a few places that reference the older Teiid doc file names (the new names are like r_...-...). Those links should either be converted to reference 13.0.x, which still uses those names, or updated to 14.0.x with new paths.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 4 months
[JBoss JIRA] (TEIIDSB-214) Incoming doc links broken by file name changes
by Steven Hawkins (Jira)
Steven Hawkins created TEIIDSB-214:
--------------------------------------
Summary: Incoming doc links broken by file name changes
Key: TEIIDSB-214
URL: https://issues.redhat.com/browse/TEIIDSB-214
Project: Teiid Spring Boot
Issue Type: Bug
Components: examples
Reporter: Steven Hawkins
Fix For: 1.6.0
There are quite a few places that reference the older Teiid doc file names (the new names are like r_...-...). Those links should either be converted to reference 13.0.x, which still uses those names, or updated to 14.0.x with new paths.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 4 months
[JBoss JIRA] (TEIIDSB-213) We have strange behavior with aliases
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-213?page=com.atlassian.jira.plug... ]
Renat Eskenin commented on TEIIDSB-213:
---------------------------------------
So, at now Teiid do not compatible with this Salesforce behavior :(
> We have strange behavior with aliases
> -------------------------------------
>
> Key: TEIIDSB-213
> URL: https://issues.redhat.com/browse/TEIIDSB-213
> Project: Teiid Spring Boot
> Issue Type: Bug
> Components: datasource
> Reporter: Renat Eskenin
> Priority: Major
> Attachments: Снимок экрана от 2020-06-26 12-37-40.png, Снимок экрана от 2020-06-26 12-38-04.png
>
>
> We have two SOQL
> SELECT ArticleNumber,Id,KnowledgeArticleId,Language FROM Regular_Articles__kav WHERE Language = 'it' LIMIT 5
>
> SELECT a.ArticleNumber,a.Id,a.KnowledgeArticleId,a.Language FROM Regular_Articles__kav a WHERE a.Language = 'it' LIMIT 5
> This SOQL requests get different responses from SF because in second request we have alias for table.
> It is mystics from SF (we made bug in sf)
> How we can call
> SELECT ArticleNumber,Id,KnowledgeArticleId,Language FROM Regular_Articles__kav WHERE Language = 'it' LIMIT 5
> in Teiid without aliases?
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 4 months
[JBoss JIRA] (TEIIDSB-213) We have strange behavior with aliases
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-213?page=com.atlassian.jira.plug... ]
Renat Eskenin commented on TEIIDSB-213:
---------------------------------------
I have response from SF support:
{code}
I have tested this in my local dev org and aliases doesn't work for other languages.
So, this seems to be not working when we use aliases to filter other languages. I kindly request you to use the query as below:
SELECT ArticleNumber,Id,Language FROM Regular_Articles__kav WHERE Language = 'it'
2.
> I can see that the default language for the user with userId: 0050e000006TIKfAAO is English.
> As the default language is English for the user, when they query on 'Regular_Articles__kav' they we see the records with language set to en-US
{code}
> We have strange behavior with aliases
> -------------------------------------
>
> Key: TEIIDSB-213
> URL: https://issues.redhat.com/browse/TEIIDSB-213
> Project: Teiid Spring Boot
> Issue Type: Bug
> Components: datasource
> Reporter: Renat Eskenin
> Priority: Major
> Attachments: Снимок экрана от 2020-06-26 12-37-40.png, Снимок экрана от 2020-06-26 12-38-04.png
>
>
> We have two SOQL
> SELECT ArticleNumber,Id,KnowledgeArticleId,Language FROM Regular_Articles__kav WHERE Language = 'it' LIMIT 5
>
> SELECT a.ArticleNumber,a.Id,a.KnowledgeArticleId,a.Language FROM Regular_Articles__kav a WHERE a.Language = 'it' LIMIT 5
> This SOQL requests get different responses from SF because in second request we have alias for table.
> It is mystics from SF (we made bug in sf)
> How we can call
> SELECT ArticleNumber,Id,KnowledgeArticleId,Language FROM Regular_Articles__kav WHERE Language = 'it' LIMIT 5
> in Teiid without aliases?
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 4 months
[JBoss JIRA] (TEIIDSB-213) We have strange behavior with aliases
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-213?page=com.atlassian.jira.plug... ]
Renat Eskenin commented on TEIIDSB-213:
---------------------------------------
Or may be you can add SQL option as MAKEDEP to do not generate full qualified names? It is better solution because we can have more granularity of requests.
> We have strange behavior with aliases
> -------------------------------------
>
> Key: TEIIDSB-213
> URL: https://issues.redhat.com/browse/TEIIDSB-213
> Project: Teiid Spring Boot
> Issue Type: Bug
> Components: datasource
> Reporter: Renat Eskenin
> Priority: Major
> Attachments: Снимок экрана от 2020-06-26 12-37-40.png, Снимок экрана от 2020-06-26 12-38-04.png
>
>
> We have two SOQL
> SELECT ArticleNumber,Id,KnowledgeArticleId,Language FROM Regular_Articles__kav WHERE Language = 'it' LIMIT 5
>
> SELECT a.ArticleNumber,a.Id,a.KnowledgeArticleId,a.Language FROM Regular_Articles__kav a WHERE a.Language = 'it' LIMIT 5
> This SOQL requests get different responses from SF because in second request we have alias for table.
> It is mystics from SF (we made bug in sf)
> How we can call
> SELECT ArticleNumber,Id,KnowledgeArticleId,Language FROM Regular_Articles__kav WHERE Language = 'it' LIMIT 5
> in Teiid without aliases?
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 4 months
[JBoss JIRA] (TEIIDSB-213) We have strange behavior with aliases
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-213?page=com.atlassian.jira.plug... ]
Renat Eskenin commented on TEIIDSB-213:
---------------------------------------
Yes it is SF Bug. I do not have issues, but my colleagues found this bug many months ago and just didn't use full qualified names or tables aliases. At now we do not have any solution with Teiid to select articles.
This problem found for Regular_Articles__kav, I do not known about other objects.
As a solution I think need to add property for Teiid "Generate full qualified names" or other similar solution. Why used full qualified names always?
> We have strange behavior with aliases
> -------------------------------------
>
> Key: TEIIDSB-213
> URL: https://issues.redhat.com/browse/TEIIDSB-213
> Project: Teiid Spring Boot
> Issue Type: Bug
> Components: datasource
> Reporter: Renat Eskenin
> Priority: Major
> Attachments: Снимок экрана от 2020-06-26 12-37-40.png, Снимок экрана от 2020-06-26 12-38-04.png
>
>
> We have two SOQL
> SELECT ArticleNumber,Id,KnowledgeArticleId,Language FROM Regular_Articles__kav WHERE Language = 'it' LIMIT 5
>
> SELECT a.ArticleNumber,a.Id,a.KnowledgeArticleId,a.Language FROM Regular_Articles__kav a WHERE a.Language = 'it' LIMIT 5
> This SOQL requests get different responses from SF because in second request we have alias for table.
> It is mystics from SF (we made bug in sf)
> How we can call
> SELECT ArticleNumber,Id,KnowledgeArticleId,Language FROM Regular_Articles__kav WHERE Language = 'it' LIMIT 5
> in Teiid without aliases?
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 4 months