[teiid-issues] [JBoss JIRA] (TEIID-5535) CTE: Inconsistent Error Message when SELECTing a View Instead of its Definition
dalex dalex (Jira)
issues at jboss.org
Thu Nov 8 12:09:00 EST 2018
[ https://issues.jboss.org/browse/TEIID-5535?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
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)
More information about the teiid-issues
mailing list