[JBoss JIRA] (TEIIDSB-110) Support kerberos over pg/jdbc
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIIDSB-110?page=com.atlassian.jira.plugi... ]
Steven Hawkins commented on TEIIDSB-110:
----------------------------------------
> I am not sure how much useful this will be at this time, as all applications in the OpenShift will be OAuth2 based AFAIK.
I'm not assuming that it is immediately useful - it's unassigned and bucketed to a later release. It's a bit of an overstatement that all openshift apps will use oath2, but yes it will be dominant. At the very least this issue can capture issues blocking this.
> Support kerberos over pg/jdbc
> -----------------------------
>
> Key: TEIIDSB-110
> URL: https://issues.jboss.org/browse/TEIIDSB-110
> Project: Teiid Spring Boot
> Issue Type: Enhancement
> Reporter: Steven Hawkins
> Priority: Major
> Fix For: 1.3.0
>
>
> Keycloak has support for spengo/kerberos, but at first glance it's only for browser/http challenge flows. It seems like using it in a direct flow is not possible out of the box: https://issues.jboss.org/browse/KEYCLOAK-1751
> Note that even if just kerberos password authentication were supported that would allow for secure logins without ssl, which can be useful for our pg transport.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
6 years, 9 months
[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 updated TEIID-5783:
-------------------------------------
Steps to Reproduce:
1. Add file resource adapter in standalone-teiid.xml:
{code:xml}
<resource-adapter id="file">
<module slot="main" id="org.jboss.teiid.resource-adapter.file"/>
<connection-definitions>
<connection-definition class-name="org.teiid.resource.adapter.file.FileManagedConnectionFactory" jndi-name="java:/files" enabled="true" pool-name="files">
<config-property name="AllowParentPaths">
true
</config-property>
<config-property name="ParentDirectory">
C:/testdata
</config-property>
</connection-definition>
</connection-definitions>
</resource-adapter>
{code}
2. Add MySQL database configuration in standalone-teiid.xml:
{code:xml}
<datasource jndi-name="java:/test_dwh_my" pool-name="test_dwh_my" enabled="true" use-java-context="true">
<connection-url>jdbc:mysql://localhost:3306/dwh?zeroDateTimeBehavior=convertToNull</connection-url>
<driver>mysql</driver>
<new-connection-sql>set SESSION sql_mode = 'ANSI'</new-connection-sql>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>70</max-pool-size>
</pool>
<security>
<user-name>root</user-name>
<password>XXXXXX</password>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
</validation>
<timeout>
<blocking-timeout-millis>120000</blocking-timeout-millis>
<idle-timeout-minutes>5</idle-timeout-minutes>
</timeout>
</datasource>
{code}
3. Add in test-vdb.xml java:/test_dwh_my configured in previous step as datasource:
{code:xml}
<model name="dwh_my">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false"/>
<source name="test_dwh_my" translator-name="mylobs" connection-jndi-name="java:/test_dwh_my"/>
</model>
<translator name="mylobs" type="mysql5">
<property name="CopyLobs" value="true" />
<property name="SupportsNativeQueries" value="true"/>
</translator>
{code}
4. Configure in the test-vdb.xml the following virtual view:
{code:xml}
<model visible = "true" type = "VIRTUAL" name = "views">
<metadata type = "DDL"><![CDATA[
create view vLeft as
SELECT
"csv_table"."created_at",
"csv_table"."order_id",
"csv_table"."store_id"
FROM
(call files.getFiles('test1.csv')) f,
TEXTTABLE(to_chars(f.file,'UTF-8')
COLUMNS
"created_at" STRING ,
"order_id" STRING ,
"store_id" STRING
DELIMITER ';'
QUOTE '"'
HEADER 1
)
"csv_table"
]]>
</metadata>
</model>
{code}
5. Load the data from the other file (test2.csv - attached to this issue) into a MySQL table or a MS SQL table (vRight table name in my case, all fields have varchar types).
6. Run the statement below (rebuy_check column contains no NULLs, that's correct):
{code:sql}
select
count( a."order_id") anzahl_orders,
(case when b."order_id" is not null then 'Direct Rebuy' else 'Standard' end) rebuy_check
FROM views.vLeft a
left JOIN dwh_my.vRight b on b.order_id=a.order_id
group by
(case when b."order_id" is not null then 'Direct Rebuy' else 'Standard' end) ;;
{code}
7. Add a DISTINCT to the COUNT (rebuy_check column now contains NULLs, that's incorrect):
{code:sql}
select
count(distinct a."order_id") anzahl_orders,
(case when b."order_id" is not null then 'Direct Rebuy' else 'Standard' end) rebuy_check
FROM views.vLeft a
left JOIN dwh_my.vRight b on b.order_id=a.order_id
group by
(case when b."order_id" is not null then 'Direct Rebuy' else 'Standard' end) ;;
{code}
8. Remove the COUNT entirely (rebuy_check column now contains NULLs, that's incorrect):
{code:sql}
select
(case when b."order_id" is not null then 'Direct Rebuy' else 'Standard' end) rebuy_check
FROM views.vLeft a
left JOIN dwh_my.vRight b on b.order_id=a.order_id
group by
(case when b."order_id" is not null then 'Direct Rebuy' else 'Standard' end) ;;
{code}
was:
1. Add file resource adapter in standalone-teiid.xml:
{code:xml}
<resource-adapter id="file">
<module slot="main" id="org.jboss.teiid.resource-adapter.file"/>
<connection-definitions>
<connection-definition class-name="org.teiid.resource.adapter.file.FileManagedConnectionFactory" jndi-name="java:/files" enabled="true" pool-name="files">
<config-property name="AllowParentPaths">
true
</config-property>
<config-property name="ParentDirectory">
C:/testdata
</config-property>
</connection-definition>
</connection-definitions>
</resource-adapter>
{code}
2. Add MySQL database configuration in standalone-teiid.xml:
{code:xml}
<datasource jndi-name="java:/test_dwh_my" pool-name="test_dwh_my" enabled="true" use-java-context="true">
<connection-url>jdbc:mysql://localhost:3306/dwh?zeroDateTimeBehavior=convertToNull</connection-url>
<driver>mysql</driver>
<new-connection-sql>set SESSION sql_mode = 'ANSI'</new-connection-sql>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>70</max-pool-size>
</pool>
<security>
<user-name>root</user-name>
<password>XXXXXX</password>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
</validation>
<timeout>
<blocking-timeout-millis>120000</blocking-timeout-millis>
<idle-timeout-minutes>5</idle-timeout-minutes>
</timeout>
</datasource>
<datasource jndi-name="java:/test" pool-name="test" enabled="true" use-java-context="true">
<connection-url>jdbc:mysql://localhost:3306/test_tables?zeroDateTimeBehavior=convertToNull</connection-url>
<driver>mysql</driver>
<new-connection-sql>set SESSION sql_mode = 'ANSI'</new-connection-sql>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>70</max-pool-size>
</pool>
<security>
<user-name>root</user-name>
<password>XXXXXX</password>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
</validation>
<timeout>
<blocking-timeout-millis>120000</blocking-timeout-millis>
<idle-timeout-minutes>5</idle-timeout-minutes>
</timeout>
</datasource>
{code}
3. Add in test-vdb.xml java:/test_dwh_my configured in previous step as datasource:
{code:xml}
<model name="dwh_my">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false"/>
<source name="test_dwh_my" translator-name="mylobs" connection-jndi-name="java:/test_dwh_my"/>
</model>
<translator name="mylobs" type="mysql5">
<property name="CopyLobs" value="true" />
<property name="SupportsNativeQueries" value="true"/>
</translator>
{code}
4. Configure in the test-vdb.xml the following virtual view:
{code:xml}
<model visible = "true" type = "VIRTUAL" name = "views">
<metadata type = "DDL"><![CDATA[
create view vLeft as
SELECT
"csv_table"."created_at",
"csv_table"."order_id",
"csv_table"."store_id"
FROM
(call files.getFiles('test1.csv')) f,
TEXTTABLE(to_chars(f.file,'UTF-8')
COLUMNS
"created_at" STRING ,
"order_id" STRING ,
"store_id" STRING
DELIMITER ';'
QUOTE '"'
HEADER 1
)
"csv_table"
]]>
</metadata>
</model>
{code}
5. Load the data from the other file (test2.csv - attached to this issue) into a MySQL table or a MS SQL table (vRight table name in my case, all fields have varchar types).
6. Run the statement below (rebuy_check column contains no NULLs, that's correct):
{code:sql}
select
count( a."order_id") anzahl_orders,
(case when b."order_id" is not null then 'Direct Rebuy' else 'Standard' end) rebuy_check
FROM views.vLeft a
left JOIN dwh_my.vRight b on b.order_id=a.order_id
group by
(case when b."order_id" is not null then 'Direct Rebuy' else 'Standard' end) ;;
{code}
7. Add a DISTINCT to the COUNT (rebuy_check column now contains NULLs, that's incorrect):
{code:sql}
select
count(distinct a."order_id") anzahl_orders,
(case when b."order_id" is not null then 'Direct Rebuy' else 'Standard' end) rebuy_check
FROM views.vLeft a
left JOIN dwh_my.vRight b on b.order_id=a.order_id
group by
(case when b."order_id" is not null then 'Direct Rebuy' else 'Standard' end) ;;
{code}
8. Remove the COUNT entirely (rebuy_check column now contains NULLs, that's incorrect):
{code:sql}
select
(case when b."order_id" is not null then 'Direct Rebuy' else 'Standard' end) rebuy_check
FROM views.vLeft a
left JOIN dwh_my.vRight b on b.order_id=a.order_id
group by
(case when b."order_id" is not null then 'Direct Rebuy' else 'Standard' end) ;;
{code}
> 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)
6 years, 9 months
[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 updated TEIID-5783:
-------------------------------------
Summary: OUTER JOIN yields wrong results when used with GROUP BY, CASE and COUNT DISTINCT (was: OUTER JOIN Yields Wrong Results When Used With GROUP BY, CASE and COUNT DISTINCT)
> 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)
6 years, 9 months
[JBoss JIRA] (TEIID-5783) OUTER JOIN Yields Wrong Results When Used With GROUP BY, CASE and COUNT DISTINCT
by Dmitrii Pogorelov (Jira)
Dmitrii Pogorelov created TEIID-5783:
----------------------------------------
Summary: 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
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)
6 years, 9 months
[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin... ]
Christoph John edited comment on TEIID-5763 at 6/23/19 7:24 PM:
----------------------------------------------------------------
Hello Steven,
I have one more note regarding the removal of the NOT NULL constraint on the autoincrement key, as I just added a further Jira issue to a similar topic.
I have not found the time to simply test the current sources out, as I am already on my way to holiday.
I was wondering, in case I do not sent a dummy primary key, if I than still get the autogenerated key in the response of the odata request back?
I am not sure what the standard specifies in this context, but I would expect that I always need to have the generated key on the client side to refer back to the created item.
Here the standard says the response shall include the created entity.Even though it does not explicitely state that each attribute needs to be included, I think it would make sense.
http://docs.oasis-open.org/odata/odata/v4.0/errata03/os/complete/part1-pr...
My thinking is that this unintuitive NOT NULL on the autogenerated key, like it was implemented in the past might derive from such a constraint.
was (Author: cjohn001):
Hello Steven,
I have one more note regarding the removal of the NOT NULL constraint on the autoincrement key, as I just added a further Jira issue to a similar topic.
I have not found the time to simply test the current sources out, as I am already on my way to holiday.
I was wondering, in case I do not sent a dummy primary key, if I than still get the autogenerated key in the response of the odata request back?
I am not sure what the standard specifies in this context, but I would expect that I always need to have the generated key on the client side to refer back to the created item. My thinking is that this unintuitive NOT NULL on the autogenerated key like it was in the past might derive from such a constraint.
> 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)
6 years, 9 months
[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5763:
---------------------------------------
Hello Steven,
I have one more note regarding the removal of the NOT NULL constraint on the autoincrement key, as I just added a further Jira issue to a similar topic.
I have not found the time to simply test the current sources out, as I am already on my way to holiday.
I was wondering, in case I do not sent a dummy primary key, if I than still get the autogenerated key in the response of the odata request back?
I am not sure what the standard specifies in this context, but I would expect that I always need to have the generated key on the client side to refer back to the created item. My thinking is that this unintuitive NOT NULL on the autogenerated key like it was in the past might derive from such a constraint.
> 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)
6 years, 9 months
[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin... ]
Christoph John edited comment on TEIID-5763 at 6/23/19 10:25 AM:
-----------------------------------------------------------------
Hello Steven,
I need to come back on you regarding the generated key topic. I tried now to use the generated key in the trigger function of a different table and always end up with the following error.
TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol key.fkProduct is specified with an unknown group context
It does not matter if I take the shortcut generated key, or the explicit cast function. The difference on the current table to the other one is, that the table has a composite primary key. I was wondering if this might be the reason for the issue. I tried to set the second key explicitely on the "key.". Unfortunately, the error remains. The source table definition is as below:
CREATE FOREIGN TABLE UserDefinedProducts_SRC (
fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),
fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
...
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE', CARDINALITY '4');
The view is:
CREATE VIEW UserDefinedProducts (
fkProduct long NOT NULL AUTO_INCREMENT,
fkProfile long NOT NULL,
...
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(UPDATABLE 'TRUE')
AS
SELECT
udp.fkProduct as fkProduct,
udp.fkProfile as fkProfile,
...
FROM UserDefinedProducts_SRC as udp;
And here is the relevant input trigger:
CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS
FOR EACH ROW
BEGIN ATOMIC
DECLARE long vIdProduct;
INSERT INTO
Products(fkDatabaseKey)
VALUES
(0);
vIdProduct = cast(generated_key('idProduct') as long);
key.fkProduct = vIdProduct;
-- key.fkProfile = new.fkProfile;
-- create a new record for the quickly added product
INSERT INTO
UserDefinedProducts_SRC(fkProduct, fkProfile, idCode, product_name, origins, brands, quantity,
serving_quantity, nova_group, nutrition_grade_fr, energy_100g, carbohydrates_100g,
sugars_100g, proteins_100g, fat_100g, saturated_fat_100g, saturated_fat_modifier,
salt_100g, salt_modifier, fiber_100g)
VALUES
(vIdProduct, new.fkProfile, new.idCode, new.product_name, new.origins, new.brands, new.quantity, new.serving_quantity,
new.nova_group, new.nutrition_grade_fr, new.energy_100g, new.carbohydrates_100g, new.sugars_100g,
new.proteins_100g, new.fat_100g, new.saturated_fat_100g, new.saturated_fat_modifier, new.salt_100g,
new.salt_modifier, new.fiber_100g);
END;
Do you have an idea what is going wrong?
Maybe I am still misunderstanding the key group. My thinking is that it reflects the keys that are defined in the view. not these of the underlaying table. is this correct?
Update: Interestingly, I found out that the procedure is working if I do not set the key explicitly. Hence, I simply removed the line
key.fkProduct = vIdProduct;
Would be great if you could explain me what is happening here with the error message and why I in the one case have to set the key. explicitly, and why it does not work in the other case. Still the topic has to much trial and error characteristic for me with my current understanding. Thanks for your help.
was (Author: cjohn001):
Hello Steven,
I need to come back on you regarding the generated key topic. I tried now to use the generated key in the trigger function of a different table and always end up with the following error.
TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol key.fkProduct is specified with an unknown group context
It does not matter if I take the shortcut generated key, or the explicit cast function. The difference on the current table to the other one is, that the table has a composite primary key. I was wondering if this might be the reason for the issue. I tried to set the second key explicitely on the "key.". Unfortunately, the error remains. The source table definition is as below:
CREATE FOREIGN TABLE UserDefinedProducts_SRC (
fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),
fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
...
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE', CARDINALITY '4');
The view is:
CREATE VIEW UserDefinedProducts (
fkProduct long NOT NULL AUTO_INCREMENT,
fkProfile long NOT NULL,
...
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(UPDATABLE 'TRUE')
AS
SELECT
udp.fkProduct as fkProduct,
udp.fkProfile as fkProfile,
...
FROM UserDefinedProducts_SRC as udp;
And here is the relevant input trigger:
CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS
FOR EACH ROW
BEGIN ATOMIC
DECLARE long vIdProduct;
INSERT INTO
Products(fkDatabaseKey)
VALUES
(0);
vIdProduct = cast(generated_key('idProduct') as long);
key.fkProduct = vIdProduct;
-- key.fkProfile = new.fkProfile;
-- create a new record for the quickly added product
INSERT INTO
UserDefinedProducts_SRC(fkProduct, fkProfile, idCode, product_name, origins, brands, quantity,
serving_quantity, nova_group, nutrition_grade_fr, energy_100g, carbohydrates_100g,
sugars_100g, proteins_100g, fat_100g, saturated_fat_100g, saturated_fat_modifier,
salt_100g, salt_modifier, fiber_100g)
VALUES
(vIdProduct, new.fkProfile, new.idCode, new.product_name, new.origins, new.brands, new.quantity, new.serving_quantity,
new.nova_group, new.nutrition_grade_fr, new.energy_100g, new.carbohydrates_100g, new.sugars_100g,
new.proteins_100g, new.fat_100g, new.saturated_fat_100g, new.saturated_fat_modifier, new.salt_100g,
new.salt_modifier, new.fiber_100g);
END;
Do you have an idea what is going wrong?
Maybe I am still misunderstanding the key group. My thinking is that it reflects the keys that are defined in the view. not these of the underlaying table. is this correct?
> 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)
6 years, 9 months
[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin... ]
Christoph John edited comment on TEIID-5763 at 6/23/19 10:01 AM:
-----------------------------------------------------------------
Hello Steven,
I need to come back on you regarding the generated key topic. I tried now to use the generated key in the trigger function of a different table and always end up with the following error.
TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol key.fkProduct is specified with an unknown group context
It does not matter if I take the shortcut generated key, or the explicit cast function. The difference on the current table to the other one is, that the table has a composite primary key. I was wondering if this might be the reason for the issue. I tried to set the second key explicitely on the "key.". Unfortunately, the error remains. The source table definition is as below:
CREATE FOREIGN TABLE UserDefinedProducts_SRC (
fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),
fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
...
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE', CARDINALITY '4');
The view is:
CREATE VIEW UserDefinedProducts (
fkProduct long NOT NULL AUTO_INCREMENT,
fkProfile long NOT NULL,
...
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(UPDATABLE 'TRUE')
AS
SELECT
udp.fkProduct as fkProduct,
udp.fkProfile as fkProfile,
...
FROM UserDefinedProducts_SRC as udp;
And here is the relevant input trigger:
CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS
FOR EACH ROW
BEGIN ATOMIC
DECLARE long vIdProduct;
INSERT INTO
Products(fkDatabaseKey)
VALUES
(0);
vIdProduct = cast(generated_key('idProduct') as long);
key.fkProduct = vIdProduct;
-- key.fkProfile = new.fkProfile;
-- create a new record for the quickly added product
INSERT INTO
UserDefinedProducts_SRC(fkProduct, fkProfile, idCode, product_name, origins, brands, quantity,
serving_quantity, nova_group, nutrition_grade_fr, energy_100g, carbohydrates_100g,
sugars_100g, proteins_100g, fat_100g, saturated_fat_100g, saturated_fat_modifier,
salt_100g, salt_modifier, fiber_100g)
VALUES
(vIdProduct, new.fkProfile, new.idCode, new.product_name, new.origins, new.brands, new.quantity, new.serving_quantity,
new.nova_group, new.nutrition_grade_fr, new.energy_100g, new.carbohydrates_100g, new.sugars_100g,
new.proteins_100g, new.fat_100g, new.saturated_fat_100g, new.saturated_fat_modifier, new.salt_100g,
new.salt_modifier, new.fiber_100g);
END;
Do you have an idea what is going wrong?
Maybe I am still misunderstanding the key group. My thinking is that it reflects the keys that are defined in the view. not these of the underlaying table. is this correct?
was (Author: cjohn001):
Hello Steven,
I need to come back on you regarding the generated key topic. I tried now to use the generated key in the trigger function of a different table and always end up with the following error.
TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol key.fkProduct is specified with an unknown group context
It does not matter if I take the shortcut generated key, or the explicit cast function. The difference on the current table to the other one is, that the table has a composite primary key. I was wondering if this might be the reason for the issue. I tried to set the second key explicitely on the "key.". Unfortunately, the error remains. The source table definition is as below:
CREATE FOREIGN TABLE UserDefinedProducts_SRC (
fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),
fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
...
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE', CARDINALITY '4');
The view is:
CREATE VIEW UserDefinedProducts (
fkProduct long NOT NULL AUTO_INCREMENT,
fkProfile long NOT NULL,
...
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(UPDATABLE 'TRUE')
AS
SELECT
udp.fkProduct as fkProduct,
udp.fkProfile as fkProfile,
...
FROM UserDefinedProducts_SRC as udp;
And here is the relevant input trigger:
CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS
FOR EACH ROW
BEGIN ATOMIC
DECLARE long vIdProduct;
INSERT INTO
Products(fkDatabaseKey)
VALUES
(0);
vIdProduct = cast(generated_key('idProduct') as long);
key.fkProduct = vIdProduct;
-- key.fkProfile = new.fkProfile;
-- create a new record for the quickly added product
INSERT INTO
UserDefinedProducts_SRC(fkProduct, fkProfile, idCode, product_name, origins, brands, quantity,
serving_quantity, nova_group, nutrition_grade_fr, energy_100g, carbohydrates_100g,
sugars_100g, proteins_100g, fat_100g, saturated_fat_100g, saturated_fat_modifier,
salt_100g, salt_modifier, fiber_100g)
VALUES
(vIdProduct, new.fkProfile, new.idCode, new.product_name, new.origins, new.brands, new.quantity, new.serving_quantity,
new.nova_group, new.nutrition_grade_fr, new.energy_100g, new.carbohydrates_100g, new.sugars_100g,
new.proteins_100g, new.fat_100g, new.saturated_fat_100g, new.saturated_fat_modifier, new.salt_100g,
new.salt_modifier, new.fiber_100g);
END;
Do you have an idea what is going wrong?
> 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)
6 years, 9 months
[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5763:
---------------------------------------
Hello Steven,
I need to come back on you regarding the generated key topic. I tried now to use the generated key in the trigger function of a different table and always end up with the following error.
TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol key.fkProduct is specified with an unknown group context
It does not matter if I take the shortcut generated key, or the explicit cast function. The difference on the current table to the other one is, that the table has a composite primary key. I was wondering if this might be the reason for the issue. I tried to set the second key explicitely on the "key.". Unfortunately, the error remains. The source table definition is as below:
CREATE FOREIGN TABLE UserDefinedProducts_SRC (
fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),
fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
...
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE', CARDINALITY '4');
The view is:
CREATE VIEW UserDefinedProducts (
fkProduct long NOT NULL AUTO_INCREMENT,
fkProfile long NOT NULL,
...
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(UPDATABLE 'TRUE')
AS
SELECT
udp.fkProduct as fkProduct,
udp.fkProfile as fkProfile,
...
FROM UserDefinedProducts_SRC as udp;
And here is the relevant input trigger:
CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS
FOR EACH ROW
BEGIN ATOMIC
DECLARE long vIdProduct;
INSERT INTO
Products(fkDatabaseKey)
VALUES
(0);
vIdProduct = cast(generated_key('idProduct') as long);
key.fkProduct = vIdProduct;
-- key.fkProfile = new.fkProfile;
-- create a new record for the quickly added product
INSERT INTO
UserDefinedProducts_SRC(fkProduct, fkProfile, idCode, product_name, origins, brands, quantity,
serving_quantity, nova_group, nutrition_grade_fr, energy_100g, carbohydrates_100g,
sugars_100g, proteins_100g, fat_100g, saturated_fat_100g, saturated_fat_modifier,
salt_100g, salt_modifier, fiber_100g)
VALUES
(vIdProduct, new.fkProfile, new.idCode, new.product_name, new.origins, new.brands, new.quantity, new.serving_quantity,
new.nova_group, new.nutrition_grade_fr, new.energy_100g, new.carbohydrates_100g, new.sugars_100g,
new.proteins_100g, new.fat_100g, new.saturated_fat_100g, new.saturated_fat_modifier, new.salt_100g,
new.salt_modifier, new.fiber_100g);
END;
Do you have an idea what is going wrong?
> 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)
6 years, 9 months