Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Joerg.

Asked: November 10, 2017 - 9:58 am UTC

Last updated: November 11, 2017 - 8:38 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hello Tom and Tom,

Linked live sql shows a condensed and "moved-to-dual" query we are using with a far resemblance on our database. It's a couple of nested "union all" statements, where we would expect the outermost union (UNION2) to deliver the union of the results of the two second-outermost unions, SUBSUBQ1 and SUBSUBQ2. However, as you can see, the query for UNION2 delivers only one row, the one resulting from SUBSUBQ1. The one from SUBSUBQ2 goes lost. How does that happen?

The issue this hinges on, btw, is the difference between 'MM' and 'MM ' (additional space). This doesn't seem to matter for SUBSUBQ2, but seemingly with UNION2 it matters in an unpredicted way...

Thanks in advance for your insight, JO

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/file/content_FTS7F02JDKYCMCK7A7PMV3DW3.html

and Chris said...

OK. Looks like this is a bug in materializing results from the with clause.

If you materialize subq1, then subsubq1 returns no rows. However, if you inline the final union all then you get both rows:

SQL> set serveroutput off
SQL> with
  2  DUAL1 as (
  3    select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID, 100 as SHIFT
  4          from dual
  5    union all
  6    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT
  7          from dual ),
  8  DUAL2 as (
  9    select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from dual
 10    union all
 11    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID from dual ),
 12  UNION1 as (
 13    select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
 14    union all
 15    select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2
 16  ),
 17  SUBQ1 as (
 18    select * from UNION1 where SHIFT = '100' ),
 19  SUBSUBQ1 as (
 20    select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ),
 21  SUBSUBQ2 as (
 22    select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ),
 23  UNION2 as (
 24    select * from SUBSUBQ1
 25    union all
 26    select * from SUBSUBQ2
 27  )
 28  select /*+ gather_plan_statistics */q.* from SUBSUBQ1 q;

ORG FIN_ID           SHIFT
--- ----------- ----------
MM  LOST_FIN_ID        100

SQL>
SQL> select *
  2  from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1sftw2x173ub4, child number 0
-------------------------------------
with DUAL1 as (   select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as
FIN_ID, 100 as SHIFT         from dual   union all   select 'DER' as
ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT         from dual ),
DUAL2 as (   select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from
dual   union all   select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID
from dual ), UNION1 as (   select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
  union all   select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2 ),
SUBQ1 as (   select * from UNION1 where SHIFT = '100' ), SUBSUBQ1 as (
 select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ), SUBSUBQ2 as (
select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ), UNION2 as (   select
* from SUBSUBQ1   union all   select * from SUBSUBQ2 ) select /*+
gather_plan_statistics */q.* from SUBSUBQ1 q

Plan hash value: 2919767015

-------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | A-Rows |   A-Time   |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      1 |        |      1 |00:00:00.01 |
|   1 |  VIEW            |      |      1 |      3 |      1 |00:00:00.01 |
|   2 |   UNION-ALL      |      |      1 |        |      1 |00:00:00.01 |
|   3 |    VIEW          |      |      1 |      2 |      1 |00:00:00.01 |
|   4 |     UNION-ALL    |      |      1 |        |      1 |00:00:00.01 |
|   5 |      FAST DUAL   |      |      1 |      1 |      1 |00:00:00.01 |
|*  6 |      FILTER      |      |      1 |        |      0 |00:00:00.01 |
|   7 |       FAST DUAL  |      |      0 |      1 |      0 |00:00:00.01 |
|*  8 |    FILTER        |      |      1 |        |      0 |00:00:00.01 |
|   9 |     VIEW         |      |      0 |      2 |      0 |00:00:00.01 |
|  10 |      UNION-ALL   |      |      0 |        |      0 |00:00:00.01 |
|  11 |       FAST DUAL  |      |      0 |      1 |      0 |00:00:00.01 |
|* 12 |       FILTER     |      |      0 |        |      0 |00:00:00.01 |
|  13 |        FAST DUAL |      |      0 |      1 |      0 |00:00:00.01 |
-------------------------------------------------------------------------

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

   6 - filter(NULL IS NOT NULL)
   8 - filter(NULL IS NOT NULL)
  12 - filter(NULL IS NOT NULL)


43 rows selected.

SQL> with
  2  DUAL1 as (
  3    select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID, 100 as SHIFT
  4          from dual
  5    union all
  6    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT
  7          from dual ),
  8  DUAL2 as (
  9    select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from dual
 10    union all
 11    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID from dual ),
 12  UNION1 as (
 13    select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
 14    union all
 15    select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2
 16  ),
 17  SUBQ1 as (
 18    select /*+ materialize */* from UNION1 where SHIFT = '100' ),
 19  SUBSUBQ1 as (
 20    select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ),
 21  SUBSUBQ2 as (
 22    select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ),
 23  UNION2 as (
 24    select * from SUBSUBQ1
 25    union all
 26    select * from SUBSUBQ2
 27  )
 28  select /*+ gather_plan_statistics */q.* from SUBSUBQ1 q;

no rows selected

SQL>
SQL> select *
  2  from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d27h9uj6z29xp, child number 0
-------------------------------------
with DUAL1 as (   select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as
FIN_ID, 100 as SHIFT         from dual   union all   select 'DER' as
ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT         from dual ),
DUAL2 as (   select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from
dual   union all   select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID
from dual ), UNION1 as (   select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
  union all   select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2 ),
SUBQ1 as (   select /*+ materialize */* from UNION1 where SHIFT = '100'
), SUBSUBQ1 as (   select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ),
SUBSUBQ2 as (   select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ),
UNION2 as (   select * from SUBSUBQ1   union all   select * from
SUBSUBQ2 ) select /*+ gather_plan_statistics */q.* from SUBSUBQ1 q

Plan hash value: 1299862411

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |      1 |        |      0 |00:00:00.01 |       2 |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6783_4168A29 |      1 |        |      0 |00:00:00.01 |       1 |
|   3 |    VIEW                                  |                            |      1 |      3 |      2 |00:00:00.01 |       0 |
|   4 |     UNION-ALL                            |                            |      1 |        |      2 |00:00:00.01 |       0 |
|   5 |      VIEW                                |                            |      1 |      2 |      2 |00:00:00.01 |       0 |
|   6 |       UNION-ALL                          |                            |      1 |        |      2 |00:00:00.01 |       0 |
|   7 |        FAST DUAL                         |                            |      1 |      1 |      1 |00:00:00.01 |       0 |
|   8 |        FAST DUAL                         |                            |      1 |      1 |      1 |00:00:00.01 |       0 |
|*  9 |      FILTER                              |                            |      1 |        |      0 |00:00:00.01 |       0 |
|  10 |       VIEW                               |                            |      0 |      2 |      0 |00:00:00.01 |       0 |
|  11 |        UNION-ALL                         |                            |      0 |        |      0 |00:00:00.01 |       0 |
|  12 |         FAST DUAL                        |                            |      0 |      1 |      0 |00:00:00.01 |       0 |
|  13 |         FAST DUAL                        |                            |      0 |      1 |      0 |00:00:00.01 |       0 |
|* 14 |   VIEW                                   |                            |      1 |      3 |      0 |00:00:00.01 |       0 |
|  15 |    TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6783_4168A29 |      1 |      3 |      2 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------------------------------------

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

   9 - filter(NULL IS NOT NULL)
  14 - filter("ORG_SYS_TYPE"='MM')


44 rows selected.

SQL> with
  2  DUAL1 as (
  3    select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID, 100 as SHIFT
  4          from dual
  5    union all
  6    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT
  7          from dual ),
  8  DUAL2 as (
  9    select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from dual
 10    union all
 11    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID from dual ),
 12  UNION1 as (
 13    select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
 14    union all
 15    select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2
 16  ),
 17  SUBQ1 as (
 18    select /*+ inline */* from UNION1 where SHIFT = '100' ),
 19  SUBSUBQ1 as (
 20    select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ),
 21  SUBSUBQ2 as (
 22    select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ),
 23  UNION2 as (
 24    select * from SUBSUBQ1
 25    union all
 26    select * from SUBSUBQ2
 27  )
 28  select /*+ gather_plan_statistics */q.* from UNION2 q;

ORG FIN_ID           SHIFT
--- ----------- ----------
MM  LOST_FIN_ID        100
DER FIN_ID_B           100

SQL>
SQL> select *
  2  from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  75cty08wfm3wk, child number 0
-------------------------------------
with DUAL1 as (   select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as
FIN_ID, 100 as SHIFT         from dual   union all   select 'DER' as
ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT         from dual ),
DUAL2 as (   select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from
dual   union all   select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID
from dual ), UNION1 as (   select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
  union all   select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2 ),
SUBQ1 as (   select /*+ inline */* from UNION1 where SHIFT = '100' ),
SUBSUBQ1 as (   select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ),
SUBSUBQ2 as (   select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ),
UNION2 as (   select * from SUBSUBQ1   union all   select * from
SUBSUBQ2 ) select /*+ gather_plan_statistics */q.* from UNION2 q

Plan hash value: 2646163190

---------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      2 |00:00:00.01 |
|   1 |  VIEW              |      |      1 |      6 |      2 |00:00:00.01 |
|   2 |   UNION-ALL        |      |      1 |        |      2 |00:00:00.01 |
|   3 |    VIEW            |      |      1 |      3 |      1 |00:00:00.01 |
|   4 |     UNION-ALL      |      |      1 |        |      1 |00:00:00.01 |
|   5 |      VIEW          |      |      1 |      2 |      1 |00:00:00.01 |
|   6 |       UNION-ALL    |      |      1 |        |      1 |00:00:00.01 |
|   7 |        FAST DUAL   |      |      1 |      1 |      1 |00:00:00.01 |
|*  8 |        FILTER      |      |      1 |        |      0 |00:00:00.01 |
|   9 |         FAST DUAL  |      |      0 |      1 |      0 |00:00:00.01 |
|* 10 |      FILTER        |      |      1 |        |      0 |00:00:00.01 |
|  11 |       VIEW         |      |      0 |      2 |      0 |00:00:00.01 |
|  12 |        UNION-ALL   |      |      0 |        |      0 |00:00:00.01 |
|  13 |         FAST DUAL  |      |      0 |      1 |      0 |00:00:00.01 |
|* 14 |         FILTER     |      |      0 |        |      0 |00:00:00.01 |
|  15 |          FAST DUAL |      |      0 |      1 |      0 |00:00:00.01 |
|  16 |    VIEW            |      |      1 |      3 |      1 |00:00:00.01 |
|  17 |     UNION-ALL      |      |      1 |        |      1 |00:00:00.01 |
|  18 |      VIEW          |      |      1 |      2 |      1 |00:00:00.01 |
|  19 |       UNION-ALL    |      |      1 |        |      1 |00:00:00.01 |
|* 20 |        FILTER      |      |      1 |        |      0 |00:00:00.01 |
|  21 |         FAST DUAL  |      |      0 |      1 |      0 |00:00:00.01 |
|  22 |        FAST DUAL   |      |      1 |      1 |      1 |00:00:00.01 |
|* 23 |      FILTER        |      |      1 |        |      0 |00:00:00.01 |
|  24 |       VIEW         |      |      0 |      2 |      0 |00:00:00.01 |
|  25 |        UNION-ALL   |      |      0 |        |      0 |00:00:00.01 |
|* 26 |         FILTER     |      |      0 |        |      0 |00:00:00.01 |
|  27 |          FAST DUAL |      |      0 |      1 |      0 |00:00:00.01 |
|  28 |         FAST DUAL  |      |      0 |      1 |      0 |00:00:00.01 |
---------------------------------------------------------------------------

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

   8 - filter(NULL IS NOT NULL)
  10 - filter(NULL IS NOT NULL)
  14 - filter(NULL IS NOT NULL)
  20 - filter(NULL IS NOT NULL)
  23 - filter(NULL IS NOT NULL)
  26 - filter(NULL IS NOT NULL)


61 rows selected.


Probably because somewhere in the materialization there's a char -> varchar2 conversion. You can also overcome this by casting MM as a char(3) in subsubq1:

SQL> with
  2  DUAL1 as (
  3    select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID, 100 as SHIFT
  4          from dual
  5    union all
  6    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT
  7          from dual ),
  8  DUAL2 as (
  9    select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from dual
 10    union all
 11    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID from dual ),
 12  UNION1 as (
 13    select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
 14    union all
 15    select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2
 16  ),
 17  SUBQ1 as (
 18    select * from UNION1 where SHIFT = '100' ),
 19  SUBSUBQ1 as (
 20    select * from SUBQ1 where ORG_SYS_TYPE = cast('MM' as char(3)) ),
 21  SUBSUBQ2 as (
 22    select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ),
 23  UNION2 as (
 24    select * from SUBSUBQ1
 25    union all
 26    select * from SUBSUBQ2
 27  )
 28  select /*+ gather_plan_statistics */q.* from UNION2 q;

ORG FIN_ID           SHIFT
--- ----------- ----------
MM  LOST_FIN_ID        100
DER FIN_ID_B           100

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

fascinating

Joerg Over, November 10, 2017 - 4:48 pm UTC

That is fascinating, if quite a bit over my head. I'll try and put the insights and methods to a good use. For the moment I'll advise the devs to not rely too much on col type dependent quirks... Thanks a bunch, jo
Connor McDonald
November 11, 2017 - 8:38 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.