[JBoss JIRA] (TEIID-5783) OUTER JOIN yields wrong results when used with GROUP BY, CASE and COUNT DISTINCT
by Dmitrii Pogorelov (Jira)
[ https://issues.jboss.org/browse/TEIID-5783?page=com.atlassian.jira.plugin... ]
Dmitrii Pogorelov commented on TEIID-5783:
------------------------------------------
Thx a lot [~shawkins] for the fix, I've just checked these changes locally for left join, right join and full join - everything works perfectly!
> OUTER JOIN yields wrong results when used with GROUP BY, CASE and COUNT DISTINCT
> --------------------------------------------------------------------------------
>
> Key: TEIID-5783
> URL: https://issues.jboss.org/browse/TEIID-5783
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 12.0
> Environment: teiid-12.0.0 on WildFly Full 14.0.1.Final (WildFly Core 6.0.2.Final)
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 12.3, 12.1.2, 12.2.2
>
> Attachments: test1.csv, test2.csv
>
>
> Some queries that involve an OUTER JOIN between objects from different data sources, a GROUP BY with a CASE statement and a COUNT DISTINCT yield inconsistent results on a SELECT expression which is identical to the GROUP BY expression with CASE.
> * Reproduced in combination of <File> LEFT JOIN <MySQL> and <File> LEFT JOIN <MS SQL> and the same for the other OUTER JOIN variants.
> * screenshot for the different queries are attached
> * sample data for reproduction is attached
> The same problem happens with LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 10 months
[JBoss JIRA] (TEIID-5782) When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin... ]
Christoph John edited comment on TEIID-5782 at 6/24/19 10:49 PM:
-----------------------------------------------------------------
Hello Steven
I just had a quick try. I deleted the dummy key value for an insert on a table without custom trigger and got the following error
{"error":{"code":null,"message":"OData Library: An exception without message text was thrown."}}
I also tried on the second example which I mentioned. The one with the compound primary key where I could not set the key group explicitly. There I now also got an error when trying to insert a new record
Log-dbg.js:414 2019-06-24 21:17:21.677909 POST on 'UserDefinedProducts' failed; will be repeated automatically - Error: TEIID16016 Insert into UserDefinedProducts success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values.
I am sorry, that I cannot look now deeper into it. Friends are waiting at the bar :) I will be back next week and then will assemble a compose file for you to show the situation. Maybe that way we will find the issue.
was (Author: cjohn001):
Hello Steven
I just had a quick try. I deleted the dummy key value for an insert on a table without custom trigger and got the following error
{"error":{"code":null,"message":"OData Library: An exception without message text was thrown."}}
I also tried on the second example which I should you with a custom insert trigger. The one with the compound primary key where I could not set the key group explicitely. There I now also got an error when trying to insert
Log-dbg.js:414 2019-06-24 21:17:21.677909 POST on 'UserDefinedProducts' failed; will be repeated automatically - Error: TEIID16016 Insert into UserDefinedProducts success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values.
I am sorry, that I cannot look now deeper into it. Friends are waiting at the bar :) I will be back next week and then will assemble a compose file for you to show the situation. Maybe that way we will find the issue.
> When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5782
> URL: https://issues.jboss.org/browse/TEIID-5782
> Project: Teiid
> Issue Type: Quality Risk
> Components: OData
> Affects Versions: 12.3
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
>
> Hello,
> in the past we had an issue in the odata layer, that the generated primary key of an autoincrement key was not corretly transfered back in the odata response. This issue was fixed.
> However, the current issues derives from the discussion in
> https://issues.jboss.org/browse/TEIID-5763
> where we havewritten an INSTEAD OF INSERT trigger. According to my last comment to https://issues.jboss.org/browse/TEIID-5763, there are two situations which can arrise. I have to set the 1. key group explicitely or 2. it is set implicitely.
> Now in both situations I observe the same result. When I POST an INSERT with a dummy primary key, say key=0 (as it was NOT NULL in the past, and I am still using the old sources) I retrieve key=0 in the odata response. Correct would be to get a response with the generated key instead.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 10 months
[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5763.
-----------------------------------
Resolution: Done
Marking as resolved. Other issues will be captured separately.
> GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
> -----------------------------------------------------------------------------
>
> Key: TEIID-5763
> URL: https://issues.jboss.org/browse/TEIID-5763
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 12.2
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 12.3
>
>
> The issue arrised in:
> https://developer.jboss.org/message/989700#989700
> Following sceanarios are given:
> Variant 1:
> Table Product(id), primary key = autoincrement;
> Table QuicklyAddedProduct, primary key is foreign key on Product.id
> Table Diary_SRC
> View Diary on Diary_SRC
> INSTEAD OF INSERT Trigger on Diary should :
> -create new record on Product,
> -get autoincremented Product.id of new record,
> - create new QuicklyAddedProduct with returned Product.id as primary key
> - create new Diary record with QuicklyAddedProduct
> {
> INSERT a new record on Product;
> idProduct = CONVERT(GENERATED_KEY('idProduct'),long); // fails
> }
> Variant 2:
> INSTEAD OF INSERT trigger on Diary should :
> - create new QuicklyAddedProduct
> - add Quickly added prodcut to Diary
> additionally a INSTEAD OF trigger on QuicklyAddedProduct exists which:
> - creates new Product record
> - uses returned GENERATED_KEY(Product.id) to add record on QuicklyAddedProduct with Product.id as primary key
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 10 months
[JBoss JIRA] (TEIID-5782) When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5782:
---------------------------------------
Hello Steven
I just had a quick try. I deleted the dummy key value for an insert on a table without custom trigger and got the following error
{"error":{"code":null,"message":"OData Library: An exception without message text was thrown."}}
I also tried on the second example which I should you with a custom insert trigger. The one with the compound primary key where I could not set the key group explicitely. There I now also got an error when trying to insert
Log-dbg.js:414 2019-06-24 21:17:21.677909 POST on 'UserDefinedProducts' failed; will be repeated automatically - Error: TEIID16016 Insert into UserDefinedProducts success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values.
I am sorry, that I cannot look now deeper into it. Friends are waiting at the bar :) I will be back next week and then will assemble a compose file for you to show the situation. Maybe that way we will find the issue.
> When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5782
> URL: https://issues.jboss.org/browse/TEIID-5782
> Project: Teiid
> Issue Type: Quality Risk
> Components: OData
> Affects Versions: 12.3
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
>
> Hello,
> in the past we had an issue in the odata layer, that the generated primary key of an autoincrement key was not corretly transfered back in the odata response. This issue was fixed.
> However, the current issues derives from the discussion in
> https://issues.jboss.org/browse/TEIID-5763
> where we havewritten an INSTEAD OF INSERT trigger. According to my last comment to https://issues.jboss.org/browse/TEIID-5763, there are two situations which can arrise. I have to set the 1. key group explicitely or 2. it is set implicitely.
> Now in both situations I observe the same result. When I POST an INSERT with a dummy primary key, say key=0 (as it was NOT NULL in the past, and I am still using the old sources) I retrieve key=0 in the odata response. Correct would be to get a response with the generated key instead.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 10 months
[JBoss JIRA] (TEIID-5783) OUTER JOIN yields wrong results when used with GROUP BY, CASE and COUNT DISTINCT
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5783?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5783.
-----------------------------------
Fix Version/s: 12.2.2
12.3
12.1.2
Resolution: Done
Thanks for raising this. I've added a check for null dependent expressions in the staged grouping symbols - they cannot be pushed to the inner side of an outer join as further null values may be introduced by the join.
> OUTER JOIN yields wrong results when used with GROUP BY, CASE and COUNT DISTINCT
> --------------------------------------------------------------------------------
>
> Key: TEIID-5783
> URL: https://issues.jboss.org/browse/TEIID-5783
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 12.0
> Environment: teiid-12.0.0 on WildFly Full 14.0.1.Final (WildFly Core 6.0.2.Final)
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 12.2.2, 12.3, 12.1.2
>
> Attachments: test1.csv, test2.csv
>
>
> Some queries that involve an OUTER JOIN between objects from different data sources, a GROUP BY with a CASE statement and a COUNT DISTINCT yield inconsistent results on a SELECT expression which is identical to the GROUP BY expression with CASE.
> * Reproduced in combination of <File> LEFT JOIN <MySQL> and <File> LEFT JOIN <MS SQL> and the same for the other OUTER JOIN variants.
> * screenshot for the different queries are attached
> * sample data for reproduction is attached
> The same problem happens with LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 10 months
[JBoss JIRA] (TEIID-5782) When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin... ]
Steven Hawkins edited comment on TEIID-5782 at 6/24/19 3:06 PM:
----------------------------------------------------------------
> Can I expect to get the correct value in the response with this patch?
Yes, as long as you don't specify the insert value, you should get the auto increment back.
I double checked what you are saying about the not null case. With or without not null a dummy value is not required.
was (Author: shawkins):
> Can I expect to get the correct value in the response with this patch?
Yes, as long as you don't specify the insert value, you should get the auto increment back.
I'll have to double check what you are saying about the not null case - that should not have required a dummy value.
> When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5782
> URL: https://issues.jboss.org/browse/TEIID-5782
> Project: Teiid
> Issue Type: Quality Risk
> Components: OData
> Affects Versions: 12.3
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
>
> Hello,
> in the past we had an issue in the odata layer, that the generated primary key of an autoincrement key was not corretly transfered back in the odata response. This issue was fixed.
> However, the current issues derives from the discussion in
> https://issues.jboss.org/browse/TEIID-5763
> where we havewritten an INSTEAD OF INSERT trigger. According to my last comment to https://issues.jboss.org/browse/TEIID-5763, there are two situations which can arrise. I have to set the 1. key group explicitely or 2. it is set implicitely.
> Now in both situations I observe the same result. When I POST an INSERT with a dummy primary key, say key=0 (as it was NOT NULL in the past, and I am still using the old sources) I retrieve key=0 in the odata response. Correct would be to get a response with the generated key instead.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 10 months
[JBoss JIRA] (TEIID-5782) When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5782:
---------------------------------------
> Can I expect to get the correct value in the response with this patch?
Yes, as long as you don't specify the insert value, you should get the auto increment back.
I'll have to double check what you are saying about the not null case - that should not have required a dummy value.
> When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5782
> URL: https://issues.jboss.org/browse/TEIID-5782
> Project: Teiid
> Issue Type: Quality Risk
> Components: OData
> Affects Versions: 12.3
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
>
> Hello,
> in the past we had an issue in the odata layer, that the generated primary key of an autoincrement key was not corretly transfered back in the odata response. This issue was fixed.
> However, the current issues derives from the discussion in
> https://issues.jboss.org/browse/TEIID-5763
> where we havewritten an INSTEAD OF INSERT trigger. According to my last comment to https://issues.jboss.org/browse/TEIID-5763, there are two situations which can arrise. I have to set the 1. key group explicitely or 2. it is set implicitely.
> Now in both situations I observe the same result. When I POST an INSERT with a dummy primary key, say key=0 (as it was NOT NULL in the past, and I am still using the old sources) I retrieve key=0 in the odata response. Correct would be to get a response with the generated key instead.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 10 months
[JBoss JIRA] (TEIID-5783) OUTER JOIN yields wrong results when used with GROUP BY, CASE and COUNT DISTINCT
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5783?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5783:
---------------------------------------
There are checks for null dependent expressions used in aggregate symbols, but that is missing from the grouping expressions.
> OUTER JOIN yields wrong results when used with GROUP BY, CASE and COUNT DISTINCT
> --------------------------------------------------------------------------------
>
> Key: TEIID-5783
> URL: https://issues.jboss.org/browse/TEIID-5783
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 12.0
> Environment: teiid-12.0.0 on WildFly Full 14.0.1.Final (WildFly Core 6.0.2.Final)
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: test1.csv, test2.csv
>
>
> Some queries that involve an OUTER JOIN between objects from different data sources, a GROUP BY with a CASE statement and a COUNT DISTINCT yield inconsistent results on a SELECT expression which is identical to the GROUP BY expression with CASE.
> * Reproduced in combination of <File> LEFT JOIN <MySQL> and <File> LEFT JOIN <MS SQL> and the same for the other OUTER JOIN variants.
> * screenshot for the different queries are attached
> * sample data for reproduction is attached
> The same problem happens with LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 10 months
[JBoss JIRA] (TEIID-5783) OUTER JOIN yields wrong results when used with GROUP BY, CASE and COUNT DISTINCT
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5783?page=com.atlassian.jira.plugin... ]
Work on TEIID-5783 started by Steven Hawkins.
---------------------------------------------
> OUTER JOIN yields wrong results when used with GROUP BY, CASE and COUNT DISTINCT
> --------------------------------------------------------------------------------
>
> Key: TEIID-5783
> URL: https://issues.jboss.org/browse/TEIID-5783
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 12.0
> Environment: teiid-12.0.0 on WildFly Full 14.0.1.Final (WildFly Core 6.0.2.Final)
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: test1.csv, test2.csv
>
>
> Some queries that involve an OUTER JOIN between objects from different data sources, a GROUP BY with a CASE statement and a COUNT DISTINCT yield inconsistent results on a SELECT expression which is identical to the GROUP BY expression with CASE.
> * Reproduced in combination of <File> LEFT JOIN <MySQL> and <File> LEFT JOIN <MS SQL> and the same for the other OUTER JOIN variants.
> * screenshot for the different queries are attached
> * sample data for reproduction is attached
> The same problem happens with LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 10 months
[JBoss JIRA] (TEIID-5782) When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5782:
---------------------------------------
Hello Steven, ok,
let us stay in this issue than :) I am not relying on sending of a value for autoincrement. This is of no use for me. But this was the requirement given by Teiid so far to be able to get a create accepted. The only requirement that I have is, that in case of a create request I need to get the newly created object back in the response message with the autocreated value for the primary key.
This currently does not work for INSTEAD OF INSERT trigger.
I have not had a chance yet to check the Teiid behavior with your latest patch which removed the NOT NULL requirement from a primary key which is AUTOINCREMENT.
Can I expect to get the correct value in the response with this patch? I can try this out not before mid of next week when I am back from holiday.
> When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5782
> URL: https://issues.jboss.org/browse/TEIID-5782
> Project: Teiid
> Issue Type: Quality Risk
> Components: OData
> Affects Versions: 12.3
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
>
> Hello,
> in the past we had an issue in the odata layer, that the generated primary key of an autoincrement key was not corretly transfered back in the odata response. This issue was fixed.
> However, the current issues derives from the discussion in
> https://issues.jboss.org/browse/TEIID-5763
> where we havewritten an INSTEAD OF INSERT trigger. According to my last comment to https://issues.jboss.org/browse/TEIID-5763, there are two situations which can arrise. I have to set the 1. key group explicitely or 2. it is set implicitely.
> Now in both situations I observe the same result. When I POST an INSERT with a dummy primary key, say key=0 (as it was NOT NULL in the past, and I am still using the old sources) I retrieve key=0 in the odata response. Correct would be to get a response with the generated key instead.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 10 months