[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)
5 years, 8 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)
5 years, 8 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)
5 years, 8 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)
5 years, 8 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)
5 years, 8 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)
5 years, 8 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)
5 years, 8 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)
5 years, 8 months
[JBoss JIRA] (TEIID-5780) Support certificate based authentication into Teiid pg
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5780?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5780:
---------------------------------------
> The SSL certificate is the one from the transport level certificate that is created as part of route creation?
There would be service signing certificate created both for the teiid pg service, and one create for the pg database.
> Where does the Teiid server get this certificate?
It would one for the Teiid pg service as shown in https://github.com/teiid/teiid-openshift-examples/blob/master/keycloak/ke...
Note that the deployment config converts the service signing certificate to a jks file: https://github.com/teiid/teiid-openshift-examples/blob/master/keycloak/sr... to use with a Teiid transport.
This needs to occur for the pg instance as well. It will have to have a signing certificate created, which an init container would turn into the the appropriate formats (pem and der) for consumption by the pg fdw connection to present to Teiid as the client certificate.
Each side will also need additional changes for another init container that creates or adds to a trust store on each side the public key from the other side.
At the end of the day that means that the teiid pg transport will support mutual authentication such that connections from the pg server are trusted.
On top of that there is the additional configuration on the Teiid session service to set a specific certificate principal as the admin account. This would need to match whatever openshift populates for the service signing certificate created for the pg database service.
> Is this for general authentication for any client for PG, or you looking only at materialization case?
It is primarily so that a pg client to Teiid can assume an "admin" role for materialization. You could extrapolate this is several ways to make it a more general mechanism.
Essentially we need the connection coming in from pg to read Teiid to be a super user. At a minimum it has to see all tables (which marking the dqpworkcontext as admin does). It will probably need to be granted all roles as well - as it needs to be able to directly read from any view that is marked as materialized, which is not required of the normal materialization logic because it performs loads via procedures.
The other approach would be to have the user designate or create an admin user in the realm used with each vdb, and the provide the credentials if materialization is being used.
> Maybe a flow diagram will help me.
A thousand words is worth one picture right?
> Support certificate based authentication into Teiid pg
> ------------------------------------------------------
>
> Key: TEIID-5780
> URL: https://issues.jboss.org/browse/TEIID-5780
> Project: Teiid
> Issue Type: Sub-task
> Components: ODBC
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 12.3
>
>
> To support the pg connection into Teiid we will do something like:
> - require a pg secure port using the service signing certificate: TEIIDSB-90 TEIIDSB-92
> -- one clarification is that we must document how to make the pg cert dominant if both pg and jdbc secure are used
> TODO:
> - configure the pg instance to have a service signing certificate and trust the Teiid service signing certificate. If that trust seems too difficult we can just configure the connection to trust all.
> - configure the pg connection to Teiid to use the pg service signing certificate as the client certificate
> - trust the pg service signing certificate at the teiid service - we need hostname validation to be enabled and the Teiid server to map the service host name to an authenticated user (this could possibly be generalized via keycloak support to more users).
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 8 months