[JBoss JIRA] (TEIID-3993) Teiid: indexOutOfBoundsException on union + count distinct query
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3993?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3993.
-----------------------------------
Fix Version/s: 9.0
8.12.5
8.13.2
Resolution: Done
The issue was we were not correcting all of the parent order bys when creating a view for the grouping in the first branch of the union, which lead to the incorrect output elements being assigned.
> Teiid: indexOutOfBoundsException on union + count distinct query
> -----------------------------------------------------------------
>
> Key: TEIID-3993
> URL: https://issues.jboss.org/browse/TEIID-3993
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.3
> Environment: Teiid 8.12.3 + posgtesDB
> Reporter: Ivan Chan
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5, 8.13.2
>
> Attachments: teiid_debug.txt, teiid_stack.txt
>
>
> I ran into IndexOfBoundsException from teiid query optimizer when running the following query:
> select "SugarCRMDataSource_public_sales_location"."country" as "SugarCRMDataSource_public_sales_location_country",
> "FoodmartDataSource_public_store"."store_country" as "FoodmartDataSource_public_store_store_country",
> '1|1' as "JS_Discriminator_COL",
> count(DISTINCT "SugarCRMDataSource_public_sales_fact"."amount") as "CountDistinct_SugarCRMDataSource_public_sales_fact_amount"
> from "SugarCRMDataSource_public"."sales_fact" "SugarCRMDataSource_public_sales_fact"
> inner join "SugarCRMDataSource_public"."sales_location" "SugarCRMDataSource_public_sales_location" on ("SugarCRMDataSource_public_sales_fact"."sales_location_id" = "SugarCRMDataSource_public_sales_location"."id")
> inner join "FoodmartDataSource_public"."store" "FoodmartDataSource_public_store" on ("SugarCRMDataSource_public_sales_location"."state" = "FoodmartDataSource_public_store"."store_state")
> group by "SugarCRMDataSource_public_sales_location"."country", "FoodmartDataSource_public_store"."store_country"
> UNION ALL
> select "SugarCRMDataSource_public_sales_location"."country" as "SugarCRMDataSource_public_sales_location_country",
> NULL as "FoodmartDataSource_public_store_store_country",
> '1|0' as "JS_Discriminator_COL",
> count(DISTINCT "SugarCRMDataSource_public_sales_fact"."amount") as "CountDistinct_SugarCRMDataSource_public_sales_fact_amount"
> from "SugarCRMDataSource_public"."sales_fact" "SugarCRMDataSource_public_sales_fact"
> inner join "SugarCRMDataSource_public"."sales_location" "SugarCRMDataSource_public_sales_location" on ("SugarCRMDataSource_public_sales_fact"."sales_location_id" = "SugarCRMDataSource_public_sales_location"."id")
> inner join "FoodmartDataSource_public"."store" "FoodmartDataSource_public_store" on ("SugarCRMDataSource_public_sales_location"."state" = "FoodmartDataSource_public_store"."store_state")
> group by "SugarCRMDataSource_public_sales_location"."country"
> UNION ALL
> select NULL as "SugarCRMDataSource_public_sales_location_country",
> "FoodmartDataSource_public_store"."store_country" as "FoodmartDataSource_public_store_store_country",
> '0|1' as "JS_Discriminator_COL",
> count(DISTINCT "SugarCRMDataSource_public_sales_fact"."amount") as "CountDistinct_SugarCRMDataSource_public_sales_fact_amount"
> from "SugarCRMDataSource_public"."sales_fact" "SugarCRMDataSource_public_sales_fact"
> inner join "SugarCRMDataSource_public"."sales_location" "SugarCRMDataSource_public_sales_location" on ("SugarCRMDataSource_public_sales_fact"."sales_location_id" = "SugarCRMDataSource_public_sales_location"."id")
> inner join "FoodmartDataSource_public"."store" "FoodmartDataSource_public_store" on ("SugarCRMDataSource_public_sales_location"."state" = "FoodmartDataSource_public_store"."store_state")
> group by "FoodmartDataSource_public_store"."store_country"
> UNION ALL
> select NULL as "SugarCRMDataSource_public_sales_location_country",
> NULL as "FoodmartDataSource_public_store_store_country",
> '0|0' as "JS_Discriminator_COL",
> count(DISTINCT "SugarCRMDataSource_public_sales_fact"."amount") as "CountDistinct_SugarCRMDataSource_public_sales_fact_amount"
> from "SugarCRMDataSource_public"."sales_fact" "SugarCRMDataSource_public_sales_fact"
> inner join "SugarCRMDataSource_public"."sales_location" "SugarCRMDataSource_public_sales_location" on ("SugarCRMDataSource_public_sales_fact"."sales_location_id" = "SugarCRMDataSource_public_sales_location"."id")
> inner join "FoodmartDataSource_public"."store" "FoodmartDataSource_public_store" on ("SugarCRMDataSource_public_sales_location"."state" = "FoodmartDataSource_public_store"."store_state")
> order by "SugarCRMDataSource_public_sales_location_country", "FoodmartDataSource_public_store_store_country"
> limit 1000
> And here is the exception:
> Caused by: java.lang.IndexOutOfBoundsException: Index: 4, Size: 4
> at java.util.ArrayList.rangeCheck(ArrayList.java:635)
> at java.util.ArrayList.get(ArrayList.java:411)
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.setColStatEstimates(NewCalculateCostUtil.java:411)
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.setCardinalityEstimate(NewCalculateCostUtil.java:313)
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.estimateSetOpCost(NewCalculateCostUtil.java:252)
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.computeNodeCost(NewCalculateCostUtil.java:204)
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:136)
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:133)
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:133)
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.computeCostForTree(NewCalculateCostUtil.java:122)
> at org.teiid.query.optimizer.relational.rules.RuleCalculateCost.execute(RuleCalculateCost.java:50)
> at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:807)
> at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:223)
> at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159)
> at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:435)
> at org.teiid.dqp.internal.process.PreparedStatementRequest.generatePlan(PreparedStatementRequest.java:119)
> at org.teiid.dqp.internal.process.Request.processRequest(Request.java:463)
> at org.teiid.dqp.internal.process.PreparedStatementRequest.processRequest(PreparedStatementRequest.java:294)
> at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:640)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:337)
> ... 22 more
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 8 months
[JBoss JIRA] (TEIID-3983) External Materialization MATVIEW_ONERROR_ACTION WAIT problem
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3983?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3983.
-----------------------------------
Resolution: Done
Changed to look at the extension metadata for the status table.
> External Materialization MATVIEW_ONERROR_ACTION WAIT problem
> ------------------------------------------------------------
>
> Key: TEIID-3983
> URL: https://issues.jboss.org/browse/TEIID-3983
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.5
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5, 8.13.2
>
>
> When using "teiid_rel:MATVIEW_ONERROR_ACTION" 'WAIT' option in materialized view definition, there's problem with blocked request.
> The blocked request is the one, that triggered the materialized view's loading (first query on the defined materialized view). After such request, one can observe, that the query execution doesn't end, but hangs.
> Meanwhile while examining the contents of 'status' table in the underlying database, the LoadNumber column's value increases regularly according to the defined ttl. Moreover the 'LOADSTATE' column changes from LOADING to LOADED in similar manner. The materialized table is populated with correct data. From this I assume, materialization is set up correctly.
> During the wait, when logging set to DEBUG, there appears regularly, apart from the logs about refreshing the mat view, this entry:
> {code:plain}
> [32m09:14:52,151 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2695) Request Thread 87XzBpSzkyk4.0 with state PROCESSING
> [32m09:14:52,151 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2695) Request Thread 87XzBpSzkyk4.0 - processor blocked
> {code}
> The log appears in 'ttl' determined intervals, each time after materialized table loading related entries.
> The thread mentioned is the one created after the original query execution was initiated.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 8 months
[JBoss JIRA] (TEIID-4001) Make error handling check for content type in OData4
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4001?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4001:
---------------------------------------
I was seeing that we are assuming JSON even for the metadata fetch, which since it doesn't specify the format or the accepts header can return a non json response. And in general if there is a higher level server error, then you are likely to get an xhtml page which the atom logic can handle, so it seems good to have the handling there.
> Make error handling check for content type in OData4
> ----------------------------------------------------
>
> Key: TEIID-4001
> URL: https://issues.jboss.org/browse/TEIID-4001
> Project: Teiid
> Issue Type: Quality Risk
> Components: Misc. Connectors
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 9.0
>
>
> Unless json is specifically requested errors are typically returned in atom.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 8 months
[JBoss JIRA] (TEIID-2476) Exclude hidden tables/columns from metadata
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2476?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-2476:
---------------------------------------
The initial commit of the code is in for 9.0 alpha2. There will probably still be refinements. There will also need to be doc updates and sub-tasks addressed.
> Exclude hidden tables/columns from metadata
> -------------------------------------------
>
> Key: TEIID-2476
> URL: https://issues.jboss.org/browse/TEIID-2476
> Project: Teiid
> Issue Type: Enhancement
> Components: JDBC Driver, Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 9.0
>
>
> We should offer or possibly default to filtering tables/columns that can not accessed out of the user metadata. This should be done on the server side - however given the reuse we have of the system tables for example for materialized pg catalog queries - it is easier to add filters to the DatabaseMetadata (and ideally pg) metadata queries.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 8 months
[JBoss JIRA] (TEIID-3983) External Materialization MATVIEW_ONERROR_ACTION WAIT problem
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3983?page=com.atlassian.jira.plugin... ]
Steven Hawkins reopened TEIID-3983:
-----------------------------------
> External Materialization MATVIEW_ONERROR_ACTION WAIT problem
> ------------------------------------------------------------
>
> Key: TEIID-3983
> URL: https://issues.jboss.org/browse/TEIID-3983
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.5
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5, 8.13.2
>
>
> When using "teiid_rel:MATVIEW_ONERROR_ACTION" 'WAIT' option in materialized view definition, there's problem with blocked request.
> The blocked request is the one, that triggered the materialized view's loading (first query on the defined materialized view). After such request, one can observe, that the query execution doesn't end, but hangs.
> Meanwhile while examining the contents of 'status' table in the underlying database, the LoadNumber column's value increases regularly according to the defined ttl. Moreover the 'LOADSTATE' column changes from LOADING to LOADED in similar manner. The materialized table is populated with correct data. From this I assume, materialization is set up correctly.
> During the wait, when logging set to DEBUG, there appears regularly, apart from the logs about refreshing the mat view, this entry:
> {code:plain}
> [32m09:14:52,151 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2695) Request Thread 87XzBpSzkyk4.0 with state PROCESSING
> [32m09:14:52,151 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2695) Request Thread 87XzBpSzkyk4.0 - processor blocked
> {code}
> The log appears in 'ttl' determined intervals, each time after materialized table loading related entries.
> The thread mentioned is the one created after the original query execution was initiated.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 8 months
[JBoss JIRA] (TEIID-3996) SAP HANA materialization: MATVIEW_ONERROR_ACTION set to WAIT problem
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3996?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3996.
-----------------------------------
Resolution: Duplicate Issue
See TEIID-3983
> SAP HANA materialization: MATVIEW_ONERROR_ACTION set to WAIT problem
> --------------------------------------------------------------------
>
> Key: TEIID-3996
> URL: https://issues.jboss.org/browse/TEIID-3996
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.x
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
>
> When using materialization wit SAP HANA datasource, a problem emerges when "teiid_rel:MATVIEW_ONERROR_ACTION" 'WAIT' is defined for the materialized view.
> The query which initiates matview's loading doesn't wait for its completion, thus returns stale data (empty resultset in case of the initial invocation).
> Materialized view definition:
> {code:sql}
> CREATE VIEW external_long_ttl (
> customer_id integer NOT NULL,
> total_amount integer
> ) OPTIONS (
> MATERIALIZED 'TRUE',
> UPDATABLE 'FALSE',
> MATERIALIZED_TABLE 'Source.JSTASTNY.dv_matviews_mat_view',
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'Source.JSTASTNY.dv_matviews_statustable',
> "teiid_rel:ON_VDB_START_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_create) SELECT id, vdb_create+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:ON_VDB_DROP_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_drop) SELECT id, vdb_drop+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_LOAD_SCRIPT" 'INSERT INTO dv_matviews_mat_view_stage(customer_id,total_amount) SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;',
> "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'exec Source.native(''truncate table dv_matviews_mat_view_stage'');MERGE INTO dv_matviews_check_table(id,before_load) SELECT id, before_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');MERGE INTO dv_matviews_check_table(id,after_load) SELECT id, after_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_ONERROR_ACTION" 'WAIT',
> "teiid_rel:MATVIEW_TTL" 20000
> ) AS SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;
> {code}
> Query performed:
> {code:sql}
> SELECT * FROM external_long_ttl;
> {code}
> Reproducer:
> {code:java}
> Statement statement = connection.createStatement();
> // invoke initial load, won't return loaded data even though it should
> ResultSet rs1 = statement.executeQuery("SELECT * FROM external_long_ttl;");
> Assert.assertFalse(rs1.next());
> // wait explicitly for the loading to finish
> Thread.sleep(7000);
> // repeat the query, to get the data after load finished
> ResultSet rs2 = statement.executeQuery("SELECT * FROM external_long_ttl;");
> Assert.assertTrue(rs2.next());
> .
> .
> .
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 8 months
[JBoss JIRA] (TEIID-3983) External Materialization MATVIEW_ONERROR_ACTION WAIT problem
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3983?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3983:
---------------------------------------
Yes, I didn't account for that being a metadata property. I'll add another commit.
> External Materialization MATVIEW_ONERROR_ACTION WAIT problem
> ------------------------------------------------------------
>
> Key: TEIID-3983
> URL: https://issues.jboss.org/browse/TEIID-3983
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.5
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5, 8.13.2
>
>
> When using "teiid_rel:MATVIEW_ONERROR_ACTION" 'WAIT' option in materialized view definition, there's problem with blocked request.
> The blocked request is the one, that triggered the materialized view's loading (first query on the defined materialized view). After such request, one can observe, that the query execution doesn't end, but hangs.
> Meanwhile while examining the contents of 'status' table in the underlying database, the LoadNumber column's value increases regularly according to the defined ttl. Moreover the 'LOADSTATE' column changes from LOADING to LOADED in similar manner. The materialized table is populated with correct data. From this I assume, materialization is set up correctly.
> During the wait, when logging set to DEBUG, there appears regularly, apart from the logs about refreshing the mat view, this entry:
> {code:plain}
> [32m09:14:52,151 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2695) Request Thread 87XzBpSzkyk4.0 with state PROCESSING
> [32m09:14:52,151 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2695) Request Thread 87XzBpSzkyk4.0 - processor blocked
> {code}
> The log appears in 'ttl' determined intervals, each time after materialized table loading related entries.
> The thread mentioned is the one created after the original query execution was initiated.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 8 months