Is this implementation SQL-92 conformant?

Question

Tony Andrews in another question gave an example of:

IF p_c_courtesies_cd 
   || p_c_language_cd 
   || v_c_name 
   || v_c_firstname 
   || v_c_function 
   || p_c_phone 
   || p_c_mobile p_c_fax 
   || v_c_email is not null
THEN
     -- Do something
END IF;

as a clever (if not a tad obscure) alternative to the Oracle COALESCE function. Sure enough, it works, if any argument is not null, the IF test is true. My question: Is Oracle's implementation of the above concatenation operation SQL-92 conforming? Shouldn't an expression involving a NULL evaluate to NULL? If you don't think so, then why should the expression 1 + NULL evaluate to NULL?

Answer 1

Well COALESCE is explicitly defined by the SQL-92 standard to return the first non-NULL value in the list; so by definition, the Oracle implementation of that is behaving correctly.

EDIT: The SQL-92 spec; search for COALESCE to see its definition.

That said, there's nothing specific about NULL that states any operation involving NULL must be NULL. The more exact restriction is that NULL is neither false nor 0 nor equal to another NULL (e.g. NULL == NULL is false, because one NULL is not equal to another NULL). That doesn't mean, however, that there can't still be logically consistent ways of working with NULL that don't always return NULL.

EDIT: So NULL + 1 is NULL in the same way that NaN + 1 is still NaN; it's effectively an undefined operation.

Answer 2

SQL>  select 'something'||null from dual;

'SOMETHIN
---------
something

string concatenation with a null doesn't result in a null. I think this is normal behaviour, I'm used to it. Don't know what else to say.

Answer 3

@Nezroy: Thanks for the link. As I read the standard, however, I believe it states that Oracle's implementation is in fact, incorrect. Section 6.13, General Rules, item 2a:

     2) If <concatenation> is specified, then let S1 and S2 be the re-
        sult of the <character value expression> and <character factor>,
        respectively.

        Case:

        a) If either S1 or S2 is the null value, then the result of the
          <concatenation> is the null value.

Answer 4

Based on the portion of the SQL-92 spec highlighted by DCookie and the behavior of other DBs, I'd say Oracle is not behaving to standard with their concatenation operator.

Oracle (from tuinstoel's answer):

SQL>  select 'something'||null from dual;

'SOMETHIN
---------
something

MSSQL:

SELECT 'something'+NULL;

NULL

PostgreSQL:

postgres=# \pset null '(null)'
Null display is "(null)".
postgres=# select 'something'||null as output;
 output
--------
 (null)
(1 row)

MySQL:

mysql> select concat('something',NULL) as output;
+--------+
| output |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

Answer 5

No, Oracle's treatment of nulls is idiosyncratic, different from everyone else's, and inconsistent with the ANSI standards. In Oracle's defence however, it probably settled on and was committed to this treatment long before there was an ANSI standard to be consistent with!

It all starts from the fact that Oracle stores strings with a character count followed by the string data. A NULL is represented by a character count of zero with no following string data - which is exactly the same as an empty string (''). Oracle simply doesn't have a way to distinguish them.

This leads to some quirky behaviour, such as this concatenation case. Oracle also has a function LENGTH to return the length of a string, but this has been defined in an opposite manner, so that LENGTH('') returns NULL not zero. So:

LENGTH('abc') + LENGTH('') IS NULL

LENGTH('abc' || '') = 3

which seems to me to violate basic mathematical principles.

Of course, Oracle developers become so used to this that many of us can't even see anything wrong or odd about it - some will in fact argue that the rest of the world is wrong and that an empty string and a NULL are the same thing!