Bug #17765 Bug in libMySQL.dll with EXPLAIN

• Submitted: 28 Feb 2006 12:12
• Modified: 1 Mar 2006 3:32
• Reporter: blubb blubb
• Status: Verified
• Category: Server: Optimizer
• Severity: S3 (Non-critical)
• OS: Any
• Assigned to: Evgeny Potemkin
• Triage: Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium)

[28 Feb 2006 12: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 16: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;
}