Oracle 减查询。如果顶部 SQL 和底部 SQL 不包含 NULL，如何获得带有 NULL 的结果？

Question

这个SQL怎么可能...

CREATE TABLE NewTable AS
    SELECT A,B,C FROM Table1
    minus
    SELECT A, B, C From Table2

...创建一个新表，A 列中包含 NULL 值当 Table1 或 Table2 的 A 列中都没有 NULL 值时？

但另一方面，这个 SQL...

SELECT * FROM
(
   SELECT A,B,C FROM Table1
    minus
    SELECT A, B, C From Table2
) 
WHERE A IS NULL 

不返回任何行!

看起来不一致!

我认为这是 Oracle 中的一个错误。

当然，真正的 SQL 要复杂得多，但我相信这准确地说明了问题的本质。

更新

这是实际的 SQL:

我执行了这个语句:

CREATE TABLE MyMinus
AS
select 
*
FROM
---begin main query 
(
SELECT expenditure_item_date, expenditure_org, expenditure_type,
       f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
       f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
       f_company_code, f_cost_center_num, f_cuic, f_currency_code,
       f_destination_type_code, f_distribution_id, f_distribution_num,
       f_exchange_rate, f_extract_date, f_gl_account,
       f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
       f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
       f_project, f_project_num, f_promised_date, f_quantity_billed,
       f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
       f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
       f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
  FROM dw_mgr.po_distributions_curr_fct a
 WHERE EXISTS (
          SELECT 1
            FROM dw_mgr.po_distributions_curr_fct b,
                 dw_mgr.po_lines_curr_fct,
                 dw_mgr.po_header_curr_fct
           WHERE a.ROWID = b.ROWID
             AND b.f_cuic = dw_mgr.po_lines_curr_fct.f_cuic
             AND b.f_line_id = dw_mgr.po_lines_curr_fct.f_line_id
             AND dw_mgr.po_lines_curr_fct.f_cuic =
                                              dw_mgr.po_header_curr_fct.f_cuic
             AND dw_mgr.po_lines_curr_fct.f_header_id =
                                         dw_mgr.po_header_curr_fct.f_header_id
             AND dw_mgr.po_header_curr_fct.f_header_creation_date <
                                      ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
                                                  -48)
             AND dw_mgr.po_header_curr_fct.f_po_status IN
                                                 ('CLOSED', 'FINALLY CLOSED'))

MINUS 

SELECT expenditure_item_date, expenditure_org, expenditure_type,
       f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
       f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
       f_company_code, f_cost_center_num, f_cuic, f_currency_code,
       f_destination_type_code, f_distribution_id, f_distribution_num,
       f_exchange_rate, f_extract_date, f_gl_account,
       f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
       f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
       f_project, f_project_num, f_promised_date, f_quantity_billed,
       f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
       f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
       f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
  FROM arch_fct.po_distributions_curr_fct a
 WHERE EXISTS (
          SELECT 1
            FROM arch_fct.po_distributions_curr_fct b,
                 arch_fct.po_lines_curr_fct,
                 arch_fct.po_header_curr_fct
           WHERE a.ROWID = b.ROWID
             AND b.f_cuic = arch_fct.po_lines_curr_fct.f_cuic
             AND b.f_line_id = arch_fct.po_lines_curr_fct.f_line_id
             AND arch_fct.po_lines_curr_fct.f_cuic =
                                            arch_fct.po_header_curr_fct.f_cuic
             AND arch_fct.po_lines_curr_fct.f_header_id =
                                       arch_fct.po_header_curr_fct.f_header_id
             AND arch_fct.po_header_curr_fct.f_header_creation_date <
                                      ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
                                                  -48)
             AND arch_fct.po_header_curr_fct.f_po_status IN
                                                 ('CLOSED', 'FINALLY CLOSED'))

) 

然后是这个。请注意，具有 NULL 值 F_DISTRIBUTION_ID 的行已插入到创建的表中。

SELECT COUNT(*) from MyMinus WHERE F_DISTRIBUTION_ID IS NULL

--17 行

但是当我执行此操作时:

select 
*
FROM
---begin main query 
(
SELECT expenditure_item_date, expenditure_org, expenditure_type,
       f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
       f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
       f_company_code, f_cost_center_num, f_cuic, f_currency_code,
       f_destination_type_code, f_distribution_id, f_distribution_num,
       f_exchange_rate, f_extract_date, f_gl_account,
       f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
       f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
       f_project, f_project_num, f_promised_date, f_quantity_billed,
       f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
       f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
       f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
  FROM dw_mgr.po_distributions_curr_fct a
 WHERE EXISTS (
          SELECT 1
            FROM dw_mgr.po_distributions_curr_fct b,
                 dw_mgr.po_lines_curr_fct,
                 dw_mgr.po_header_curr_fct
           WHERE a.ROWID = b.ROWID
             AND b.f_cuic = dw_mgr.po_lines_curr_fct.f_cuic
             AND b.f_line_id = dw_mgr.po_lines_curr_fct.f_line_id
             AND dw_mgr.po_lines_curr_fct.f_cuic =
                                              dw_mgr.po_header_curr_fct.f_cuic
             AND dw_mgr.po_lines_curr_fct.f_header_id =
                                         dw_mgr.po_header_curr_fct.f_header_id
             AND dw_mgr.po_header_curr_fct.f_header_creation_date <
                                      ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
                                                  -48)
             AND dw_mgr.po_header_curr_fct.f_po_status IN
                                                 ('CLOSED', 'FINALLY CLOSED'))

MINUS 

SELECT expenditure_item_date, expenditure_org, expenditure_type,
       f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
       f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
       f_company_code, f_cost_center_num, f_cuic, f_currency_code,
       f_destination_type_code, f_distribution_id, f_distribution_num,
       f_exchange_rate, f_extract_date, f_gl_account,
       f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
       f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
       f_project, f_project_num, f_promised_date, f_quantity_billed,
       f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
       f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
       f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
  FROM arch_fct.po_distributions_curr_fct a
 WHERE EXISTS (
          SELECT 1
            FROM arch_fct.po_distributions_curr_fct b,
                 arch_fct.po_lines_curr_fct,
                 arch_fct.po_header_curr_fct
           WHERE a.ROWID = b.ROWID
             AND b.f_cuic = arch_fct.po_lines_curr_fct.f_cuic
             AND b.f_line_id = arch_fct.po_lines_curr_fct.f_line_id
             AND arch_fct.po_lines_curr_fct.f_cuic =
                                            arch_fct.po_header_curr_fct.f_cuic
             AND arch_fct.po_lines_curr_fct.f_header_id =
                                       arch_fct.po_header_curr_fct.f_header_id
             AND arch_fct.po_header_curr_fct.f_header_creation_date <
                                      ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
                                                  -48)
             AND arch_fct.po_header_curr_fct.f_po_status IN
                                                 ('CLOSED', 'FINALLY CLOSED'))

) 
WHERE

f_distribution_id is null

我得到 0 行。

为什么将记录插入到临时表中似乎会引入具有 NULL DIST ID 的行？

这个负查询 SQL 是由自定义数据归档程序动态生成的，它尝试验证应该归档在 DW_MGR 模式中的数据实际上是否已复制到 ARCH_FCT(归档)模式中。它返回的差异包括 17 条记录，其中 MyMinus 临时表中的 F_DISTRIBUTION_ID 与源 DW_MG.PO_DISTRIBUTIONS_CURR_FCT 表中的记录不匹配，因为它们为 NULL。因此，归档过程是在发现差异时设计的。问题是为什么存在差异，即当 NULL 值不在 SOURCE PO_DISTRIBUTIONS_CURR_FCT 表中时，它们如何进入 MyMinus 表？

编辑:

具有 Oracle META 访问权限的人可以发布有关 Oracle bug 的信息吗？我被转介给他们，但我在公司里找到了一个人，他可以告诉我我们的支持 ID 号是什么。我最终会知道的，但如果能早点知道就好了。如果您不想发布它，请考虑以下错误引用作为我的问题的潜在相关信息:

Bug 8209309: MINUS IS SHOWING DIFFERENCES WITH CTAS + INSERT 
Bug 7834950: WRONG RESULTS WITH MINUS OPERATOR

Answer 1

别再破坏你的排骨了。这是一个 Oracle 错误。我会向你证明这一点:

首先，它必须是第一个为 DISTRIBUTION ID 返回 NULL 的 SQL，因此隔离该 SQL，我们将其称为“SQL1”。

好吧，为了讨论方便，让我们简化一下 SQL1，并说它是这样的格式:

CREATE TABLE TempTable AS 
SELECT
   F_DISTRIBUTION_ID,
   FIELD2,
   FIELD3,...FIELD99

FROM WHATEVER 
WHERE WHATEVER

然后，您会发现，当您执行此操作时，您会发现具有 NULL DIST ID 的行:

SELECT COUNT(*) FROM TempTable WHERE F_DISTRIBUTION_ID IS NULL
--Some positive number of rows returned.

如果 Oracle 不是废话，您可以更改所选字段的数量，以便仅选择 F_DISTRIBUTION_ID，并且当您计算 F_DISTRIBUTION_ID 为 NULL 值的行数时，您会得到相同的结果，对吗？正确的!但事实并非如此，因为 Oracle 是一只不可靠的恐龙。

试试这个:

CREATE TABLE TempTable AS 
SELECT
   F_DISTRIBUTION_ID
FROM WHATEVER 
WHERE WHATEVER

SELECT COUNT(*) FROM TempTable WHERE F_DISTRIBUTION_ID IS NULL

我敢打赌，你会返回 0 行。

现在，调用 Microsoft 并告诉他们您想要升级到 SQL Server 2008 R2。