MySQL Bugs: #46606: Casting error for large numbers in 5.4 when 'div' is used

Bug #46606	Casting error for large numbers in 5.4 when 'div' is used
Submitted:	7 Aug 2009 16:00	Modified:	20 Oct 2010 1:59
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.4.4	OS:	Linux (32/64 bit system)
Assigned to:	Tor Didriksen	CPU Architecture:	Any
Tags:	regression

Description:
The query "select 3230303430323231303134383334 div 1;" returns out of range and out of memory errors 
 - Error (Code 1264): Out of range value for column '3230303430323231303134383334 
   div 1' at row 1
 - Error (Code 1041): Out of memory; check if mysqld or some other process uses 
   all available memory; if not, you may have to use 'ulimit' to allow mysqld to 
   use more memory or you can add more swap space

when run against 5.4.4. No errors are returned when running against 5.1.
The 'out of memory' error is observed on 32-bit systems only, the ;out of range error on both 32 and 65 bit systems

Details:

omer@linux-10-3:~/mysql/bin_5130/mysql-test> ../bin/mysql --user=root --port=9306 --protocol=tcp --column-type-info --show-warnings --table test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.30-enterprise-commercial-advanced-debug-log MySQL Enterprise Server - Advanced Edition Debug (Commercial)

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

mysql> select 3230303430323231303134383334;
Field   1:  `3230303430323231303134383334`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     29
Max_length: 28
Decimals:   0
Flags:      NOT_NULL BINARY

+------------------------------+
| 3230303430323231303134383334 |
+------------------------------+
| 3230303430323231303134383334 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select 3230303430323231303134383334 div 1;
Field   1:  `3230303430323231303134383334 div 1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   0
Flags:      BINARY NUM

+------------------------------------+
| 3230303430323231303134383334 div 1 |
+------------------------------------+
|                9223372036854775807 |
+------------------------------------+
1 row in set, 1 warning (0.00 sec)

Error (Code 1292): Truncated incorrect DECIMAL value: ''
mysql> quit
Bye
omer@linux-10-3:~/mysql/bin_5130/mysql-test> ../bin/mysql --user=root --port=13000 --protocol=tcp --column-type-info --show-warnings --table test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.4.4-alpha-log Source distribution

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

mysql> select 3230303430323231303134383334;
Field   1:  `3230303430323231303134383334`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     29
Max_length: 28
Decimals:   0
Flags:      NOT_NULL BINARY

+------------------------------+
| 3230303430323231303134383334 |
+------------------------------+
| 3230303430323231303134383334 |
+------------------------------+
1 row in set (0.01 sec)

mysql> select 3230303430323231303134383334 div 1;
ERROR 1264 (22003): Out of range value for column '3230303430323231303134383334 div 1' at row 1
Warning (Code 1292): Truncated incorrect DECIMAL value: ''
Error (Code 1264): Out of range value for column '3230303430323231303134383334 div 1' at row 1
Error (Code 1041): Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
mysql>

When running the lat query on a 64 bit system only the following was returned:
Error (Code 1264): Out of range value for column '3230303430323231303134383334 div 1' at row 1

The following is shown against 5.1.30 but was verified against 5.1.37 as well

How to repeat:
Start the mysql client (with --column-type-info and --show-warnings - to show the additional info).

Run the following queries:
select 3230303430323231303134383334;
select 3230303430323231303134383334 div 1;

Repeat the run with 5.1 and with 5.4.4

Suggested fix:
5.4 should truncate the value with a warning and not error out

Thank you for the bug report. Verified just as described on Mac OS X with recent azalea from bzr vs. 5.1.38.

No errors are logged in the server err file
Problem checked using a 5.1 client against 5.1. and 5.4.4 server and 5.4 client against 5.4 server

No sql-mode setting was set on either server

Problem seems to be observed with other queries as wel such as :
select -8.23567634357708e+18 div 'bbbaaa' ^ 2005; 

It also seems that the problem is limited to the 'div' operation. no error was observed when using '/' '-' 'mod'

and some more:
SELECT 6.79480593779524e+18 && (ATAN(-80) ^ (TAN('2001') & (OCT(-73) div 14)));

 CEILING((  b'0101111100011100110111111110110101111101010010111011101010100' * '2006-09-26 12:37:30') div ATAN(1137770496.2782068736 & CEIL(2.67457522870465e+18)))

Added "when 'div' is used to the title"

Results when running under valgrind (memory error did not show):

Warnings from just before the error:
Warning 1292 Truncated incorrect DECIMAL value: ''
Error 1264 Out of range value for column '3230303430323231303134383334 div 1' at row 1
==27548==
==27548== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 5 from 1)
==27548== malloc/free: in use at exit: 284 bytes in 2 blocks.
==27548== malloc/free: 203 allocs, 201 frees, 282,507 bytes allocated.
==27548== For counts of detected errors, rerun with: -v
==27548== searching for pointers to 2 not-freed blocks.
==27548== checked 2,091,224 bytes.
==27548==
==27548== 256 bytes in 1 blocks are still reachable in loss record 2 of 2
==27548==    at 0x4022D98: malloc (vg_replace_malloc.c:207)
==27548==    by 0x40B00BF: my_malloc (my_malloc.c:37)
==27548==    by 0x40BA7CA: init_dynamic_string (string.c:39)
==27548==    by 0x805C5D7: run_query(st_connection*, st_command*, int) (mysqltest.cc:7111)
==27548==    by 0x805E1DA: main (mysqltest.cc:7899)
==27548==
==27548== LEAK SUMMARY:
==27548==    definitely lost: 0 bytes in 0 blocks.
==27548==      possibly lost: 0 bytes in 0 blocks.
==27548==    still reachable: 256 bytes in 1 blocks.
==27548==         suppressed: 28 bytes in 1 blocks.

According to the discussion of
http://bugs.mysql.com/bug.php?id=8457
we want to return an overflow error (rather than warning) for this case.

For the problem described in bug#8457 we now get (using 5.4 code):

mysql> create table t1 (col1 decimal(60,30));
mysql> insert into t1
       values(123456789012345678901234567890.123456789012345678901234567890);
mysql> select col1 div 1 from t1;
ERROR 1264 (22003): Out of range value for column 'col1 div 1' at row 1
Warning (Code 1292): Truncated incorrect DECIMAL value: ''
Error (Code 1264): Out of range value for column 'col1 div 1' at row 1

This was implemented with this fix:
================================================================
Revision: 
2613.149.1 revid:alexeyk@mysql.com-20081211152705-zeoslkrsoxzphb2j
Parents: 
2613.126.…: due to merge.
Children: 
2707.3.1: Merge to mysql-6.0-alik.
Committer: 
Alexey Kopytov <alexeyk@mysql.com>
Branch: 
my60-bug8457
================================================================

That code made its way into
bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-next-bugfixing
with this merge:
================================================================
Revision: 
2710 revid:horst@mysql.com-20090121133202-2ygkbxoxkuvkcmgb
Parents: 
2709: Make test executable on 32 bit systems., 2617.117.…: Fix default.conf
Children: 
2613.156.6: Merge main 6.0 into 6.0-build, 2599.35.14: merge: 6.0 -> 6.0-rpl-merge, 2711: Move checks for OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN to a s…, 2613.147.…: Merge mysql-6.0 -> mysql-6.0, 2617.117.…: Merge mysql-6.0 -> mysql-6.0-backup-merge, 2613.176.2: Merged:, 2613.153.5: merge
Committer: 
Horst Hunger <horst@mysql.com>
Branch: 
mysql-6.0-qa

merge of main to qa
================================================================

The current problem is due to an interaction with:
================================================================
Revision: 
2617.1.4 revid:sp1r-thek@adventure.(none)-20080422095307-08000
Parents: 
2617.1.3: Merge bk-internal.mysql.com:/home/bk/mysql-6.0-runtime
Children: 
2617.11.1: Merge adventure.(none):/home/thek/Development/cpp/mysql-5.1…, 2617.1.5: Post merge fix for bug 31881.
Committer: 
thek@adventure.(none)
================================================================
which aborts sending of items if thd->is_error()
The relevant diff is here: http://lists.mysql.com/commits/45807
That code has later moved from sql_class.cc/select_send::send_data()
to protocol.cc/Protocol::send_result_set_row

The intention of the fix for
http://bugs.mysql.com/bug.php?id=31881
was to abort the field processing loop.
However, as a side effect, it returns a (quite confusing)
ER_OUT_OF_RESOURCES to the client.

Solution:
Abort the item loop, but *do not* generate an ER_OUT_OF_RESOURCES
if Item::send() has generated an error.

Additionally:
Reconsider whether we want to generate error (rather than warning) for overflow:

mysql> select 3230303430323231303134383334 div 1;
gives an error

mysql> select 3230303430323231303134383334 div 0;
Field   1:  `3230303430323231303134383334 div 0`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     29
Max_length: 0
Decimals:   0
Flags:      BINARY NUM 

+------------------------------------+
| 3230303430323231303134383334 div 0 |
+------------------------------------+
|                               NULL |
+------------------------------------+
1 row in set (0.01 sec)

We actually already have a test case for this problem.
Filed a bug against mtr for that: #47302

func_math.test already has
select 123456789012345678901234567890.123456789012345678901234567890 div 1 as x;

which generates the same errors as 
"select 3230303430323231303134383334 div 1;"

but the second error is not detected by MTR.

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/83195

2817 Tor Didriksen	2009-09-14
      BUG#46606
      
      Don't generate 'Out of memory' errors for 'div' queries with results that are out of range.
     @ mysql-test/r/func_math.result
        Use SHOW WARNINGS to verify the expected warnings/errors.
     @ mysql-test/t/func_math.test
        Use SHOW WARNINGS to verify the expected warnings/errors.
     @ sql/protocol.cc
        Item::send() may generate an error.
        If so, abort the loop without generating the ER_OUT_OF_RESOURCES error.

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/83272

2816 Tor Didriksen	2009-09-15
      BUG#46606
      
      Don't generate 'Out of memory' errors for 'div' queries with results that are out of range.
     @ mysql-test/r/func_math.result
        Use SHOW WARNINGS to verify the expected warnings/errors.
     @ mysql-test/t/func_math.test
        Use SHOW WARNINGS to verify the expected warnings/errors.
     @ sql/protocol.cc
        Item::send() may generate an error.
        If so, abort the loop without generating the ER_OUT_OF_RESOURCES error.

Please note that the bug is not observed in the mysql-next-mr tree (rev2878).
The server version in that branch in 5.4.3 so bug likely introduced in code added between 5.4.3 and 5.4.4

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/83674

2824 Tor Didriksen	2009-09-18 [merge]
      Bug#46606  	Casting error for large numbers in 5.4 when 'div' is used
      
      DIV queries which are out of range will generate an error: Error (Code 1264): Out of range value
      Prior to this fix, they would also generate an: Error (Code 1041): Out of memory; 
     @ mysql-test/r/func_math.result
        Use SHOW WARNINGS to verify the expected warnings/errors.
     @ mysql-test/t/func_math.test
        Use SHOW WARNINGS to verify the expected warnings/errors.
     @ sql/protocol.cc
        Item::send() may generate an error.
        If so, abort the loop without generating the ER_OUT_OF_RESOURCES error.

Approved with some comments over email.

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/83918

2828 Tor Didriksen	2009-09-21 [merge]
      Bug#46606  	Casting error for large numbers in 5.4 when 'div' is used
      
      DIV queries which are out of range will generate an error: Error (Code 1264): Out of range value
      Prior to this fix, they would also generate an: Error (Code 1041): Out of memory; 
     @ mysql-test/r/func_math.result
        Use SHOW WARNINGS to verify the expected warnings/errors.
     @ mysql-test/t/func_math.test
        Use SHOW WARNINGS to verify the expected warnings/errors.
     @ sql/protocol.cc
        Item::send() may generate an error.
        If so, abort the loop without generating the ER_OUT_OF_RESOURCES error.

bzr push into mysql-6.0-bugfixing

Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-20090923103200-kyo2bakdo6tfb2fb) (merge vers: 6.0.14-alpha) (pib:11)

Noted in 6.0.14 changelog.

DIV operations that are out of range generate an error "Error (Code 
1264): Out of range value" (correct), but also an error: "Error (Code
1041): Out of memory" (incorrect). 

Setting report to NDI pending push into 5.4.x.

Noted in 5.5.0 changelog.