BUG#11753852: IF() VALUES ARE EVALUATED DIFFERENTLY IN A

              REGULAR SQL VS PREPARED STATEMENT

Analysis:
---------

When passing user variables as parameters to the
prepared statements, the IF() function evaluation
turns out to be incorrect.

Consider the example:

SET @var1='0.038687';
SELECT @var1 , IF( @var1 = 0 , 1 ,@var1 ) AS sqlif ;
+----------+----------+
| @var1    | sqlif    |
+----------+----------+
| 0.038687 | 0.038687 |
+----------+----------+

Executing a prepared statement where the parameters are
supplied:

PREPARE fail_stmt FROM "SELECT ? ,
IF( ? = 0 , 1 , ? ) AS ps_if_fail" ;
EXECUTE fail_stmt USING @var1 ,@var1 , @var1 ;
+----------+------------+
| ?        | ps_if_fail |
+----------+------------+
| 0.038687 | 1          |
+----------+------------+
1 row in set (0.00 sec)

In the regular statement or while executing the prepared
statements without passing parameters, the decimal
precision is set for the user variable of type string.
The comparison function used for evaluation considered
the precision while comparing the values.

But while executing the prepared statement with the
parameters supplied, the decimal precision was not
set. Thus the comparison function chosen was different
which looked at the absolute values for comparison.

Fix:
----

The fix is to set 'decimals' field of Item_param to the
default value which is nothing but the maximum number of
decimals(NOT_FIXED_DEC). This is set for cases where the
strings are converted to the numeric form within certain
functions. Thus the value is not rounded off during
comparison, ensuring correct evaluation.

1 files changed, 3 insertions, 1 deletions

diff --git a/sql/item.cc b/sql/item.cc
index 6038ea7fde6..bd80d73ebfb 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3164,7 +3164,9 @@ bool Item_param::convert_str_value(THD *thd)
     /* Here str_value is guaranteed to be in final_character_set_of_str_value */
 
     max_length= str_value.numchars() * str_value.charset()->mbmaxlen;
-    decimals= 0;
+
+    /* For the strings converted to numeric form within some functions */
+    decimals= NOT_FIXED_DEC;
     /*
       str_value_ptr is returned from val_str(). It must be not alloced
       to prevent it's modification by val_str() invoker.