[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, 2 days
[JBoss JIRA] (TEIID-6020) How to specify different config properties while connecting to FTP using Teiid Resource Adapter
by rahul gholap (Jira)
rahul gholap created TEIID-6020:
-----------------------------------
Summary: How to specify different config properties while connecting to FTP using Teiid Resource Adapter
Key: TEIID-6020
URL: https://issues.redhat.com/browse/TEIID-6020
Project: Teiid
Issue Type: Feature Request
Reporter: rahul gholap
Assignee: Steven Hawkins
We are trying to connect FTP and SFTP files using org.teiid.resource.adapter.ftp.FtpManagedConnectionFactory adapter.While configuring Resource Adapter,below options are available in config properties.
Host,Port,ParentDirectory,UserName,Password,Protocol.
Lets say JDBC url for connecting to SFTP file is like below.
jdbc:text:sftp://\{{host}}: \{{port}}(a)\{{ip}}//\{{file_path}}/\{{file_name}}.txt?odbcSchemaFile=\{{schema_file}}.sch;fileExtension=txt;delayedClose=0;refreshInterval=0
My Questions are below .
1)how to specify schema file and actual filepath?.
2)If file is delimited then how to specify delimiter?
3)If parent directory contain multiple files then how to specify file that we want to connect?
4)In case of header false and header true , which config properties need to be added and how?
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 2 months
[JBoss JIRA] (TEIID-6018) jayway json processing logic returns single object values as just maps, not JSONObject
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-6018?page=com.atlassian.jira.plugi... ]
Steven Hawkins commented on TEIID-6018:
---------------------------------------
It looks an odd result as well when dealing with something like a column path of $..prop and json that looks like {"a":1, "prop":2}. Instead of just [2], I'm seeing [null,2].
> jayway json processing logic returns single object values as just maps, not JSONObject
> --------------------------------------------------------------------------------------
>
> Key: TEIID-6018
> URL: https://issues.redhat.com/browse/TEIID-6018
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 15.0.1, 14.0.2, 16.0
>
> Original Estimate: 2 hours
> Time Spent: 2 hours, 30 minutes
> Remaining Estimate: 0 minutes
>
> When a value is returned from the context read for arrays it's an instanceof JSONArray. There was an expectation that for objects it was JSONObject. Instead it's just a hashmap leading to an invalid string conversion.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 2 months
[JBoss JIRA] (TEIID-6018) jayway json processing logic returns single object values as just maps, not JSONObject
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-6018?focusedWorklogId=12451914&pag... ]
Steven Hawkins logged work on TEIID-6018:
-----------------------------------------
Author: Steven Hawkins
Created on: 13/Aug/20 7:03 PM
Start Date: 13/Aug/20 7:02 PM
Worklog Time Spent: 2 hours, 30 minutes
Work Description: Time spent on this issue and finding a workaround for Nelson
Issue Time Tracking
-------------------
Remaining Estimate: 0 minutes (was: 2 hours)
Time Spent: 2 hours, 30 minutes
Worklog Id: (was: 12451914)
> jayway json processing logic returns single object values as just maps, not JSONObject
> --------------------------------------------------------------------------------------
>
> Key: TEIID-6018
> URL: https://issues.redhat.com/browse/TEIID-6018
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 15.0.1, 14.0.2, 16.0
>
> Original Estimate: 2 hours
> Time Spent: 2 hours, 30 minutes
> Remaining Estimate: 0 minutes
>
> When a value is returned from the context read for arrays it's an instanceof JSONArray. There was an expectation that for objects it was JSONObject. Instead it's just a hashmap leading to an invalid string conversion.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 2 months
[JBoss JIRA] (TEIID-6019) Normalize and expand file api and file glob search
by Steven Hawkins (Jira)
Steven Hawkins created TEIID-6019:
-------------------------------------
Summary: Normalize and expand file api and file glob search
Key: TEIID-6019
URL: https://issues.redhat.com/browse/TEIID-6019
Project: Teiid
Issue Type: Enhancement
Components: Misc. Connectors
Reporter: Steven Hawkins
Assignee: Steven Hawkins
The file sources need to have consistent implementation of getFiles and should provide additional information such as the full file path and if it's a directory. For example currently it's possible to return directories in the file source if you specify a non-glob directory path, but that information is not exposed.
The glob paths should all support multiple, not just a single * in the file name.
At a first glance we may not implement these changes for ftp, but just throw appropriate exceptions - it currently only knows about returning a single file. It would be possible to eventually add logic for that though as it supports similar file visiting logic as Java.
So the focus will be on file, hdfs, and s3 - which [~invinci_1] is already looking into.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 2 months
[JBoss JIRA] (TEIID-6017) Not able to connect GCP bucket using S3 translator
by Nayan Bija (Jira)
[ https://issues.redhat.com/browse/TEIID-6017?page=com.atlassian.jira.plugi... ]
Nayan Bija commented on TEIID-6017:
-----------------------------------
Thanks Ramesh for quick reply.
Yes, I have already added admin user.
As you suggested, I will try to download squirrel SQL and test the connection with vdb
Thank you.
> Not able to connect GCP bucket using S3 translator
> --------------------------------------------------
>
> Key: TEIID-6017
> URL: https://issues.redhat.com/browse/TEIID-6017
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Nayan Bija
> Assignee: Steven Hawkins
> Priority: Major
>
> As suggested in the ticket TEIID-6015
> We have tried to connect the GCP bucket using the S3 translator(not amazon-s3).
> As mentioned, S3 doesn't support google authentication,
> so we have created access key and secret key of Google bucket by logging into the GCP accoudnt.
> We have followed below steps to create access key and secret key.
> _*Storage->Setting->INTEROPERABILITY-> Service account HMAC->Create a key for service account"*_
>
> We have also created below adapter file and VDB but still, we are not able to connect to GCP.
>
> <resource-adapter id="amazon-s3-custom">
> <module slot="main" id="org.jboss.teiid.resource-adapter.s3"/>
> <transaction-support>NoTransaction</transaction-support>
> <connection-definitions>
> <connection-definition class-name="org.teiid.resource.adapter.s3.S3ManagedConnectionFactory" jndi-name="java:/s3DS" enabled="true" use-java-context="true" pool-name="teiid-s3-ds">
> <config-property name="bucket">abc</config-property>
> <config-property name="endpoint">[https://storage.googleapis.com|https://storage.googleapis.com/]</config-property>
> <config-property name="secretKey">xxx</config-property>
> <config-property name="accessKey">xxx</config-property>
> </connection-definition>
> </connection-definitions>
> </resource-adapter>
>
>
>
> VDB
> ----
>
> <?xml version="1.0"?>
> <vdb name="gcp" version="1">
> <connection-type>BY_VERSION</connection-type>
> <model name="s3">
> <source name="web-connector" translator-name="amazon-s3-custom" connection-jndi-name="java:/s3DS"/>
> </model>
> <translator name="amazon-s3-custom" type="amazon-s3">
> <property name="accesskey" value="xxx"/>
> <property name="secretkey" value="xxx"/>
> <property name="bucket" value="abc"/>
> </translator>
> </vdb
>
>
> as we tried to connect we are getting below error.
>
> *_"_*_org.teiid.jdbc.TeiidSQLException: TEIID20018 Unable to find a component used authenticate on to Teiid_*_"_*
>
> In the above configuration, where should we mention the File name?
> As I have read the properties in the documentation, I could see bucket property, where we can add a bucket name. but where should we add the file name which is present in the bucket?
>
> Could you please help us with it?
>
>
>
>
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 2 months
[JBoss JIRA] (TEIID-6017) Not able to connect GCP bucket using S3 translator
by Ramesh Reddy (Jira)
[ https://issues.redhat.com/browse/TEIID-6017?page=com.atlassian.jira.plugi... ]
Ramesh Reddy commented on TEIID-6017:
-------------------------------------
Did you create a user in Teiid? there is script called "bin/add-user.sh" run it first and add "application realm" user. Then using that user, using a JDBC client tool like `[Squirrel|[http://squirrel-sql.sourceforge.net/]]` connect to the VDB "dummy" and make sure that works. The JDBC url will be something like "jdbc:teiid:dummy@mm://loacalhost:31000"
Once this works, then we will look into your GCP bucket based VDB
> Not able to connect GCP bucket using S3 translator
> --------------------------------------------------
>
> Key: TEIID-6017
> URL: https://issues.redhat.com/browse/TEIID-6017
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Nayan Bija
> Assignee: Steven Hawkins
> Priority: Major
>
> As suggested in the ticket TEIID-6015
> We have tried to connect the GCP bucket using the S3 translator(not amazon-s3).
> As mentioned, S3 doesn't support google authentication,
> so we have created access key and secret key of Google bucket by logging into the GCP accoudnt.
> We have followed below steps to create access key and secret key.
> _*Storage->Setting->INTEROPERABILITY-> Service account HMAC->Create a key for service account"*_
>
> We have also created below adapter file and VDB but still, we are not able to connect to GCP.
>
> <resource-adapter id="amazon-s3-custom">
> <module slot="main" id="org.jboss.teiid.resource-adapter.s3"/>
> <transaction-support>NoTransaction</transaction-support>
> <connection-definitions>
> <connection-definition class-name="org.teiid.resource.adapter.s3.S3ManagedConnectionFactory" jndi-name="java:/s3DS" enabled="true" use-java-context="true" pool-name="teiid-s3-ds">
> <config-property name="bucket">abc</config-property>
> <config-property name="endpoint">[https://storage.googleapis.com|https://storage.googleapis.com/]</config-property>
> <config-property name="secretKey">xxx</config-property>
> <config-property name="accessKey">xxx</config-property>
> </connection-definition>
> </connection-definitions>
> </resource-adapter>
>
>
>
> VDB
> ----
>
> <?xml version="1.0"?>
> <vdb name="gcp" version="1">
> <connection-type>BY_VERSION</connection-type>
> <model name="s3">
> <source name="web-connector" translator-name="amazon-s3-custom" connection-jndi-name="java:/s3DS"/>
> </model>
> <translator name="amazon-s3-custom" type="amazon-s3">
> <property name="accesskey" value="xxx"/>
> <property name="secretkey" value="xxx"/>
> <property name="bucket" value="abc"/>
> </translator>
> </vdb
>
>
> as we tried to connect we are getting below error.
>
> *_"_*_org.teiid.jdbc.TeiidSQLException: TEIID20018 Unable to find a component used authenticate on to Teiid_*_"_*
>
> In the above configuration, where should we mention the File name?
> As I have read the properties in the documentation, I could see bucket property, where we can add a bucket name. but where should we add the file name which is present in the bucket?
>
> Could you please help us with it?
>
>
>
>
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
5 years, 2 months