32

I have this Tags table

CREATE TABLE IF NOT EXISTS `Tags` (
   `id_tag` int(10) unsigned NOT NULL auto_increment,
   `tag` varchar(255) default NULL,
   PRIMARY KEY  (`id_tag`),
   UNIQUE KEY `tag` (`tag`),
   KEY `id_tag` (`id_tag`),
   KEY `tag_2` (`tag`),
   KEY `tag_3` (`tag`),
   KEY `tag_4` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2937 ;

INSERT INTO `Tags` (`id_tag`, `tag`) VALUES
   (1816, '(class'),
   (2642, 'class\r\n\r\nâ?¬35'),
   (1906, 'class\r\nif'),
   (1398, 'class'),
   (2436, 'class)'),
   (1973, 'class:\n1.'),
   (2791, 'classes'),
   (1325, 'New'),
   (2185, 'pack'),
   (1905, 'packed'),
   (1389, 'WebClass');

I want to fetch all records where tag matches keywords class or pack or new, along with another field which indicates which of the 3 keywords actually matched with the tag field.

The following query does not give correct results Query 1

select id_tag,
case tag 
   when tag LIKE "%class%" then "class" 
   when tag LIKE "%new%" then "new"
   when tag LIKE "%pack%" then "pack"
end as matching_tag 
from Tags 
where tag LIKE "%class%" OR tag LIKE "%new%" OR tag LIKE "%pack%"

I have to use the like inside the case. Otherwise complete matching works. The following query works:-

Query 2

select id_tag,
case tag 
   when "class" then "class" 
   when "new" then "new"
   when "pack" then "pack"
end as matching_tag 
from Tags 
where tag = "class" OR tag = "new" OR tag = "pack"

What is wrong with the query 1. Please help.

STW
  • 44,917
  • 17
  • 105
  • 161
Sandeepan Nath
  • 9,966
  • 17
  • 86
  • 144
  • What's the problem here? Query 1 gives 11 results and there are 11 entries, which is what would be expected. The where clause in query 2 is different and changes the resultset. – cEz Jun 21 '10 at 08:06
  • btw, you can use `WHERE tag IN ("class", "new", "pack")` – abatishchev Jun 21 '10 at 08:11
  • @Cez The problem is with the matching_id column. If you run the query 1, you will see that against `id_tag` = 1398, for example, matching_tag comes `new`, whereas it actually contains `class`. Similarly different values are coming for all the records, the number of results is however correct – Sandeepan Nath Jun 21 '10 at 08:12
  • @abatishchev thanks I will use that. – Sandeepan Nath Jun 21 '10 at 08:12
  • @sandeepan Sorry, as the 2 queries gave different size resultsets, I didn't spot the mismatched result – cEz Jun 21 '10 at 08:51
  • 1
    @sandeepan, Cez - I've expanded explanation on what really happens with the case... (explains why for id_tag = 1398 the case returns 'new' when it contains 'class'). – Unreason Jun 21 '10 at 10:29

2 Answers2

46

Mysql supports two variants of case, the one you use in query 2 is less flexible but supports only equality on a single variable. The other version specifies no variable after case and then conditions need not be only equality:

select id_tag,
case  
   when tag LIKE "%class%" then "class" 
   when tag LIKE "%new%" then "new"
   when tag LIKE "%pack%" then "pack"
end as matching_tag 
from Tags 
where tag LIKE "%class%" OR tag LIKE "%new%" OR tag LIKE "%pack%"

See documentation for further details

EDIT: Here's a bit more explanation on why your query #1 returned what it returned:

case tag
   when tag LIKE "%class%" then "class" 
   when tag LIKE "%new%" then "new"
   when tag LIKE "%pack%" then "pack"
end as matching_tag

expects to get a literal value for comparison between when ... then In the above case the expressions tag LIKE "%class%", tag LIKE "%new%" and tag LIKE "%pack%" are all evaluated before the actual case comparison. However (!), what happens is that they become either 0 or 1 and when compared to the value of tag it is the first value of 0 that will match any char (char will get cast to 0) - this is consistent with the results of your first query.

Here's a query that shows the logical values for the relevant expressions:

select id_tag, tag LIKE "%class%", tag LIKE "%new%", tag = 0, case tag     when tag LIKE "%class%" then "class"     when tag LIKE "%new%" then "new"    when tag LIKE "%pack%" then "pack" end as matching_tag  from Tags  where tag LIKE "%class%" OR tag LIKE "%new%" OR tag LIKE "%pack%";

That's why you get unexpected results; the silent CAST is a standard pitfall here.

Unreason
  • 12,556
  • 2
  • 34
  • 50
16

Just want remind, about else clause:

case  
   when tag LIKE "%class%" then "class" 
   when tag LIKE "%new%" then "new"
   when tag LIKE "%pack%" then "pack"
   else "no one"
end as matching_tag
cn007b
  • 16,596
  • 7
  • 59
  • 74