Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5437

ORDER BY combined with UNION/UNION ALL fails on PostgreSQL

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Critical Critical
    • 11.1, 10.3.4, 11.0.2
    • None
    • Query Engine
    • None
    • Hide

      1. Add postgres data sources dsp with table test_ep_ds with the column str(varchar) , i (integer) and dwh with table test_ep_dwh and a column str(varchar).
      2. Run the query which is mentioned as fails.

      Show
      1. Add postgres data sources dsp with table test_ep_ds with the column str(varchar) , i (integer) and dwh with table test_ep_dwh and a column str(varchar). 2. Run the query which is mentioned as fails.

      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,

      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

      Fails with the below errors:

      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

      Where as the following query works without any problem:

       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

              rhn-engineering-shawkins Steven Hawkins
              sameerp_jira sameer P (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: