Bug #36139 float, zerofill, crash with subquery
Submitted: 16 Apr 2008 13:44 Modified: 7 May 2008 19:22
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.1.25-bk, 5.0-bk, 6.0.5-bk OS:Any
Assigned to: Sergey Petrunya CPU Architecture:Any
Tags: ZEROFILL
Triage: D1 (Critical)

[16 Apr 2008 13:44] Shane Bester
Description:
when a float with zerofill is used in some comparison, we get this crash:

mysqld-debug.exe!String::length
mysqld-debug.exe!Field_num::prepend_zeros
mysqld-debug.exe!convert_zerofill_number_to_string
mysqld-debug.exe!Item_field::equal_fields_propagator
mysqld-debug.exe!Item::compile
mysqld-debug.exe!Item_func::compile
mysqld-debug.exe!build_equal_items_for_cond
mysqld-debug.exe!build_equal_items_for_cond
mysqld-debug.exe!build_equal_items
mysqld-debug.exe!optimize_cond
mysqld-debug.exe!JOIN::optimize
mysqld-debug.exe!mysql_select
mysqld-debug.exe!handle_select
mysqld-debug.exe!execute_sqlcom_select
mysqld-debug.exe!mysql_execute_command
mysqld-debug.exe!mysql_parse
mysqld-debug.exe!dispatch_command
mysqld-debug.exe!do_command
mysqld-debug.exe!handle_one_connection
mysqld-debug.exe!pthread_start
mysqld-debug.exe!_callthreadstart
mysqld-debug.exe!_threadstart
kernel32.dll!BaseThreadStart

How to repeat:
drop table if exists t1;
drop table if exists t2;
create table `t1` (`a` float(5,4) zerofill) engine=myisam;
create table `t2` (`a` float(5,4),`b` float(2,0)) engine=myisam;
select t1.a from t1 where   
t1.a= (select b from t2 limit 1) and not
t1.a= (select a from t2 limit 1) ;
[16 Apr 2008 13:57] Miguel Solorzano
Thank you for the bug report. Verified on Suse 10.3 X64:

miguel@hegel:~/dbs> 6.0/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.5-alpha-debug Source distribution

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

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table `t1` (`a` float(5,4) zerofill) engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> create table `t2` (`a` float(5,4),`b` float(2,0)) engine=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql> select t1.a from t1 where   
    -> t1.a= (select b from t2 limit 1) and not
    -> t1.a= (select a from t2 limit 1) ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[16 Apr 2008 14:09] Shane Bester
this bug seems to have appeared in 5.1.24.  earlier versions didn't crash.
[21 Apr 2008 18:56] Sergey Petrunya
The crash can be also observed on 5.0.60-bk.
[21 Apr 2008 19:42] Sergey Petrunya
The problem has been introduced by this change:

# ChangeSet
#   2008/02/15 15:47:32+02:00 gkodinov@magare.gmz 
#   Bug #31887: DML Select statement not returning same results 
#   when executed in version 5
#
[21 Apr 2008 20:20] Sergey Petrunya
item.cc:

static void convert_zerofill_number_to_string(Item **item, Field_num *field)
{
  char buff[MAX_FIELD_WIDTH],*pos;
  String tmp(buff,sizeof(buff), field->charset()), *res;

  res= (*item)->val_str(&tmp);
// The problem starts here. (*item)->val_str() evaluates to SQL's NULL, i.e
// we get:
//    res= NULL
//    (*item)->is_null() == TRUE.
// and the following statement will crash because it can't handle res==NULL
// case:

  field->prepend_zeros(res);
  pos= (char *) sql_strmake (res->ptr(), res->length());
  *item= new Item_string(pos, res->length(), field->charset());
}
[21 Apr 2008 21:46] 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/45790

ChangeSet@1.2610, 2008-04-22 01:45:48+04:00, sergefp@mysql.com +3 -0
  BUG#36139 "float, zerofill, crash with subquery"
  - Make convert_zerofill_number_to_string() take into account that the 
    constant it is converting may evaluate to NULL.
[21 Apr 2008 22:53] 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/45791

ChangeSet@1.2609, 2008-04-22 02:53:12+04:00, sergefp@mysql.com +3 -0
  BUG#36139 "float, zerofill, crash with subquery"
  - Make convert_zerofill_number_to_string() take into account that the 
    constant it is converting may evaluate to NULL.
[22 Apr 2008 2:31] Sergey Petrunya
Pushed into {5.0,5.1}-bugteam trees.
[23 Apr 2008 9:24] 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/45869

ChangeSet@1.2612, 2008-04-23 14:22:49+05:00, gshchepa@host.loc +2 -0
  subselect.test, subselect.result:
    Post-commit minor cleanup of testcase (bug#36139).
[1 May 2008 6:16] Bugs System
Pushed into 5.1.25-rc
[1 May 2008 6:19] Bugs System
Pushed into 6.0.6-alpha
[6 May 2008 0:25] Bugs System
Pushed into 5.0.62
[7 May 2008 19:22] Paul Dubois
Noted in 5.0.62, 5.1.25, 6.0.6 changelogs.

Conversion of a FLOAT ZEROFILL value to string could cause a server
crash if the value was NULL.