Bug #33544 UDF_INIT member decimals initialized wrong with STRING_RESULT argument
Submitted: 28 Dec 2007 0:19 Modified: 6 Mar 2010 18:32
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.22 OS:Linux
Assigned to: Paul Dubois CPU Architecture:Any
Triage: D2 (Serious)

[28 Dec 2007 0:19] Roland Bouman
Description:
the decimals member of UDF_INIT is supposed to be initialized to the largest number of decimals of the arguments passed to the UDF. 

This works fine when passing integers, decimals and floats. However, when even one single string is passed, the decimals member is set to 31. 

This behaviour is not correct: strings have no decimal digits at all, so certainly not 31 of them
This behaviour is also not consistent: when passing only integers, which also do not have decimal digits, the decimals member is initialized to zero - this seems to be the sensible behaviour.

How to repeat:
Code for the UDF:

my_bool udf_initid_decimals_init(
		UDF_INIT *initid
	,	UDF_ARGS *args
	,	char *message
){
	return 0;
}

void udf_initid_decimals_deinit(
	UDF_INIT *initid
){
	
}

double udf_initid_decimals(
	UDF_INIT *initid
,	UDF_ARGS *args
,   char *is_null
,	char *error
){
	return (double)initid->decimals;
}

SQL:

 select udf_initid_decimals(1,2);
+--------------------------+
| udf_initid_decimals(1,2) |
+--------------------------+
|                        0 |
+--------------------------+

(correct: integers do not have decimal digits, so initid->decimals is initialized to 0)

mysql> select udf_initid_decimals(0.1,0.12);
+-------------------------------+
| udf_initid_decimals(0.1,0.12) |
+-------------------------------+
|                          2.00 |
+-------------------------------+
1 row in set (0.00 sec)

(correct: 2 decimal digits is the largest number of digits of all arguments)

mysql> select udf_initid_decimals('',0.12);
+------------------------------+
| udf_initid_decimals('',0.12) |
+------------------------------+
|                           31 |
+------------------------------+
1 row in set (0.00 sec)

NOT CORRECT! largest number of decimals passed through arguments is 2, not 31. The string does not have decimal digits at all - it should not influence the value of the decimals member.

Suggested fix:
Do not take string arguments into account when setting the decimals member. Implement advertised behaviour and set the decimals member to the largest number of decimal digits.

String arguments should be treated like integer arguments and considered to have no decimal digits at all - in other words, they should be considered to have zero decimal digits.
[29 Feb 2008 15:20] Miguel Solorzano
Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.24-rc-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select udf_initid_decimals(1,2);
+--------------------------+
| udf_initid_decimals(1,2) |
+--------------------------+
|                        0 | 
+--------------------------+
1 row in set (0.00 sec)

mysql> select udf_initid_decimals(0.1,0.12);
+-------------------------------+
| udf_initid_decimals(0.1,0.12) |
+-------------------------------+
|                          2.00 | 
+-------------------------------+
1 row in set (0.00 sec)

mysql> select udf_initid_decimals('',0.12);
+------------------------------+
| udf_initid_decimals('',0.12) |
+------------------------------+
|                           31 | 
+------------------------------+
1 row in set (0.00 sec)

mysql>
[5 Mar 2008 14:28] 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/commits/43462

ChangeSet@1.2585, 2008-03-05 16:27:35+02:00, gkodinov@magare.gmz +8 -0
  Bug #33544: UDF_INIT member decimals initialized wrong with 
    STRING_RESULT argument
  
  There is a "magic" number for precision : NOT_FIXED_DEC. 
  This means that the precision is not a fixed number.
  But this constant was re-defined in several files and 
  was not available to the UDF developers.
  
  Moved the NOT_FIXED_DEC definition to the correct header
  and removed the redundant definitions.
[5 Mar 2008 14:52] Georgi Kodinov
What we see here (31) is expected behavior. 31 is the "magic" precision NOT_FIXED_DEC (that means there's no fixed precision).
But this constant was neither available to the UDF developer, nor was it documented.
[5 Mar 2008 14:55] Roland Bouman
I don't understand how it's expected behaviour..
I mean, i pass one decimal:

my_func(1.2)

and the value of initid->decimals is 2. 

Now, we pass a string too:

my_funct(1.2, '')

and now it's 31? 

How is a UDF implementor to know the max no of decimals? The string does not have any decimals, why should it effect the value of initid->decimals?
[28 May 2008 10:01] Bugs System
Pushed into 6.0.6-alpha
[30 Jul 2008 19:39] Paul Dubois
Additional data point:

For a FLOAT column defined with a specified number of decimals, initid->decimals is set to that number of decimals.

For a FLOAT column specified without decimals, or a float constant (like 1E0), initid->decimals gets set to NOT_FIXED_DEC.
[1 Aug 2008 15:12] Paul Dubois
This has become a documentation bug. Recategorizing as Server:Documentation and assigning to myself.
[1 Aug 2008 15:31] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I am adding this to the decimals description at http://dev.mysql.com/doc/refman/6.0/en/udf-calling.html:

For arguments that have no fixed number of decimals, the decimals
value is set to 31, which is 1 more than the maximum number of
decimals allowed for the DECIMAL, FLOAT, and DOUBLE data types. As of
MySQL 6.0.6, this value is available as the constant NOT_FIXED_DEC in
the mysql_com.h header file.

A decimals value of 31 is used for arguments in cases such as a FLOAT
or DOUBLE column declared without an explicit number of decimals (for
example, FLOAT rather than FLOAT(10,3)) and for floating-point 
constants such as 1345E-3. It is also used for string and other
non-number arguments that might be converted within the function to
numeric form. 

The value to which the decimals member is initialized is only a
default. It can be changed within the function to reflect the actual
calculation performed. The default is determined such that the
largest number of decimals of the arguments is used. If the number of
decimals is NOT_FIXED_DEC for even one of the arguments, that is the value used
for decimals. 

For earlier-version manuals, I added the same text but without reference to NOT_FIXED_DEC.
[11 Nov 2009 20:22] 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/commits/90160

2931 Magne Mahre	2009-11-11 [merge]
      Bug #33544 UDF_INIT member decimals initialized wrong with 
                 STRING_RESULT argument
      
      There is a "magic" number for precision : NOT_FIXED_DEC. 
      This means that the precision is not a fixed number.
      But this constant was re-defined in several files and 
      was not available to the UDF developers.
       
      Moved the NOT_FIXED_DEC definition to the correct header
      and removed the redundant definitions.
      
      Backported to 5.6.0  (mysql-next-mr-runtime)
     @ client/sql_string.h
        moved NOT_FIXED_DEC to the correct header
     @ include/mysql_com.h
        moved NOT_FIXED_DEC to the correct header
     @ libmysql/libmysql.c
        moved NOT_FIXED_DEC to the correct header
     @ sql/field.h
        moved NOT_FIXED_DEC to the correct header
     @ sql/sql_string.h
        moved NOT_FIXED_DEC to the correct header
     @ storage/ndb/include/kernel/signaldata/DictTabInfo.hpp
        moved NOT_FIXED_DEC to the correct header
[11 Nov 2009 20:23] Magne Mæhre
6.0-codebase revid: 2497.248.1
[20 Nov 2009 12:54] Bugs System
Pushed into 5.6.0-beta (revid:davi.arnaut@sun.com-20091119234808-xbjpkwaxjt5x5c0b) (version source revid:davi.arnaut@sun.com-20091119234808-xbjpkwaxjt5x5c0b) (merge vers: 5.6.0-beta) (pib:13)
[20 Nov 2009 12:56] Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:kostja@sun.com-20091119222407-5a7cjlhw06trtxnf) (merge vers: 6.0.14-alpha) (pib:13)
[26 Nov 2009 15:30] Paul Dubois
Already fixed for 6.0.x.

Added modified text to 5.6 manual.
[6 Mar 2010 10:55] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091120154107-6awpq04plug2xlri) (merge vers: 5.6.0-beta) (pib:16)
[6 Mar 2010 18:32] Paul Dubois
Modified text is in 5.5 manual, not 5.6 manual.