MySQL Bugs: #10151: When using UNION with CASE and function call the result is wrong

Bug #10151	When using UNION with CASE and function call the result is wrong
Submitted:	25 Apr 2005 19:10	Modified:	23 Jun 2005 19:43
Reporter:	Emil Isberg
Status:	Closed
Category:	Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.11	OS:	FreeBSD (FreeBSD 4.10)
Assigned to:	Sergey Petrunia	Target Version:

Description:
When using the following query the result is 'BUG' instead of expected 'nobug':
SELECT 'case+union+test'
UNION 
SELECT CASE LOWER(  '1'  ) 
WHEN LOWER(  '2'  ) 
THEN  'BUG'
ELSE  'nobug'
END

When removing the UNION the result is as expected 'nobug':
SELECT CASE LOWER(  '1'  ) 
WHEN LOWER(  '2'  ) 
THEN  'BUG'
ELSE  'nobug'
END

Also when removing the call to LOWER() the result is as expected 'nobug':
SELECT 'case+union+test'
UNION 
SELECT CASE '1' 
WHEN '2' 
THEN  'BUG'
ELSE  'nobug'
END

Why is that so? No workaround (except not using UNION or LOWER is found).
I've tested the same query on 4.0.18 with the same result.

How to repeat:
SELECT 'case+union+test'
UNION 
SELECT CASE LOWER(  '1'  ) 
WHEN LOWER(  '2'  ) 
THEN  'BUG'
ELSE  'nobug'
END

This happens ony when in both CASE and THEN some string function is used.
I have tried UPPER() and behaviour is the same.
In short, if string function is not used on CASE  and / or on THEN then it works just
fine.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26171

The fix has been pushed into 4.1.13 tree.

Bug description for the changelog:
CASE function returns incorrect result when its arguments are not constants and its return
value is put into a regular or temporary table (temporary == created by SQL engine for
UNION/non-indexed GROUP BY and such operation)

Documented in 4.1.13 changelog:

<listitem><para><literal>CASE</literal> function returns incorrect result when its
arguments are not constants and
its return value is put into a regular or temporary table (temporary == created
by SQL engine for <literal>UNION</literal>/non-indexed <literal>GROUP BY</literal> and
such operations). (Bug #10151)</para></listitem>