Bug #11111 fetch from view returns wrong data
Submitted: 6 Jun 2005 12:15 Modified: 17 Jun 2005 2:39
Reporter: Georg Richter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.7-beta OS:Linux (Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[6 Jun 2005 12:15] Georg Richter
Description:
When using a prepared statement to retrieve data from a view via SELECT DISTINCT column order isn't correct.

Without DISTINCT it works ok.

Output from sample program:

Query returns SDLTIME: 094129
Stmt returns SDLTIME: 20050606

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

int main (int argc, char **argv)
{
	MYSQL 		*mysql;
	MYSQL_STMT 	*stmt;
	MYSQL_RES 	*res;
	MYSQL_ROW   row;
	MYSQL_BIND	bind[10];
	char		buf[10][100];
	long		len[10];
	int i;
	char * query = "SELECT DISTINCT JOBNAME ,JOBCOUNT ,JOBGROUP ,INTREPORT ,STEPCOUNT, SDLSTRTDT," \
                   "SDLSTRTTM ,BTCSYSTEM ,SDLDATE ,SDLTIME FROM V_OP WHERE JOBNAME='RDDIMPDP'";

	mysql = mysql_init(NULL);
	mysql_real_connect(mysql, "localhost", "root", "", "test", 0, NULL, 0);

	mysql_query(mysql, query);

	res = mysql_store_result(mysql);
	row = mysql_fetch_row(res);

	printf("Query returns SDLTIME: %s\n", row[9]);
	mysql_free_result(res);
	

	stmt = mysql_stmt_init(mysql);

	mysql_stmt_prepare(stmt, query, strlen(query));
	mysql_stmt_execute(stmt);

	for (i=0; i < 10; i++) {
		memset(&bind[i], '\0', sizeof(MYSQL_BIND));
		bind[i].buffer_type= MYSQL_TYPE_STRING;
		bind[i].buffer= (gptr *)&buf[i];
		bind[i].buffer_length= 100;
		bind[i].length= &len[i];
	}

	if (mysql_stmt_bind_result(stmt, bind))
		printf("Error: %s\n", mysql_stmt_error(stmt));

	mysql_stmt_fetch(stmt);
	printf("Stmt returns SDLTIME: %s\n", buf[9]);
	
	mysql_stmt_close(stmt);
	mysql_close(mysql);
}
[6 Jun 2005 12:16] Georg Richter
Tables and View for Bug #11111

Attachment: test.sql.gz (application/x-gzip, text), 20.12 KiB.

[6 Jun 2005 15:04] MySQL Verification Team
Thank you for the bug report.
[10 Jun 2005 22:45] 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/internals/25895
[14 Jun 2005 13:31] 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/internals/25978
[14 Jun 2005 19:19] Evgeny Potemkin
Wrong method for creating temporary field was choosen, which results in
sending int field with int header but lonlong data.

Fixed in 5.0.7, cs 1.1936
[14 Jun 2005 22:22] 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/internals/26003
[17 Jun 2005 2:39] Paul DuBois
Noted in 5.0.7 changelog.
[19 Jun 2005 13:03] 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/internals/26155