MySQL Bugs: #5615: type of aggregate function column wrong when using group by

Bug #5615	type of aggregate function column wrong when using group by
Submitted:	16 Sep 2004 19:37	Modified:	2 Mar 2005 21:18
Reporter:	Jim Nusbaum	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.3	OS:	Any (any)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
When using the C API the type of a returned column is wrong in certain circumstances.  In particular the type of a MAX(datetime) column is sometimes returned as datetime and sometimes returned as varchar, depending on whether a group by is used or not.

How to repeat:
Consider the following code run against a MySQL server 4.1.3 running under Solaris 8 on a multiprocessor SPARC machine:

#include <mysql.h> /* Headers for MySQL usage */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/time.h>

static MYSQL test_db;

int main(int argc, 
         char **argv)
{
   MYSQL test_db;
   MYSQL_RES* res = 0;
   MYSQL_FIELD* fmt = 0;
   int x;

   if (!mysql_init(&test_db))
   {
      printf("Error initing mysql lib\n");
      exit(1);
   }
   
   mysql_options(&test_db, MYSQL_READ_DEFAULT_GROUP, "client");

   if (!mysql_real_connect(&test_db, "server", 
                                     "operator", "pwd", "test", 0, 0, 0))
   { 
      printf("Error connecting to database: %s\n", mysql_error(&test_db));
      exit(1);
   }

   /* create tables */
   char tcreate[] = 
   "CREATE TABLE test (product VARCHAR(32), stamp DATETIME) TYPE = MEMORY";

   if (mysql_real_query(&test_db, tcreate, strlen(tcreate)))
   { /* Make query */
      printf("error creating test: %s\n", mysql_error(&test_db));
      exit(1);
   }

   for (x = 0; x < 100; ++x)
   {
      char insert[1024];
      const char* products[] = {
         "A", "B", "C", "D", "E"
      };
      int i = x % 5;

      sprintf(insert, 
              "INSERT INTO test(product, stamp) VALUES('%s', '2004-09-15 13:45:05')",
              products[i]);
      if (mysql_real_query(&test_db, insert, strlen(insert)))
      { /* Make query */
         printf("%s\n", mysql_error(&test_db));
         exit(1);
      }
   }

   const char* select1 = "SELECT MAX(stamp) FROM test";
   if (mysql_real_query(&test_db, select1, strlen(select1)))
   { /* Make query */
      printf("%s\n", mysql_error(&test_db));
      exit(1);
   }

   res = mysql_store_result(&test_db); /* Download result from server */

   // print out types of result columns
   fmt = mysql_fetch_field_direct(res, 0);
   printf("field[0] (MAX(datetime)) type is %d\n", fmt->type);

   mysql_free_result(res); /* Release memory used to store results. */

   const char* select2 = "SELECT product, MAX(stamp) FROM test GROUP BY product";
   if (mysql_real_query(&test_db, select2, strlen(select2)))
   { /* Make query */
      printf("%s\n", mysql_error(&test_db));
      exit(1);
   }

   res = mysql_store_result(&test_db); /* Download result from server */

   // print out types of result columns
   fmt = mysql_fetch_field_direct(res, 0);
   printf("field[0] (varchar) type is %d\n", fmt->type);
   fmt = mysql_fetch_field_direct(res, 1);
   printf("field[1] (MAX(datetime)) type is %d\n", fmt->type);
   
   mysql_free_result(res); /* Release memory used to store results. */

   char drop[] = "DROP TABLE test";
   if (mysql_real_query(&test_db, drop, strlen(drop)))
   { /* Make query */
      printf("error dropping test: %s\n", mysql_error(&test_db));
      exit(1);
   }

   mysql_close(&test_db);

   return 0;
}

When run this code produces:

field[0] (MAX(datetime)) type is 12
field[0] (varchar) type is 253
field[1] (MAX(datetime)) type is 253

Notice that the first query produces a correct type (12 = datetime).  The second query however incorrectly identifies the MAX(datetime) field as being of varchar type (253 = varchar).  This is clearly incorrect.  The data is correct but the type is wrong.

I have verified this only on MEMORY type tables and only on 4.1.3.

Suggested fix:
Fix the client lib or server to return the correct type.

I have verified that the same bug occurs for all table types and in 4.1.4.

the following PHP script was used to reproduce the problem:

<?php
	mysql_connect("localhost", "root", "") or die(mysql_error());
	mysql_select_db("test") or die(mysql_error());

	mysql_query("CREATE TEMPORARY TABLE test (product VARCHAR(32), stamp DATETIME)");

	for ($i = 0; $i < 100; $i++) {
		$t = chr(65 + $i % 5);
		mysql_query("INSERT INTO test(product, stamp) VALUES('$t', '2004-09-15 13:45:05')");
	}

	$res = mysql_query("SELECT MAX(stamp) FROM test");
	echo mysql_field_type($res, 0) . "\n";

	$res = mysql_query("SELECT MAX(stamp) FROM test GROUP BY product");
	echo mysql_field_type($res, 0) . "\n";
?>

An easier way to check it is something like:

drop table if exists t1, t2;
create table t1(a int, b datetime);
insert into t1 values (1, NOW()), (2, NOW());
create table t2 select MAX(b) from t1 order by a;
show create table t2;

Sorry, 
create table t2 select MAX(b) from t1 order by a;   --->
create table t2 select MAX(b) from t1 group by a;

fixed in 4.1.11

Noted in 4.1.11 changelog.