Bug #48034 pipe concat and concat returns NULL in sql_mode oracle
Submitted: 14 Oct 2009 8:23 Modified: 26 Oct 2009 9:28
Reporter: Oli Sennhauser Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.1.39 OS:Any
Assigned to: CPU Architecture:Any
Tags: pipe concat NULL sql_mode oracle

[14 Oct 2009 8:23] Oli Sennhauser 
Description:
SELECT 'a' || NULL || 'b' in ORACLE gives 'ab' in MySQL NULL even if sql_mode is set to oracle.

How to repeat:
set session sql_mode='oracle';
SELECT 'a' || NULL || 'b';

Suggested fix:
Make sql_mode='ORACLE' working like on oracle.
[14 Oct 2009 15:04] Sergei Golubchik 
Expected behavior.

"ORACLE" sql mode is not "exactly like Oracle in every single case" but "equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER."

See http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html
[14 Oct 2009 15:05] Sergei Golubchik 
the same applies to "ANSI", "POSTGRESQL" and other meta-modes
[14 Oct 2009 15:06] Sergei Golubchik 
see also bug#31131