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:Microsoft Windows (Windows XP Professional SP1)
Assigned to: CPU Architecture:Any
Tags: affects_connectors

[17 Jun 2004 0:46] Matt Solnit
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);
}
[17 Jun 2004 20:28] Sinisa Milivojevic
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.
[1 Jul 2004 18:00] Paolo Campanella
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
[13 Aug 2004 16:02] Sinisa Milivojevic
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.