Skip to Main Content
  • Questions
  • Using a view with table/cast/multiset within a cte utilizing a union statement returns null

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Keith.

Asked: March 20, 2017 - 1:01 pm UTC

Last updated: April 11, 2017 - 12:36 am UTC

Version: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Viewed 1000+ times

You Asked

Trying to utilize TABLE/CAST/MULTISET in Ora11 to duplicate some functionality from converted SQL using outer apply. Since Oracle doesn't have that in version 11 I defined an object and table of said object, cross join it and basically get the same thing. Then when I use that view in a cte with a union all I get NULL instead of the values. I broke it down to the flaw and is duplicated with the enclosed. Obviously what I'm trying to do is a lot more complex but for testing purposes this is as minimalistic as I can make it.

I did work around it by changing the table/cast/multiset inline and do have a work around but, this shouldn't happen.

CREATE TYPE obj AS OBJECT(fld CHAR(1))
/
CREATE TYPE tbl AS TABLE OF obj
/
CREATE VIEW vwTblCstMset AS
  SELECT dummy, tblCstMset.fld
  FROM dual
  CROSS JOIN TABLE(CAST(MULTISET(
    SELECT CASE dual.dummy WHEN 'X' THEN 'Y' ELSE 'N' END fld
    FROM dual ign
  ) AS tbl )) tblCstmset
/

WITH cte AS (
  SELECT *
  FROM vwTblCstMset
)
SELECT * FROM cte
/

WITH cte AS (
  SELECT *
  FROM vwTblCstMset
)
SELECT * FROM cte UNION ALL
SELECT * FROM cte
/

DROP VIEW vwTblCstMset
/
DROP TYPE tbl
/
DROP TYPE obj
/


I'm trying to get the results of fld to be consistently returned. The first query returns Y as expected but when union all (or union) is used NULL is returned. Is this an optimizer bug or incorrect usage?

with LiveSQL Test Case:

and Chris said...

Yes, this looks like a bug, I can see this up to 12.2:

SQL> set null <null>
SQL> col fld format a10
SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                                0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

SQL>
SQL> CREATE TYPE obj AS OBJECT(fld CHAR(1))
  2  /

Type created.

SQL> CREATE TYPE tbl AS TABLE OF obj
  2  /

Type created.

SQL> CREATE VIEW vwTblCstMset AS
  2    SELECT dummy, tblCstMset.fld
  3    FROM dual
  4    CROSS JOIN TABLE(CAST(MULTISET(
  5      SELECT CASE dual.dummy WHEN 'X' THEN 'Y' ELSE 'N' END fld
  6      FROM dual ign
  7    ) AS tbl )) tblCstmset
  8  /

View created.

SQL>
SQL> WITH cte AS (
  2    SELECT *
  3    FROM vwTblCstMset
  4  )
  5  SELECT * FROM cte
  6  /

D FLD
- ----------
X Y

SQL>
SQL> WITH cte AS (
  2    SELECT *
  3    FROM vwTblCstMset
  4  )
  5  SELECT * FROM cte UNION ALL SELECT * FROM cte
  6  /

D FLD
- ----------
X <null>
X <null>


I'm not sure what you're trying to achieve by using "cross join table cast multiset" though. If you provide more details about what you're trying to do with your query we may be able to help find a solution avoiding this issue.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Keeping it DRYer

Keith, March 22, 2017 - 1:10 pm UTC

I was converting SQL Server code where they're using cross apply.

Since I was using Oracle 11 I couldn't use outer apply and found that cross join table cast multiset did basically the same thing I was after. It was easier for me to define the table type and inline the query. I was able to work around this one with putting the conditions directly in the select portion but reviewing some of the other code it looks like I'll have to build functions to return tables and see where that gets me. The database I'm working with is using a lot of CROSS APPLY (SELECT TOP 1 ORDER BY key DESC) lookup because they want the last record. Unfortunately there would be a lot of DDL and data examples beyond what I can put together for a more valid reason but I appreciate the offer.

tip

Ghassan, March 22, 2017 - 4:33 pm UTC

using the inline hint will work thus;

WITH cte AS (
SELECT /*+ inline */ *
FROM vwTblCstMset
)
SELECT * FROM cte
UNION ALL SELECT * FROM cte
;

DUMMY FLD
----- ---
X Y
X Y

Any follow up?

A reader, April 10, 2017 - 2:27 pm UTC


Connor McDonald
April 11, 2017 - 12:36 am UTC

Sorry - not sure what you're after.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.