Bug #4562 mysqld crash when date "0000-00-00" is used
Submitted: 15 Jul 2004 10:55 Modified: 2 Sep 2004 18:18
Reporter: Joerg Rueschenschmidt Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.3beta OS:Windows (win 2000)
Assigned to: Konstantin Osipov CPU Architecture:Any

[15 Jul 2004 10:55] Joerg Rueschenschmidt 
Description:
This happends when the C API for prepared statements is used.

query : select t_string from t where t_date = ?

You need to define a index on column t_date, otherwise no chrash happends but wrong rows are returned like in bug #4231 reported.

The problem was reproduced on
4.1.3 beta (windows 200) and 4.1.2 (HPUX11.00)

A sample program and table dump is given in how to repeat part.

How to repeat:
Table and data are :
-------------------

CREATE TABLE `t` (
  `t_string` varchar(30) default NULL,
  `t_date` date default NULL,
  KEY `idx1` (`t_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `t` VALUES ('0000-00-00 date','0000-00-00');
INSERT INTO `t` VALUES ('1000-01-01 date','1000-01-01');

C program :
-----------

#include<windows.h> 
#include<stdio.h> 
#include<mysql.h> 

#define BUFFERLENGTH	128

void main( void )
{
	MYSQL_STMT *	stmt;
	char *		query;

	MYSQL_BIND      ParamBind[1];
             unsigned long   ParamLength;
             my_bool         ParamIsNull;
	MYSQL_TIME	date = { 0 };

	MYSQL_BIND      ResultBind[1];
             unsigned long   ResultLength;
             my_bool         ResultIsNull;
             char            ResultStrData[ BUFFERLENGTH ];

	MYSQL *		mysql = mysql_init( NULL );

	if ( ! mysql_real_connect( mysql, "", "", "", "test", 0, NULL, 0 ) )
	{
		printf("mysql_real_connec() failed\n");
		exit(0);
	}

	if ( !(stmt = mysql_stmt_init(mysql) ) )
		exit(0);
	

	query = "select t_string from t where t_date = ?";

	if (mysql_stmt_prepare(stmt, query, strlen(query))) {
		printf( "Prepare ERR: %s\n", query );
		mysql_stmt_close(stmt);
		exit(0);
	}
	

	/* Bind the param buffers */
	ParamBind[0].buffer_type= MYSQL_TYPE_DATE;
	ParamBind[0].buffer = (char*) &date;
	ParamBind[0].is_null= 0;
	ParamBind[0].length = 0;

	if (mysql_stmt_bind_param(stmt, ParamBind)) {
		mysql_stmt_close(stmt);
		exit(0);
	}

	/* Bind the result buffers */
	ResultBind[0].buffer_type	= MYSQL_TYPE_STRING;
	ResultBind[0].buffer		= ResultStrData;
	ResultBind[0].buffer_length	= BUFFERLENGTH;
	ResultBind[0].is_null		= &ResultIsNull;
	ResultBind[0].length		= &ResultLength;

	if (mysql_stmt_bind_result(stmt, ResultBind)) {
		mysql_stmt_close(stmt);
		exit(0);
	}

	/* Set actual parameter values */
	date.time_type	= MYSQL_TIMESTAMP_DATE;
	date.year	= 0;
	date.month	= 0; 
	date.day	= 0;
	
	/* Execute */
	if (mysql_stmt_execute(stmt)) {
		mysql_stmt_close(stmt);
		exit(0);
	}
	

	/* Fetch all rows */
	while (!mysql_stmt_fetch(stmt) )
	{
		printf("fetched : %s\n", ResultStrData);
	}
	

	/* Close the statement */
	mysql_stmt_close(stmt);
	
}
[15 Jul 2004 13:50] Antony Curtis 
The server does indeed crash - during make_join_statistics. 
Verified on FreeBSD 4 with current bk sources. 
The tail of the tracefile is: 
 
WHERE:(original) (test.t.t_date = ?) 
 
WHERE:(after negation elimination) (test.t.t_date = ?) 
 
WHERE:(after const change) (test.t.t_date = ?) 
 
WHERE:(after remove) (test.t.t_date = ?) 
T@3    : | | | | | | | <optimize_cond 
T@3    : | | | | | | | >get_sort_by_table 
T@3    : | | | | | | | <get_sort_by_table 
T@3    : | | | | | | | >make_join_statistics 
T@3    : | | | | | | | | >mi_status 
T@3    : | | | | | | | | <mi_status 
T@3    : | | | | | | | | >init_dynamic_array 
T@3    : | | | | | | | | | >_mymalloc 
T@3    : | | | | | | | | | | enter: Size: 720 
T@3    : | | | | | | | | | | exit: ptr: 8c61c18 
T@3    : | | | | | | | | | <_mymalloc 
T@3    : | | | | | | | | <init_dynamic_array 
T@3    : | | | | | | | | >cleanup 
T@3    : | | | | | | | | | >MYSQL_LOG::close 
T@3    : | | | | | | | | | | enter: exiting: 1 
T@3    : | | | | | | | | | <MYSQL_LOG::close 
T@3    : | | | | | | | | <cleanup 
T@3    : | | | | | | | | >cleanup 
T@3    : | | | | | | | | | >MYSQL_LOG::close 
T@3    : | | | | | | | | | | enter: exiting: 1 
T@3    : | | | | | | | | | <MYSQL_LOG::close 
T@3    : | | | | | | | | <cleanup 
T@3    : | | | | | | | | >cleanup 
T@3    : | | | | | | | | | >MYSQL_LOG::close 
T@3    : | | | | | | | | | | enter: exiting: 1 
T@3    : | | | | | | | | | <MYSQL_LOG::close 
T@3    : | | | | | | | | <cleanup 
T@3    : | | | | | | | | >cleanup 
T@3    : | | | | | | | | | >MYSQL_LOG::close 
T@3    : | | | | | | | | | | enter: exiting: 1 
T@3    : | | | | | | | | | <MYSQL_LOG::close 
T@3    : | | | | | | | | <cleanup
[15 Jul 2004 14:30] Antony Curtis 
Flag bits were being misused. The following patch allows test to run. 
 
===== sql/sql_select.cc 1.350 vs edited ===== 
--- 1.350/sql/sql_select.cc	2004-07-08 13:45:22 +01:00 
+++ edited/sql/sql_select.cc	2004-07-15 13:27:07 +01:00 
@@ -2456,8 +2456,8 @@ 
   if (a->keypart != b->keypart) 
     return (int) (a->keypart - b->keypart); 
   // Place const values before other ones 
-  if ((res= test((a->used_tables & ~OUTER_REF_TABLE_BIT)) - 
-       test((b->used_tables & ~OUTER_REF_TABLE_BIT)))) 
+  if ((res= test((a->used_tables & ~TABLE_MAP_FLAGS)) - 
+       test((b->used_tables & ~TABLE_MAP_FLAGS)))) 
     return res; 
   /* Place rows that are not 'OPTIMIZE_REF_OR_NULL' first */ 
   return (int) ((a->optimize & KEY_OPTIMIZE_REF_OR_NULL) - 
@@ -2589,7 +2589,7 @@ 
     keyuse->ref_table_rows= ~(ha_rows) 0;	// If no ref 
     if (keyuse->used_tables & 
 	(map= (keyuse->used_tables & ~join->const_table_map & 
-	       ~OUTER_REF_TABLE_BIT))) 
+	       ~TABLE_MAP_FLAGS))) 
     { 
       uint tablenr; 
       for (tablenr=0 ; ! (map & 1) ; map>>=1, tablenr++) ; 
@@ -3113,7 +3113,7 @@ 
 prev_record_reads(JOIN *join,table_map found_ref) 
 { 
   double found=1.0; 
-  found_ref&= ~OUTER_REF_TABLE_BIT; 
+  found_ref&= ~TABLE_MAP_FLAGS; 
   for (POSITION *pos=join->positions ; found_ref ; pos++) 
   { 
     if (pos->table->table->map & found_ref) 
@@ -4014,7 +4014,7 @@ 
     for (i=0 ; i < ref->key_parts ; i++,item++) 
       depend_map|=(*item)->used_tables(); 
     ref->depend_map=depend_map & ~OUTER_REF_TABLE_BIT; 
-    depend_map&= ~OUTER_REF_TABLE_BIT; 
+    depend_map&= ~TABLE_MAP_FLAGS; 
     for (JOIN_TAB **tab=join->map2table; 
 	 depend_map ; 
 	 tab++,depend_map>>=1 ) 
===== sql/unireg.h 1.33 vs edited ===== 
--- 1.33/sql/unireg.h	2004-04-26 20:07:09 +01:00 
+++ edited/sql/unireg.h	2004-07-15 13:14:38 +01:00 
@@ -72,6 +72,7 @@ 
 #define PARAM_TABLE_BIT	(((table_map) 1) << (sizeof(table_map)*8-3)) 
 #define OUTER_REF_TABLE_BIT	(((table_map) 1) << (sizeof(table_map)*8-2)) 
 #define RAND_TABLE_BIT	(((table_map) 1) << (sizeof(table_map)*8-1)) 
+#define TABLE_MAP_FLAGS (PARAM_TABLE_BIT|OUTER_REF_TABLE_BIT|RAND_TABLE_BIT) 
 #define MAX_FIELDS	4096			/* Limit in the .frm file */ 
  
 #define MAX_SORT_MEMORY (2048*1024-MALLOC_OVERHEAD)
[15 Jul 2004 16:00] Joerg Rueschenschmidt 
Antony,

i tested your patch. The crash does not appear anymore.

Thanks
[16 Jul 2004 16:16] Joerg Rueschenschmidt 
Anthony,

the problem is not solved. Today i found that the server will still crash if your table contains one row only. The row contains 0000-00-00 as date value.

To reproduce the problem you can remove the other records from your table.
[16 Jul 2004 18:52] Antony Curtis 
After patch, and the change to the test as the user reported, an assertion failure occurs in item.cc line 
974. This is the DBUG_ASSERT(0) line in Item_param::val_str(String *); 
 
The tail of the trace file follows. 
 
WHERE:(original) (test.t.t_date = ?) 
 
WHERE:(after negation elimination) (test.t.t_date = ?) 
 
WHERE:(after const change) (test.t.t_date = ?) 
 
WHERE:(after remove) (test.t.t_date = ?) 
T@3    : | | | | | | | <optimize_cond 
T@3    : | | | | | | | >get_sort_by_table 
T@3    : | | | | | | | <get_sort_by_table 
T@3    : | | | | | | | >make_join_statistics 
T@3    : | | | | | | | | >mi_status 
T@3    : | | | | | | | | <mi_status 
T@3    : | | | | | | | | >init_dynamic_array 
T@3    : | | | | | | | | | >_mymalloc 
T@3    : | | | | | | | | | | enter: Size: 720 
T@3    : | | | | | | | | | | exit: ptr: 8c61c18 
T@3    : | | | | | | | | | <_mymalloc 
T@3    : | | | | | | | | <init_dynamic_array 
T@3    : | | | | | | | | >join_read_const_table 
T@3    : | | | | | | | | | >handler::read_first_row 
T@3    : | | | | | | | | | | >mi_scan_init 
T@3    : | | | | | | | | | | <mi_scan_init 
T@3    : | | | | | | | | | | >mi_scan 
T@3    : | | | | | | | | | | <mi_scan 
T@3    : | | | | | | | | | | >_mi_read_rnd_dynamic_record 
T@3    : | | | | | | | | | | | >my_seek 
T@3    : | | | | | | | | | | | | my: Fd: 13  Hpos: 0  Pos: 0  Whence: 0  MyFlags: 0 
T@3    : | | | | | | | | | | | <my_seek 
T@3    : | | | | | | | | | | | >my_read 
T@3    : | | | | | | | | | | | | my: Fd: 13  Buffer: 8ca21fc  Count: 20  MyFlags: 0 
T@3    : | | | | | | | | | | | <my_read 
T@3    : | | | | | | | | | | | header: Memory: 8ca21fc  Bytes: (20) 
03 00 12 02 03 FC 0F 30 30 30 30 2D 30 30 2D 30 30 20 64 61  
T@3    : | | | | | | | | | | | >my_read 
T@3    : | | | | | | | | | | | | my: Fd: 13  Buffer: 83fdb40  Count: 2  MyFlags: 4 
T@3    : | | | | | | | | | | | <my_read 
T@3    : | | | | | | | | | | | >_mi_rec_unpack 
T@3    : | | | | | | | | | | | <_mi_rec_unpack 
T@3    : | | | | | | | | | | <_mi_read_rnd_dynamic_record 
T@3    : | | | | | | | | | <handler::read_first_row 
T@3    : | | | | | | | | <join_read_const_table 
T@3    : | | | | | | | <make_join_statistics 
T@3    : | | | | | | | >make_select 
T@3    : | | | | | | | <make_select 
T@3    : | | | | | | | >make_join_select 
T@3    : | | | | | | | <make_join_select 
T@3    : | | | | | | | >make_join_readinfo 
T@3    : | | | | | | | <make_join_readinfo 
T@3    : | | | | | | | >TEST_join 
 
Info about JOIN 
t                 type: system   q_keys: 1  refs: 0  key: -1  len: 0 
T@3    : | | | | | | | <TEST_join 
T@3    : | | | | | | <JOIN::optimize 
T@3    : | | | | | | >JOIN::exec 
T@3    : | | | | | | | >do_select 
T@3    : | | | | | | | | >send_fields 
T@3    : | | | | | | | | | packet_header: Memory: 8ca2468  Bytes: (4) 
01 00 00 01  
T@3    : | | | | | | | | | >Protocol::write 
T@3    : | | | | | | | | | <Protocol::write 
T@3    : | | | | | | | | | packet_header: Memory: 8ca2428  Bytes: (4) 
2C 00 00 02  
T@3    : | | | | | | | | | packet_header: Memory: 8ca2468  Bytes: (4) 
01 00 00 03  
T@3    : | | | | | | | | <send_fields 
T@3    : | | | | | | | | >_mymalloc 
T@3    : | | | | | | | | | enter: Size: 16 
T@3    : | | | | | | | | | exit: ptr: 8c71358 
T@3    : | | | | | | | | <_mymalloc 
T@3    : | | | | | | | | >cleanup 
T@3    : | | | | | | | | | >MYSQL_LOG::close 
T@3    : | | | | | | | | | | enter: exiting: 1 
T@3    : | | | | | | | | | <MYSQL_LOG::close 
T@3    : | | | | | | | | <cleanup 
T@3    : | | | | | | | | >cleanup 
T@3    : | | | | | | | | | >MYSQL_LOG::close 
T@3    : | | | | | | | | | | enter: exiting: 1 
T@3    : | | | | | | | | | <MYSQL_LOG::close 
T@3    : | | | | | | | | <cleanup 
T@3    : | | | | | | | | >cleanup 
T@3    : | | | | | | | | | >MYSQL_LOG::close 
T@3    : | | | | | | | | | | enter: exiting: 1 
T@3    : | | | | | | | | | <MYSQL_LOG::close 
T@3    : | | | | | | | | <cleanup 
T@3    : | | | | | | | | >cleanup 
T@3    : | | | | | | | | | >MYSQL_LOG::close 
T@3    : | | | | | | | | | | enter: exiting: 1 
T@3    : | | | | | | | | | <MYSQL_LOG::close 
T@3    : | | | | | | | | <cleanup
[29 Jul 2004 20:22] Konstantin Osipov 
Test case in SQL syntax for prepared statements:
create table t1
(
 a varchar(30) default NULL,
 b date default NULL,
 key `idx` (b)
) engine=MYISAM DEFAULT CHARSET=latin1;
insert into t1 values ('0000-00-00 date','0000-00-00');
insert into t1 values ('1000-01-01 date','1000-01-01');
set @arg1='0000-00-00';
prepare stmt1 from 'select a from t1  where b=?'
execute stmt1 using @arg1;
deallocate prepare stmt1;
drop table t1;
[2 Sep 2004 16:22] Konstantin Osipov 
This should be fixed along with bug#4231.
[2 Sep 2004 18:18] Konstantin Osipov 
Fixed in 4.1.5