Skip to Main Content
  • Questions
  • Select returns rows that it should not

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Stas.

Asked: February 04, 2020 - 8:44 am UTC

Answered by: Chris Saxon - Last updated: February 04, 2020 - 2:57 pm UTC

Category: SQL - Version: 12.1.0.2.0

Viewed 100+ times

You Asked

Hi. I have a reproduction of strange behavior of select statement.

Reproduction:
--drop table test_a1
--drop table test_a2
create table test_a1
(
 id1 number(19),
 value1 number(1)
)
/
create table test_a2
(
 id2 number(19),
 value2 number(1) not null
)
/
insert into test_a1 values (1, 1)
/
commit
/
select *
from
 test_a1
 left join test_a2 on test_a1.id1 = test_a2.id2 and test_a2.value2 = 100
where 1=1
 and test_a1.value1 = 1
 and (test_a1.value1 = 0 or test_a2.value2 = 100)
/


According to conditions it should not return anything. But result is:
       ID1     VALUE1        ID2     VALUE2
---------- ---------- ---------- ----------
         1          1                      

The plan is:
Plan hash value: 2122087141
 
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    52 |     5   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |         |     1 |    52 |     5   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TEST_A1 |     1 |    26 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST_A2 |     1 |    26 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("TEST_A1"."ID1"="TEST_A2"."ID2"(+))
   2 - filter("TEST_A1"."VALUE1"=1)
   3 - filter("TEST_A2"."VALUE2"(+)=100)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Where it considers last predicate as left join (+). But it should be inner join because it is in 'where' clause!
So the question is why is it happening or maybe I was wrong somewhere? Maybe there is some logical explanation?

and we said...

19c gives the behaviour you expect:

select * from v$version;

BANNER                                                             BANNER_FULL                                                                           BANNER_LEGACY                                                      CON_ID   
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production    Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.3.0.0.0    Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production            0 


create table test_a1
(
 id1 number(19),
 value1 number(1)
)
/
create table test_a2
(
 id2 number(19),
 value2 number(1) not null
)
/
insert into test_a1 values (1, 1)
/
commit
/
select *
from
 test_a1
 left join test_a2 on test_a1.id1 = test_a2.id2 and test_a2.value2 = 100
where 1=1
 and test_a1.value1 = 1
 and (test_a1.value1 = 0 or test_a2.value2 = 100)
/

no rows selected


I can't track down which bug is causing this. But it also gives the expected result in 11.2. So using the /*+ optimizer_features_enable('11.2.0.4') */ hint may be a workout until you can patch/upgrade:

select * from v$version;

BANNER                                                                   
Oracle Database 11g EE High Perf Release 11.2.0.4.0 - 64bit Production    
PL/SQL Release 11.2.0.4.0 - Production                                    
CORE 11.2.0.4.0 Production                                                
TNS for Linux: Version 11.2.0.4.0 - Production                            
NLSRTL Version 11.2.0.4.0 - Production     

create table test_a1
(
 id1 number(19),
 value1 number(1)
)
/
create table test_a2
(
 id2 number(19),
 value2 number(1) not null
)
/
insert into test_a1 values (1, 1)
/
commit
/
select *
from
 test_a1
 left join test_a2 on test_a1.id1 = test_a2.id2 and test_a2.value2 = 100
where 1=1
 and test_a1.value1 = 1
 and (test_a1.value1 = 0 or test_a2.value2 = 100)
/

no rows selected

and you rated our response

  (1 rating)

Reviews

fix_control 13245379

February 04, 2020 - 1:03 pm UTC

Reviewer: Mikhail Velikikh from Dublin, Ireland

Apparently this issue is related to transitive predicate generation and Bug 13245379 - Hang/spin during query parse / optimization processing transitive predicates.
I can reproduce this issue in 12.2.0.1.190416. The problem is gone once I disable fix control 13245379.
SQL> select
  2     *
  3  from
  4   test_a1
  5   left join test_a2 on test_a1.id1 = test_a2.id2 and test_a2.value2 = 100
  6  where 1=1
  7   and test_a1.value1 = 1
  8   and (test_a1.value1 = 0 or test_a2.value2 = 100)
  9  /

       ID1     VALUE1        ID2     VALUE2
---------- ---------- ---------- ----------
         1          1

SQL> #0 explain plan for
SQL> /

Explained.

SQL> select * from dbms_xplan.display( format=> 'outline');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2122087141

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    52 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |         |     1 |    52 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TEST_A1 |     1 |    26 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST_A2 |     1 |    26 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$2BFA4EE4" "TEST_A2"@"SEL$1")
      LEADING(@"SEL$2BFA4EE4" "TEST_A1"@"SEL$1" "TEST_A2"@"SEL$1")
      FULL(@"SEL$2BFA4EE4" "TEST_A2"@"SEL$1")
      FULL(@"SEL$2BFA4EE4" "TEST_A1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      ANSI_REARCH(@"SEL$1")
      OUTLINE(@"SEL$8812AA4E")
      ANSI_REARCH(@"SEL$2")
      OUTLINE(@"SEL$948754D7")
      MERGE(@"SEL$8812AA4E" >"SEL$948754D7")
      OUTLINE_LEAF(@"SEL$2BFA4EE4")
      ALL_ROWS
      OPT_PARAM('_fix_control' '22174392:1')
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TEST_A1"."ID1"="TEST_A2"."ID2"(+))
   2 - filter("TEST_A1"."VALUE1"=1)
   3 - filter("TEST_A2"."VALUE2"(+)=100)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

46 rows selected.

SQL>
SQL> select  /*+ opt_param('_fix_control' '13245379:0')*/
  2     *
  3  from
  4   test_a1
  5   left join test_a2 on test_a1.id1 = test_a2.id2 and test_a2.value2 = 100
  6  where 1=1
  7   and test_a1.value1 = 1
  8   and (test_a1.value1 = 0 or test_a2.value2 = 100)
  9  /

no rows selected

SQL> #0 explain plan for
SQL> /

Explained.

SQL> select * from dbms_xplan.display( format=> 'outline');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2773199921

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |    52 |     4   (0)| 00:00:01 |
|*  1 |  FILTER             |         |       |       |            |          |
|*  2 |   HASH JOIN OUTER   |         |     1 |    52 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| TEST_A1 |     1 |    26 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| TEST_A2 |     1 |    26 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$2BFA4EE4" "TEST_A2"@"SEL$1")
      LEADING(@"SEL$2BFA4EE4" "TEST_A1"@"SEL$1" "TEST_A2"@"SEL$1")
      FULL(@"SEL$2BFA4EE4" "TEST_A2"@"SEL$1")
      FULL(@"SEL$2BFA4EE4" "TEST_A1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      ANSI_REARCH(@"SEL$1")
      OUTLINE(@"SEL$8812AA4E")
      ANSI_REARCH(@"SEL$2")
      OUTLINE(@"SEL$948754D7")
      MERGE(@"SEL$8812AA4E" >"SEL$948754D7")
      OUTLINE_LEAF(@"SEL$2BFA4EE4")
      ALL_ROWS
      OPT_PARAM('_fix_control' '13245379:0 22174392:1')
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TEST_A2"."VALUE2"=100)
   2 - access("TEST_A1"."ID1"="TEST_A2"."ID2"(+))
   3 - filter("TEST_A1"."VALUE1"=1)
   4 - filter("TEST_A2"."VALUE2"(+)=100)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

48 rows selected.

SQL>
SQL> @fix 13245379

     BUGNO      VALUE SQL_FEATURE                        DESCRIPTION                                                      OPTIMIZE      EVENT IS_DEFAULT
---------- ---------- ---------------------------------- ---------------------------------------------------------------- -------- ---------- ----------
  13245379          1 QKSFM_CBO_13245379                 avoid infinite looping while generating transitive preds         8.0.0             0          1

Chris Saxon

Followup  

February 04, 2020 - 2:57 pm UTC

Thanks for digging that out.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.