MySQL Bugs: #7455: unexpected result: TRIM(<NULL> FROM <whatever>) gives NOT NULL

Bug #7455	unexpected result: TRIM(<NULL> FROM <whatever>) gives NOT NULL
Submitted:	21 Dec 2004 12:48	Modified:	28 Dec 2004 11:35
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1	OS:
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
# test case derived from NIST script dml112 TEST:0633 TRIM function!

SELECT TRIM(NULL FROM 'KATE') AS "my_column";
            my_column
            KATE
This result is not direct covered by the manual, because it does
not tell anything about the result, when remstr or str IS NULL:
  TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
  TRIM(remstr FROM] str)
      Returns the string str with all remstr prefixes and/or suffixes removed.
      If none of the specifiers BOTH, LEADING, or TRAILING is given, 
      BOTH is assumed. 
      If remstr is optional and not specified, spaces are removed.

So I assume the result is not mentioned because the result is
too obvious. If we try to remove prefixes and/or suffixes 
of unknown content(NULL), we get a result of also unknown content.
That means the result must be NULL.

MySQL 4.1 and 5.0 give the same wrong results.

My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1738, 2004-12-21
        Version 4.1 ChangeSet@1.2119, 2004-12-20 
   

How to repeat:
Please use my attached test file ml71.test , copy it to mysql-test/t
  ./mysql-test-run ml71

test case

Attachment: ml71.test (application/test, text), 731 bytes.