[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 day, 5 hours
[JBoss JIRA] (TEIID-4854) Google translator fails to update timestamp value reformatted after update
by Lucie Fabrikova (JIRA)
[ https://issues.jboss.org/browse/TEIID-4854?page=com.atlassian.jira.plugin... ]
Lucie Fabrikova commented on TEIID-4854:
----------------------------------------
Another observation: the reformatting seems to depend on the value of mm/dd:
# for values <1,12> like 1994-01-17, the date is reformatted to 01/17/1994
# for other values like 1994-01-07, the update switches day and month producing: 1994-07-01
> Google translator fails to update timestamp value reformatted after update
> --------------------------------------------------------------------------
>
> Key: TEIID-4854
> URL: https://issues.jboss.org/browse/TEIID-4854
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3, 8.12.10.6_3
> Reporter: Lucie Fabrikova
> Assignee: Steven Hawkins
> Fix For: 9.3
>
> Attachments: gs-reformatted-timestamp.log
>
>
> Cell of type timestamp is reformatted after an update. Data inserted from teiid are in format "yyyy-mm-dd hh:mm:ss[.fffffffff]", which is reformatted to "dd/mm/yyyy hh:mm:ss[.fffffffff]". Subsequent update of such row throws exception "Could not convert source column Source.smalla.timestampvalue to the expected runtime type timestamp".
> Google spreadsheet locale was UK, timezone was set to Berlin.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 5 months
[JBoss JIRA] (TEIID-4854) Google translator fails to update timestamp value reformatted after update
by Lucie Fabrikova (JIRA)
[ https://issues.jboss.org/browse/TEIID-4854?page=com.atlassian.jira.plugin... ]
Lucie Fabrikova edited comment on TEIID-4854 at 4/28/17 11:34 PM:
------------------------------------------------------------------
Another observation: the reformatting seems to depend on the value of mm/dd:
# for values of day/month within interval <1,12> like 1994-01-17, the date is reformatted to 01/17/1994
# for other values like 1994-01-07, the update switches day and month producing: 1994-07-01
was (Author: lfabriko):
Another observation: the reformatting seems to depend on the value of mm/dd:
# for values <1,12> like 1994-01-17, the date is reformatted to 01/17/1994
# for other values like 1994-01-07, the update switches day and month producing: 1994-07-01
> Google translator fails to update timestamp value reformatted after update
> --------------------------------------------------------------------------
>
> Key: TEIID-4854
> URL: https://issues.jboss.org/browse/TEIID-4854
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3, 8.12.10.6_3
> Reporter: Lucie Fabrikova
> Assignee: Steven Hawkins
> Fix For: 9.3
>
> Attachments: gs-reformatted-timestamp.log
>
>
> Cell of type timestamp is reformatted after an update. Data inserted from teiid are in format "yyyy-mm-dd hh:mm:ss[.fffffffff]", which is reformatted to "dd/mm/yyyy hh:mm:ss[.fffffffff]". Subsequent update of such row throws exception "Could not convert source column Source.smalla.timestampvalue to the expected runtime type timestamp".
> Google spreadsheet locale was UK, timezone was set to Berlin.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 5 months
[JBoss JIRA] (TEIID-4880) Join Elimination in Star Schema
by Madou Coulibaly (JIRA)
[ https://issues.jboss.org/browse/TEIID-4880?page=com.atlassian.jira.plugin... ]
Madou Coulibaly updated TEIID-4880:
-----------------------------------
Description:
Suppose we have the following star schema :
* Fact_Sales (Fact table)
* Dim_Date, Dim_Store and Dim_Product (Dimension tables)
Each table has a PK (id field)
Fact_Sales has a foreign key for each dimension (date_id, store_id, product_id) in order to link them.
Then, suppose we create an aggregate view of the fact table and its dimensions:
SELECT
*
FROM Fact_Sales F
INNER JOIN Dim_Date D ON (F.Date_Id = D.Id)
INNER JOIN Dim_Store S ON (F.Store_Id = S.Id)
INNER JOIN Dim_Product P ON (F.Product_Id = P.Id)
Suppose now we only project fields from the fact table (Fact_Sales) and just one of its dimension (Dim_Product) in a query:
SELECT
Fact_Sales.<fields>, Dim_Product.<fields>
FROM aggregate_view
During the Query Optimization, Teiid should be able to use the PK_FK constraints which link the fact table with its dimension in order to deduct that no fields from the other dimensions (Dim_Date, Dim_Store) are projected so the joins with these tables can be removed for the Query Plan.
was:
Suppose we have the following star schema :
* Fact_Sales (Fact table)
* Dim_Date, Dim_Store and Dim_Product (Dimension tables)
Each table has a PK (id field)
Fact_Sales has a foreign key for each dimension (date_id, store_id, product_id) in order to link them.
Then, suppose we create an aggregate view of the fact table and its dimensions:
_SELECT
*
FROM Fact_Sales F
INNER JOIN Dim_Date D ON (F.Date_Id = D.Id)
INNER JOIN Dim_Store S ON (F.Store_Id = S.Id)
INNER JOIN Dim_Product P ON (F.Product_Id = P.Id)_
Suppose now we only project fields from the fact table (Fact_Sales) and just one of its dimension (Dim_Product) in a query:
_SELECT
Fact_Sales.*, Dim_Product.*
FROM aggregate_view_
During the Query Optimization, Teiid should be able to use the PK_FK constraints which link the fact table with its dimension in order to deduct that no fields from the other dimensions (Dim_Date, Dim_Store) are projected so the joins with these tables can be removed for the Query Plan.
> Join Elimination in Star Schema
> -------------------------------
>
> Key: TEIID-4880
> URL: https://issues.jboss.org/browse/TEIID-4880
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Affects Versions: 8.7.11.6_2
> Reporter: Madou Coulibaly
> Assignee: Steven Hawkins
>
> Suppose we have the following star schema :
> * Fact_Sales (Fact table)
> * Dim_Date, Dim_Store and Dim_Product (Dimension tables)
> Each table has a PK (id field)
> Fact_Sales has a foreign key for each dimension (date_id, store_id, product_id) in order to link them.
> Then, suppose we create an aggregate view of the fact table and its dimensions:
> SELECT
> *
> FROM Fact_Sales F
> INNER JOIN Dim_Date D ON (F.Date_Id = D.Id)
> INNER JOIN Dim_Store S ON (F.Store_Id = S.Id)
> INNER JOIN Dim_Product P ON (F.Product_Id = P.Id)
> Suppose now we only project fields from the fact table (Fact_Sales) and just one of its dimension (Dim_Product) in a query:
> SELECT
> Fact_Sales.<fields>, Dim_Product.<fields>
> FROM aggregate_view
> During the Query Optimization, Teiid should be able to use the PK_FK constraints which link the fact table with its dimension in order to deduct that no fields from the other dimensions (Dim_Date, Dim_Store) are projected so the joins with these tables can be removed for the Query Plan.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 5 months
[JBoss JIRA] (TEIID-4880) Join Elimination in Star Schema
by Madou Coulibaly (JIRA)
[ https://issues.jboss.org/browse/TEIID-4880?page=com.atlassian.jira.plugin... ]
Madou Coulibaly updated TEIID-4880:
-----------------------------------
Description:
Suppose we have the following star schema :
* Fact_Sales (Fact table)
* Dim_Date, Dim_Store and Dim_Product (Dimension tables)
Each table has a PK (id field)
Fact_Sales has a foreign key for each dimension (date_id, store_id, product_id) in order to link them.
Then, suppose we create an aggregate view of the fact table and its dimensions:
_SELECT
*
FROM Fact_Sales F
INNER JOIN Dim_Date D ON (F.Date_Id = D.Id)
INNER JOIN Dim_Store S ON (F.Store_Id = S.Id)
INNER JOIN Dim_Product P ON (F.Product_Id = P.Id)_
Suppose now we only project fields from the fact table (Fact_Sales) and just one of its dimension (Dim_Product) in a query:
_SELECT
Fact_Sales.<fields>, Dim_Product.<fields>
FROM aggregate_view_
During the Query Optimization, Teiid should be able to use the PK_FK constraints which link the fact table with its dimension in order to deduct that no fields from the other dimensions (Dim_Date, Dim_Store) are projected so the joins with these tables can be removed for the Query Plan.
was:
Suppose we have the following star schema :
* Fact_Sales (Fact table)
* Dim_Date, Dim_Store and Dim_Product (Dimension tables)
Each table has a PK (id field)
Fact_Sales has a foreign key for each dimension (date_id, store_id, product_id) in order to link them.
Then, suppose we create an aggregate view of the fact table and its dimensions:
SELECT
*
FROM Fact_Sales F
INNER JOIN Dim_Date D ON (F.Date_Id = D.Id)
INNER JOIN Dim_Store S ON (F.Store_Id = S.Id)
INNER JOIN Dim_Product P ON (F.Product_Id = P.Id)
Suppose now we only project fields from the fact table (Fact_Sales) and just one of its dimension (Dim_Product) in a query:
SELECT
Fact_Sales.<fields>, Dim_Product.<fields>
FROM aggregate_view
During the Query Optimization, Teiid should be able to use the PK_FK constraints which link the fact table with its dimension in order to deduct that no fields from the other dimensions (Dim_Date, Dim_Store) are projected so the joins with these tables can be removed for the Query Plan.
> Join Elimination in Star Schema
> -------------------------------
>
> Key: TEIID-4880
> URL: https://issues.jboss.org/browse/TEIID-4880
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Affects Versions: 8.7.11.6_2
> Reporter: Madou Coulibaly
> Assignee: Steven Hawkins
>
> Suppose we have the following star schema :
> * Fact_Sales (Fact table)
> * Dim_Date, Dim_Store and Dim_Product (Dimension tables)
> Each table has a PK (id field)
> Fact_Sales has a foreign key for each dimension (date_id, store_id, product_id) in order to link them.
> Then, suppose we create an aggregate view of the fact table and its dimensions:
> _SELECT
> *
> FROM Fact_Sales F
> INNER JOIN Dim_Date D ON (F.Date_Id = D.Id)
> INNER JOIN Dim_Store S ON (F.Store_Id = S.Id)
> INNER JOIN Dim_Product P ON (F.Product_Id = P.Id)_
> Suppose now we only project fields from the fact table (Fact_Sales) and just one of its dimension (Dim_Product) in a query:
> _SELECT
> Fact_Sales.<fields>, Dim_Product.<fields>
> FROM aggregate_view_
> During the Query Optimization, Teiid should be able to use the PK_FK constraints which link the fact table with its dimension in order to deduct that no fields from the other dimensions (Dim_Date, Dim_Store) are projected so the joins with these tables can be removed for the Query Plan.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 5 months
[JBoss JIRA] (TEIID-4880) Join Elimination in Star Schema
by Madou Coulibaly (JIRA)
[ https://issues.jboss.org/browse/TEIID-4880?page=com.atlassian.jira.plugin... ]
Madou Coulibaly updated TEIID-4880:
-----------------------------------
Description:
Suppose we have the following star schema :
* Fact_Sales (Fact table)
* Dim_Date, Dim_Store and Dim_Product (Dimension tables)
Each table has a PK (id field)
Fact_Sales has a foreign key for each dimension (date_id, store_id, product_id) in order to link them.
Then, suppose we create an aggregate view of the fact table and its dimensions:
SELECT
\*
FROM Fact_Sales F
INNER JOIN Dim_Date D ON (F.Date_Id = D.Id)
INNER JOIN Dim_Store S ON (F.Store_Id = S.Id)
INNER JOIN Dim_Product P ON (F.Product_Id = P.Id)
Suppose now we only project fields from the fact table (Fact_Sales) and just one of its dimension (Dim_Product) in a query:
SELECT
Fact_Sales.<fields>, Dim_Product.<fields>
FROM aggregate_view
During the Query Optimization, Teiid should be able to use the PK_FK constraints which link the fact table with its dimension in order to deduct that no fields from the other dimensions (Dim_Date, Dim_Store) are projected so the joins with these tables can be removed for the Query Plan.
was:
Suppose we have the following star schema :
* Fact_Sales (Fact table)
* Dim_Date, Dim_Store and Dim_Product (Dimension tables)
Each table has a PK (id field)
Fact_Sales has a foreign key for each dimension (date_id, store_id, product_id) in order to link them.
Then, suppose we create an aggregate view of the fact table and its dimensions:
_SELECT
*
FROM Fact_Sales F
INNER JOIN Dim_Date D ON (F.Date_Id = D.Id)
INNER JOIN Dim_Store S ON (F.Store_Id = S.Id)
INNER JOIN Dim_Product P ON (F.Product_Id = P.Id)_
Suppose now we only project fields from the fact table (Fact_Sales) and just one of its dimension (Dim_Product) in a query:
_SELECT
Fact_Sales.<fields>, Dim_Product.<fields>
FROM aggregate_view_
During the Query Optimization, Teiid should be able to use the PK_FK constraints which link the fact table with its dimension in order to deduct that no fields from the other dimensions (Dim_Date, Dim_Store) are projected so the joins with these tables can be removed for the Query Plan.
> Join Elimination in Star Schema
> -------------------------------
>
> Key: TEIID-4880
> URL: https://issues.jboss.org/browse/TEIID-4880
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Affects Versions: 8.7.11.6_2
> Reporter: Madou Coulibaly
> Assignee: Steven Hawkins
>
> Suppose we have the following star schema :
> * Fact_Sales (Fact table)
> * Dim_Date, Dim_Store and Dim_Product (Dimension tables)
> Each table has a PK (id field)
> Fact_Sales has a foreign key for each dimension (date_id, store_id, product_id) in order to link them.
> Then, suppose we create an aggregate view of the fact table and its dimensions:
> SELECT
> \*
> FROM Fact_Sales F
> INNER JOIN Dim_Date D ON (F.Date_Id = D.Id)
> INNER JOIN Dim_Store S ON (F.Store_Id = S.Id)
> INNER JOIN Dim_Product P ON (F.Product_Id = P.Id)
> Suppose now we only project fields from the fact table (Fact_Sales) and just one of its dimension (Dim_Product) in a query:
> SELECT
> Fact_Sales.<fields>, Dim_Product.<fields>
> FROM aggregate_view
> During the Query Optimization, Teiid should be able to use the PK_FK constraints which link the fact table with its dimension in order to deduct that no fields from the other dimensions (Dim_Date, Dim_Store) are projected so the joins with these tables can be removed for the Query Plan.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 5 months
[JBoss JIRA] (TEIID-4880) Join Elimination in Star Schema
by Madou Coulibaly (JIRA)
Madou Coulibaly created TEIID-4880:
--------------------------------------
Summary: Join Elimination in Star Schema
Key: TEIID-4880
URL: https://issues.jboss.org/browse/TEIID-4880
Project: Teiid
Issue Type: Feature Request
Components: Query Engine
Affects Versions: 8.7.11.6_2
Reporter: Madou Coulibaly
Assignee: Steven Hawkins
Suppose we have the following star schema :
* Fact_Sales (Fact table)
* Dim_Date, Dim_Store and Dim_Product (Dimension tables)
Each table has a PK (id field)
Fact_Sales has a foreign key for each dimension (date_id, store_id, product_id) in order to link them.
Then, suppose we create an aggregate view of the fact table and its dimensions:
_SELECT
*
FROM Fact_Sales F
INNER JOIN Dim_Date D ON (F.Date_Id = D.Id)
INNER JOIN Dim_Store S ON (F.Store_Id = S.Id)
INNER JOIN Dim_Product P ON (F.Product_Id = P.Id)_
Suppose now we only project fields from the fact table (Fact_Sales) and just one of its dimension (Dim_Product) in a query:
_SELECT
Fact_Sales.*, Dim_Product.*
FROM aggregate_view_
During the Query Optimization, Teiid should be able to use the PK_FK constraints which link the fact table with its dimension in order to deduct that no fields from the other dimensions (Dim_Date, Dim_Store) are projected so the joins with these tables can be removed for the Query Plan.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 5 months
[JBoss JIRA] (TEIID-4851) Random errors about PostGIS without using it
by Madou Coulibaly (JIRA)
[ https://issues.jboss.org/browse/TEIID-4851?page=com.atlassian.jira.plugin... ]
Madou Coulibaly commented on TEIID-4851:
----------------------------------------
I will provide you more information ASAP
> Random errors about PostGIS without using it
> --------------------------------------------
>
> Key: TEIID-4851
> URL: https://issues.jboss.org/browse/TEIID-4851
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.8.6_3
> Environment: Squirrel v3.7
> Teiid JDBC 8.13.1
> JDV 6.3.4
> PostgreSQL 9.6
> Reporter: Madou Coulibaly
> Assignee: Steven Hawkins
>
> When I issue queries using SQuirrel, I get the following error at random whereas I do not use any Geo functions:
> Originally TeiidProcessingException 'ERROR: function postgis_full_version() does not exist
> Hint: No function matches the given name and argument types. You might need to add explicit type casts.
> Position: 8' QueryExecutorImpl.java:2455. Enable more detailed logging to see the entire stacktrace.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 5 months