Bug #4184 mysql_stmt_fetch() fetches wrong MSQL_TIME values

• Submitted: 17 Jun 2004 13:51
• Modified: 1 Sep 2004 16:26
• Reporter: Anthon Ouwendijk
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.2
• OS: HP/UX (HP-UX 11)
• Assigned to: Konstantin Osipov
• CPU Architecture: Any

[17 Jun 2004 13:51] Anthon Ouwendijk

Description:
Description: This happens when the C API for prepared statements is used.
A sample C source has been included.

Queries that
 - GROUP on more than one column,
 - AND have a aggr function (max/min/avg) on a <date> column
 - AND have a DESC order on one of the group-by columns
return corrupt MYSQL_TIME values.

The following query succeeds:
    select col1, col2, MIN(<date>) group by col1, col2 order by col1<n>
The following query fails:
    select col1, col2, MIN(<date>) group by col1, col2 order by col1<n> DESC

How to repeat:
Table dump:

CREATE TABLE tdbtst180000 (
  t_empno int(11) NOT NULL default '0',
  t_projno varchar(6) collate latin1_bin NOT NULL default '',
  t_emstdate date NOT NULL default '0000-00-00',
  t_emendate date NOT NULL default '0000-00-00',
  PRIMARY KEY  (t_empno,t_projno,t_emstdate,t_emendate),
  UNIQUE KEY tdbtst180000$idx2 (t_projno,t_empno,t_emstdate,t_emendate)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (10, 'AD3100', '1982-01-01', '1982-07-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (10, 'MA2100', '1982-01-01', '1982-11-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (10, 'MA2110', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (20, 'PL2100', '1982-01-01', '1982-09-15');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (30, 'IF1000', '1982-06-01', '1983-01-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (30, 'IF2000', '1982-01-01', '1983-01-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (50, 'OP1000', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (50, 'OP2010', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (70, 'AD3110', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (90, 'OP1010', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (100, 'OP2010', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (110, 'MA2100', '1982-01-01', '1982-03-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (130, 'IF1000', '1982-01-01', '1982-10-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (140, 'IF1000', '1982-10-01', '1983-01-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (140, 'IF2000', '1982-01-01', '1982-03-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (150, 'MA2112', '1982-01-01', '1982-07-15');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (160, 'MA2113', '1982-07-15', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (170, 'MA2112', '1982-01-01', '1983-06-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (170, 'MA2113', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (180, 'MA2113', '1982-04-01', '1982-06-15');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (190, 'MA2112', '1982-02-01', '1982-10-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (200, 'MA2111', '1982-01-01', '1982-06-15');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (210, 'MA2113', '1982-10-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (220, 'MA2111', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (230, 'AD3111', '1982-01-01', '1982-03-15');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (240, 'AD3111', '1982-02-15', '1982-09-15');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (250, 'AD3112', '1982-01-01', '1982-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (260, 'AD3113', '1982-06-15', '1982-07-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (270, 'AD3113', '1982-03-01', '1982-04-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (280, 'OP1010', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (290, 'OP1010', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (300, 'OP1010', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (310, 'OP1010', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (320, 'OP2011', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (330, 'OP2012', '1982-01-01', '1983-02-01');
INSERT INTO tdbtst180000 (t_empno, t_projno, t_emstdate, t_emendate) VALUES (340, 'OP2013', '1982-01-01', '1983-02-01');

Reproducing C-code:

#include <stdlib.h>
#include <mysql.h>

typedef enum {
	BUG_UNKNOWN	= -1,	/* Error occurred */
	BUG_SOLVED	= 0,
	BUG_PRESENT	= 1,
} BugStatus;

char * str_bug_stat( BugStatus bugstat )
{
	if ( bugstat == BUG_PRESENT  ) {
		return "Bug still present";
	} else if ( bugstat == BUG_SOLVED ) {
		return "Bug solved";
	}
	return "Status UNKNOWN (or error occurred)";
}

BugStatus do_the_bug_test( MYSQL * mysql, char ** desc )
{
	MYSQL_STMT *	stmt;

	/* Bind data */
	MYSQL_BIND	bind[3];
	unsigned long	length[2];
	my_bool		is_null[2];
	int		int_data;
	MYSQL_TIME	time_data;
	char		str_data[64];

	char		query[256];
	int		rows = 0;

	*desc =
	"MySQL Bug Baan-040616	\"SELECT col1, col2, fun(<date>) group by col1, col2 order by col<n> DESC\" returns corrupt <date>\n"
	"			ok:	select col1, col2, MIN(<date>) group by col1, col2 order by col1<n>\n"
	"			err:	select col1, col2, MIN(<date>) group by col1, col2 order by col1<n> DESC\n";

	if ( !(stmt = mysql_stmt_init(mysql) ) ) {
		return BUG_UNKNOWN;
	}
	sprintf( query, "select t_empno, t_projno, MIN( t_emstdate ) from tdbtst180000 group by 1, 2 order by 1 desc" );

	if (mysql_stmt_prepare(stmt, query, strlen(query))) {
		mysql_stmt_close(stmt);
		return BUG_UNKNOWN;
	}

	if (mysql_stmt_execute(stmt)) {
		mysql_stmt_close(stmt);
		return BUG_UNKNOWN;
	}

	/* Bind the result buffers */

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

	bind[1].buffer_type= MYSQL_TYPE_STRING;
	bind[1].buffer= str_data;
	bind[1].buffer_length = sizeof(str_data);
	bind[1].is_null= &is_null[1];
	bind[1].length= &length[1];

	bind[2].buffer_type= MYSQL_TYPE_DATE;
	bind[2].buffer= (char *)&time_data;
	bind[2].is_null= &is_null[2];
	bind[2].length= &length[2];

	if (mysql_stmt_bind_result(stmt, bind)) {
		mysql_stmt_close(stmt);
		return BUG_UNKNOWN;
	}

	/* Fetch all rows */
	while (!mysql_stmt_fetch(stmt))
	{
		MYSQL_TIME * tp = &time_data;

		rows++;

		if (is_null[0] || is_null[1] || is_null[2])
			return BUG_UNKNOWN;
			
		printf ( "empno %d, projno %s, date %d/%d/%d\n", int_data, str_data, tp->year, tp->month, tp->day );

		if ( tp->year != 1982
		  || tp->month < 1	|| tp->month > 12
		  || tp->day < 1	|| tp->day > 31
		) {
			return BUG_PRESENT; }
	}

	/* Close the statement */
	mysql_stmt_close(stmt);

	if ( rows ) {
		return BUG_SOLVED;
	} else {
		return BUG_UNKNOWN;
	}
}

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

	/* Bind data */

	if ( ! mysql_real_connect( mysql,
					/* host */ NULL,
					/* user */ User,
					/* pwd */ Passwd,
					/* dbase */ Dbase, 

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

	bugstat = do_the_bug_test( mysql, &desc );
	fprintf(stdout, "%s\n%s\n\n", desc, str_bug_stat( bugstat ) );

	return 0;
}

[20 Jul 2004 5:34] Konstantin Osipov

Could you please check if this one is still present?
See also my comment for Bug #4183 "mysql_stmt_fetch() fetches numbers in wrong byte order"

[1 Sep 2004 16:26] Konstantin Osipov

This bug was fixed along with Bug#4183.

mysqldev@hpux11:~/kostja/mysql-4.1.4-gamma/tests> make
        cc -DUNDEF_THREADS_HACK -I. -I. -I.. -I../include      -O -DDBUG_OFF -g 
   -DHPUX11 -DHAVE_BROKEN_PREAD -DDONT_USE_FINITE -DHAVE_BROKEN_GETPASS -DNO_FCN
_REMOVE_THREAD_WRAPPERS -DHAVE_BROKEN_PTHREAD_COND_TIMEDWAIT -DHAVE_BROKEN_INLIN
cc: warning 454: Option O0 overrides previous setting of O2.
cc: warning 483: The DOC (Debug of Optimized Code) option is unavailable with th
ored.
        /bin/sh ../libtool --preserve-dup-deps --mode=link cc  -O -DDBUG_OFF -g 
   -DHPUX11 -DHAVE_BROKEN_PREAD -DDONT_USE_FINITE -DHAVE_BROKEN_GETPASS -DNO_FCN
_REMOVE_THREAD_WRAPPERS -DHAVE_BROKEN_PTHREAD_COND_TIMEDWAIT -DHAVE_BROKEN_INLIN
 ../libmysql/libmysqlclient.la  -lnsl -lm  ../zlib/libz.la 
cc -O -DDBUG_OFF -g -z +O0 +DA2.0W +DD64 -DHPUX11 -DHAVE_BROKEN_PREAD -DDONT_USE
N_GETPASS -DNO_FCNTL_NONBLOCK -DDO_NOT_REMOVE_THREAD_WRAPPERS -DHAVE_BROKEN_PTHR
DHAVE_BROKEN_INLINE -o 4184 4184.o  ../libmysql/.libs/libmysqlclient.a -lpthread
libs/libz.a -lpthread -lnsl -lm -lpthread
cc: warning 454: Option O0 overrides previous setting of O2.
mysqldev@hpux11:~/kostja/mysql-4.1.4-gamma/tests> ./4184
empno 340, projno OP2013, date 1982/1/1
empno 330, projno OP2012, date 1982/1/1
empno 320, projno OP2011, date 1982/1/1
empno 310, projno OP1010, date 1982/1/1
empno 300, projno OP1010, date 1982/1/1
empno 290, projno OP1010, date 1982/1/1
empno 280, projno OP1010, date 1982/1/1
empno 270, projno AD3113, date 1982/3/1
empno 260, projno AD3113, date 1982/6/15
empno 250, projno AD3112, date 1982/1/1
empno 240, projno AD3111, date 1982/2/15
empno 230, projno AD3111, date 1982/1/1
empno 220, projno MA2111, date 1982/1/1
empno 210, projno MA2113, date 1982/10/1
empno 200, projno MA2111, date 1982/1/1
empno 190, projno MA2112, date 1982/2/1
empno 180, projno MA2113, date 1982/4/1
empno 170, projno MA2112, date 1982/1/1
empno 170, projno MA2113, date 1982/1/1
empno 160, projno MA2113, date 1982/7/15
empno 150, projno MA2112, date 1982/1/1
empno 140, projno IF1000, date 1982/10/1
empno 140, projno IF2000, date 1982/1/1
empno 130, projno IF1000, date 1982/1/1
empno 110, projno MA2100, date 1982/1/1
empno 100, projno OP2010, date 1982/1/1
empno 90, projno OP1010, date 1982/1/1
empno 70, projno AD3110, date 1982/1/1
empno 50, projno OP1000, date 1982/1/1
empno 50, projno OP2010, date 1982/1/1
empno 30, projno IF1000, date 1982/6/1
empno 30, projno IF2000, date 1982/1/1
empno 20, projno PL2100, date 1982/1/1
empno 10, projno AD3100, date 1982/1/1
empno 10, projno MA2100, date 1982/1/1
empno 10, projno MA2110, date 1982/1/1
MySQL Bug Baan-040616  "SELECT col1, col2, fun(<date>) group by col1, col2 order
urns corrupt <date>
     ok: select col1, col2, MIN(<date>) group by col1, col2 order by col1<n>
     err:  select col1, col2, MIN(<date>) group by col1, col2 order by col1<n> D

Bug solved

mysqldev@hpux11:~/kostja/mysql-4.1.4-gamma/tests>