[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
6 days, 9 hours
[JBoss JIRA] (TEIID-5131) Create ansible playbooks for the ansible service broker
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5131?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5131:
---------------------------------------
This should at least be able to bind to provisioned instances. We also need to define security expectations.
> Create ansible playbooks for the ansible service broker
> -------------------------------------------------------
>
> Key: TEIID-5131
> URL: https://issues.jboss.org/browse/TEIID-5131
> Project: Teiid
> Issue Type: Enhancement
> Components: Build/Kits
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 10.x
>
>
> For some of the most common usage patterns, we should create playbooks that contribute service brokers via the ansible service broker. This could be:
> salesforce as db
> couchbase as db/odata
> mongodb as db/odata
> other openshift databases as odata
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 12 months
[JBoss JIRA] (TEIID-5131) Create ansible playbooks for the ansible service broker
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5131:
-------------------------------------
Summary: Create ansible playbooks for the ansible service broker
Key: TEIID-5131
URL: https://issues.jboss.org/browse/TEIID-5131
Project: Teiid
Issue Type: Enhancement
Components: Build/Kits
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 10.x
For some of the most common usage patterns, we should create playbooks that contribute service brokers via the ansible service broker. This could be:
salesforce as db
couchbase as db/odata
mongodb as db/odata
other openshift databases as odata
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 12 months
[JBoss JIRA] (TEIID-4892) oData v4 error using $expand
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4892?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4892:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1506985|https://bugzilla.redhat.com/show_bug.cgi?id=1506985] from NEW to ON_QA
> oData v4 error using $expand
> ----------------------------
>
> Key: TEIID-4892
> URL: https://issues.jboss.org/browse/TEIID-4892
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 9.1.4
> Environment: Teiid 9.1.4 with WildFly 10.0.0.Final running on Windows server 2012 R2 Datacenter Edition.
> Reporter: Marco Monti
> Assignee: Steven Hawkins
> Fix For: 9.3, 8.12.x-6.4, 9.1.5, 9.2.3
>
> Attachments: server.log
>
>
> Hello,
>
> we are querying Teiid 9.1.4 using the following oData v4 syntax:
>
> {{http://host:port/odata4/<vdbname>/<model-name>/<table-name>?$expand=<navigation>}}
>
> It doesn't matter what navigation property we choose; we always get following error message:
>
> {{{"error":{"code":null,"message":"String index out of range: -1"}}}}
>
> Please find the attached logfile.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 12 months
[JBoss JIRA] (TEIID-4876) Salesforce connector not working properly in 9.2.2 release
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-4876?page=com.atlassian.jira.plugin... ]
Van Halbert updated TEIID-4876:
-------------------------------
Fix Version/s: 8.12.x-6.4
> Salesforce connector not working properly in 9.2.2 release
> ----------------------------------------------------------
>
> Key: TEIID-4876
> URL: https://issues.jboss.org/browse/TEIID-4876
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Affects Versions: 9.2.2
> Environment: MacOS{{monospaced text}}
> Reporter: Jeff Davis
> Assignee: Steven Hawkins
> Fix For: 9.3, 8.12.x-6.4, 9.2.3
>
>
> The Salesforce connector does not appear to work in the 9.2.2 release (using resource adpater salesforce-34). Here's the relevant entry in the standalone-teiid.xml file:
> {{<resource-adapter id="sf34">
> <module slot="main" id="org.jboss.teiid.resource-adapter.salesforce-34"/>
> <transaction-support>NoTransaction</transaction-support>
> <connection-definitions>
> <connection-definition class-name="org.teiid.resource.adapter.salesforce.SalesForceManagedConnectionFactory" jndi-name="java:/sfDS" enabled="true" use-java-context="true" pool-name="teiid-sf-ds">
> <config-property name="URL">
> https://www.salesforce.com/services/Soap/u/34.0
> </config-property>
> <config-property name="password">
> [[not shown]]
> </config-property>
> <config-property name="username">
> lightning(a)zazarie.com
> </config-property>
> </connection-definition>
> </connection-definitions>
> </resource-adapter>}}
> And here is my dynamic VDB xml (SFDCVdb-vdb.xml):
> {{<vdb name="SFDCVdb" version="1">
> <description/>
> <property name="validationDateTime" value="Fri Apr 21 14:41:27 MDT 2017"/>
> <property name="validationVersion" value="9.2.2"/>
> <model name="SFSourceModel">
> <source connection-jndi-name="java:/sfDS" name="SFSourceModel"
> translator-name="salesforce-34"/>
> <metadata type="DDL"><![CDATA[
> CREATE FOREIGN TABLE Account (
> Id string(18) NOT NULL DEFAULT 'Generated upon creation' OPTIONS(NAMEINSOURCE 'Id', NATIVE_TYPE 'id', "teiid_sf:Defaulted on Create" 'TRUE'),
> IsDeleted boolean OPTIONS(NAMEINSOURCE 'IsDeleted', NATIVE_TYPE 'boolean', "teiid_sf:Defaulted on Create" 'TRUE'),
> MasterRecordId string(18) OPTIONS(NAMEINSOURCE 'MasterRecordId', NATIVE_TYPE 'id'),
> Name string(255) OPTIONS(NAMEINSOURCE 'Name', NATIVE_TYPE 'string'),
> Type string(40) OPTIONS(NAMEINSOURCE 'Type', NATIVE_TYPE 'picklist', "teiid_sf:Picklist Values" 'Prospect,Customer - Direct,Customer - Channel,Channel Partner / Reseller,Installation Partner,Technology Partner,Other'),
> ParentId string(18) OPTIONS(NAMEINSOURCE 'ParentId', NATIVE_TYPE 'id'),
> BillingStreet string(255) OPTIONS(NAMEINSOURCE 'BillingStreet', NATIVE_TYPE 'textarea', SEARCHABLE 'UNSEARCHABLE'),
> BillingCity string(40) OPTIONS(NAMEINSOURCE 'BillingCity', NATIVE_TYPE 'string'),
> BillingState string(80) OPTIONS(NAMEINSOURCE 'BillingState', NATIVE_TYPE 'string'),
> BillingPostalCode string(20) OPTIONS(NAMEINSOURCE 'BillingPostalCode', NATIVE_TYPE 'string'),
> BillingCountry string(80) OPTIONS(NAMEINSOURCE 'BillingCountry', NATIVE_TYPE 'string'),
> ShippingStreet string(255) OPTIONS(NAMEINSOURCE 'ShippingStreet', NATIVE_TYPE 'textarea', SEARCHABLE 'UNSEARCHABLE'),
> ShippingCity string(40) OPTIONS(NAMEINSOURCE 'ShippingCity', NATIVE_TYPE 'string'),
> ShippingState string(80) OPTIONS(NAMEINSOURCE 'ShippingState', NATIVE_TYPE 'string'),
> ShippingPostalCode string(20) OPTIONS(NAMEINSOURCE 'ShippingPostalCode', NATIVE_TYPE 'string'),
> ShippingCountry string(80) OPTIONS(NAMEINSOURCE 'ShippingCountry', NATIVE_TYPE 'string'),
> Phone string(40) OPTIONS(NAMEINSOURCE 'Phone', NATIVE_TYPE 'phone'),
> Fax string(40) OPTIONS(NAMEINSOURCE 'Fax', NATIVE_TYPE 'phone'),
> AccountNumber string(40) OPTIONS(NAMEINSOURCE 'AccountNumber', NATIVE_TYPE 'string'),
> Website string(255) OPTIONS(NAMEINSOURCE 'Website', NATIVE_TYPE 'url'),
> Sic string(20) OPTIONS(NAMEINSOURCE 'Sic', NATIVE_TYPE 'string'),
> Industry string(40) OPTIONS(NAMEINSOURCE 'Industry', NATIVE_TYPE 'picklist', "teiid_sf:Picklist Values" 'Agriculture,Apparel,Banking,Biotechnology,Chemicals,Communications,Construction,Consulting,Education,Electronics,Energy,Engineering,Entertainment,Environmental,Finance,Food & Beverage,Government,Healthcare,Hospitality,Insurance,Machinery,Manufacturing,Media,Not For Profit,Recreation,Retail,Shipping,Technology,Telecommunications,Transportation,Utilities,Other'),
> AnnualRevenue double OPTIONS(NAMEINSOURCE 'AnnualRevenue', NATIVE_TYPE 'currency', CURRENCY 'TRUE'),
> NumberOfEmployees integer OPTIONS(NAMEINSOURCE 'NumberOfEmployees', NATIVE_TYPE 'int'),
> Ownership string(40) OPTIONS(NAMEINSOURCE 'Ownership', NATIVE_TYPE 'picklist', "teiid_sf:Picklist Values" 'Public,Private,Subsidiary,Other'),
> TickerSymbol string(20) OPTIONS(NAMEINSOURCE 'TickerSymbol', NATIVE_TYPE 'string'),
> Description string(32000) OPTIONS(NAMEINSOURCE 'Description', NATIVE_TYPE 'textarea', SEARCHABLE 'UNSEARCHABLE'),
> Rating string(40) OPTIONS(NAMEINSOURCE 'Rating', NATIVE_TYPE 'picklist', "teiid_sf:Picklist Values" 'Hot,Warm,Cold'),
> Site string(80) OPTIONS(NAMEINSOURCE 'Site', NATIVE_TYPE 'string'),
> OwnerId string(18) OPTIONS(NAMEINSOURCE 'OwnerId', NATIVE_TYPE 'id', "teiid_sf:Defaulted on Create" 'TRUE'),
> LastActivityDate date OPTIONS(NAMEINSOURCE 'LastActivityDate', NATIVE_TYPE 'date'),
> Jigsaw string(20) OPTIONS(NAMEINSOURCE 'Jigsaw', NATIVE_TYPE 'string'),
> pnccanvaspck__CustomerPriority__c string(255) OPTIONS(NAMEINSOURCE 'pnccanvaspck__CustomerPriority__c', NATIVE_TYPE 'picklist', "teiid_sf:Picklist Values" 'High,Low,Medium', "teiid_sf:Custom" 'TRUE'),
> pnccanvaspck__SLA__c string(255) OPTIONS(NAMEINSOURCE 'pnccanvaspck__SLA__c', NATIVE_TYPE 'picklist', "teiid_sf:Picklist Values" 'Gold,Silver,Platinum,Bronze', "teiid_sf:Custom" 'TRUE'),
> pnccanvaspck__Active__c string(255) OPTIONS(NAMEINSOURCE 'pnccanvaspck__Active__c', NATIVE_TYPE 'picklist', "teiid_sf:Picklist Values" 'No,Yes', "teiid_sf:Custom" 'TRUE'),
> pnccanvaspck__NumberofLocations__c double OPTIONS(NAMEINSOURCE 'pnccanvaspck__NumberofLocations__c', NATIVE_TYPE 'double', "teiid_sf:Custom" 'TRUE'),
> pnccanvaspck__UpsellOpportunity__c string(255) OPTIONS(NAMEINSOURCE 'pnccanvaspck__UpsellOpportunity__c', NATIVE_TYPE 'picklist', "teiid_sf:Picklist Values" 'Maybe,No,Yes', "teiid_sf:Custom" 'TRUE'),
> pnccanvaspck__SLASerialNumber__c string(10) OPTIONS(NAMEINSOURCE 'pnccanvaspck__SLASerialNumber__c', NATIVE_TYPE 'string', "teiid_sf:Custom" 'TRUE'),
> pnccanvaspck__SLAExpirationDate__c date OPTIONS(NAMEINSOURCE 'pnccanvaspck__SLAExpirationDate__c', NATIVE_TYPE 'date', "teiid_sf:Custom" 'TRUE'),
> pnccanvaspck__Business_Hours__c string(100) OPTIONS(NAMEINSOURCE 'pnccanvaspck__Business_Hours__c', NATIVE_TYPE 'string', "teiid_sf:Custom" 'TRUE'),
> pnccanvaspck__Location__Latitude__s double OPTIONS(NAMEINSOURCE 'pnccanvaspck__Location__Latitude__s', NATIVE_TYPE 'double', "teiid_sf:Custom" 'TRUE'),
> pnccanvaspck__Location__Longitude__s double OPTIONS(NAMEINSOURCE 'pnccanvaspck__Location__Longitude__s', NATIVE_TYPE 'double', "teiid_sf:Custom" 'TRUE'),
> pnccanvaspck__Time_Zone__c string(10) OPTIONS(NAMEINSOURCE 'pnccanvaspck__Time_Zone__c', NATIVE_TYPE 'string', "teiid_sf:Custom" 'TRUE'),
> CONSTRAINT Id_PK PRIMARY KEY(Id),
> CONSTRAINT FK_Account_ParentId FOREIGN KEY(ParentId) REFERENCES Account(Id)
> ) OPTIONS(NAMEINSOURCE 'Account', UPDATABLE 'TRUE', "teiid_sf:Supports Search" 'TRUE', "teiid_sf:Supports Replicate" 'TRUE', "teiid_sf:Supports Merge" 'TRUE', "teiid_sf:Supports Create" 'TRUE', "teiid_sf:Supports Delete" 'TRUE', "teiid_sf:Supports Query" 'TRUE', "teiid_sf:Supports Retrieve" 'TRUE')
> ]]></metadata>
> </model>
> <model name="SFViewModel" type="VIRTUAL">
> <property name="imports" value="SFSourceModel"/>
> <metadata type="DDL"><![CDATA[
> CREATE VIEW Account (
> Name string(255)
> ) OPTIONS(UPDATABLE 'TRUE')
> AS
> SELECT
> SFSourceModel.Account.Name
> FROM
> SFSourceModel.Account;
> ]]></metadata>
> </model>
> </vdb>}}
> I tried this same exact configuration in the 8.13.7 release and it worked.
> Steven remarked the following:
> "It looks like the change to build using the feature-packs broke the dependencies for the salesforce-34 resource adapter."
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 12 months