May 2020 - teiid-issues - Jboss List Archives

[JBoss JIRA] (TEIID-5961) create foreign data wrapper statements

by Steven Hawkins (Jira)

Steven Hawkins created TEIID-5961: ------------------------------------- Summary: create foreign data wrapper statements Key: TEIID-5961 URL: https://issues.redhat.com/browse/TEIID-5961 Project: Teiid Issue Type: Quality Risk Components: Query Engine Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 15.0 ConvertVDB is still adding create foreign data wrapper x; statements. These are not needed and should be suppressed. It's also causing some confusion that a user can enter create foreign data wrapper x options (...); The options are actually ignored. We should probably throw an exception instead. Either a type or a handler are required (we don't currently support the notion of a handler). See also TEIID-5850 -- This message was sent by Atlassian Jira (v7.13.8#713008)

4 years, 6 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5959) Teiid doesn't re-optimize select sub-nodes after adding new criteria

by Dmitrii Pogorelov (Jira)

[ https://issues.redhat.com/browse/TEIID-5959?page=com.atlassian.jira.plugi... ] Dmitrii Pogorelov commented on TEIID-5959: ------------------------------------------ great, thx! > Teiid doesn't re-optimize select sub-nodes after adding new criteria > -------------------------------------------------------------------- > > Key: TEIID-5959 > URL: https://issues.redhat.com/browse/TEIID-5959 > Project: Teiid > Issue Type: Quality Risk > Components: Query Engine > Affects Versions: 13.1 > Reporter: Dmitrii Pogorelov > Assignee: Steven Hawkins > Priority: Major > Fix For: 15.0 > > Original Estimate: 4 hours > Time Spent: 4 hours > Remaining Estimate: 0 minutes > > It looks like Teiid doesn't re-optimize union nodes after adding new criteria to their sub-select nodes. For example, for the query (the user has only role1): > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > {code} > Teiid will generate the following Query plan: > {code:xml} > AccessNode(0) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 LIMIT 100 > {code} > what is correct because the user has only role1 and Teiid optimizes union removing the first sub-query as its condition in total is false. > But for this query: > {code:sql} > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} > Teiid will propagate the "a = 1" condition in all union sub-queries and generate the following query plan: > {code:xml} > LimitNode(0) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] limit 100 > UnionAllNode(1) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] > AccessNode(2) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > AccessNode(3) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 WHERE g_0.a = 1 LIMIT 100 > {code} > what is incorrect as the > {code:xml} > SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > {code} > still has false condition and logically the query plan for the second query should be the same as for the first one, though it doesn't have an influence on results. > I don't know whether it's a bug it not. That's why I created the issue with the "Quality Risk" type. > Steps to reproduce: > 1. In PostgreSQL create the test_a table using the following scripts: > {code:sql} > CREATE TABLE public.test_a > ( > a integer, > b integer > ); > INSERT INTO public.test_a(a, b) VALUES (1, 1); > INSERT INTO public.test_a(a, b) VALUES (1, 2); > INSERT INTO public.test_a(a, b) VALUES (2, 1); > INSERT INTO public.test_a(a, b) VALUES (2, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 10); > {code} > 2. Add PostgreSQL database configuration in standalone-teiid.xml: > {code:xml} > <datasource jndi-name="java:/test_tables_pg" pool-name="test_tables_pg" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </validation> > <timeout> > <blocking-timeout-millis>120000</blocking-timeout-millis> > <idle-timeout-minutes>5</idle-timeout-minutes> > </timeout> > </datasource> > <datasource jndi-name="java:/test_tables_pg2" pool-name="test_tables_pg2" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </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_tables_pg configured in the previous step as a data source: > {code:xml} > <model name="test_tables_pg"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg" translator-name="myPg" connection-jndi-name="java:/test_tables_pg"/> > </model> > <model name="test_tables_pg2"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg2" translator-name="myPg" connection-jndi-name="java:/test_tables_pg2"/> > </model> > {code} > 4. add the "role1" configuration: > {code:xml} > <data-role name="role1" any-authenticated="true" allow-create-temporary-tables="true"> > <description>Allow read only</description> > <permission> > <resource-name>test_tables_pg2.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > <permission> > <resource-name>test_tables_pg.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > </data-role> > {code} > 5. run the following queries and compare their query plans: > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > select hasRole('data','report-role') ;; > select hasRole('role1') ;; > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} -- This message was sent by Atlassian Jira (v7.13.8#713008)

4 years, 6 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5960) Setting connection url property for MYSQL XA Datasource

by Manoj Majumdar (Jira)

[ https://issues.redhat.com/browse/TEIID-5960?page=com.atlassian.jira.plugi... ] Manoj Majumdar commented on TEIID-5960: --------------------------------------- I got to find a way to set connection url in xa-datasource. It is by setting a property called "connection-properties", and the value will be "URL=". It works for mysql and sqlserver xa datasources as far as I have checked. > Setting connection url property for MYSQL XA Datasource > ------------------------------------------------------- > > Key: TEIID-5960 > URL: https://issues.redhat.com/browse/TEIID-5960 > Project: Teiid > Issue Type: Bug > Components: AdminApi, JDBC Connector > Affects Versions: 11.1.2 > Reporter: Manoj Majumdar > Assignee: Steven Hawkins > Priority: Minor > Labels: Mysql-connector, teiid, xa-datasource-property > > I am trying to create a mysql xa datasource, but am unable to mention connection url properties in it. I have used property names such as "URL" and "connection-url", but the property is not visible in datasource. > Also when I explicitly make a property tag in datasource for the url, it is working, but not when I am creating it through Admin Api, > I need the connection url for setting mysql connection arguments such as "rewriteBatchedStatements=true" -- This message was sent by Atlassian Jira (v7.13.8#713008)

4 years, 6 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5959) Teiid doesn't re-optimize select sub-nodes after adding new criteria

by Steven Hawkins (Jira)

[ https://issues.redhat.com/browse/TEIID-5959?page=com.atlassian.jira.plugi... ] Steven Hawkins commented on TEIID-5959: --------------------------------------- It's there now. > Teiid doesn't re-optimize select sub-nodes after adding new criteria > -------------------------------------------------------------------- > > Key: TEIID-5959 > URL: https://issues.redhat.com/browse/TEIID-5959 > Project: Teiid > Issue Type: Quality Risk > Components: Query Engine > Affects Versions: 13.1 > Reporter: Dmitrii Pogorelov > Assignee: Steven Hawkins > Priority: Major > Fix For: 15.0 > > Original Estimate: 4 hours > Time Spent: 4 hours > Remaining Estimate: 0 minutes > > It looks like Teiid doesn't re-optimize union nodes after adding new criteria to their sub-select nodes. For example, for the query (the user has only role1): > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > {code} > Teiid will generate the following Query plan: > {code:xml} > AccessNode(0) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 LIMIT 100 > {code} > what is correct because the user has only role1 and Teiid optimizes union removing the first sub-query as its condition in total is false. > But for this query: > {code:sql} > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} > Teiid will propagate the "a = 1" condition in all union sub-queries and generate the following query plan: > {code:xml} > LimitNode(0) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] limit 100 > UnionAllNode(1) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] > AccessNode(2) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > AccessNode(3) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 WHERE g_0.a = 1 LIMIT 100 > {code} > what is incorrect as the > {code:xml} > SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > {code} > still has false condition and logically the query plan for the second query should be the same as for the first one, though it doesn't have an influence on results. > I don't know whether it's a bug it not. That's why I created the issue with the "Quality Risk" type. > Steps to reproduce: > 1. In PostgreSQL create the test_a table using the following scripts: > {code:sql} > CREATE TABLE public.test_a > ( > a integer, > b integer > ); > INSERT INTO public.test_a(a, b) VALUES (1, 1); > INSERT INTO public.test_a(a, b) VALUES (1, 2); > INSERT INTO public.test_a(a, b) VALUES (2, 1); > INSERT INTO public.test_a(a, b) VALUES (2, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 10); > {code} > 2. Add PostgreSQL database configuration in standalone-teiid.xml: > {code:xml} > <datasource jndi-name="java:/test_tables_pg" pool-name="test_tables_pg" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </validation> > <timeout> > <blocking-timeout-millis>120000</blocking-timeout-millis> > <idle-timeout-minutes>5</idle-timeout-minutes> > </timeout> > </datasource> > <datasource jndi-name="java:/test_tables_pg2" pool-name="test_tables_pg2" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </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_tables_pg configured in the previous step as a data source: > {code:xml} > <model name="test_tables_pg"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg" translator-name="myPg" connection-jndi-name="java:/test_tables_pg"/> > </model> > <model name="test_tables_pg2"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg2" translator-name="myPg" connection-jndi-name="java:/test_tables_pg2"/> > </model> > {code} > 4. add the "role1" configuration: > {code:xml} > <data-role name="role1" any-authenticated="true" allow-create-temporary-tables="true"> > <description>Allow read only</description> > <permission> > <resource-name>test_tables_pg2.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > <permission> > <resource-name>test_tables_pg.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > </data-role> > {code} > 5. run the following queries and compare their query plans: > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > select hasRole('data','report-role') ;; > select hasRole('role1') ;; > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} -- This message was sent by Atlassian Jira (v7.13.8#713008)

4 years, 6 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5959) Teiid doesn't re-optimize select sub-nodes after adding new criteria

by Dmitrii Pogorelov (Jira)

[ https://issues.redhat.com/browse/TEIID-5959?page=com.atlassian.jira.plugi... ] Dmitrii Pogorelov commented on TEIID-5959: ------------------------------------------ Hi [~shawkins] thx a lot for the quick fix, only one problem, I can't see any commits in the scope of the issue, only via https://source.jboss.org/changelog/Teiid > Teiid doesn't re-optimize select sub-nodes after adding new criteria > -------------------------------------------------------------------- > > Key: TEIID-5959 > URL: https://issues.redhat.com/browse/TEIID-5959 > Project: Teiid > Issue Type: Quality Risk > Components: Query Engine > Affects Versions: 13.1 > Reporter: Dmitrii Pogorelov > Assignee: Steven Hawkins > Priority: Major > Fix For: 15.0 > > Original Estimate: 4 hours > Time Spent: 4 hours > Remaining Estimate: 0 minutes > > It looks like Teiid doesn't re-optimize union nodes after adding new criteria to their sub-select nodes. For example, for the query (the user has only role1): > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > {code} > Teiid will generate the following Query plan: > {code:xml} > AccessNode(0) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 LIMIT 100 > {code} > what is correct because the user has only role1 and Teiid optimizes union removing the first sub-query as its condition in total is false. > But for this query: > {code:sql} > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} > Teiid will propagate the "a = 1" condition in all union sub-queries and generate the following query plan: > {code:xml} > LimitNode(0) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] limit 100 > UnionAllNode(1) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] > AccessNode(2) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > AccessNode(3) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 WHERE g_0.a = 1 LIMIT 100 > {code} > what is incorrect as the > {code:xml} > SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > {code} > still has false condition and logically the query plan for the second query should be the same as for the first one, though it doesn't have an influence on results. > I don't know whether it's a bug it not. That's why I created the issue with the "Quality Risk" type. > Steps to reproduce: > 1. In PostgreSQL create the test_a table using the following scripts: > {code:sql} > CREATE TABLE public.test_a > ( > a integer, > b integer > ); > INSERT INTO public.test_a(a, b) VALUES (1, 1); > INSERT INTO public.test_a(a, b) VALUES (1, 2); > INSERT INTO public.test_a(a, b) VALUES (2, 1); > INSERT INTO public.test_a(a, b) VALUES (2, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 10); > {code} > 2. Add PostgreSQL database configuration in standalone-teiid.xml: > {code:xml} > <datasource jndi-name="java:/test_tables_pg" pool-name="test_tables_pg" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </validation> > <timeout> > <blocking-timeout-millis>120000</blocking-timeout-millis> > <idle-timeout-minutes>5</idle-timeout-minutes> > </timeout> > </datasource> > <datasource jndi-name="java:/test_tables_pg2" pool-name="test_tables_pg2" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </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_tables_pg configured in the previous step as a data source: > {code:xml} > <model name="test_tables_pg"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg" translator-name="myPg" connection-jndi-name="java:/test_tables_pg"/> > </model> > <model name="test_tables_pg2"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg2" translator-name="myPg" connection-jndi-name="java:/test_tables_pg2"/> > </model> > {code} > 4. add the "role1" configuration: > {code:xml} > <data-role name="role1" any-authenticated="true" allow-create-temporary-tables="true"> > <description>Allow read only</description> > <permission> > <resource-name>test_tables_pg2.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > <permission> > <resource-name>test_tables_pg.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > </data-role> > {code} > 5. run the following queries and compare their query plans: > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > select hasRole('data','report-role') ;; > select hasRole('role1') ;; > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} -- This message was sent by Atlassian Jira (v7.13.8#713008)

4 years, 6 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5960) Setting connection url property for MYSQL XA Datasource

by Manoj Majumdar (Jira)

Manoj Majumdar created TEIID-5960: ------------------------------------- Summary: Setting connection url property for MYSQL XA Datasource Key: TEIID-5960 URL: https://issues.redhat.com/browse/TEIID-5960 Project: Teiid Issue Type: Bug Components: AdminApi, JDBC Connector Affects Versions: 11.1.2 Reporter: Manoj Majumdar Assignee: Steven Hawkins I am trying to create a mysql xa datasource, but am unable to mention connection url properties in it. I have used property names such as "URL" and "connection-url", but the property is not visible in datasource. Also when I explicitly make a property tag in datasource for the url, it is working, but not when I am creating it through Admin Api, I need the connection url for setting mysql connection arguments such as "rewriteBatchedStatements=true" -- This message was sent by Atlassian Jira (v7.13.8#713008)

4 years, 6 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5959) Teiid doesn't re-optimize select sub-nodes after adding new criteria

by Steven Hawkins (Jira)

[ https://issues.redhat.com/browse/TEIID-5959?page=com.atlassian.jira.plugi... ] Steven Hawkins updated TEIID-5959: ---------------------------------- Fix Version/s: 15.0 Original Estimate: 4 hours Remaining Estimate: 4 hours Story Points: 0.5 Sprint: DV Sprint 64 > Teiid doesn't re-optimize select sub-nodes after adding new criteria > -------------------------------------------------------------------- > > Key: TEIID-5959 > URL: https://issues.redhat.com/browse/TEIID-5959 > Project: Teiid > Issue Type: Quality Risk > Components: Query Engine > Affects Versions: 13.1 > Reporter: Dmitrii Pogorelov > Assignee: Steven Hawkins > Priority: Major > Fix For: 15.0 > > Original Estimate: 4 hours > Remaining Estimate: 4 hours > > It looks like Teiid doesn't re-optimize union nodes after adding new criteria to their sub-select nodes. For example, for the query (the user has only role1): > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > {code} > Teiid will generate the following Query plan: > {code:xml} > AccessNode(0) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 LIMIT 100 > {code} > what is correct because the user has only role1 and Teiid optimizes union removing the first sub-query as its condition in total is false. > But for this query: > {code:sql} > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} > Teiid will propagate the "a = 1" condition in all union sub-queries and generate the following query plan: > {code:xml} > LimitNode(0) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] limit 100 > UnionAllNode(1) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] > AccessNode(2) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > AccessNode(3) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 WHERE g_0.a = 1 LIMIT 100 > {code} > what is incorrect as the > {code:xml} > SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > {code} > still has false condition and logically the query plan for the second query should be the same as for the first one, though it doesn't have an influence on results. > I don't know whether it's a bug it not. That's why I created the issue with the "Quality Risk" type. > Steps to reproduce: > 1. In PostgreSQL create the test_a table using the following scripts: > {code:sql} > CREATE TABLE public.test_a > ( > a integer, > b integer > ); > INSERT INTO public.test_a(a, b) VALUES (1, 1); > INSERT INTO public.test_a(a, b) VALUES (1, 2); > INSERT INTO public.test_a(a, b) VALUES (2, 1); > INSERT INTO public.test_a(a, b) VALUES (2, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 10); > {code} > 2. Add PostgreSQL database configuration in standalone-teiid.xml: > {code:xml} > <datasource jndi-name="java:/test_tables_pg" pool-name="test_tables_pg" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </validation> > <timeout> > <blocking-timeout-millis>120000</blocking-timeout-millis> > <idle-timeout-minutes>5</idle-timeout-minutes> > </timeout> > </datasource> > <datasource jndi-name="java:/test_tables_pg2" pool-name="test_tables_pg2" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </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_tables_pg configured in the previous step as a data source: > {code:xml} > <model name="test_tables_pg"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg" translator-name="myPg" connection-jndi-name="java:/test_tables_pg"/> > </model> > <model name="test_tables_pg2"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg2" translator-name="myPg" connection-jndi-name="java:/test_tables_pg2"/> > </model> > {code} > 4. add the "role1" configuration: > {code:xml} > <data-role name="role1" any-authenticated="true" allow-create-temporary-tables="true"> > <description>Allow read only</description> > <permission> > <resource-name>test_tables_pg2.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > <permission> > <resource-name>test_tables_pg.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > </data-role> > {code} > 5. run the following queries and compare their query plans: > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > select hasRole('data','report-role') ;; > select hasRole('role1') ;; > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} -- This message was sent by Atlassian Jira (v7.13.8#713008)

4 years, 6 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5959) Teiid doesn't re-optimize select sub-nodes after adding new criteria

by Steven Hawkins (Jira)

[ https://issues.redhat.com/browse/TEIID-5959?page=com.atlassian.jira.plugi... ] Steven Hawkins resolved TEIID-5959. ----------------------------------- Resolution: Done My initial thought was to add a late rewrite in rule clean criteria, but that isn't really needed. This can be resolved by completing the rewrite of the initial evaluation of hasRole - which is parsed as expression criteria as it's just a boolean expression and not some other predicate form. > Teiid doesn't re-optimize select sub-nodes after adding new criteria > -------------------------------------------------------------------- > > Key: TEIID-5959 > URL: https://issues.redhat.com/browse/TEIID-5959 > Project: Teiid > Issue Type: Quality Risk > Components: Query Engine > Affects Versions: 13.1 > Reporter: Dmitrii Pogorelov > Assignee: Steven Hawkins > Priority: Major > Fix For: 15.0 > > Original Estimate: 4 hours > Remaining Estimate: 4 hours > > It looks like Teiid doesn't re-optimize union nodes after adding new criteria to their sub-select nodes. For example, for the query (the user has only role1): > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > {code} > Teiid will generate the following Query plan: > {code:xml} > AccessNode(0) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 LIMIT 100 > {code} > what is correct because the user has only role1 and Teiid optimizes union removing the first sub-query as its condition in total is false. > But for this query: > {code:sql} > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} > Teiid will propagate the "a = 1" condition in all union sub-queries and generate the following query plan: > {code:xml} > LimitNode(0) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] limit 100 > UnionAllNode(1) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] > AccessNode(2) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > AccessNode(3) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 WHERE g_0.a = 1 LIMIT 100 > {code} > what is incorrect as the > {code:xml} > SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > {code} > still has false condition and logically the query plan for the second query should be the same as for the first one, though it doesn't have an influence on results. > I don't know whether it's a bug it not. That's why I created the issue with the "Quality Risk" type. > Steps to reproduce: > 1. In PostgreSQL create the test_a table using the following scripts: > {code:sql} > CREATE TABLE public.test_a > ( > a integer, > b integer > ); > INSERT INTO public.test_a(a, b) VALUES (1, 1); > INSERT INTO public.test_a(a, b) VALUES (1, 2); > INSERT INTO public.test_a(a, b) VALUES (2, 1); > INSERT INTO public.test_a(a, b) VALUES (2, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 10); > {code} > 2. Add PostgreSQL database configuration in standalone-teiid.xml: > {code:xml} > <datasource jndi-name="java:/test_tables_pg" pool-name="test_tables_pg" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </validation> > <timeout> > <blocking-timeout-millis>120000</blocking-timeout-millis> > <idle-timeout-minutes>5</idle-timeout-minutes> > </timeout> > </datasource> > <datasource jndi-name="java:/test_tables_pg2" pool-name="test_tables_pg2" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </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_tables_pg configured in the previous step as a data source: > {code:xml} > <model name="test_tables_pg"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg" translator-name="myPg" connection-jndi-name="java:/test_tables_pg"/> > </model> > <model name="test_tables_pg2"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg2" translator-name="myPg" connection-jndi-name="java:/test_tables_pg2"/> > </model> > {code} > 4. add the "role1" configuration: > {code:xml} > <data-role name="role1" any-authenticated="true" allow-create-temporary-tables="true"> > <description>Allow read only</description> > <permission> > <resource-name>test_tables_pg2.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > <permission> > <resource-name>test_tables_pg.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > </data-role> > {code} > 5. run the following queries and compare their query plans: > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > select hasRole('data','report-role') ;; > select hasRole('role1') ;; > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} -- This message was sent by Atlassian Jira (v7.13.8#713008)

4 years, 6 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5959) Teiid doesn't re-optimize select sub-nodes after adding new criteria

by Steven Hawkins (Jira)

[ https://issues.redhat.com/browse/TEIID-5959?focusedWorklogId=12451173&pag... ] Steven Hawkins logged work on TEIID-5959: ----------------------------------------- Author: Steven Hawkins Created on: 28/May/20 10:56 PM Start Date: 28/May/20 10:56 PM Worklog Time Spent: 4 hours Issue Time Tracking ------------------- Remaining Estimate: 0 minutes (was: 4 hours) Time Spent: 4 hours Worklog Id: (was: 12451173) > Teiid doesn't re-optimize select sub-nodes after adding new criteria > -------------------------------------------------------------------- > > Key: TEIID-5959 > URL: https://issues.redhat.com/browse/TEIID-5959 > Project: Teiid > Issue Type: Quality Risk > Components: Query Engine > Affects Versions: 13.1 > Reporter: Dmitrii Pogorelov > Assignee: Steven Hawkins > Priority: Major > Fix For: 15.0 > > Original Estimate: 4 hours > Time Spent: 4 hours > Remaining Estimate: 0 minutes > > It looks like Teiid doesn't re-optimize union nodes after adding new criteria to their sub-select nodes. For example, for the query (the user has only role1): > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > {code} > Teiid will generate the following Query plan: > {code:xml} > AccessNode(0) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 LIMIT 100 > {code} > what is correct because the user has only role1 and Teiid optimizes union removing the first sub-query as its condition in total is false. > But for this query: > {code:sql} > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} > Teiid will propagate the "a = 1" condition in all union sub-queries and generate the following query plan: > {code:xml} > LimitNode(0) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] limit 100 > UnionAllNode(1) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] > AccessNode(2) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > AccessNode(3) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 WHERE g_0.a = 1 LIMIT 100 > {code} > what is incorrect as the > {code:xml} > SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > {code} > still has false condition and logically the query plan for the second query should be the same as for the first one, though it doesn't have an influence on results. > I don't know whether it's a bug it not. That's why I created the issue with the "Quality Risk" type. > Steps to reproduce: > 1. In PostgreSQL create the test_a table using the following scripts: > {code:sql} > CREATE TABLE public.test_a > ( > a integer, > b integer > ); > INSERT INTO public.test_a(a, b) VALUES (1, 1); > INSERT INTO public.test_a(a, b) VALUES (1, 2); > INSERT INTO public.test_a(a, b) VALUES (2, 1); > INSERT INTO public.test_a(a, b) VALUES (2, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 10); > {code} > 2. Add PostgreSQL database configuration in standalone-teiid.xml: > {code:xml} > <datasource jndi-name="java:/test_tables_pg" pool-name="test_tables_pg" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </validation> > <timeout> > <blocking-timeout-millis>120000</blocking-timeout-millis> > <idle-timeout-minutes>5</idle-timeout-minutes> > </timeout> > </datasource> > <datasource jndi-name="java:/test_tables_pg2" pool-name="test_tables_pg2" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </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_tables_pg configured in the previous step as a data source: > {code:xml} > <model name="test_tables_pg"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg" translator-name="myPg" connection-jndi-name="java:/test_tables_pg"/> > </model> > <model name="test_tables_pg2"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg2" translator-name="myPg" connection-jndi-name="java:/test_tables_pg2"/> > </model> > {code} > 4. add the "role1" configuration: > {code:xml} > <data-role name="role1" any-authenticated="true" allow-create-temporary-tables="true"> > <description>Allow read only</description> > <permission> > <resource-name>test_tables_pg2.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > <permission> > <resource-name>test_tables_pg.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > </data-role> > {code} > 5. run the following queries and compare their query plans: > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > select hasRole('data','report-role') ;; > select hasRole('role1') ;; > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} -- This message was sent by Atlassian Jira (v7.13.8#713008)

4 years, 6 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5959) Teiid doesn't re-optimize select sub-nodes after adding new criteria

by Steven Hawkins (Jira)

[ https://issues.redhat.com/browse/TEIID-5959?page=com.atlassian.jira.plugi... ] Steven Hawkins commented on TEIID-5959: --------------------------------------- This is happening because the initial predicate manipulation is combining the two predicates, but not performing a rewrite so downstream logic does not perform the pruning. > Teiid doesn't re-optimize select sub-nodes after adding new criteria > -------------------------------------------------------------------- > > Key: TEIID-5959 > URL: https://issues.redhat.com/browse/TEIID-5959 > Project: Teiid > Issue Type: Quality Risk > Components: Query Engine > Affects Versions: 13.1 > Reporter: Dmitrii Pogorelov > Assignee: Steven Hawkins > Priority: Major > > It looks like Teiid doesn't re-optimize union nodes after adding new criteria to their sub-select nodes. For example, for the query (the user has only role1): > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > {code} > Teiid will generate the following Query plan: > {code:xml} > AccessNode(0) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 LIMIT 100 > {code} > what is correct because the user has only role1 and Teiid optimizes union removing the first sub-query as its condition in total is false. > But for this query: > {code:sql} > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} > Teiid will propagate the "a = 1" condition in all union sub-queries and generate the following query plan: > {code:xml} > LimitNode(0) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] limit 100 > UnionAllNode(1) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] > AccessNode(2) output=[test_tables_pg.test_a.a, test_tables_pg.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > AccessNode(3) output=[test_tables_pg2.test_a.a, test_tables_pg2.test_a.b] SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg2.test_a AS g_0 WHERE g_0.a = 1 LIMIT 100 > {code} > what is incorrect as the > {code:xml} > SELECT g_0.a AS c_0, g_0.b AS c_1 FROM test_tables_pg.test_a AS g_0 WHERE (FALSE = TRUE) AND (g_0.a = 1) LIMIT 100 > {code} > still has false condition and logically the query plan for the second query should be the same as for the first one, though it doesn't have an influence on results. > I don't know whether it's a bug it not. That's why I created the issue with the "Quality Risk" type. > Steps to reproduce: > 1. In PostgreSQL create the test_a table using the following scripts: > {code:sql} > CREATE TABLE public.test_a > ( > a integer, > b integer > ); > INSERT INTO public.test_a(a, b) VALUES (1, 1); > INSERT INTO public.test_a(a, b) VALUES (1, 2); > INSERT INTO public.test_a(a, b) VALUES (2, 1); > INSERT INTO public.test_a(a, b) VALUES (2, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 2); > INSERT INTO public.test_a(a, b) VALUES (3, 10); > {code} > 2. Add PostgreSQL database configuration in standalone-teiid.xml: > {code:xml} > <datasource jndi-name="java:/test_tables_pg" pool-name="test_tables_pg" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </validation> > <timeout> > <blocking-timeout-millis>120000</blocking-timeout-millis> > <idle-timeout-minutes>5</idle-timeout-minutes> > </timeout> > </datasource> > <datasource jndi-name="java:/test_tables_pg2" pool-name="test_tables_pg2" enabled="true" use-java-context="true"> > <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> > <driver-class>org.postgresql.Driver</driver-class> > <driver>org.postgresql</driver> > <pool> > <min-pool-size>2</min-pool-size> > <max-pool-size>70</max-pool-size> > <prefill>false</prefill> > <use-strict-min>false</use-strict-min> > <flush-strategy>FailingConnectionOnly</flush-strategy> > </pool> > <security> > <user-name>XXXXX</user-name> > <password>XXXXX</password> > </security> > <validation> > <check-valid-connection-sql>select 0</check-valid-connection-sql> > </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_tables_pg configured in the previous step as a data source: > {code:xml} > <model name="test_tables_pg"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg" translator-name="myPg" connection-jndi-name="java:/test_tables_pg"/> > </model> > <model name="test_tables_pg2"> > <property name="importer.useFullSchemaName" value="false"/> > <property name="importer.tableTypes" value="TABLE,VIEW"/> > <property name="importer.importKeys" value="false"/> > <source name="test_tables_pg2" translator-name="myPg" connection-jndi-name="java:/test_tables_pg2"/> > </model> > {code} > 4. add the "role1" configuration: > {code:xml} > <data-role name="role1" any-authenticated="true" allow-create-temporary-tables="true"> > <description>Allow read only</description> > <permission> > <resource-name>test_tables_pg2.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > <permission> > <resource-name>test_tables_pg.test_a</resource-name> > <allow-read>true</allow-read> > <allow-execute>true</allow-execute> > </permission> > </data-role> > {code} > 5. run the following queries and compare their query plans: > {code:sql} > -- QUERY 1 > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1') ;; > select hasRole('data','report-role') ;; > select hasRole('role1') ;; > -- QUERY 2 (adds a where condition) > select * from ( > select * from "test_tables_pg.test_a" where hasRole('report-role') > union all > select * from "test_tables_pg2.test_a" where hasRole('role1')) a > where a = 1 ;; > {code} -- This message was sent by Atlassian Jira (v7.13.8#713008)

4 years, 6 months

1
0
0 / 0

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues May 2020