Bug #6058 Prepared statements return '0000-00-00' (date) as empty string

• Submitted: 13 Oct 2004 6:44
• Modified: 20 Oct 2004 14:40
• Reporter: Georg Richter
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: >= 4.1
• OS: Linux (Linux)
• Assigned to: Konstantin Osipov
• CPU Architecture: Any

[13 Oct 2004 6:44] Georg Richter

Description:
When retrieving a date value which contains '0000-00-00' via a prepared statement result buffer 
contains an empty string. 
 
Output:  
georg@beethoven:~/work/mysql/bugs> ./test 
Result from prepared statement: '' 

How to repeat:
#include <stdio.h> 
#include <mysql.h> 
 
int main () { 
  MYSQL       *mysql = mysql_init(NULL); 
  MYSQL_STMT  *stmt; 
  MYSQL_BIND  bind[1]; 
  MYSQL_RES   *res; 
  MYSQL_ROW   row; 
  char        query[500]; 
  char        buffer[100]; 
  int         length; 
  int         isnull = 0; 
 
  if (!mysql_real_connect(mysql, "localhost", "", "", "test", 0, NULL, 0)) { 
      printf("Can't connect! Error: %s\n", mysql_error(mysql)); 
      return 1; 
  } 
 
  stmt = mysql_stmt_init(mysql); 
  strcpy(query, "SELECT CAST('0000-00-00' AS DATE)"); 
 
  if (mysql_stmt_prepare(stmt, query, strlen(query))) { 
    printf("Error in mysql_stmt_prepare: %s\n", mysql_error(mysql)); 
    return 1; 
  } 
 
  if (mysql_stmt_execute(stmt)) { 
    printf("Error in mysql_stmt_execute: %s\n", mysql_stmt_error(stmt)); 
    return 1; 
  } 
 
  bind[0].buffer_type    = MYSQL_TYPE_STRING; 
  bind[0].buffer         = &buffer; 
  bind[0].buffer_length  = 100; 
  bind[0].length         = &length; 
  bind[0].is_unsigned    = 0; 
  bind[0].is_null        = &isnull; 
 
  mysql_stmt_bind_result(stmt, bind); 
  mysql_stmt_fetch(stmt); 
 
  printf("Result from prepared statement: '%s'\n", buffer); 
 
  mysql_stmt_close(stmt); 
  mysql_close(mysql); 
}

[15 Oct 2004 20:56] Konstantin Osipov

Subject: bk commit - 4.1 tree (konstantin:1.2108) BUG#6058

ChangeSet
  1.2108 04/10/16 00:50:50 [e-mail] +2 -0
  A fix and test case for bug#6058 "Prepared statements return '0000-00-00'
  (date) as empty  string": preserve time type (date, time, or datetime) for
  zero dates, times, and datetimes.

[20 Oct 2004 14:40] Konstantin Osipov

Fixed in 4.1.7