[JBoss JIRA] (TEIID-4740) Update statement doesn't work correctly for data sources in some cases
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4740?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4740.
-----------------------------------
Resolution: Rejected
Rejecting at this time. There is more that could be done to compensate, but this should be an infrequent case.
> Update statement doesn't work correctly for data sources in some cases
> ----------------------------------------------------------------------
>
> Key: TEIID-4740
> URL: https://issues.jboss.org/browse/TEIID-4740
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.0.3
> Environment: teiid-9.0.3 on WildFly Full 9.0.2.Final (WildFly Core 1.0.2.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
>
> Running the following query:
> {code:sql}
> UPDATE test_int.contacttest
> SET test_int.contacttest.salutation = (
> select
> test_int.updatetest.prefix
> from test_int.updatetest
> where test_int.updatetest.id = test_int.contacttest.id
> );
> {code}
> works correctly changing salutation field value according to where condition (note that contacttest and updatetest table are in the same test_int data source). But the following query:
> {code:sql}
> UPDATE test.contacttest
> SET test.contacttest.salutation = (
> select
> test_int.updatetest.prefix
> from test_int.updatetest
> where test_int.updatetest.id = test.contacttest.id
> );
> {code}
> will fail as we try to update "contacttest" table of "test" data source from another "test_int" data source using "updatetest" table in where condition. The query fails showing the following error message:
> {code}
> Error: TEIID30253 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30253 Source UPDATE or DELETE command "UPDATE test.contacttest SET salutation = (SELECT test_int.updatetest.prefix FROM test_int.updatetest WHERE test_int.updatetest.id = test.contacttest.id LIMIT 2)" contains non-pushdown constructs and no compensating action can be taken as the table lacks a unique key or the source does not support equality predicates.
> SQLState: 50000
> ErrorCode: 30253
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 10 months
[JBoss JIRA] (TEIID-4740) Update statement doesn't work correctly for data sources in some cases
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-4740?page=com.atlassian.jira.plugin... ]
dalex dalex commented on TEIID-4740:
------------------------------------
[~shawkins] thx for the good explanation, now I understand. Please, close the issue.
> Update statement doesn't work correctly for data sources in some cases
> ----------------------------------------------------------------------
>
> Key: TEIID-4740
> URL: https://issues.jboss.org/browse/TEIID-4740
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.0.3
> Environment: teiid-9.0.3 on WildFly Full 9.0.2.Final (WildFly Core 1.0.2.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
>
> Running the following query:
> {code:sql}
> UPDATE test_int.contacttest
> SET test_int.contacttest.salutation = (
> select
> test_int.updatetest.prefix
> from test_int.updatetest
> where test_int.updatetest.id = test_int.contacttest.id
> );
> {code}
> works correctly changing salutation field value according to where condition (note that contacttest and updatetest table are in the same test_int data source). But the following query:
> {code:sql}
> UPDATE test.contacttest
> SET test.contacttest.salutation = (
> select
> test_int.updatetest.prefix
> from test_int.updatetest
> where test_int.updatetest.id = test.contacttest.id
> );
> {code}
> will fail as we try to update "contacttest" table of "test" data source from another "test_int" data source using "updatetest" table in where condition. The query fails showing the following error message:
> {code}
> Error: TEIID30253 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30253 Source UPDATE or DELETE command "UPDATE test.contacttest SET salutation = (SELECT test_int.updatetest.prefix FROM test_int.updatetest WHERE test_int.updatetest.id = test.contacttest.id LIMIT 2)" contains non-pushdown constructs and no compensating action can be taken as the table lacks a unique key or the source does not support equality predicates.
> SQLState: 50000
> ErrorCode: 30253
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 10 months
[JBoss JIRA] (TEIID-4740) Update statement doesn't work correctly for data sources in some cases
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4740?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4740:
---------------------------------------
The entire update is pushed to the source for processing, when it is to the same source. A compensating action is not required.
Spanning two sources however we need to compensate for the lack of being pushed down completely. The default compensation logic uses row by row processing, but that is only applicable when there is a key. This is different case.
> Update statement doesn't work correctly for data sources in some cases
> ----------------------------------------------------------------------
>
> Key: TEIID-4740
> URL: https://issues.jboss.org/browse/TEIID-4740
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.0.3
> Environment: teiid-9.0.3 on WildFly Full 9.0.2.Final (WildFly Core 1.0.2.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
>
> Running the following query:
> {code:sql}
> UPDATE test_int.contacttest
> SET test_int.contacttest.salutation = (
> select
> test_int.updatetest.prefix
> from test_int.updatetest
> where test_int.updatetest.id = test_int.contacttest.id
> );
> {code}
> works correctly changing salutation field value according to where condition (note that contacttest and updatetest table are in the same test_int data source). But the following query:
> {code:sql}
> UPDATE test.contacttest
> SET test.contacttest.salutation = (
> select
> test_int.updatetest.prefix
> from test_int.updatetest
> where test_int.updatetest.id = test.contacttest.id
> );
> {code}
> will fail as we try to update "contacttest" table of "test" data source from another "test_int" data source using "updatetest" table in where condition. The query fails showing the following error message:
> {code}
> Error: TEIID30253 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30253 Source UPDATE or DELETE command "UPDATE test.contacttest SET salutation = (SELECT test_int.updatetest.prefix FROM test_int.updatetest WHERE test_int.updatetest.id = test.contacttest.id LIMIT 2)" contains non-pushdown constructs and no compensating action can be taken as the table lacks a unique key or the source does not support equality predicates.
> SQLState: 50000
> ErrorCode: 30253
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 10 months
[JBoss JIRA] (TEIID-4740) Update statement doesn't work correctly for data sources in some cases
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-4740?page=com.atlassian.jira.plugin... ]
dalex dalex commented on TEIID-4740:
------------------------------------
[~shawkins] Then such question: the first query works correctly (when all tables are within one data source). Can the behavior be spread when we have tables from another data sources? You say that If there is no key, then we can't do row by row updates but how does it work in the first query? I'm trying just to understand the logic.
> Update statement doesn't work correctly for data sources in some cases
> ----------------------------------------------------------------------
>
> Key: TEIID-4740
> URL: https://issues.jboss.org/browse/TEIID-4740
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.0.3
> Environment: teiid-9.0.3 on WildFly Full 9.0.2.Final (WildFly Core 1.0.2.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
>
> Running the following query:
> {code:sql}
> UPDATE test_int.contacttest
> SET test_int.contacttest.salutation = (
> select
> test_int.updatetest.prefix
> from test_int.updatetest
> where test_int.updatetest.id = test_int.contacttest.id
> );
> {code}
> works correctly changing salutation field value according to where condition (note that contacttest and updatetest table are in the same test_int data source). But the following query:
> {code:sql}
> UPDATE test.contacttest
> SET test.contacttest.salutation = (
> select
> test_int.updatetest.prefix
> from test_int.updatetest
> where test_int.updatetest.id = test.contacttest.id
> );
> {code}
> will fail as we try to update "contacttest" table of "test" data source from another "test_int" data source using "updatetest" table in where condition. The query fails showing the following error message:
> {code}
> Error: TEIID30253 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30253 Source UPDATE or DELETE command "UPDATE test.contacttest SET salutation = (SELECT test_int.updatetest.prefix FROM test_int.updatetest WHERE test_int.updatetest.id = test.contacttest.id LIMIT 2)" contains non-pushdown constructs and no compensating action can be taken as the table lacks a unique key or the source does not support equality predicates.
> SQLState: 50000
> ErrorCode: 30253
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 10 months
[JBoss JIRA] (TEIID-4737) Incorrect work of left join statement
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4737?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration updated TEIID-4737:
-------------------------------------------
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1419084, https://bugzilla.redhat.com/show_bug.cgi?id=1419092
Bugzilla Update: Perform
> Incorrect work of left join statement
> -------------------------------------
>
> Key: TEIID-4737
> URL: https://issues.jboss.org/browse/TEIID-4737
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.0.3
> Environment: teiid-9.0.3 on WildFly Full 9.0.2.Final (WildFly Core 1.0.2.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 9.0.6, 9.2, 9.1.4
>
> Attachments: test_emails_bug1.jpg, test_emails_pg.jpg
>
>
> the following query:
> {code:sql}
> SELECT a.email, b.email, lower(a.email), lower(b.email) FROM test_tables.test_emails as a left join views.test_view_emails as b on lower(a.email)=lower(b.email)
> {code}
> leads to the following results:
> !test_emails_bug1.jpg|thumbnail!
> though running the same query on PostgreSQL leads to the following results:
> !test_emails_pg.jpg|thumbnail!
> so this query mentioned above must work as on PostgreSQL putting null values for the b field table where conditions can't be performed.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 10 months
[JBoss JIRA] (TEIID-4742) Provide query prioritization feature
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4742?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-4742:
-------------------------------------
IMO user role seems most logical one. Also, we should limit to only queries that are being submitted rather than in-flight to reduce the complexity of the feature.
> Provide query prioritization feature
> ------------------------------------
>
> Key: TEIID-4742
> URL: https://issues.jboss.org/browse/TEIID-4742
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 9.3
>
>
> as the system’s utilization surges, query prioritization, queuing and resource scaling will dynamically manage the added load without degrading or crashing the system or impacting essential / critical mission operations
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 10 months
[JBoss JIRA] (TEIID-4742) Provide query prioritization feature
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4742?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4742:
---------------------------------------
As worded this is more of a vm scaling issue.
Otherwise the mechanisms we have in place ensure graceful handling of a load serge.
>From the title, what we don't have is a notion of user determined priority queries. Is this something that we expect users to set in flight? Is this something that remains an internal concept based upon new factors besides our existing logic? If this is user configurable, what are the parameters - use role, particular sources, etc.?
> Provide query prioritization feature
> ------------------------------------
>
> Key: TEIID-4742
> URL: https://issues.jboss.org/browse/TEIID-4742
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 9.3
>
>
> as the system’s utilization surges, query prioritization, queuing and resource scaling will dynamically manage the added load without degrading or crashing the system or impacting essential / critical mission operations
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 10 months
[JBoss JIRA] (TEIID-4743) Dependent View with External Materialization load problems
by Pedro Inácio (JIRA)
Pedro Inácio created TEIID-4743:
-----------------------------------
Summary: Dependent View with External Materialization load problems
Key: TEIID-4743
URL: https://issues.jboss.org/browse/TEIID-4743
Project: Teiid
Issue Type: Bug
Components: Common
Environment: # MySql 5.6.35
# WildFly 10
# Teiid Server 9.1.2
Reporter: Pedro Inácio
Assignee: Steven Hawkins
Fix For: 9.1.2
Attachments: ddl.sql, externalMaterializationOrderProblem-vdb.xml, numberingPlan-vdb.xml
Having defined two views with external materialization, and second view (my_view) uses the first view (numbering_plan).
{panel:title=Second view (my_view) definition}
CREATE VIEW my_view(
...
)
OPTIONS(
MATERIALIZED 'TRUE',
MATERIALIZED_TABLE 'MyMaterialized.my_view_cache',
....
)
AS
SELECT cns,
country_code
FROM NumberingPlan2.numbering_plan;
{panel}
When _my_view_ is loaded for the first time the following error appears:
{panel:title=First Warning}
14:41:13,693 WARN [org.teiid.MATVIEWS] (Worker1_QueryProcessorQueue27) 7qmYa3eWflIY org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate (SELECT mvstatus('NumberingPlan2', 'numbering_plan', Valid, LoadState, 'THROW_EXCEPTION') FROM (SELECT 1) AS x LEFT OUTER JOIN NumberingPlanMaterialized2.status ON SchemaName = 'NumberingPlan2' AND Name = 'numbering_plan' LIMIT 2): TEIID30328 Unable to evaluate mvstatus('NumberingPlan2', 'numbering_plan', Valid, LoadState, 'THROW_EXCEPTION'): TEIID30384 Error while evaluating function mvstatus
{panel}
If _my_view _is queried in the meantime something like this appears in logs (and in the client who tried to perform the query against _my_view_):
{panel:title=My title}
14:49:04,493 WARN [org.teiid.PROCESSOR] (Worker7_QueryProcessorQueue10979) 57KCZOf/UjCV TEIID30020 Processing exception for request 57KCZOf/UjCV.0 'Group does not exist: my_view'. Originally QueryResolverException ResolverUtil.java:880. Enable more detailed logging to see the entire stacktrace.
{panel}
We have to wait until _my_view_ *MATVIEW_TTL* 'expires' in order for the view to be loaded and start to have results being returned. Of course we can call *loadMatView *explicitly, but the system could be wise enough to know that has one view that is dependent of another and that the materialization must occur first, so it should have waited in the background for the _numbering_plan _materialization to occur and then trigger the materialization of _my_view_ afterwards (or if the materialization is not available then at least consult the 'original' data instead).
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 10 months
[JBoss JIRA] (TEIID-4743) Dependent View with External Materialization load problems
by Pedro Inácio (JIRA)
[ https://issues.jboss.org/browse/TEIID-4743?page=com.atlassian.jira.plugin... ]
Pedro Inácio updated TEIID-4743:
--------------------------------
Description:
Having defined two views with external materialization, and second view (my_view) uses the first view (numbering_plan).
{panel:title=Second view (my_view) definition}
CREATE VIEW my_view(
...
)
OPTIONS(
MATERIALIZED 'TRUE',
MATERIALIZED_TABLE 'MyMaterialized.my_view_cache',
....
)
AS
SELECT cns,
country_code
FROM NumberingPlan2.numbering_plan;
{panel}
When _my_view_ is loaded for the first time the following error appears:
{panel:title=First Warning}
14:41:13,693 WARN [org.teiid.MATVIEWS] (Worker1_QueryProcessorQueue27) 7qmYa3eWflIY org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate (SELECT mvstatus('NumberingPlan2', 'numbering_plan', Valid, LoadState, 'THROW_EXCEPTION') FROM (SELECT 1) AS x LEFT OUTER JOIN NumberingPlanMaterialized2.status ON SchemaName = 'NumberingPlan2' AND Name = 'numbering_plan' LIMIT 2): TEIID30328 Unable to evaluate mvstatus('NumberingPlan2', 'numbering_plan', Valid, LoadState, 'THROW_EXCEPTION'): TEIID30384 Error while evaluating function mvstatus
{panel}
If _my_view _is queried in the meantime something like this appears in logs (and in the client who tried to perform the query against _my_view_):
{panel:title=Invoking my_view error}
14:49:04,493 WARN [org.teiid.PROCESSOR] (Worker7_QueryProcessorQueue10979) 57KCZOf/UjCV TEIID30020 Processing exception for request 57KCZOf/UjCV.0 'Group does not exist: my_view'. Originally QueryResolverException ResolverUtil.java:880. Enable more detailed logging to see the entire stacktrace.
{panel}
We have to wait until _my_view_ *MATVIEW_TTL* 'expires' in order for the view to be loaded and start to have results being returned. Of course we can call *loadMatView *explicitly, but the system could be wise enough to know that has one view that is dependent of another and that the materialization must occur first, so it should have waited in the background for the _numbering_plan _materialization to occur and then trigger the materialization of _my_view_ afterwards (or if the materialization is not available then at least consult the 'original' data instead).
was:
Having defined two views with external materialization, and second view (my_view) uses the first view (numbering_plan).
{panel:title=Second view (my_view) definition}
CREATE VIEW my_view(
...
)
OPTIONS(
MATERIALIZED 'TRUE',
MATERIALIZED_TABLE 'MyMaterialized.my_view_cache',
....
)
AS
SELECT cns,
country_code
FROM NumberingPlan2.numbering_plan;
{panel}
When _my_view_ is loaded for the first time the following error appears:
{panel:title=First Warning}
14:41:13,693 WARN [org.teiid.MATVIEWS] (Worker1_QueryProcessorQueue27) 7qmYa3eWflIY org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate (SELECT mvstatus('NumberingPlan2', 'numbering_plan', Valid, LoadState, 'THROW_EXCEPTION') FROM (SELECT 1) AS x LEFT OUTER JOIN NumberingPlanMaterialized2.status ON SchemaName = 'NumberingPlan2' AND Name = 'numbering_plan' LIMIT 2): TEIID30328 Unable to evaluate mvstatus('NumberingPlan2', 'numbering_plan', Valid, LoadState, 'THROW_EXCEPTION'): TEIID30384 Error while evaluating function mvstatus
{panel}
If _my_view _is queried in the meantime something like this appears in logs (and in the client who tried to perform the query against _my_view_):
{panel:title=My title}
14:49:04,493 WARN [org.teiid.PROCESSOR] (Worker7_QueryProcessorQueue10979) 57KCZOf/UjCV TEIID30020 Processing exception for request 57KCZOf/UjCV.0 'Group does not exist: my_view'. Originally QueryResolverException ResolverUtil.java:880. Enable more detailed logging to see the entire stacktrace.
{panel}
We have to wait until _my_view_ *MATVIEW_TTL* 'expires' in order for the view to be loaded and start to have results being returned. Of course we can call *loadMatView *explicitly, but the system could be wise enough to know that has one view that is dependent of another and that the materialization must occur first, so it should have waited in the background for the _numbering_plan _materialization to occur and then trigger the materialization of _my_view_ afterwards (or if the materialization is not available then at least consult the 'original' data instead).
> Dependent View with External Materialization load problems
> ----------------------------------------------------------
>
> Key: TEIID-4743
> URL: https://issues.jboss.org/browse/TEIID-4743
> Project: Teiid
> Issue Type: Bug
> Components: Common
> Environment: # MySql 5.6.35
> # WildFly 10
> # Teiid Server 9.1.2
> Reporter: Pedro Inácio
> Assignee: Steven Hawkins
> Fix For: 9.1.2
>
> Attachments: ddl.sql, externalMaterializationOrderProblem-vdb.xml, numberingPlan-vdb.xml
>
>
> Having defined two views with external materialization, and second view (my_view) uses the first view (numbering_plan).
> {panel:title=Second view (my_view) definition}
> CREATE VIEW my_view(
> ...
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> MATERIALIZED_TABLE 'MyMaterialized.my_view_cache',
> ....
> )
> AS
> SELECT cns,
> country_code
> FROM NumberingPlan2.numbering_plan;
> {panel}
> When _my_view_ is loaded for the first time the following error appears:
> {panel:title=First Warning}
> 14:41:13,693 WARN [org.teiid.MATVIEWS] (Worker1_QueryProcessorQueue27) 7qmYa3eWflIY org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate (SELECT mvstatus('NumberingPlan2', 'numbering_plan', Valid, LoadState, 'THROW_EXCEPTION') FROM (SELECT 1) AS x LEFT OUTER JOIN NumberingPlanMaterialized2.status ON SchemaName = 'NumberingPlan2' AND Name = 'numbering_plan' LIMIT 2): TEIID30328 Unable to evaluate mvstatus('NumberingPlan2', 'numbering_plan', Valid, LoadState, 'THROW_EXCEPTION'): TEIID30384 Error while evaluating function mvstatus
> {panel}
> If _my_view _is queried in the meantime something like this appears in logs (and in the client who tried to perform the query against _my_view_):
> {panel:title=Invoking my_view error}
> 14:49:04,493 WARN [org.teiid.PROCESSOR] (Worker7_QueryProcessorQueue10979) 57KCZOf/UjCV TEIID30020 Processing exception for request 57KCZOf/UjCV.0 'Group does not exist: my_view'. Originally QueryResolverException ResolverUtil.java:880. Enable more detailed logging to see the entire stacktrace.
> {panel}
> We have to wait until _my_view_ *MATVIEW_TTL* 'expires' in order for the view to be loaded and start to have results being returned. Of course we can call *loadMatView *explicitly, but the system could be wise enough to know that has one view that is dependent of another and that the materialization must occur first, so it should have waited in the background for the _numbering_plan _materialization to occur and then trigger the materialization of _my_view_ afterwards (or if the materialization is not available then at least consult the 'original' data instead).
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 10 months