Bug #17765 Bug in libMySQL.dll with EXPLAIN
Submitted: 28 Feb 2006 11:12 Modified: 1 Mar 2006 2:32
Reporter: blubb blubb Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium)

[28 Feb 2006 11:12] blubb blubb
Description:
While executing the below mentioned query I get this result:

+----+--------------+------------+------+---------------+------+---------+------+------+----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+--------------+------------+------+---------------+------+---------+------+------+----------------+
|  1 | PRIMARY      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
|  2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
|NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL |                |
+----+--------------+------------+------+---------------+------+---------+------+------+----------------+

But the field definitions given by mysql_fetch_field reports for the field "id" the flag "NOT_NULL_FLAG".

... but the last record has a NULL value.

How to repeat:
EXPLAIN (SELECT 1) UNION (SELECT 2);
[28 Feb 2006 15:45] Jorge del Conde
Thanks for your bug report.  I was able to reproduce this under FC4:

int do_select(MYSQL *mysql, const char *qry)
{
  MYSQL_RES *res;
  MYSQL_FIELD *field;
  MYSQL_ROW row;

  if ((mysql_query(mysql, qry) != 0))
    return -1;
  else
    if ((res = mysql_store_result(mysql)))
    {
      printf("Query: %s\n", qry);
      while ((row = mysql_fetch_row(res)))
      {
        field = mysql_fetch_field(res);
        printf ("Result: %s\n", row[0]);
        printf ("Field Type: %d\n\n", field->type);                
      }
      mysql_free_result(res);
      return 0;
    }

  return -1;
}

int main ()
{
  MYSQL *mysql;

  mysql= mysql_init(NULL);
  mysql_real_connect(mysql,"localhost","root",NULL,"test",0,NULL,0);

  do_select(mysql, "EXPLAIN (SELECT 1) UNION (SELECT 2)");  // field->type == MYSQL_TYPE_NEWDECIMAL

  printf ("NOT_NULL_FLAG: %d\n", NOT_NULL_FLAG);

  mysql_close(mysql);
  return 0;
}