[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 year
[teiid-issues] [JBoss JIRA] (TEIID-6025) Add support for escape syntax
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-6025?page=com.atlassian.jira.plugi... ]
Steven Hawkins commented on TEIID-6025:
---------------------------------------
The resolution here is to support ** as an escape for a literal * - as it's unlikely that we'll support additional metacharacters and this won't introduce any breaking changes. The other change is to stop checking for a literal match before checking for the use of a wildcard - this will be noted in the docs / release notes. This simplifies the logic and makes things consistent. We'll also deprecate using any windows style path separator - which wasn't explicitly called out before.
> Add support for escape syntax
> -----------------------------
>
> Key: TEIID-6025
> URL: https://issues.redhat.com/browse/TEIID-6025
> Project: Teiid
> Issue Type: Sub-task
> Components: Misc. Connectors
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
>
> The current path syntax handles * by trying both a literal match and a wildcard match. We should instead be consistent and require the usage of escapes to prevent unwanted matches.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 2 months
[teiid-issues] [JBoss JIRA] (TEIID-6027) Combine overlapping comparisons
by Steven Hawkins (Jira)
Steven Hawkins created TEIID-6027:
-------------------------------------
Summary: Combine overlapping comparisons
Key: TEIID-6027
URL: https://issues.redhat.com/browse/TEIID-6027
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
The engine will combine comparisons involving equality predicates, but hasn't been expanded to any comparisons that overlap >= and > for example.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 2 months
[teiid-issues] [JBoss JIRA] (TEIID-6023) How to connect Azure Blob storage using Teiid
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-6023?page=com.atlassian.jira.plugi... ]
Steven Hawkins resolved TEIID-6023.
-----------------------------------
Resolution: Explained
You would use either s3 or rest access.
> How to connect Azure Blob storage using Teiid
> ---------------------------------------------
>
> Key: TEIID-6023
> URL: https://issues.redhat.com/browse/TEIID-6023
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Nayan Bija
> Assignee: Steven Hawkins
> Priority: Major
>
> We need to read flat files or CSV file tabular data using Teiid.
> Requirements
> ==========
> # There will be a CSV file in the Azure blob storage.
> 2. We need to read the data of the CSV file using Simple SQL queries. just like we read the data by using a JDBC connection.
>
> Could you please suggest any solution on how to connect to Azure blob storage using Teiid.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 2 months
[teiid-issues] [JBoss JIRA] (TEIID-4594) Add ability to read Parquet Files
by Aditya Manglam Sharma (Jira)
[ https://issues.redhat.com/browse/TEIID-4594?page=com.atlassian.jira.plugi... ]
Aditya Manglam Sharma commented on TEIID-4594:
----------------------------------------------
The type mapping and limitations for the initial implementation are as follows:
# Only directory-based partitioning is supported.
# Only an “equals” comparison supported for the partitioned column.
# Only String, BigInteger, Long, Integer, Boolean are supported for comparison of partitioned column values
# Only String, BinaryType, Long, Integer, Boolean, Double, and Float are supported for comparison of non-partitioned columns.
# No whitespace is allowed in the names of partitioned columns while describing them. We assume that a partitioned column is represented in the name of a file in the manner “PartitionedColumnName=Value”.
# Supported sources are File, HDFS, and S3.
# The only logical type that we are supporting right now is a list of primitive types(which are INT96, INT64, INT32, BOOLEAN, FLOAT, DOUBLE, BINARY, FIXED_LEN_BYTE_ARRAY) with no nesting. The type mapping of Parquet Primitive types are as following: INT96 -> BigInteger, INT64->Long, INT32 -> Integer, Boolean -> Boolean, FLOAT -> Float, DOUBLE -> Double, Binary(of String Logical annotation) -> String, Binary(except String) -> byte array, FIXED_LEN_BYTE_ARRAY -> byte array.
# A file’s path should not have an asterisk.
> Add ability to read Parquet Files
> ---------------------------------
>
> Key: TEIID-4594
> URL: https://issues.redhat.com/browse/TEIID-4594
> Project: Teiid
> Issue Type: Feature Request
> Components: Misc. Connectors
> Affects Versions: 9.2
> Reporter: Van Halbert
> Assignee: Aditya Manglam Sharma
> Priority: Major
> Fix For: 16.0
>
>
> Integration with Parquet files on Gluster is an important requirement. RADAnalytics will be accessing data from Parquet which is a common file format for Spark.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 2 months