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 17:10	Modified:	23 Jun 2005 17:43
Reporter:	Emil Isberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.11	OS:	FreeBSD (FreeBSD 4.10)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

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>