Query Get Different Result After Upgrade From 11g To 19c (Doc ID 2746407.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• Returning wrong result after upgrade from 11g to 19c:
• Correct count is 1:
  SQL> select count(1)...
  (select 1
  from ))
  and d.doc_type = 13;
  
  COUNT(1)
  ----------
  0

  SQL> alter session set "_optimizer_nlj_hj_adaptive_join"=FALSE;

  Session altered.

  SQL> SQL> select count(1)...
  (select 1
  from ))
  and d.doc_type = 13;

  COUNT(1)
  ----------
  1
  ========================================
  SQL> alter session set optimizer_features_enable='11.2.0.4';

  Session altered.

  SQL> select count(1)...
  (select 1
  from ))
  and d.doc_type = 13;

  COUNT(1)
  ----------
  1
  =======================================
  3.
  SQL> alter session set "_optimizer_null_accepting_semijoin"=FALSE;

  Session altered.

  SQL>SQL> select count(1)...
  (select 1
  from ))
  and d.doc_type = 13;

  COUNT(1)
  ----------
  1

   

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.