MySQL Bugs: #11594: CONCAT

Bug #11594	CONCAT_WS Behavior
Submitted:	27 Jun 2005 20:47	Modified:	28 Jun 2005 17:30
Reporter:	Sergio Salvatore	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.11	OS:	Linux (RedHat Linux ES3)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
CONCAT_WS does not return rows that should be found when used in conjunction with a LIKE statement.  I believe this may be a regression from previous versions, because I don't remember it being an issue in the past.  Please see the following example....

How to repeat:
Consider the following schema:

CREATE TABLE `artists` (
  `artistid` int(10) unsigned NOT NULL auto_increment,
  `artistname` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`artistid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `tracks` (
  `trackid` int(10) unsigned NOT NULL auto_increment,
  `trackname` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`trackid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `trackstoartists` (
  `trackid` int(10) unsigned NOT NULL default '0',
  `artistid` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`trackid`,`artistid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Then, consider the following data:

mysql> select * from tracks;
+---------+--------------------+
| trackid | trackname          |
+---------+--------------------+
|       1 | April In Paris     |
|       2 | Autumn In New York |
+---------+--------------------+

mysql> select * from artists;
+----------+-------------+
| artistid | artistname  |
+----------+-------------+
|        1 | Vernon Duke |
+----------+-------------+
1 row in set (0.00 sec)

mysql> select * from trackstoartists;
+---------+----------+
| trackid | artistid |
+---------+----------+
|       1 |        1 |
+---------+----------+
1 row in set (0.00 sec)

According to the MySQL documentation, the CONCAT_WS function is supposed to skip NULL columns.  There are a few places in our searching code where we'll run something like this:

SELECT CONCAT_WS(' ', trackname, artistname) trackname, artistname FROM tracks LEFT JOIN trackstoartists ON (tracks.trackid = trackstoartists.trackid) LEFT JOIN artists ON (trackstoartists.artistid = artists.artistid) WHERE CONCAT_WS(' ', trackname, artistname) LIKE '%in%in';

I would expect this query to return something like this:

April In Paris	|	Vernon Duke
Autumn In New York 	|	NULL

But it doesn't -- I get this instead:

mysql> SELECT concat_ws(  ' ', trackname, artistname ) trackname, artistname
    -> FROM tracks
    -> LEFT JOIN trackstoartists ON ( tracks.trackid = trackstoartists.trackid ) 
    -> LEFT JOIN artists ON ( trackstoartists.artistid = artists.artistid ) 
    -> WHERE concat_ws(  ' ', trackname, artistname ) LIKE  '%in%';
+----------------------------+-------------+
| trackname                  | artistname  |
+----------------------------+-------------+
| April In Paris Vernon Duke | Vernon Duke |
+----------------------------+-------------+
1 row in set (0.00 sec)

And, just for reference, the row is returning with what looks like the proper data with the 'LIKE' removed:

mysql> select concat_ws(' ', trackname, artistname), trackname, artistname from tracks left join trackstoartists on (tracks.trackid = trackstoartists.trackid) left join artists on (trackstoartists.artistid = artists.artistid);
+---------------------------------------+--------------------+-------------+
| concat_ws(' ', trackname, artistname) | trackname          | artistname  |
+---------------------------------------+--------------------+-------------+
| April In Paris Vernon Duke            | April In Paris     | Vernon Duke |
| Autumn In New York                    | Autumn In New York | NULL        |
+---------------------------------------+--------------------+-------------+
2 rows in set (0.00 sec)

Suggested fix:
Please make CONCAT_WS in the WHERE clause return correctly, as it did in previous versions.

reproduced on MySQL 5.0.7 (on winXP SP2)

mysql> select concat_ws(' ', trackname, artistname), trackname, artistname from
    -> tracks left join trackstoartists on (tracks.trackid = trackstoartists.trackid)
    -> left join artists on (trackstoartists.artistid = artists.artistid) WHERE trackname LIKE '%in%';
+---------------------------------------+---------------------+-------------+
| concat_ws(' ', trackname, artistname) | trackname           | artistname  |
+---------------------------------------+---------------------+-------------+
| April In Paris Vernon Duke            | April In Paris      | Vernon Duke |
| Autumn In New York                    | Autumn In New York  | NULL        |
+---------------------------------------+---------------------+-------------+
2 rows in set (0.01 sec)

mysql> select concat_ws(' ', trackname, artistname), trackname, artistname from
    -> tracks left join trackstoartists on (tracks.trackid = trackstoartists.trackid)
    -> left join artists on (trackstoartists.artistid = artists.artistid) WHERE concat_ws(' ', trackname, artistname ) LIKE  '%in%';
+---------------------------------------+----------------+-------------+
| concat_ws(' ', trackname, artistname) | trackname      | artistname  |
+---------------------------------------+----------------+-------------+
| April In Paris Vernon Duke            | April In Paris | Vernon Duke |
+---------------------------------------+----------------+-------------+
1 row in set (0.00 sec)

My comment to that:
It might not be an issue with the concat_ws() function but with the parser that parses WHERE...LIKE's

@Sergio:
1) do you seriously mean that the first column in result is completely skipped with your MySQL-version ?
2) I believe that " LIKE '%in%in' " is a TYPO and should be  " LIKE '%in%' " only ?!

I see now that the reason for the missing column in result is a missing ","

SELECT concat_ws(' ', trackname, artistname ) trackname, ......
>>>
SELECT concat_ws(' ', trackname, artistname ), trackname, ....

Hmm. 
I would say that  changing "concat_ws() like .." to "trackname like .. or artistname like .. " gives proper result. 

changing "like '%In%'" to "locate ('In',concat_ws()) > 0"  gives wrong result. 

looks like bug

This bug is a duplicate of bug #11469.