MySQL Bugs: #4912: mysqld crashs in case a statement is executed a second time

Bug #4912	mysqld crashs in case a statement is executed a second time
Submitted:	5 Aug 2004 16:07	Modified:	21 Aug 2004 0:05
Reporter:	Joerg Rueschenschmidt	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.3.beta	OS:	Linux (LINUX and others)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
This happends when the C API for prepared statements is used.

We are using a query like this :

     select col1,col2 for t1 where ( not (col1 = 'aa' and col2 < 'zzz'))

The problem does not appear when the "NOT" is removed from the where clause.

It was reproduced on following platforms

4.1.2  (HPUX11.00)
4.1.3 beta (windows 2000)
4.1.3 beta (Linux Suse ES 8.0 SP3)

How to repeat:
#include <stdlib.h>
#include <mysql.h>

#define DROP_SAMPLE_TABLE 	"DROP TABLE IF EXISTS t1"

#define CREATE_SAMPLE_TABLE	"CREATE TABLE t1( col1 VARCHAR(2) not null,\
						  col2 VARCHAR(3) not null) TYPE = INNODB"

#define SAMPLE_QUERY    	"SELECT col1,col2\
                         	 FROM t1\
                         	 WHERE ( NOT (col1 = 'aa' AND col2 < 'zzz'))"

void do_test( MYSQL * mysql, char ** desc )
{
	MYSQL_STMT *	stmt;
	int rc;

	if( mysql_query(mysql, DROP_SAMPLE_TABLE ) ) {
		printf("ERR : Drop table failed (%s)\n", mysql_error(mysql));
		return;
	}

	if( mysql_query(mysql, CREATE_SAMPLE_TABLE )  ) {
		printf("ERR : Create table error (%s)\n", mysql_error(mysql));
		return ;
	}

	if ( !(stmt = mysql_stmt_init(mysql) ) ) {
                return ;
        }
	
	if (mysql_stmt_prepare(stmt, SAMPLE_QUERY, strlen(SAMPLE_QUERY))) {
		printf( "ERR : Prepare (%s)\n", mysql_error(mysql));
		mysql_stmt_close(stmt);
		return ;
	}

	if (mysql_stmt_execute(stmt)) {
		printf("ERR : First execute failed (%s)\n",mysql_error(mysql));
                mysql_stmt_close(stmt);
                return ;
        }

	while ( 1 ) {
		rc = mysql_stmt_fetch(stmt);

		if ( rc == 1 ) {
			printf("ERR : fetch (%s)",mysql_error(mysql));
			mysql_stmt_close(stmt);
			return ;
		}

		if ( rc == MYSQL_NO_DATA ) {
			printf("INF : First execute (end of data found) - okay !\n");
			break;
		}
	}
 
	if (mysql_stmt_execute(stmt)) {
                printf("ERR : Second execute failed (%s)\n",mysql_error(mysql));
		if (mysql_errno(mysql) == 2013)
			printf("RESULT : Bug still present !!!\n");		
                mysql_stmt_close(stmt);
                return ;
        }
	
	printf("RESULT : Bug is solved !!!\n");

	mysql_stmt_close(stmt);

}

main()
{
	MYSQL *		mysql = mysql_init( NULL );
	int		bugstat;
	char *		desc;

	/* Bind data */

	if ( ! mysql_real_connect( mysql,
					/* host */ "test",
					/* user */ "test",
					/* pwd */ "test",
					/* dbase */ "test", 

					/* uint port */ 0,
					/* char * socket */ NULL,
					/* client_flag */ 0 ) )
	{
		printf(" mysql_real_connec() failed\n");
		exit(0);
	}

	do_test( mysql, &desc );
}

Hi,

the given query is an example only. It looks like the problem appears in general when a filter methode is used in a query.

e.g.

where ( not ( <expr with more then 2 conditions >)

Regards ...

Joerg

Joerg, thank you very much for this detailed bug report and program, which made it easy to reproduce.
Konstantin: I am assigning it to you as it pops during prepared statements. It first aborts on your assertion DBUG_ASSERT("Pure virtual method called." == "Aborted"); and then after removing this assertion it segfaults here:
longlong Item_func_not::val_int()
{
  DBUG_ASSERT(fixed == 1);
  double value=args[0]->val(); // <-- here
Here's how I compiled the testcase provided by our user:
client]$ gcc -g3 -I ../include -c -o test.o test.c ;gcc  -o test test.o  ../libmysql/.libs/libmysqlclient.a -L/usr/lib -lcrypt   -lz -lssl ; ./test
INF : First execute (end of data found) - okay !
and then it hangs because server crashed.

Very good to see that my assertions work!-) - it means I'm on the right track.
Thanks, Guilhem.

I was able to repeat the bug using SQL syntax for prepared statements:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (col1 VARCHAR(2), col2 VARCHAR(3))

PREPARE stmt1 FROM 
"SELECT col1, col2 FROM t1 WHERE (NOT (col1 = 'aa' AND col2 < 'zzz'))"

EXECUTE stmt1;
EXECUTE stmt1;

Fixed in 4.1.4: bk commit - 4.1 tree (konstantin:1.1979) BUG#4912