Bug #58028 EXPORT_SET (too) high argument not honoured
Submitted: 6 Nov 2010 10:18 Modified: 26 Jun 2011 1:43
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:any, 5.0, 5.1, 5.6.99 OS:Any (MS Windows 7/64 - 64bit server, Linux)
Assigned to: Paul DuBois CPU Architecture:Any
Tags: qc

[6 Nov 2010 10:18] Peter Laursen
Description:
Here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_export-set

.. it is not documented than any higher value than '64' for 'number_of_bits'-parameter will 'reduce' the argument to 64 'silently'.  

Further this reduction does not raise a warning (what I think it should).

It may be obvious for (C-)programmers (ie. MAXINT will not require anything higher than 64 bit for 'number_of_bits'), but SQL users should not be assumed to be (C-)programmers.  If the behavior of this 'reduction' differs on 32- and 64 bit platforms also this should be documented (and besides I am not sure if MySQL will (now or in the future) support integer-length higher than 64 bit on some architectures).

(I am not sure about the category here.)

How to repeat:
SELECT EXPORT_SET(127,'1','0',',',64);
/*
EXPORT_SET(127,'1','0',',',64)                                                                                                 
-------------------------------------------------------------------------------------------------------------------------------
1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
*/

SELECT EXPORT_SET(127,'1','0',',',128);
/*
EXPORT_SET(127,'1','0',',',128)                                                                                                
-------------------------------------------------------------------------------------------------------------------------------
1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
*/
SHOW WARNINGS; -- empty set

Suggested fix:
1) raise warning

2) document this 'reduction'
[6 Nov 2010 10:34] Peter Laursen
Also negative numbers for 'bits' argumanet could need a doc note (like 'bits is evaluated as a signed integer" - if the output here is correct at all - I am not sure)

SELECT EXPORT_SET(-127,'1','0',',');
/*
EXPORT_SET(-127,'1','0',',')                                                                                                   
-------------------------------------------------------------------------------------------------------------------------------
1,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
*/
[6 Nov 2010 11:42] Peter Laursen
one more:

SELECT EXPORT_SET(-127,'1','0',',',-11);

/*
EXPORT_SET(-127,'1','0',',',-11)                                                                                               
-------------------------------------------------------------------------------------------------------------------------------
1,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
*/

should not '-11' raise an error or at least a warning?

My point is that the parameters may not be entered by a human, but could be generated by code. The should be some way to detect if parameters get 'out of (reasonable) range'
[6 Nov 2010 11:53] MySQL Verification Team
sounds like you want a strict sql_mode for selects..
[10 Nov 2010 21:41] Sveta Smirnova
Thank you for the report.

Verified as described.

$vim sql/item_strfunc.cc 
3001 String* Item_func_export_set::val_str(String* str)
...
3026   switch(arg_count) {
3027   case 5:
3028     num_set_values = (uint) args[4]->val_int();
3029     if (num_set_values > 64)
3030       num_set_values=64;

This should be documented at least.
[26 Jun 2011 1:43] 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.

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_export-set

"The number of bits examined is given by number_of_bits, which has a default of 64 if not specified. number_of_bits is silently clipped to 64 if larger than 64. It is treated as an unsigned integer if negative, so a value of –1 is effectively the same as 64."