Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives

In Oracle, NULL does not equal NULL:

  EDDIE@XE> SELECT 'hi there'
    2    FROM DUAL
    3   WHERE NULL = NULL;

  no rows selected

But sometimes, if not most of the times, you do want the condition NULL = NULL to return true. For example:

  EDDIE@XE> CREATE TABLE t (
    2  col1  VARCHAR2(10),
    3  col2  VARCHAR2(10));

  Table created.

  EDDIE@XE> INSERT INTO t VALUES ('monkey', NULL);

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES ('monkey', 'chicken');

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES ('monkey', 'monkey');

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES (NULL, 'monkey');

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES (NULL, NULL);

  1 row created.

  EDDIE@XE> COMMIT;

  Commit complete.

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t;

  COL1       COL2
  ---------- ----------
  monkey     I am null
  monkey     chicken
  monkey     monkey
  I am null  monkey
  I am null  I am null

Now, I want to select all the records from table t where col1 equals col2:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE col1 = col2;

  COL1       COL2
  ---------- ----------
  monkey     monkey

As expected, the row that has NULL in both col1 and col2 was not returned. You want this record returned. Here is how you can do it:

Using the undocumented function SYS_OP_MAP_NONNULL:

This function has been around for a while, but is still undocumented as of the latest Oracle database release (10gR2). Being undocumented means that it should not be used in production code, but it does not hurt to know that it exists and explore how it works. This function makes it possible to have NULL = NULL:

  EDDIE@XE> SELECT sys_op_map_nonnull (NULL)
    2    FROM DUAL;

  SY
  --
  FF

  EDDIE@XE> SELECT 'hi there'
    2    FROM DUAL
    3   WHERE sys_op_map_nonnull (NULL) = sys_op_map_nonnull (NULL);

  'HITHERE
  --------
  hi there

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE sys_op_map_nonnull(col1) = sys_op_map_nonnull(col2);

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Using simple logical operators:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE (col1 = col2 OR (col1 IS NULL AND col2 IS NULL));

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Using DECODE:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE DECODE (col1, col2, 'match', 'no match') = 'match';

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Using NVL:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE NVL (col1, 'impossible value') = NVL (col2, 'impossible value');

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Of course, in this case, you have to be absolutely, positively 100 % sure that col1 and col2 will never have a value of ‘impossible value’ in them. Moreover, instead of NVL, you can also use similar functions.

Sources and Resources:

• AskTom thread
• PSOUG Morgan’s Library

Share this:

• Share

• Twitter
• Facebook
• LinkedIn
• Email
• StumbleUpon
• Google +1

Related articles:

• Cool Undocumented SQL Function: REVERSE
• Previously Undocumented LNNVL SQL Function Buggy
• Undocumented LNNVL SQL function now safe to use

Filed in Oracle, Tips on 19 Sep 06 | Tags: function, null, sql, undocumented

---

Reader's Comments

1. Pete_s | 19 Sep 2006 8:46 am

   Oracle uses this function in the ‘default’ index on materialized views for fast refresh – which in the case of DW Mviews is a little odd as I would not expect NULLS in my fact summaries!

2. Eddie Awad | 19 Sep 2006 9:29 pm

   Thanks for the extra tip Pete.

3. Michael Friedman | 20 Sep 2006 5:02 am

   This would be a lot more interesting if it did not just act like nvl(x,’FF’). What’s the point? You could have FF in your data.

4. Eddie Awad | 20 Sep 2006 9:39 am

   Michael, I guess you have just found out why it’s not documented. There are many other ways to compare two nulls, so there is really no need to use sys_op_map_nonnull. However, I will always be interested in the “undocumented”, just to satisfy my curiosity.

5. Jonathan Lewis | 25 Sep 2006 2:48 pm

   sys_op_map_nonnull() is a little more subtle than nvl(x,’0xff’).

   The thing it maps to is an internally stored FF, and I don’t think you can get this actual value stored for any type except RAW. Then, to deal with the problem of comparing raws when using sys_op_mapnonnull(), sys_op_mapnonnull(ff) returns a two-byte value which is 00ff, so sys_op_map_nonnull(ff) != ff.

6. Eddie Awad | 25 Sep 2006 5:16 pm

   Jonathan, thanks for clarifying the subtlety of sys_op_map_nonnull.

   Here is a quick test that I think demonstrates Michael’s point that sys_op_map_nonnull (col1) is the same as nvl(col1,’FF’):

     EDDIE@XE> CREATE TABLE t (
       2   col1  VARCHAR2(10),
       3   col2  VARCHAR2(10));
   
     Table created.
   
     EDDIE@XE> INSERT INTO t
       2       VALUES (NULL,
       3               NULL
       4              )
       5  /
   
     1 row created.
   
     EDDIE@XE> INSERT INTO t
       2       VALUES ('FF',
       3               'FF'
       4              )
       5  /
   
     1 row created.
   
     EDDIE@XE> INSERT INTO t
       2       VALUES ('Eddie',
       3               'Awad'
       4              )
       5  /
   
     1 row created.
   
     EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
       2         NVL (col2, 'I am null') col2
       3    FROM t
       4   WHERE nvl(col1,'FF') = nvl(col2,'FF')
       5  /
   
     COL1       COL2
     ---------- ----------
     I am null  I am null
     FF         FF
   
     EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
       2         NVL (col2, 'I am null') col2
       3    FROM t
       4   WHERE sys_op_map_nonnull (col1) = sys_op_map_nonnull (col2)
       5  /
   
     COL1       COL2
     ---------- ----------
     I am null  I am null
     FF         FF
   

   So, if sys_op_map_nonnull (col1) did not act like nvl(col2,’FF’), Oracle would not return the row with the varchar2 value FF in both col1 and col2.

   I think the explanation here is that sys_op_map_nonnull (‘FF’) equals sys_op_map_nonnull (‘FF’) but sys_op_map_nonnull (‘FF’) does not equal ‘FF’:

     EDDIE@XE> SELECT *
       2  FROM dual
       3  where  sys_op_map_nonnull ('FF') = sys_op_map_nonnull ('FF')
       4  /
   
     D
     -
     X
   
     EDDIE@XE> SELECT *
       2  FROM dual
       3  where  sys_op_map_nonnull ('FF') = 'FF'
       4  /
   
     no rows selected
   

7. Michael Friedman | 25 Sep 2006 6:18 pm

   Whoa.

   Am I confused?

   I thought sys_op_map_nonnull(‘any non-null value’) was supposed be ‘any non-null value’.

   I can also guarantee to you that

   select *
   from dual
   where sys_op_map_nonnull(null) = 'FF'

   returns a row.

   Now I think it’s doing something more subtle – it’s doing a mapping of anything you put in it so

   select *
   from dual
   where sys_op_map_nonnull(null) = sys_op_map_nonnull('FF')

   will not return a row… need to test.

8. Laurent Schneider | 06 Dec 2007 8:26 am

   I tried

   select * from dual where sys_op_map_nonnull(null) = 'FF' ;
   D
   -
   X
   

   It does return a row on my 10.2.0.3