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

[25 Apr 2005 17:10] Emil Isberg
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
[25 Apr 2005 18:11] Sinisa Milivojevic
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.
[20 Jun 2005 7:43] Bugs System
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
[23 Jun 2005 15:19] Sergey Petrunya
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)
[23 Jun 2005 17:43] Mike Hillyer
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>