title: Unexpected result of multiset mapping in Oracle SQL
body: <p>Please help me to confirm is that behavior explained below is a bug, or clearly explain why it's right.
  There are a high probability that I misunderstood some concept, but now for me it looks like a bug.</p>
  <p>All examples below simplified as much as possible to demonstrate core of the issue. Real situation is very complex, so only general answers and workarounds related to principle of query construction is acceptable.<br />
  You are welcome to ask clarifying questions in comments and i'll try to do my best to answer them.<br />
  Thank you for attention. :)</p>
  <h2>Question</h2>
  <p>Why in last Example (Example 5) <code>collection</code> instance in  <code>(select count(1) ...</code> subquery from first row mapped to all rows of the table, while expected result is to map each <code>collection</code> instance to it's own row?<br />
  At the same time <code>collections</code> used in <code>cardinality(...)</code> expression chosen properly.<br />
  Same situation (not covered in examples) exists if constructed in this way collections used in <code>from</code> or <code>where</code> part of a query.</p>
  <h2>Test schema setup</h2>
  <p>(<a href="http://www.sqlfiddle.com/#!4/ea363/50" rel="nofollow noreferrer">SQLFiddle</a>)</p>
  <pre><code>create or replace type TabType0 as table of varchar2(100)
  /
  
  create table Table0( tab_str_field varchar2(100), tab_field TabType0)
  nested table tab_field store as tab_field_table
  /
  
  insert into table0 (tab_str_field, tab_field) values (
     'A',
     cast(multiset(
       select 'A' from dual union all
       select 'B' from dual union all
       select 'C' from dual 
     ) as TabType0) 
  )
  /
  
  insert into table0 (tab_str_field, tab_field) values (
     'B',
     cast(multiset(
       select 'B' from dual union all
       select 'C' from dual 
     ) as TabType0) 
  )
  /
  
  insert into table0 (tab_str_field, tab_field) values (
     'C',
     cast(multiset(
       select 'A' from dual union all
       select 'B' from dual union all
       select 'C' from dual union all
       select 'D' from dual 
     ) as TabType0) 
  )
  /
  
  insert into table0 (tab_str_field, tab_field) values (
     'D',
     cast(multiset(
       select 'A' from dual 
     ) as TabType0) 
  )
  /
  select 'Initial table data' caption from dual
  /
  select * from table0
  /
  </code></pre>
  <p>table data:</p>
  <pre><code>| TAB_STR_FIELD | TAB_FIELD |
  -----------------------------
  |             A |     A,B,C |
  |             B |       B,C |
  |             C |   A,B,C,D |
  |             D |         A |
  </code></pre>
  <h2>Examples</h2>
  <p><strong>Example 1</strong> (<a href="http://www.sqlfiddle.com/#!4/ea363/49" rel="nofollow noreferrer">SQLFiddle</a>)  - work with nested table fields - <strong>OK</strong></p>
  <pre><code>select 'Work with nested table - OK' caption from dual
  /
  select 
    tab_field                               tab_field,
  
    -- cardinality
    cardinality(tab_field)                  tab_cardinality,
  
    -- select from table field of current row
    (select count(1) from table(tab_field)) tab_count,
  
    -- select from field of current row while joining 
    -- with another field of same row
    ( select column_value from table(tab_field) 
      where column_value = tab_str_field
    )                                       same_value
  from table0
  /
  </code></pre>
  <p>results:</p>
  <pre><code>| TAB_FIELD | TAB_CARDINALITY | TAB_COUNT | SAME_VALUE |
  --------------------------------------------------------
  |     A,B,C |               3 |         3 |          A |
  |       B,C |               2 |         2 |          B |
  |   A,B,C,D |               4 |         4 |          C |
  |         A |               1 |         1 |     (null) |
  </code></pre>
  <p><strong>Example 2</strong> (<a href="http://www.sqlfiddle.com/#!4/ea363/51" rel="nofollow noreferrer">SQLFiddle</a>) - work with constructed source data alone - <strong>OK</strong></p>
  <pre><code>select 'Work with constructed source data alone - OK' caption from dual
  /
  with table_data_from_set as (
    select
      'A' tab_str_field,
      cast(multiset(
        select 'A' from dual union all
        select 'B' from dual union all
        select 'C' from dual
      ) as TabType0)  tab_field
    from dual union all
    select
      'B' tab_str_field,
      cast(multiset(
        select 'B' from dual union all
        select 'C' from dual
      ) as TabType0)  tab_field
    from dual union all
    select
      'C' tab_str_field,
      cast(multiset(
        select 'A' from dual union all
        select 'B' from dual union all
        select 'C' from dual union all
        select 'D' from dual
      ) as TabType0) tab_field
    from dual union all
    select
      'D' tab_str_field,
      cast(multiset(
        select 'A' from dual
      ) as TabType0) tab_field
    from dual
  )
  select
    tab_field                                tab_field,
  
    -- cardinality
    cardinality(tab_field)                   tab_cardinality,
  
    -- select from table field of current row
    (select count(1) from table(tab_field))  tab_count,
  
    -- select from field of current row while joining
    -- with another field of same row
    ( select column_value from table(tab_field)
      where column_value = tab_str_field
    )                                        same_value
  from table_data_from_set
  /
  </code></pre>
  <p>results:</p>
  <pre><code>| TAB_FIELD | TAB_CARDINALITY | TAB_COUNT | SAME_VALUE |
  --------------------------------------------------------
  |     A,B,C |               3 |         3 |          A |
  |       B,C |               2 |         2 |          B |
  |   A,B,C,D |               4 |         4 |          C |
  |         A |               1 |         1 |     (null) |
  </code></pre>
  <p><strong>Example 3</strong> (<a href="http://www.sqlfiddle.com/#!4/ea363/52" rel="nofollow noreferrer">SQLFiddle</a>) - join table with multisets constructed in <code>WITH</code> - <strong>OK</strong></p>
  <pre><code>select 'Join table with multisets constructed in WITH - OK' caption from dual
  /
  with table_data_from_set as (
    select
      'A' tab_str_field,
      cast(multiset(
        select 'A' from dual union all
        select 'B' from dual union all
        select 'C' from dual
      ) as TabType0)  tab_field
    from dual union all
    select
      'B' tab_str_field,
      cast(multiset(
        select 'B' from dual union all
        select 'C' from dual
      ) as TabType0)  tab_field
    from dual union all
    select
      'C' tab_str_field,
      cast(multiset(
        select 'A' from dual union all
        select 'B' from dual union all
        select 'C' from dual union all
        select 'D' from dual
      ) as TabType0) tab_field
    from dual union all
    select
      'D' tab_str_field,
      cast(multiset(
        select 'A' from dual
      ) as TabType0) tab_field
    from dual
  )
  select
    table0.tab_field                                            table0_tab_field,
    table_data_from_set.tab_field                               set_tab_field,
  
    -- cardinality
    cardinality(table0.tab_field) table0_tab_cardinality,
    cardinality(table_data_from_set.tab_field)                  set_tab_cardinality,
  
    -- select from table field of current row
    (select count(1) from table(table_data_from_set.tab_field)) set_tab_count,
  
    -- select from field of current row while joining
    -- with another field of same row
    ( select column_value from table(table_data_from_set.tab_field)
      where column_value = table0.tab_str_field
    )                                                           same_value
  from 
    table0, 
    table_data_from_set 
  where 
    table_data_from_set.tab_str_field = table0.tab_str_field
  /
  </code></pre>
  <p>results:</p>
  <pre><code>| TABLE0_TAB_FIELD | SET_TAB_FIELD | TABLE0_TAB_CARDINALITY | SET_TAB_CARDINALITY | SET_TAB_COUNT | SAME_VALUE |
  ----------------------------------------------------------------------------------------------------------------
  |            A,B,C |         A,B,C |                      3 |                   3 |             3 |          A |
  |              B,C |           B,C |                      2 |                   2 |             2 |          B |
  |          A,B,C,D |       A,B,C,D |                      4 |                   4 |             4 |          C |
  |                A |             A |                      1 |                   1 |             1 |     (null) |
  </code></pre>
  <p><strong>Example 4</strong> (<a href="http://www.sqlfiddle.com/#!4/ea363/53" rel="nofollow noreferrer">SQLFiddle</a>) - join table with multisets constructed in WITH + subquery - <strong>OK</strong></p>
  <pre><code>select 'Join table with multisets constructed in WITH and subquery - OK' caption from dual
  /
  with table_data_from_set as (
    select
      'A' tab_str_field,
      cast(multiset(
        select 'A' from dual union all
        select 'B' from dual union all
        select 'C' from dual
      ) as TabType0)  tab_field
    from dual union all
    select
      'B' tab_str_field,
      cast(multiset(
        select 'B' from dual union all
        select 'C' from dual
      ) as TabType0)  tab_field
    from dual union all
    select
      'C' tab_str_field,
      cast(multiset(
        select 'A' from dual union all
        select 'B' from dual union all
        select 'C' from dual union all
        select 'D' from dual
      ) as TabType0) tab_field
    from dual union all
    select
      'D' tab_str_field,
      cast(multiset(
        select 'A' from dual
      ) as TabType0) tab_field
    from dual
  )
  select
    table0_tab_field                            table0_tab_field,
    set_tab_field                               set_tab_field,
  
    -- cardinality
    cardinality(table0_tab_field)               table0_tab_cardinality,
    cardinality(set_tab_field)                  set_tab_cardinality,
  
    -- select from table field of current row
    (select count(1) from table(set_tab_field)) set_tab_count,
  
    -- select from field of current row while joining
    -- with another field of same row
    ( select column_value from table(set_tab_field)
      where column_value = table0_tab_str_field
    )                                           same_value
  from (
    select 
      table0.tab_str_field              table0_tab_str_field,
      table0.tab_field                  table0_tab_field,
      table_data_from_set.tab_str_field set_tab_str_field,
      table_data_from_set.tab_field     set_tab_field
    from 
      table0, 
      table_data_from_set 
    where 
      table_data_from_set.tab_str_field = table0.tab_str_field
  )
  /
  </code></pre>
  <p>results:</p>
  <pre><code>| TABLE0_TAB_FIELD | SET_TAB_FIELD | TABLE0_TAB_CARDINALITY | SET_TAB_CARDINALITY | SET_TAB_COUNT | SAME_VALUE |
  ----------------------------------------------------------------------------------------------------------------
  |            A,B,C |         A,B,C |                      3 |                   3 |             3 |          A |
  |              B,C |           B,C |                      2 |                   2 |             2 |          B |
  |          A,B,C,D |       A,B,C,D |                      4 |                   4 |             4 |          C |
  |                A |             A |                      1 |                   1 |             1 |     (null) |
  </code></pre>
  <p><strong>Example 5</strong> (<a href="http://www.sqlfiddle.com/#!4/ea363/54" rel="nofollow noreferrer">SQLFiddle</a>) - join table with multisets constructed on the fly - <strong>FAILED</strong></p>
  <pre><code>select 'Join table with multisets constructed on the fly - FAIL (set_tab_count wrong)' caption from dual
  /
  with string_set as (
    select 'A' str_field from dual union all
    select 'B' str_field from dual union all
    select 'C' str_field from dual union all
    select 'D' str_field from dual union all
    select 'E' str_field from dual 
  )
  select
    table0_tab_field                            table0_tab_field,
    set_tab_field                               set_tab_field,
  
    -- cardinality
    cardinality(table0_tab_field)               table0_tab_cardinality,
    cardinality(set_tab_field)                  set_tab_cardinality,
  
    -- select from table field of current row
    (select count(1) from table(set_tab_field)) set_tab_count,
  
    -- select from field of current row while joining
    -- with another field of same row
    ( select column_value from table(set_tab_field)
      where column_value = table0_tab_str_field
    )                                            same_value
  from (
    select 
      table0.tab_str_field     table0_tab_str_field,
      table0.tab_field         table0_tab_field,
      ( 
        cast(multiset(
          
          select 
            string_set.str_field 
          from 
            string_set, 
            table(table0.tab_field) tab_table
          where 
            string_set.str_field = tab_table.column_value
          
        ) as TabType0)
      )                        set_tab_field
    from 
      table0 
  )  
  /
  </code></pre>
  <p>result (all values in <code>set_tab_count</code> column are same - <em><strong>wrong!</strong></em> ) :</p>
  <pre><code>| TABLE0_TAB_FIELD | SET_TAB_FIELD | TABLE0_TAB_CARDINALITY | SET_TAB_CARDINALITY | SET_TAB_COUNT | SAME_VALUE |
  ----------------------------------------------------------------------------------------------------------------
  |            A,B,C |         A,B,C |                      3 |                   3 |             3 |          A |
  |              B,C |           B,C |                      2 |                   2 |             3 |          B |
  |          A,B,C,D |       A,B,C,D |                      4 |                   4 |             3 |          C |
  |                A |             A |                      1 |                   1 |             3 |     (null) |
  </code></pre>
  <h2>Oracle version information</h2>
  <p><strong>Instance 1</strong></p>
  <pre><code>BANNER
  --------------------------------------------------------------------------------
  Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  PL/SQL Release 11.2.0.3.0 - Production
  CORE    11.2.0.3.0  Production
  TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
  NLSRTL Version 11.2.0.3.0 - Production
  </code></pre>
  <p><strong>Instance 2</strong></p>
  <pre><code>BANNER 
  --------------------------------------------------------------------------------
  Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production 
  PL/SQL Release 11.2.0.2.0 - Production 
  CORE    11.2.0.2.0  Production 
  TNS for 32-bit Windows: Version 11.2.0.2.0 - Production 
  NLSRTL Version 11.2.0.2.0 - Production 
  </code></pre>
  <p><a href="http://www.sqlfiddle.com/#!4/ea363/48" rel="nofollow noreferrer">SQLFiddle</a> with all queries together.</p>