Re: CASE and common table expression in mysql

[Peter Brawley]

Converting the query to conventional subqueries ...

select 
  user_name, rating, 
  CASE rating
    WHEN 0 THEN 'Really bad'
    WHEN 1 THEN 'Bad'
    WHEN 2 THEN 'Almost bad'
    WHEN 3 THEN 'So-so'
    WHEN 4 THEN 'OK'
    ELSE 'Loved it!'
  END AS rating_text
from (
  select 
    a.user_name, 
    (FLOOR(0 + RAND() * (5 - 0))) AS rating
  from (
    SELECT 'Asher' as user_name
    UNION ALL
    SELECT 'Cyd'
    UNION ALL
    SELECT 'Joe'
  ) a
) b;

manifests the same problem, I think Rand() causes the inconsistency---you'd need to move that random generation out of the query to make the ratings match.