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

Oracle - table alias is not used in whole query

    Details

      Description

      When I run query like [1] Teiid translates it to query which does not use one of table aliases in whole query. Oracle (11g 11.2.0.2.0) seems to be sensitive to aliasing table and then not using it. Query fails. Source specific query (problematic one) [2].

      Sample query which fails if running directly against Oracle database [3]

      [1] Query

      select
        s_name,
        s_address
      from
        tpch1.soaeds.supplier,
        tpch1.soaeds.nation
      where
        s_supplierkey in (
          select
            ps_supplierkey
          from
            tpch2.soaeds.partsupp
          where
            ps_partkey in (
              select
                p_partkey
              from
                tpch2.soaeds.part
              where
                p_name like 'powder%'
            )
            and ps_availqty > (
              select
                0.5 * sum(l_quantity)
              from
                tpch2.soaeds.lineitem
              where
                l_partkey = ps_partkey
                and l_supplierkey = ps_supplierkey
                and l_shipdate >=  '1994-01-01'
                and l_shipdate <  TIMESTAMPADD(SQL_TSI_YEAR,'1', '1994-01-01')
            )
        )
        and s_nationkey = n_nationkey
        and n_name = 'BRAZIL'
      order by
        s_name;
      

      [2] Source-specific query

      SELECT g_0."PS_SUPPLIERKEY" AS c_0 FROM "SOAEDS"."PARTSUPP" g_0 WHERE g_0."PS_PARTKEY" IN (SELECT g_1."P_PARTKEY" FROM "SOAEDS"."PART" g_1 WHERE g_1."P_NAME" LIKE 'powder%') AND g_0."PS_AVAILQTY" > (SELECT (0.5 * SUM(g_2."L_QUANTITY")) FROM "SOAEDS"."LINEITEM" g_2 WHERE g_2."L_PARTKEY" = "SOAEDS"."PARTSUPP"."PS_PARTKEY" AND g_2."L_SUPPLIERKEY" = "SOAEDS"."PARTSUPP"."PS_SUPPLIERKEY" AND g_2."L_SHIPDATE" >= to_date('1994-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND g_2."L_SHIPDATE" < ?) ORDER BY c_0
      

      [3] Oracle query

      SELECT smalla.intkey FROM smalla a
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                jdurani Juraj Duráni
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: