[JBoss JIRA] (TEIID-5532) Common table projection minimization does not account for usage in a subquery nested in an aggregate
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5532?page=com.atlassian.jira.plugin... ]
Steven Hawkins reopened TEIID-5532:
-----------------------------------
> Common table projection minimization does not account for usage in a subquery nested in an aggregate
> ----------------------------------------------------------------------------------------------------
>
> Key: TEIID-5532
> URL: https://issues.jboss.org/browse/TEIID-5532
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 11.0.3, 11.1.2, 12.0, 11.2.1
>
>
> Nesting the usage of a common table in an aggregate means that the columns can be removed inappropriately by projection minimization. A query such as:
> with CTE1 as /*+ no_inline */ (SELECT e1, e2, e3 from pm1.g1)
> select array_agg((select e3 from cte1 where e1=pm1.g2.e1 and e2=pm1.g2.e2)) from pm1.g2
> will return null values for all e3 as projection minimization of CTE1 will replace the column with a null value.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
[JBoss JIRA] (TEIID-5535) CTE: Inconsistent Error Message when SELECTing a View Instead of its Definition
by dalex dalex (Jira)
[ https://issues.jboss.org/browse/TEIID-5535?page=com.atlassian.jira.plugin... ]
dalex dalex updated TEIID-5535:
-------------------------------
Steps to Reproduce:
1. Create several empty tables based on the code below in postgreSQL (in my case I created them in "test_dwh" database):
{code:sql}
CREATE TABLE public.tab1
(
name character varying(4000)
);
CREATE TABLE public.tab2
(
course_id integer
);
CREATE TABLE public.tab3
(
course_id bigint
);
{code}
2. Add postgresql (in my example I use "test_dwh" database) database configurations in standalone-teiid.xml:
{code:xml}
<datasource jndi-name="java:/test_dwh_pg" pool-name="test_dwh_pg" enabled="true" use-java-context="true">
<connection-url>jdbc:postgresql://localhost:5432/test_dwh?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>postgres</user-name>
<password>xxxxxx</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_dwh_pg configured in previous step as data source:
{code:xml}
<model name="dwh">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false"/>
<source name="test_dwh_pg" translator-name="myPg" connection-jndi-name="java:/test_dwh_pg"/>
</model>
<translator name="myPg" type="postgresql">
<property name="SupportsNativeQueries" value="true"/>
</translator>
{code}
4. Add in test-vdb.xml the virtual "instructor_statement_2_3" view:
{code:xml}
<model visible = "true" type = "VIRTUAL" name = "views">
<metadata type = "DDL"><![CDATA[
create view instructor_statement_2_3 as
with base_data as (
select
instructor_payment.course_id
from dwh.tab3 as instructor_payment
)
,adjustments as (
select
course_id
from dwh.tab2
)
,union_data as (
select
course_id
from base_data
union
select
course_id
from base_data
union
select
course_id
from adjustments
)
,line_items as (
select
dim_playlist.name as playlist_name
from union_data
left join dwh.tab1 as dim_playlist on true
)
,sub_totals as (
select
playlist_name
from line_items
)
select
line_items.playlist_name
from line_items
union
select
sub_totals.playlist_name
from sub_totals
]]>
</metadata>
</model>
{code}
5. Run the following query:
{code:sql}
select * from views.instructor_statement_2_3 ;;
{code}
which will fail with the following stack trace (but if I run the query second time it will work):
{code:noformat}
2018-11-08 17:45:35,858 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue6) HBMErVHDTEsV Connector worker process failed for atomic-request=HBMErVHDTEsV.0.2.0: org.teiid.trans
lator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH base_data (course_id) AS (SELECT NULL FROM "public"."tab3" AS
g_0), line_items (playlist_name) AS (SELECT g_3."name" FROM (SELECT g_2.course_id AS c_0 FROM base_data AS g_2 UNION SELECT g_1.course_id AS c_0 FROM base_data AS g_1 UNION SELECT ca
st(g_0."course_id" AS bigint) AS c_0 FROM "public"."tab2" AS g_0) AS v_0 LEFT OUTER JOIN "public"."tab1" AS g_3 ON 1 = 1) SELECT g_1.playlist_name AS c_0 FROM line_items AS g_1 UNION
SELECT g_0.playlist_name AS c_0 FROM line_items AS g_0 LIMIT 100]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
at com.sun.proxy.$Proxy36.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException-FEHLER: UNION-Typen text und bigint passen nicht zusammen
Position: 248
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119)
... 18 more
{code}
If I run select against definition of the view it will work:
{code:sql}
with base_data as (
select
instructor_payment.course_id
from dwh.tab3 as instructor_payment) ,adjustments as (
select
course_id
from dwh.tab2
)
,union_data as (
select
course_id
from base_data
union
select
course_id
from base_data
union
select
course_id
from adjustments
)
,line_items as (
select
dim_playlist.name as playlist_name
from union_data
left join dwh.tab1 as dim_playlist on true
)
,sub_totals as (
select
playlist_name
from line_items
)
select
line_items.playlist_name
from line_items
union
select
sub_totals.playlist_name
from sub_totals ;;
{code}
was:
1. Create several empty tables based on the code below in postgreSQL (in my case I created them in "test_dwh" database):
{code:sql}
CREATE TABLE public.tab1
(
name character varying(4000)
);
CREATE TABLE public.tab2
(
course_id integer
);
CREATE TABLE public.tab3
(
course_id bigint
);
{code}
2. Add postgresql (in my example I use "test_dwh" database) database configurations in standalone-teiid.xml:
{code:xml}
<datasource jndi-name="java:/test_dwh_pg" pool-name="test_dwh_pg" enabled="true" use-java-context="true">
<connection-url>jdbc:postgresql://localhost:5432/test_dwh?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>postgres</user-name>
<password>xxxxxx</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_dwh_pg configured in previous step as data source:
{code:xml}
<model name="dwh">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false"/>
<source name="test_dwh_pg" translator-name="myPg" connection-jndi-name="java:/test_dwh_pg"/>
</model>
<translator name="myPg" type="postgresql">
<property name="SupportsNativeQueries" value="true"/>
</translator>
{code}
4. Add in test-vdb.xml the virtual "instructor_statement_2_3" view:
{code:xml}
<model visible = "true" type = "VIRTUAL" name = "views">
<metadata type = "DDL"><![CDATA[
create view instructor_statement_2_3 as
with base_data as (
select
instructor_payment.course_id
from dwh.tab3 as instructor_payment
)
,adjustments as (
select
course_id
from dwh.tab2
)
,union_data as (
select
course_id
from base_data
union
select
course_id
from base_data
union
select
course_id
from adjustments
)
,line_items as (
select
dim_playlist.name as playlist_name
from union_data
left join dwh.tab1 as dim_playlist on true
)
,sub_totals as (
select
playlist_name
from line_items
)
select
line_items.playlist_name
from line_items
union
select
sub_totals.playlist_name
from sub_totals
]]>
</metadata>
</model>
{code}
5. Run the following query:
{code:sql}
select * from views.instructor_statement_2_3 ;;
{code}
which will fail with the following stack trace (but if I run the query second time it will work):
{code:noformat}
2018-11-08 17:45:35,858 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue6) HBMErVHDTEsV Connector worker process failed for atomic-request=HBMErVHDTEsV.0.2.0: org.teiid.trans
lator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH base_data (course_id) AS (SELECT NULL FROM "public"."tab3" AS
g_0), line_items (playlist_name) AS (SELECT g_3."name" FROM (SELECT g_2.course_id AS c_0 FROM base_data AS g_2 UNION SELECT g_1.course_id AS c_0 FROM base_data AS g_1 UNION SELECT ca
st(g_0."course_id" AS bigint) AS c_0 FROM "public"."tab2" AS g_0) AS v_0 LEFT OUTER JOIN "public"."tab1" AS g_3 ON 1 = 1) SELECT g_1.playlist_name AS c_0 FROM line_items AS g_1 UNION
SELECT g_0.playlist_name AS c_0 FROM line_items AS g_0 LIMIT 100]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
at com.sun.proxy.$Proxy36.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException-FEHLER: UNION-Typen text und bigint passen nicht zusammen
Позиция: 248
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119)
... 18 more
{code}
If I run select against definition of the view it will work:
{code:sql}
with base_data as (
select
instructor_payment.course_id
from dwh.tab3 as instructor_payment) ,adjustments as (
select
course_id
from dwh.tab2
)
,union_data as (
select
course_id
from base_data
union
select
course_id
from base_data
union
select
course_id
from adjustments
)
,line_items as (
select
dim_playlist.name as playlist_name
from union_data
left join dwh.tab1 as dim_playlist on true
)
,sub_totals as (
select
playlist_name
from line_items
)
select
line_items.playlist_name
from line_items
union
select
sub_totals.playlist_name
from sub_totals ;;
{code}
> CTE: Inconsistent Error Message when SELECTing a View Instead of its Definition
> -------------------------------------------------------------------------------
>
> Key: TEIID-5535
> URL: https://issues.jboss.org/browse/TEIID-5535
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 11.1
> Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Critical
>
> There are queries involving CTEs and set operators that can be executed and return results.
> However, creating a view based on the working queries makes these views unusable as they return an error message.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
[JBoss JIRA] (TEIID-5535) CTE: Inconsistent Error Message when SELECTing a View Instead of its Definition
by dalex dalex (Jira)
[ https://issues.jboss.org/browse/TEIID-5535?page=com.atlassian.jira.plugin... ]
dalex dalex updated TEIID-5535:
-------------------------------
Steps to Reproduce:
1. Create several empty tables based on the code below in postgreSQL (in my case I created them in "test_dwh" database):
{code:sql}
CREATE TABLE public.tab1
(
name character varying(4000)
);
CREATE TABLE public.tab2
(
course_id integer
);
CREATE TABLE public.tab3
(
course_id bigint
);
{code}
2. Add postgresql (in my example I use "test_dwh" database) database configurations in standalone-teiid.xml:
{code:xml}
<datasource jndi-name="java:/test_dwh_pg" pool-name="test_dwh_pg" enabled="true" use-java-context="true">
<connection-url>jdbc:postgresql://localhost:5432/test_dwh?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>postgres</user-name>
<password>xxxxxx</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_dwh_pg configured in previous step as data source:
{code:xml}
<model name="dwh">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false"/>
<source name="test_dwh_pg" translator-name="myPg" connection-jndi-name="java:/test_dwh_pg"/>
</model>
<translator name="myPg" type="postgresql">
<property name="SupportsNativeQueries" value="true"/>
</translator>
{code}
4. Add in test-vdb.xml the virtual "instructor_statement_2_3" view:
{code:xml}
<model visible = "true" type = "VIRTUAL" name = "views">
<metadata type = "DDL"><![CDATA[
create view instructor_statement_2_3 as
with base_data as (
select
instructor_payment.course_id
from dwh.tab3 as instructor_payment
)
,adjustments as (
select
course_id
from dwh.tab2
)
,union_data as (
select
course_id
from base_data
union
select
course_id
from base_data
union
select
course_id
from adjustments
)
,line_items as (
select
dim_playlist.name as playlist_name
from union_data
left join dwh.tab1 as dim_playlist on true
)
,sub_totals as (
select
playlist_name
from line_items
)
select
line_items.playlist_name
from line_items
union
select
sub_totals.playlist_name
from sub_totals
]]>
</metadata>
</model>
{code}
5. Run the following query:
{code:sql}
select * from views.instructor_statement_2_3 ;;
{code}
which will fail with the following stack trace (but if I run the query second time it will work):
{code:noformat}
2018-11-08 17:45:35,858 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue6) HBMErVHDTEsV Connector worker process failed for atomic-request=HBMErVHDTEsV.0.2.0: org.teiid.trans
lator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH base_data (course_id) AS (SELECT NULL FROM "public"."tab3" AS
g_0), line_items (playlist_name) AS (SELECT g_3."name" FROM (SELECT g_2.course_id AS c_0 FROM base_data AS g_2 UNION SELECT g_1.course_id AS c_0 FROM base_data AS g_1 UNION SELECT ca
st(g_0."course_id" AS bigint) AS c_0 FROM "public"."tab2" AS g_0) AS v_0 LEFT OUTER JOIN "public"."tab1" AS g_3 ON 1 = 1) SELECT g_1.playlist_name AS c_0 FROM line_items AS g_1 UNION
SELECT g_0.playlist_name AS c_0 FROM line_items AS g_0 LIMIT 100]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
at com.sun.proxy.$Proxy36.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException-FEHLER: UNION-Typen text und bigint passen nicht zusammen
Позиция: 248
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119)
... 18 more
{code}
If I run select against definition of the view it will work:
{code:sql}
with base_data as (
select
instructor_payment.course_id
from dwh.tab3 as instructor_payment) ,adjustments as (
select
course_id
from dwh.tab2
)
,union_data as (
select
course_id
from base_data
union
select
course_id
from base_data
union
select
course_id
from adjustments
)
,line_items as (
select
dim_playlist.name as playlist_name
from union_data
left join dwh.tab1 as dim_playlist on true
)
,sub_totals as (
select
playlist_name
from line_items
)
select
line_items.playlist_name
from line_items
union
select
sub_totals.playlist_name
from sub_totals ;;
{code}
was:
1. Create several empty tables based on the code below in postgreSQL:
{code:sql}
CREATE TABLE public.tab1
(
name character varying(4000)
);
CREATE TABLE public.tab2
(
course_id integer
);
CREATE TABLE public.tab3
(
course_id bigint
);
{code}
2. Add postgresql (in my example I use "test_dwh" database) database configurations in standalone-teiid.xml:
{code:xml}
<datasource jndi-name="java:/test_dwh_pg" pool-name="test_dwh_pg" enabled="true" use-java-context="true">
<connection-url>jdbc:postgresql://localhost:5432/test_dwh?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>postgres</user-name>
<password>xxxxxx</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_dwh_pg configured in previous step as data source:
{code:xml}
<model name="dwh">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false"/>
<source name="test_dwh_pg" translator-name="myPg" connection-jndi-name="java:/test_dwh_pg"/>
</model>
<translator name="myPg" type="postgresql">
<property name="SupportsNativeQueries" value="true"/>
</translator>
{code}
4. Add in test-vdb.xml the virtual "instructor_statement_2_3" view:
{code:xml}
<model visible = "true" type = "VIRTUAL" name = "views">
<metadata type = "DDL"><![CDATA[
create view instructor_statement_2_3 as
with base_data as (
select
instructor_payment.course_id
from dwh.tab3 as instructor_payment
)
,adjustments as (
select
course_id
from dwh.tab2
)
,union_data as (
select
course_id
from base_data
union
select
course_id
from base_data
union
select
course_id
from adjustments
)
,line_items as (
select
dim_playlist.name as playlist_name
from union_data
left join dwh.tab1 as dim_playlist on true
)
,sub_totals as (
select
playlist_name
from line_items
)
select
line_items.playlist_name
from line_items
union
select
sub_totals.playlist_name
from sub_totals
]]>
</metadata>
</model>
{code}
5. Run the following query:
{code:sql}
select * from views.instructor_statement_2_3 ;;
{code}
which will fail with the following stack trace (but if I run the query second time it will work):
{code:noformat}
2018-11-08 17:45:35,858 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue6) HBMErVHDTEsV Connector worker process failed for atomic-request=HBMErVHDTEsV.0.2.0: org.teiid.trans
lator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH base_data (course_id) AS (SELECT NULL FROM "public"."tab3" AS
g_0), line_items (playlist_name) AS (SELECT g_3."name" FROM (SELECT g_2.course_id AS c_0 FROM base_data AS g_2 UNION SELECT g_1.course_id AS c_0 FROM base_data AS g_1 UNION SELECT ca
st(g_0."course_id" AS bigint) AS c_0 FROM "public"."tab2" AS g_0) AS v_0 LEFT OUTER JOIN "public"."tab1" AS g_3 ON 1 = 1) SELECT g_1.playlist_name AS c_0 FROM line_items AS g_1 UNION
SELECT g_0.playlist_name AS c_0 FROM line_items AS g_0 LIMIT 100]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
at com.sun.proxy.$Proxy36.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException-FEHLER: UNION-Typen text und bigint passen nicht zusammen
Позиция: 248
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119)
... 18 more
{code}
If I run select against definition of the view it will work:
{code:sql}
with base_data as (
select
instructor_payment.course_id
from dwh.tab3 as instructor_payment) ,adjustments as (
select
course_id
from dwh.tab2
)
,union_data as (
select
course_id
from base_data
union
select
course_id
from base_data
union
select
course_id
from adjustments
)
,line_items as (
select
dim_playlist.name as playlist_name
from union_data
left join dwh.tab1 as dim_playlist on true
)
,sub_totals as (
select
playlist_name
from line_items
)
select
line_items.playlist_name
from line_items
union
select
sub_totals.playlist_name
from sub_totals ;;
{code}
> CTE: Inconsistent Error Message when SELECTing a View Instead of its Definition
> -------------------------------------------------------------------------------
>
> Key: TEIID-5535
> URL: https://issues.jboss.org/browse/TEIID-5535
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 11.1
> Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Critical
>
> There are queries involving CTEs and set operators that can be executed and return results.
> However, creating a view based on the working queries makes these views unusable as they return an error message.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
[JBoss JIRA] (TEIID-5535) CTE: Inconsistent Error Message when SELECTing a View Instead of its Definition
by dalex dalex (Jira)
dalex dalex created TEIID-5535:
----------------------------------
Summary: CTE: Inconsistent Error Message when SELECTing a View Instead of its Definition
Key: TEIID-5535
URL: https://issues.jboss.org/browse/TEIID-5535
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 11.1
Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
Reporter: dalex dalex
Assignee: Steven Hawkins
There are queries involving CTEs and set operators that can be executed and return results.
However, creating a view based on the working queries makes these views unusable as they return an error message.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
[JBoss JIRA] (TEIID-5534) Smaller initial memory buffer allocations
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5534?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5534:
----------------------------------
Fix Version/s: 11.2.1
> Smaller initial memory buffer allocations
> -----------------------------------------
>
> Key: TEIID-5534
> URL: https://issues.jboss.org/browse/TEIID-5534
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 12.0, 11.2.1
>
>
> For smaller vms, under 16 GB, the default allocation scheme for on-heap fixed memory buffers is in 1 GB increments.
> This can be quite large for VMs under 8 GBs - especially if the full commit of that memory is not needed.
> The allocation scheme should introduce the buffers more incrementally and release them when compaction is run if they aren't needed.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
[JBoss JIRA] (TEIID-5534) Smaller initial memory buffer allocations
by Steven Hawkins (Jira)
Steven Hawkins created TEIID-5534:
-------------------------------------
Summary: Smaller initial memory buffer allocations
Key: TEIID-5534
URL: https://issues.jboss.org/browse/TEIID-5534
Project: Teiid
Issue Type: Quality Risk
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 12.0
For smaller vms, under 16 GB, the default allocation scheme for on-heap fixed memory buffers is in 1 GB increments.
This can be quite large for VMs under 8 GBs - especially if the full commit of that memory is not needed.
The allocation scheme should introduce the buffers more incrementally and release them when compaction is run if they aren't needed.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
[JBoss JIRA] (TEIID-5532) Common table projection minimization does not account for usage in a subquery nested in an aggregate
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5532?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5532.
-----------------------------------
Resolution: Done
The fixes uses the original command to look for the columns used by from the common tables as the command passed to the planning methods gets modified.
> Common table projection minimization does not account for usage in a subquery nested in an aggregate
> ----------------------------------------------------------------------------------------------------
>
> Key: TEIID-5532
> URL: https://issues.jboss.org/browse/TEIID-5532
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 11.0.3, 11.1.2, 12.0, 11.2.1
>
>
> Nesting the usage of a common table in an aggregate means that the columns can be removed inappropriately by projection minimization. A query such as:
> with CTE1 as /*+ no_inline */ (SELECT e1, e2, e3 from pm1.g1)
> select array_agg((select e3 from cte1 where e1=pm1.g2.e1 and e2=pm1.g2.e2)) from pm1.g2
> will return null values for all e3 as projection minimization of CTE1 will replace the column with a null value.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
[JBoss JIRA] (TEIID-5533) Improve odbc index metadata query performance
by Steven Hawkins (Jira)
Steven Hawkins created TEIID-5533:
-------------------------------------
Summary: Improve odbc index metadata query performance
Key: TEIID-5533
URL: https://issues.jboss.org/browse/TEIID-5533
Project: Teiid
Issue Type: Quality Risk
Components: ODBC
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 12.0
For a large number of tables/columns the query for pg_index takes an inordinate amount of time to complete - for releases after 10.1.1 this will happen only for the initial load.
The structure of the query should be improved as well as a managed materialization load to better ensure that it does not block a user query.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
[JBoss JIRA] (TEIID-5532) Common table projection minimization does not account for usage in a subquery nested in an aggregate
by Steven Hawkins (Jira)
Steven Hawkins created TEIID-5532:
-------------------------------------
Summary: Common table projection minimization does not account for usage in a subquery nested in an aggregate
Key: TEIID-5532
URL: https://issues.jboss.org/browse/TEIID-5532
Project: Teiid
Issue Type: Bug
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 11.0.3, 11.1.2, 12.0, 11.2.1
Nesting the usage of a common table in an aggregate means that the columns can be removed inappropriately by projection minimization. A query such as:
with CTE1 as /*+ no_inline */ (SELECT e1, e2, e3 from pm1.g1)
select array_agg((select e3 from cte1 where e1=pm1.g2.e1 and e2=pm1.g2.e2)) from pm1.g2
will return null values for all e3 as projection minimization of CTE1 will replace the column with a null value.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
[JBoss JIRA] (TEIID-5531) Implicit indexing of common tables doesn't consider composite keys
by Steven Hawkins (Jira)
Steven Hawkins created TEIID-5531:
-------------------------------------
Summary: Implicit indexing of common tables doesn't consider composite keys
Key: TEIID-5531
URL: https://issues.jboss.org/browse/TEIID-5531
Project: Teiid
Issue Type: Quality Risk
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 12.0
Using a common table that is referenced by a common predicate will create an implicit index, but only for the first attribute - in the case of a composite key a multi-column index will only be created if an array/row value comparison is used.
For example:
with (x) as (...)
select (... from x where x.col1 = y.col1 and x.col2 = y.col2) ... from y
should add an index on x using both col1 and col2.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months