MySQL Bugs: #36139: float, zerofill, crash with subquery

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

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) ;

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>

this bug seems to have appeared in 5.1.24.  earlier versions didn't crash.

The crash can be also observed on 5.0.60-bk.

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
#

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());
}

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.

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.

Pushed into {5.0,5.1}-bugteam trees.

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).

Pushed into 5.1.25-rc

Pushed into 6.0.6-alpha

Pushed into 5.0.62

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.