Bug #71160 concat_ws causes case sensitivity issue

• Submitted: 17 Dec 2013 11:11
• Modified: 28 Feb 2014 19:08
• Reporter: Peter Janssens
• Status: No Feedback
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.1.69
• OS: Any
• CPU Architecture: Any
• Tags: concat_ws like search

[17 Dec 2013 11:11] Peter Janssens

Description:
concat_ws(" ",field,field_maybe_null) like '%something%' causes case sensitive results when field_maybe_null from a left join has null value. The search is case insensitive when you omit the field_maybe null. The documentation states concat_ws is null safe (drops null from the concatenation) so it shouldn't influence the search at all ?

concat_ws(" ",field,field_maybe_null) like '%something%' -> case sensitive (unwanted).
concat_ws(" ",field) like '%something%' -> case insensitive, OK
concat_ws(" ",field,NULL) like '%something%' -> case insensitive with hard coded null value, OK
concat_ws(" ",field,null) like '%something%' -> case insensitive with hard coded null value, OK
concat_ws(" ",field,ifnull(field_maybe_null,NULL)) like '%something%' -> case insensitive, OK ?

so replace field_maybe_null with null if it has the value of null 'fixes' my case sensitivity issue. Seems odd to me.

How to repeat:
select * 
from table left join emtpy_table on table.id=empty_table.id 
where concat_ws(" ",table.title,empty_table.title) like '%title%';

where the empty table has all or some missing matches.

Suggested fix:
(left)(joined) null'ed fields should behave like null.
I don't know if you would consider the ifnull from client side a proper fix ?

[28 Jan 2014 19:08] Sveta Smirnova

Thank you for the report.

I can not repeat described behavior:

create table t1(id int, f1 varchar(255));
create table t2(id int, f1 varchar(255));
insert into t1 values(1,'foo'), (2,'bar'), (3,'Foo'), (4,'Bar');
select * from t1 left join t2 using(id) where concat_ws(" ", t1.f1, t2.f1) like '%foo%';
id	f1	f1
1	foo	NULL
3	Foo	NULL
insert into t2 values(1,'foo'), (2,'bar'), (3,'Foo'), (4,'Bar');
select * from t1 left join t2 using(id) where concat_ws(" ", t1.f1, t2.f1) like '%foo%';
id	f1	f1
1	foo	foo
3	Foo	Foo

Please provide complete test case, demonstrating the issue.

[1 Mar 2014 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".