Oracle SQL中多集映射的意外结果 [英] Unexpected result of multiset mapping in Oracle SQL

问题描述

请帮助我确认是下面解释的行为是一个bug，或清楚解释为什么它是正确的。
很有可能我误解了一些概念，但现在对我来说，它看起来像一个错误。

以下所有示例尽可能简化，核心的问题。实际情况非常复杂，因此只有与查询构造原则相关的一般答案和解决方法是可以接受的。

欢迎您在评论中提出澄清问题，我会尽力回答。

谢谢您的关注。 ：

问题

为什么在最后的例子（示例5） code>实例在（select count（1）... 子查询从第一行映射到表的所有行，而预期的结果是映射每个集合在
同时集合 >基数（...）表达式选择正确。

如果以这种方式构造，则存在相同的情况（不包括在示例中） / code>或其中是查询的一部分。

测试架构设置

（ SQLFiddle ）

 创建或替换类型TabType0为varchar2（100）的表
 / 
 
创建表Table0（tab_str_field varchar2 （100），tab_field TabType0）
嵌套表tab_field存储为tab_field_table 
 / 
 
插入到table0（tab_str_field，tab_field）值（
'A'，
 cast（multiset（
从双联合选择'A'全部
从双联合选择'B'全部
从双
选择'C'作为TabType0）
 
 / 
 
 insert into table0（tab_str_field，tab_field）values（
'B'，
 cast（multiset（
 select'B' from dual union all 
从双重
中选择'C'作为TabType0）
）
 / 
 
插入到table0（tab_str_field，tab_field） 
'C'，
 cast（multiset（
从双联合选择'A'全部
从双联合选择'B'全部
从双重选择'C' union all 
从双重
中选择'D'作为TabType0）
）
 / 
 
插入table0（tab_str_field，tab_field） $ b'D'，
 cast（multiset（
从双重
中选择'A'）作为TabType0）
）
 / 
 select'数据从双重
 / 
 select * from table0 
 / 
  

表数据：

  | TAB_STR_FIELD | TAB_FIELD | 
 ----------------------------- 
 | A | A，B，C | 
 | B | B，C | 
 | C | A，B，C，D | 
 | D | A | 
  

示例

示例1 （ SQLFiddle ） - 使用嵌套表字段 - 确定

 从双重
 / 
中选择使用嵌套表 -  OK选择
 tab_field tab_field，
 
  - 基数
基数（tab_field）tab_cardinality，
 
  - 从当前行的表字段中选择
 （从表（tab_field）中选择计数（1））tab_count，
 
  - 在加入
时从当前行的字段中选择 - 与同一行的另一个字段选择
 column_value from table（tab_field）
其中column_value = tab_str_field 
）same_value 
来自table0 
 / 
  

结果：

  | 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）| 
  

示例2 （ SQLFiddle ） - 只使用构建的源数据 - OK

 选择使用构造的源数据单独 - 确定标题从双
 / 
 with table_data_from_set as（
 select 
 'a'tab_str_field，
 cast（multiset（
从双联合选择'A'全部
从双联合选择'B'全部
从双$ b $选择'C' b）as TabType0）tab_field 
 from dual union all 
 select 
'B'tab_str_field，
 cast（multiset（
 select'B'from dual union all 
从双$ b中选择'C'b）b）作为TabType0）tab_field 
从双联合全部
选择
'C'tab_str_field，
 cast（multiset b从双联合中选择A
从双联合中选择B全部
从双联合选择'C'全部
从双重
中选择'D'作为TabType0 ）tab_field 
 from dual union all 
 select 
'D'tab_str_field，
 cast（multiset（
从双重
中选择'A'）作为TabType0） tab_field 
 from dual 
）
 select 
 tab_field tab_field，
 
  - 基数
基数（tab_field）tab_cardinality，
 
  - 从当前行的表字段中选择
（从表（tab_field）中选择count（1））tab_count，
 
  - 在加入$ b时从当前行的字段中选择$ b  - 与同一行的另一个字段
（从table（tab_field）中选择column_value 
其中column_value = tab_str_field 
）same_value 
从table_data_from_set 
 / 
  

结果：

  | 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）| 
  

示例3 （ SQLFiddle ） - 在 WITH - OK中构造的多集合连接表

 选择从双
 / $ b中加入用WITH  -  OK建立的multisets加入表$ b 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 
从双重
中选择'C'作为TabType0）tab_field 
从双联合全部
选择
'B'tab_str_field，
 cast（multiset （
从双联合中选择'B'
从双
中选择'C'作为TabType0）tab_field 
从双联合全部
选择
' C'tab_str_field，
 cast（multiset（
从双联合选择'A'全部
从双联合选择'B'全部
从双联合选择'C'全部
从双$ b中选择'D'b）b）作为TabType0）tab_field 
从双联合全部
选择
'D'tab_str_field，
 cast（multiset b从双
中选择'A'作为TabType0）tab_field 
从双
）
选择
 table0.tab_field table0_tab_field，
 table_data_from_set.tab_field set_tab_field， 
 
  - 基数
基数（table0.tab_field）table0_tab_cardinality，
基数（table_data_from_set.tab_field）set_tab_cardinality，
 
  - 从表字段中选择当前行
（从表（table_data_from_set.tab_field）中选择count（1））set_tab_count，
 
  - 在加入
时从当前行的字段中选择 - 同一行
（从表中选择column_value（table_data_from_set.tab_field）
其中column_value = table0.tab_str_field 
）same_value 
来自
 table0，
 table_data_from_set 
其中
 table_data_from_set.tab_str_field = table0.tab_str_field 
 / 
  

  | 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）| 
  

示例4 （ SQLFiddle ） - 在WITH +子查询中构建的多集合连接表 - 确定

 选择使用WITH和子查询构建的多表集合加入表 - 双击
 / 
中的table_data_from_set为（
 select 
'A'tab_str_field，
 cast（multiset（
从双联合选择'A'全部
从双联合选择'B'全部
选择'C' 'from dual 
）as TabType0）tab_field 
 from dual union all 
 select 
'B'tab_str_field，
 cast（multiset（
 select'B' from dual union all 
从双重
中选择'C'作为TabType0）tab_field 
从双联合全部
 select 
'C'tab_str_field，
 cast （multiset（
从双联合选择'A'全部
从双联合选择'B'全部
从双联合选择'C'全部
从双$选择'D' b $ b）as TabType0）tab_field 
 from dual union all 
 select 
'D'tab_str_field，
 cast（multiset（
从dual $ b中选择'A' $ b）as TabType0）tab_field 
 from dual 
）
 select 
 table0_tab_field table0_tab_field，
 set_tab_field set_tab_field，
 
  - 基数
 cardinality（table0_tab_field）table0_tab_cardinality，
 cardinality（set_tab_field）set_tab_cardinality，
 
  - 从当前行的表字段中选择
（select count（1）from table（set_tab_field） ）set_tab_count，
 
  - 在加入
时从当前行的字段中选择 - 与同一行的另一个字段选择
（从表中选择column_value（set_tab_field）
 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 
其中
 table_data_from_set.tab_str_field = table0.tab_str_field 
）
 / 
  

结果：

  | 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）| 
  

示例5 （ SQLFiddle ） - 连接多表面构建的表 - 失败

选择'join table with multisets when 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，

- 基数$ b b基数（table0_tab_field）table0_tab_cardinality，
基数（set_tab_field）set_tab_cardinality，

- 从当前行的表字段中选择
（从表（set_tab_field）中选择计数set_tab_count，

- 在加入
时从当前行的字段中选择 - 与同一行的另一个字段选择
（从表中选择column_value（set_tab_field）
其中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
从
string_set，
表（table0.tab_field）tab_table
其中
string_set.str_field = tab_table.column_value

）as TabType0）
）set_tab_field
from
table0
）
/


结果（ set_tab_count 列中的所有值都相同 - 错误！ ）：

  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）| 
  

Oracle版本信息

实例1

  BANNER 
 --------------- -------------------------------------------------- --------------- 
 Oracle Database 11g企业版11.2.0.3.0  -  64位生产
 PL / SQL版本11.2.0.3.0  - 生产
 CORE 11.2.0.3.0生产
 IBM / AIX的TNS RISC System / 6000：版本11.2.0.3.0  - 生产
 NLSRTL版本11.2.0.3.0  - 生产
  
 
 
 
 实例2  
  BANNER 
 --------------------------------------- ----------------------------------------- 
 Oracle数据库11g快捷版版本11.2.0.2.0  - 生产
 PL / SQL版本11.2.0.2.0  - 生产
 CORE 11.2.0.2.0生产
 32位Windows的TNS：版本11.2.0.2。 0  - 生产
 NLSRTL版本11.2.0.2.0  - 生产
  
  SQLFiddle 与所有查询结合。
解决方案
这是一个错误。在上一个示例中向第二个内联视图添加 / * + NO_MERGE * / 提示将生成预期结果。有关示例，请参见此SQL小提示。不管查询，该提示永远不应该更改结果。还有一些其他看似无关的更改可以生成正确的结果，例如删除一些列，或在中间添加一个未使用的 ROWNUM 。 / p> 
 
 
 Oracle重写您的查询以优化它，但做错了。你可能通过跟踪查询获得一些更多的信息，但我怀疑你将能够真正解决这个问题。现在解决它，并向Oracle提交服务请求，以便他们可以创建一个错误，并最终修复它。
 
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.

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.

You are welcome to ask clarifying questions in comments and i'll try to do my best to answer them.

Thank you for attention. :)

Question

Why in last Example (Example 5) collection instance in  (select count(1) ... subquery from first row mapped to all rows of the table, while expected result is to map each collection instance to it's own row?

At the same time collections used in cardinality(...) expression chosen properly.

Same situation (not covered in examples) exists if constructed in this way collections used in from or where part of a query.

Test schema setup

(SQLFiddle)
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
/
table data:
| TAB_STR_FIELD | TAB_FIELD |
-----------------------------
|             A |     A,B,C |
|             B |       B,C |
|             C |   A,B,C,D |
|             D |         A |


Examples

Example 1 (SQLFiddle)  - work with nested table fields - OK
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
/
results:
| 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) |
Example 2 (SQLFiddle) - work with constructed source data alone - OK
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
/
results:
| 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) |
Example 3 (SQLFiddle) - join table with multisets constructed in WITH - OK
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
/
results:
| 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) |
Example 4 (SQLFiddle) - join table with multisets constructed in WITH + subquery - OK
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
)
/
results:
| 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) |
Example 5 (SQLFiddle) - join table with multisets constructed on the fly - FAILED
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 
)  
/
result (all values in set_tab_count column are same - wrong! ) :
| 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) |


Oracle version information

Instance 1
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
Instance 2
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 
SQLFiddle with all queries together.
解决方案
It's a bug.  Adding a /*+ NO_MERGE */ hint to the second inline view in the last example will generate the expected results.  See this SQL Fiddle for an example.  Regardless of the query, that hint should never change the results.  There are a couple of other seemingly unrelated changes you can make that will generate the correct results, such as removing some of the columns, or adding an unused ROWNUM in the middle.

Oracle is re-writing your query to optimize it, but doing something wrong.  You could probably get some more information by tracing the query, but I doubt you'll be able to truly fix the issue.  Work around it for now and submit a service request to Oracle so they can create a bug and eventually fix it.

                        
这篇关于Oracle SQL中多集映射的意外结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！