MySQL Bugs: #71259: LOAD DATA into a SET with exactly 64 elements gives bogus errors

Bug #71259	LOAD DATA into a SET with exactly 64 elements gives bogus errors
Submitted:	1 Jan 2014 0:42	Modified:	12 Nov 2014 22:48
Reporter:	Arthur O'Dwyer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.5.34	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
(Similar to Bug 69796.)

Columns of type SET where the set has exactly 64 elements do not correctly parse inserted strings (such as come from LOAD DATA).

The bug is in Field_set::store(), where the code evaluates

    1 << typelib->count

without checking that typelib->count is between 0 and 63 inclusive. When typelib->count is exactly 64, this test invokes undefined behavior and in fact gives wrong results on common x86 hardware.

How to repeat:
create table foo(id set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32', '33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63'));
insert into foo values ("100");
select * from foo;

+-------+
| id    |
+-------+
| 3,6,7 |  (this is the correct answer)
+-------+

create table bar(id set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32', '33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64'));
insert into bar values ("100");

ERROR 1265 (01000): Data truncated for column 'id' at row 1  (this is the incorrect answer)

Suggested fix:
diff --git a/sql/field.cc b/sql/field.cc
index c653464..6c58c08 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -8214,9 +8214,9 @@ int Field_set::store(const char *from,uint length,CHARSET_INFO *cs)
   {
     /* This is for reading numbers with LOAD DATA INFILE */
     char *end;
+    ulonglong all_bits_set = (typelib->count == 64) ? ~(ulonglong)0 : (((ulonglong)1 << typelib->count) - 1);
     tmp=my_strntoull(cs,from,length,10,&end,&err);
-    if (err || end != from+length ||
-       tmp > (ulonglong) (((longlong) 1 << typelib->count) - (longlong) 1))
+    if (err || end != from+length || tmp > all_bits_set)
     {
       tmp=0;      
       set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, 1);

Thanks for the bug report. Verified as described.

mysql> insert into bar values ("100");
Query OK, 1 row affected, 1 warning (0.15 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'id' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.02 sec)

Arthur, for devs to consider your patch, you should first sign the Oracle Contribution Agreement (OCA).
http://www.oracle.com/technetwork/community/oca-486395.html

[15 Aug 2014 0:39] Haixiang Li:

1 Testing it but can not be reproduced.
2 Analyzeing code and there is no chance to file a 'Error' and only may file
  a warnings. That is not consistent with bug description.

--------------------------------------
Testing it on 5.7.5-m15, 5.6.16, 5.5.36, 5.5.34
this bug can not be reproduced.
For example, testing it in 5.5.34 as follow:
--------------------------------------
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.34    |
+-----------+
1 row in set (0.00 sec)

mysql> use test
Database changed
mysql> create table foo(id
    ->
set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17

    '>
','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32',
    ->
'33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48

    '>
','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63'))

    -> ;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into foo values ("100");
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;
+-------+
| id    |
+-------+
| 3,6,7 |
+-------+
1 row in set (0.00 sec)

mysql>
mysql> create table bar(id
    ->
set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17

    '>
','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32',
    ->
'33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48

    '>
','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','

    '> 64'));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into bar values ("100");
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'id' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

@Erlend Dahl, @Haixiang Li: You're kidding, right? April Fools?

I tested the code, cut-and-pasted the bogus warning message, and reported it as a bug.
Shane tested the code, cut-and-pasted the bogus warning message, and verified it as a bug.
Haixiang tested the code, cut-and-pasted the bogus warning message, and proposed closing the bug as "Cannot Reproduce" — DESPITE GETTING EXACTLY THE SAME BOGUS MESSAGE AS EVERY OTHER REPORTER!

Let's reopen and fix this bogus message.

ASAN build on my system gives us a meaningful error to verify original bug:

./sql/field.cc:8768:35: runtime error: shift exponent 64 is too large for 64-bit type 'longlong' (aka 'long long')

Noted in 5.7.6 changelog.

The server could fail to parse inserted strings for SET columns for
which the column definition had exactly 64 elements.