Bug #3420 Prepared statement returns all rows with 2 bounds parameters

• Submitted: 8 Apr 2004 7:18
• Modified: 13 Apr 2004 14:51
• Reporter: Fabrice Gony
• Status: Duplicate
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.1a
• OS: Windows (win32)
• Assigned to: Assigned Account
• CPU Architecture: Any

[8 Apr 2004 7:18] Fabrice Gony

Description:
When I try this query : 
SELECT ID1,VALUE1 FROM TABLE1 WHERE ID1=? OR VALUE1=?
I receive all rows from the table.
but this queries works:
SELECT ID1,VALUE1 FROM TABLE1 WHERE ID1=? AND VALUE1=?
SELECT ID1,VALUE1 FROM TABLE1 WHERE VALUE1=?
SELECT ID1,VALUE1 FROM TABLE1 WHERE ID1=?

+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id1    | int(11)      |      |     | 0       |       |
| value2 | varchar(100) | YES  |     |         |       |
| value1 | varchar(100) | YES  |     |         |       |
+--------+--------------+------+-----+---------+-------+

How to repeat:
	#define STRING_SIZE 50
	#define SELECT_SAMPLE "SELECT ID1,VALUE1 FROM TABLE1 WHERE ID1=? OR VALUE1=?"

	MYSQL_STMT    *stmt;
	MYSQL_BIND    bind[2];
	my_ulonglong  affected_rows;
	int           param_count,column_count;
	int           int_data;
	char          str_data[STRING_SIZE];
	unsigned long str_length;
	MYSQL		  *mysql;
	MYSQL_RES     *prepare_meta_result;
	MYSQL_BIND    bind_r[2];
	my_bool       is_null[2];
	unsigned long length[2];
	int           int_data_r;
	char          str_data_r[STRING_SIZE];
	int			  row_count;

	*stderr = *stdout;

	if ((mysql = mysql_init(NULL)) == NULL) {
		fprintf(stderr, " mysql_init() failed\n");
		fprintf(stderr, " %s\n", mysql_error(mysql));
		exit(0);
	}

	if (mysql_real_connect(mysql,"host","root","",
				"test",3307,NULL,0) == NULL) {
		fprintf(stderr, " mysql_real_connect() failed\n");
		fprintf(stderr, " %s\n", mysql_error(mysql));
		exit(0);
	}

	stmt = mysql_prepare(mysql, SELECT_SAMPLE, strlen(SELECT_SAMPLE));
	if (!stmt) {
		fprintf(stderr, " mysql_prepare() failed\n");
		fprintf(stderr, " %s\n", mysql_error(mysql));
		exit(0);
	}
	fprintf(stdout, " prepare successful\n");

	param_count= mysql_param_count(stmt);
	fprintf(stdout, " total parameters in SELECT: %d\n", param_count);

	if (param_count != 2)
		fprintf(stderr," invalid parameter count returned by MySQL\n");
	else {
		bind[0].buffer_type= MYSQL_TYPE_LONG;
		bind[0].buffer= (char *)&int_data;
		bind[0].is_null= 0;
		bind[0].length= 0;

		bind[1].buffer_type= MYSQL_TYPE_VAR_STRING;
		bind[1].buffer= (char *)str_data;
		bind[1].buffer_length= STRING_SIZE;
		bind[1].is_null= 0;
		bind[1].length= &str_length;
			
		if (mysql_bind_param(stmt, bind)) {
			fprintf(stderr, " mysql_bind_param() failed\n");
			fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
			exit(0);
		}

		int_data= 1;
		strncpy(str_data, "hh", STRING_SIZE); 
		str_length= strlen(str_data);
	}

	if (mysql_execute(stmt)) {
		fprintf(stderr, " mysql_execute(), 1 failed\n");
		fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
		exit(0);
	}
	  
	affected_rows= mysql_stmt_affected_rows(stmt);
	fprintf(stdout, " total affected rows: %ld\n", affected_rows);

	prepare_meta_result = mysql_get_metadata(stmt);
	if (!prepare_meta_result) {
		fprintf(stderr, " mysql_get_metadata(), returned no meta information\n");
		fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
		exit(0);
	}

	column_count= mysql_num_fields(prepare_meta_result);
	fprintf(stdout, " total columns in SELECT statement: %d\n", column_count);

	bind_r[0].buffer_type= MYSQL_TYPE_LONG;
	bind_r[0].buffer= (char *)&int_data_r;
	bind_r[0].is_null= &is_null[0];
	bind_r[0].length= &length[0];

	bind_r[1].buffer_type= MYSQL_TYPE_VAR_STRING;
	bind_r[1].buffer= (char *)str_data_r;
	bind_r[1].buffer_length= STRING_SIZE;
	bind_r[1].is_null= &is_null[1];
	bind_r[1].length= &length[1];
	 
	if (mysql_bind_result(stmt, bind_r)) {
		fprintf(stderr, " mysql_bind_result() failed\n");
		fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
		exit(0);
	}

	if (mysql_stmt_store_result(stmt)) {
		fprintf(stderr, " mysql_stmt_store_result() failed\n");
		fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
		exit(0);
	}

	row_count= 0;
	fprintf(stdout, "Fetching results ...\n");

	while (!mysql_fetch(stmt)) {
		row_count++;
		fprintf(stdout, "  row %d\n", row_count);

		fprintf(stdout, "   column1 (integer)  : ");
		if (is_null[0])
			fprintf(stdout, " NULL\n");
		else
			fprintf(stdout, " %d(%ld)\n", int_data_r, length[0]);

		fprintf(stdout, "   column2 (string)   : ");
		if (is_null[1])
			fprintf(stdout, " NULL\n");
		else
			fprintf(stdout, " %s(%ld)\n", str_data_r, length[1]);

		fprintf(stdout, "\n");
	}

	fprintf(stdout, " total rows fetched: %d\n", row_count);

	mysql_free_result(prepare_meta_result);

	if (mysql_stmt_close(stmt)) {
		fprintf(stderr, " failed while closing the statement\n");
		fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
		exit(0);
	}

[12 Apr 2004 16:29] Sergey Petrunya

Results are correct on server built from today's 4.1 repository.

[12 Apr 2004 20:17] Sergey Petrunya

I was able to repeat with 4.1.1a binary provided at www.mysql.com

[13 Apr 2004 14:51] Sergey Petrunya

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

Thank you for the bug report. The bug has already been fixed in the source repository. 

Additional info: added test case:
ChangeSet@1.1791, 2004-04-14 01:42:01+04:00, [e-mail]