MySQL Bugs: #4176: Incorrect metadata from DATE

Bug #4176	Incorrect metadata from DATE_ADD() with JOIN and ORDER BY
Submitted:	17 Jun 2004 0:46	Modified:	13 Aug 2004 16:02
Reporter:	Matt Solnit	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	4.0.20a	OS:	Windows (Windows XP Professional SP1)
Assigned to:		CPU Architecture:	Any
Tags:	affects_connectors

Description:
When the DATE_ADD() function is used on a date obtained using a LEFT JOIN, and the SELECT contains an ORDER BY, the metadata for the resulting field is incorrect.  Instead of the MYSQL_FIELD->type value being FIELD_TYPE_DATETIME, it is FIELD_TYPE_STRING.

Reproduced in Embedded MySQL but occurs in "regular" MySQL as well.

Following sample shows how to reproduce it in C++.

How to repeat:
// MySQL Test 2.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"
#include "MySQL Test 2.h"
#ifdef _DEBUG
#define new DEBUG_NEW
#endif

#include <winsock.h>
#include <mysql.h>

// The one and only application object

CWinApp theApp;

using namespace std;

MYSQL *db_connect(const char *dbname);
void db_query(MYSQL* db, const char* query);
void process_result(MYSQL* db, int datefield);
void db_disconnect(MYSQL *db);
void db_do_query(MYSQL *db, const char *query);

int _tmain(int argc, TCHAR* argv[], TCHAR* envp[])
{
	int nRetCode = 0;

	// initialize MFC and print and error on failure
	if (!AfxWinInit(::GetModuleHandle(NULL), NULL, ::GetCommandLine(), 0))
	{
		// TODO: change error code to suit your needs
		_tprintf(_T("Fatal Error: MFC initialization failed\n"));
		nRetCode = 1;
	}
	else
	{
		// TODO: code your application's behavior here.

    // Copied from test_dll.cpp:
	  MYSQL *one;

    const char *server_groups[] = {
      "test_libmysqld_SERVER", "embedded", "server", NULL
    };

    const char *args[] = {"thisapp", "--datadir=c:/iter/source/ADCData"};
    const int argcount = 2;

    mysql_server_init(argcount, (char **)args, (char **)server_groups);

    one = db_connect("test");

    db_query(one, "CREATE TABLE IF NOT EXISTS date1 (pk int, dt datetime) TYPE=InnoDB");
    db_query(one, "CREATE TABLE IF NOT EXISTS other1 (anydata varchar(10), fk int) TYPE=InnoDB");

    db_query(one, "INSERT INTO date1 (pk, dt) VALUES (0, NOW())");
    db_query(one, "INSERT INTO other1 (anydata, fk) VALUES (NULL, 0)");

    printf("trying SELECT DATE_ADD(NOW(), INTERVAL 1 DAY)\n");
    db_query(one, "SELECT DATE_ADD(NOW(), INTERVAL 1 DAY)");
    process_result(one, 0);

    printf("trying SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date1\n");
    db_query(one, "SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date1");
    process_result(one, 0);

    printf("trying SELECT other1.*, DATE_ADD(dt, INTERVAL 1 DAY) FROM other1 LEFT JOIN date1 ON other1.fk = date1.pk\n");
    db_query(one, "SELECT other1.*, DATE_ADD(dt, INTERVAL 1 DAY) FROM other1 LEFT JOIN date1 ON other1.fk = date1.pk");
    process_result(one, 2);

    printf("trying SELECT other1.*, DATE_ADD(dt, INTERVAL 1 DAY) FROM other1 LEFT JOIN date1 ON other1.fk = date1.pk ORDER BY anydata ASC\n");
    db_query(one, "SELECT other1.*, DATE_ADD(dt, INTERVAL 1 DAY) FROM other1 LEFT JOIN date1 ON other1.fk = date1.pk ORDER BY anydata ASC");
    process_result(one, 2);

    db_disconnect(one);

    /* This must be called after all other mysql functions */
    mysql_server_end();
	}

	return nRetCode;
}

static void
die(MYSQL *db, char *fmt, ...)
{
  va_list ap;
  va_start(ap, fmt);
  vfprintf(stderr, fmt, ap);
  va_end(ap);
  (void)putc('\n', stderr);
  if (db)
    db_disconnect(db);
  exit(EXIT_FAILURE);
}

MYSQL *
db_connect(const char *dbname)
{
  MYSQL *db = mysql_init(NULL);
  if (!db)
    die(db, "mysql_init failed: no memory");
  /*
   * Notice that the client and server use separate group names.
   * This is critical, because the server will not accept the
   * client's options, and vice versa.
   */
  mysql_options(db, MYSQL_READ_DEFAULT_GROUP, "test_libmysqld_CLIENT");
  if (!mysql_real_connect(db, NULL, NULL, NULL, dbname, 0, NULL, 0))
    die(db, "mysql_real_connect failed: %s", mysql_error(db));

  return db;
}

void
db_query(MYSQL* db, const char* query)
{
  if (mysql_query(db, query))
    die(db, "mysql_query failed: %s", mysql_error(db));
}

void
process_result(MYSQL* db, int datefield)
{
  MYSQL_RES* res;
  
  if ((res = mysql_use_result(db)) == NULL)
    die(db, "mysql_use_result failed: %s", mysql_error(db));

  int ifields = mysql_num_fields(res);
  for (int i = 0; i < ifields; i++)
  {
    MYSQL_FIELD* field = mysql_fetch_field(res);
    if (i == datefield)
      if (field->type != FIELD_TYPE_DATETIME)
      {
        mysql_free_result(res);
        die(db, "expected datetime field result!");
      }
  }

  mysql_free_result(res);
}

void
db_disconnect(MYSQL *db)
{
  mysql_close(db);
}

Thank you very much for writting to us and thank you for such a very nicely elaborated test case.

What you have informed us about is not a bug, but a deficiency in our code of which we are aware of very much.

This deficiency occurs when a query has to be resolved by usage of temporary tables. 

Unlike in case of CREATE .. SELECT, internal temporary tables, that are used to resolve a query, are created much faster and with lesser regard to column type precision. 

That is why some functions which normally return DATETIME (or similar) field type, here return a string. Our current internal temporary tables only are provided with ability to store integer, float and string type.

We intend to solve this in 4.1 or 5.0, but not in 4.0.

Resubmitting here as requested (originally submitted a duplicate of this bug report):

Typing is broken when GROUPing by on a SUMmed INT column (outputs
a floating point number - strange: it's claimed in this ticket that  temp tables support integers).

try {
        Statement cstmt = con.createStatement();
        cstmt.executeUpdate("create table intbug(x int, y int)");
        cstmt.executeUpdate("insert into intbug values(1,1)");
        cstmt.executeUpdate("insert into intbug values(1,1)");
        ResultSet rs = cstmt.executeQuery("select x from intbug");
        if (rs.next()) System.out.println("Class is " +
rs.getObject("x").getClass());

        cstmt = con.createStatement();
        String query = "select sum(x) as x from intbug";
        query += " group by y";
        rs = cstmt.executeQuery(query);
        if (rs.next()) System.out.println("Class is " +
rs.getObject("x").getClass());
        cstmt.executeUpdate("drop table intbug");
}
catch (Exception e) { e.printStackTrace(); }

Output is:
Class is class java.lang.Integer
Class is class java.lang.Double

Test case of timestamps returned as a String when group
and date_add are both used.

try {
        Statement cstmt = con.createStatement();
        cstmt.executeUpdate("create table timestampbug(created_time
timestamp)");
        // NB - table must have at least 2 rows for bug to show up):
        cstmt.executeUpdate("insert into timestampbug values(now())");
        cstmt.executeUpdate("insert into timestampbug values(now())");
        ResultSet rs = cstmt.executeQuery("select created_time as x from
timestampbug");
        if (rs.next()) System.out.println("Class is " +
rs.getObject("x").getClass());

        cstmt = con.createStatement();
        String query = "select date_add(created_time, interval 1 hour) as x
from timestampbug";
        query += " group by created_time";
        rs = cstmt.executeQuery(query);
        if (rs.next()) System.out.println("Class is " +
rs.getObject("x").getClass());
        cstmt.executeUpdate("drop table timestampbug");
}
catch (Exception e) { e.printStackTrace(); }

// Output is:
// Class is class java.sql.Timestamp
// Class is class java.lang.String

If a query is resolved by the usage of temporary tables then this is expected behaviour. 

We have a WorkLog entry to fix this in 4.1.

Fix will not come in 4.0.