[teiid-issues] [JBoss JIRA] (TEIID-5437) ORDER BY combined with UNION/UNION ALL fails on PostgreSQL

[sameer P (JIRA)]

sameer P created TEIID-5437:
-------------------------------

             Summary: ORDER BY combined with UNION/UNION ALL fails on PostgreSQL
                 Key: TEIID-5437
                 URL: https://issues.jboss.org/browse/TEIID-5437
             Project: Teiid
          Issue Type: Bug
          Components: Connector API, Grammar, Query Engine
            Reporter: sameer P
            Assignee: Barry LaFond
            Priority: Blocker


Combining union all and order by does not work when the query is pushed down to PostgreSQL.

The following query with dsp and dwh as postgres datasources,

{code:sql}
select * from (
    select b.i as col1, 'def' as col2 from dsp.test_ep_ds a
    left join  dwh.test_ep_dwh b on b.str=a.str    
    union all
    select 2 as col1, 'abc' as col2
)x order by col1
{code}

Fails with the below errors:

{code:java}
Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 dsp_VDB_ID_1: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."str" AS c_0 FROM "public"."test_ep_ds" AS g_0 ORDER BY b."i" LIMIT 100]
SQLState:  50000
ErrorCode: 30504
{code}

Where as the following query works without any problem:

{code:sql}
 select b.i as col1, 'def' as col2 from dsp.test_ep_ds a
    left join  dwh.test_ep_dwh b on b.str=a.str    
    union all
    select 2 as col1, 'abc' as col2
{code}




--
This message was sent by Atlassian JIRA
(v7.5.0#75005)