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

ORDER BY combined with UNION/UNION ALL fails on PostgreSQL

    Details

    • Steps to Reproduce:
      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.

      Description

      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
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                sameerp sameer P
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: