Bug #21635 MYSQL_FIELD struct's member strings seem to misbehave for expression cols
Submitted: 14 Aug 2006 20:35 Modified: 4 Dec 2006 14:09
Reporter: Bryan Maloney Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S3 (Non-critical)
Version:4.1, 5.0.26-BK, 5.0.24 OS:Linux (Linux, Redhat 3.2.2-5)
Assigned to: Tomash Brechko CPU Architecture:Any
Tags: expression, MYSQL_FIELD, org_name

[14 Aug 2006 20:35] Bryan Maloney
Description:
According to the Paul DuBois MySQL book's appendix on the 4.1 and 5.0 C API (page 1105 of the 3rd edition), the org_name member in the MYSQL_FIELD struct is an empty string if the column is calculated as the result of an expression. But it seems the C API alongside the 5.0 server doesn't behave that way. If the book is incorrect and the server makes no promises about the behavior of these members for expression-produced columns, please accept my apologies, but as I demonstrate in the 'how to repeat' section, the results seem unpredictable and I don't understand the logic of how values are/are not assigned to these members.

My machine info:
- running Red Hat Linux 3.2.2-5 Linux 2.4.27-wacls #24 SMP Fri Sep 9 00:53:05 EDT 2005 with gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5) + GNU make 3.79.1
- mysql  Ver 14.12 Distrib 5.0.15, for pc-linux-gnu (i686) using readline 5.0

How to repeat:
1) Create and populate the following test tables:

mysql> show create table Test\G
*************************** 1. row ***************************
       Table: Test
Create Table: CREATE TABLE `Test` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `name` char(50) default NULL,
  `abbr` char(10) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table tv\G
*************************** 1. row ***************************
       Table: tv
Create Table: CREATE TABLE `tv` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `value` char(10) default NULL,
  `valid` int(10) unsigned default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from Test;
+----+--------------------+------+
| ID | name               | abbr |
+----+--------------------+------+
|  1 | Franklin Rousevelt | FDR  |
|  2 | John Kennedy       | JFK  |
|  3 | Lyndon Johnson     | LBJ  |
|  4 | George Washington  | GW   |
+----+--------------------+------+
4 rows in set (0.00 sec)

mysql> select * from tv;
+----+-------+-------+
| ID | value | valid |
+----+-------+-------+
|  1 | A     |     0 |
|  2 | A     |   100 |
|  3 | A     |   100 |
|  4 | C     |   100 |
|  5 | C     |     0 |
|  6 | C     |   100 |
|  7 | C     |   100 |
+----+-------+-------+
7 rows in set (0.00 sec)

2) Create the following query (2a), expecting the following results (2b):

2a. SELECT    f.ID        AS "tID",
          max(f.ID)   AS "max1",
          1           AS "juan",
          count(f.ID),
          2,
          f.name      AS "Test Name",
          f.abbr      AS "Test Short Name",
          Y.*,
          f.ID        AS "__[test][Test][ID]",
          Y.ID        AS "__[test][tv][ID]"
FROM      (Test AS f,
          tv   AS Y)
GROUP BY  f.id
LIMIT 10

2b. 
+-----+------+------+-------------+---+--------------------+-----------------+----+-------+-------+--------------------+------------------+
| tID | max1 | juan | count(f.ID) | 2 | Test Name          | Test Short Name | ID | value | valid | __[test][Test][ID] | __[test][tv][ID] |
+-----+------+------+-------------+---+--------------------+-----------------+----+-------+-------+--------------------+------------------+
|   1 |    1 |    1 |           7 | 2 | Franklin Rousevelt | FDR             |  1 | A     |     0 |                  1 |                1 |
|   2 |    2 |    1 |           7 | 2 | John Kennedy       | JFK             |  1 | A     |     0 |                  2 |                1 |
|   3 |    3 |    1 |           7 | 2 | Lyndon Johnson     | LBJ             |  1 | A     |     0 |                  3 |                1 |
|   4 |    4 |    1 |           7 | 2 | George Washington  | GW              |  1 | A     |     0 |                  4 |                1 |
+-----+------+------+-------------+---+--------------------+-----------------+----+-------+-------+--------------------+------------------+

3a. Compile and run the following code snippet (assumes connection 'conn', query string 'sql_s'):

 
if (mysql_real_query(conn, sql_s, strlen(sql_s)) != 0) mysql_raise(conn);

resp = mysql_use_result(conn);
if (resp == NULL) {
	mysql_free_result(resp);
}

MYSQL_FIELD *field;
int x;
for(x = 0; x < field_count; x++) {
	field = mysql_fetch_field_direct(resp, x);
	printf("field %d\n\tname: %s\n\torg_name: %s\n\ttable: %s\n\torg_table: %s\n", x, field->name, field->org_name, field->table, field->org_table);
}

This results in the following output on the cmdline:

field 0
        name: tID
        org_name: ID
        table: f
        org_table: Test
field 1
        name: max1
        org_name: max1
        table: f
        org_table: Test
field 2
        name: juan
        org_name: 
        table: 
        org_table: 
field 3
        name: count(f.ID)
        org_name: count(f.ID)
        table: 
        org_table: 
field 4
        name: 2
        org_name: 
        table: 
        org_table: 
field 5
        name: Test Name
        org_name: name
        table: f
        org_table: Test
field 6
        name: Test Short Name
        org_name: abbr
        table: f
        org_table: Test
field 7
        name: ID
        org_name: ID
        table: Y
        org_table: tv
field 8
        name: value
        org_name: value
        table: Y
        org_table: tv
field 9
        name: valid
        org_name: valid
        table: Y
        org_table: tv
field 10
        name: __[test][Test][ID]
        org_name: ID
        table: f
        org_table: Test
field 11
        name: __[test][tv][ID]
        org_name: ID
        table: Y
        org_table: tv
---------------------
As you can see, the values don't match up - fields 1 - 4 are expressions; field one has nonempty org_name, org_table and table values; field 2 seems to behave normally; field 3 has a nonempty org_name (but empty table and org_table values); and field 4 also seems to behave normally.

The online documentation (http://dev.mysql.com/doc/refman/5.0/en/c-api-datatypes.html) makes no mention of this (intended?) behavior, but DuBois's book does, and in any event if this behavior is correct, I don't understand the logic behind it (and I haven't found anything on a google search of "MYSQL_FIELD org_name expression" that helps either).

Thanks!
[14 Aug 2006 20:37] Bryan Maloney
After proofing the bug report I notice I missed a line in the code fragment. Add the following before the loop:

unsigned long field_count = mysql_field_count(conn);

sorry!
[26 Aug 2006 10:47] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.24, and inform about the results.
[30 Aug 2006 20:57] Bryan Maloney
I am still seeing the problem with both server and client libraries upgraded to v 5.0.24:

MySQL Client: 5.0.24
MySql Server: 5.0.24-standard-log

field 0
        name: tID
        org_name: ID
        table: f
        org_table: Test
field 1
        name: max1
        org_name: max1
        table: 
        org_table: 
field 2
        name: count(f.ID)
        org_name: count(f.ID)
        table: 
        org_table: 
field 3
        name: juan
        org_name: 
        table: 
        org_table: 
field 4
        name: 2
        org_name: 
        table: 
        org_table: 
field 5
        name: Test Name
        org_name: name
        table: f
        org_table: Test
field 6
        name: Test Short Name
        org_name: abbr
        table: f
        org_table: Test
field 7
        name: ID
        org_name: ID
        table: Y
        org_table: tv
field 8
        name: value
        org_name: value
        table: Y
        org_table: tv
field 9
        name: valid
        org_name: valid
        table: Y
        org_table: tv
[27 Sep 2006 20:24] Valeriy Kravchuk
Verified with 5.0.26-BK on linux, with a similar test case:

openxs@suse:~/dbs/5.0> cat 21635.c
#include <stdio.h>
#include "mysql.h"

int main()
{
  MYSQL *conn;

  char SELECT1[1000]= "SELECT    f.ID        AS 'tID', max(f.ID)   AS 'max1', 1
          AS 'juan', count(f.ID), 2, f.name      AS 'Test Name', f.abbr      AS
'Test Short Name', Y.*, f.ID        AS '__[test][Test][ID]', Y.ID        AS '__[
test][tv][ID]' FROM      (Test AS f, tv   AS Y) GROUP BY  f.id LIMIT 10"
;

  conn= mysql_init(NULL);
  conn= mysql_real_connect(conn, "127.0.0.1", "root", "", "test", 3306, 0, 0);
  if (!conn) {
    fprintf(stderr, "Cannot connect\n");
    exit(1);
  }

  printf("Client info: %s\n", mysql_get_client_info());
  printf("Server info: %s\n", mysql_get_server_info(conn));

  mysql_real_query(conn, SELECT1, strlen(SELECT1));

MYSQL_RES *resp = mysql_store_result(conn);

if (resp == NULL) {
        mysql_free_result(resp);
}

MYSQL_FIELD *field;
int x;

unsigned long field_count = mysql_num_fields(resp);

for(x = 0; x < field_count; x++) {
        field = mysql_fetch_field_direct(resp, x);
        printf("field %d\n\tname: %s\n\torg_name: %s\n\ttable: %s\n\torg_table:
%s\n",
x, field->name, field->org_name, field->table, field->org_table);
}
  mysql_close(conn);
  return 0;
}

openxs@suse:~/dbs/5.0> gcc -o 21635 `$CFG --cflags` 21635.c `$CFG --libs`
openxs@suse:~/dbs/5.0> ./21635
Client info: 5.0.22
Server info: 5.0.26-debug
field 0
        name: tID
        org_name: ID
        table: f
        org_table: Test
field 1
        name: max1
        org_name: max1
        table: f
        org_table: Test
field 2
        name: juan
        org_name:
        table:
        org_table:
field 3
        name: count(f.ID)
        org_name: count(f.ID)
        table:
        org_table:
field 4
        name: 2
        org_name:
        table:
        org_table:
field 5
        name: Test Name
        org_name: name
        table: f
        org_table: Test
field 6
        name: Test Short Name
        org_name: abbr
        table: f
        org_table: Test
field 7
        name: ID
        org_name: ID
        table: Y
        org_table: tv
field 8
        name: value
        org_name: value
        table: Y
        org_table: tv
field 9
        name: valid
        org_name: valid
        table: Y
        org_table: tv
field 10
        name: __[test][Test][ID]
        org_name: ID
        table: f
        org_table: Test
field 11
        name: __[test][tv][ID]
        org_name: ID
        table: Y
        org_table: tv
[17 Nov 2006 15:07] Tomash Brechko
Adding 4.1, as the bug is there too.
[17 Nov 2006 16:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15511

ChangeSet@1.2540, 2006-11-17 19:14:09+03:00, kroki@moonlight.intranet +2 -0
  BUG#21635: MYSQL_FIELD struct's member strings seem to misbehave for
  expression cols.
  
  The problem was that MYSQL_FIELD::org_name was set for MIN() and MAX()
  functions (COUNT() was already fixed).  The solution is to reset this
  field for all functions.
[21 Nov 2006 16:34] Konstantin Osipov
Sent a reivew by email.
[22 Nov 2006 11:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15693

ChangeSet@1.2540, 2006-11-22 13:58:00+03:00, kroki@moonlight.intranet +2 -0
  BUG#21635: MYSQL_FIELD struct's member strings seem to misbehave for
  expression cols.
  
  The problem was that MYSQL_FIELD::org_name was set for MIN() and MAX()
  functions (COUNT() is also mentioned in the bug report but was already
  fixed).
  
  After this patch for expressions MYSQL_FIELD::name is set to either
  expression itself or its alias, and other data origin fields of
  MYSQL_FILED (db, org_table, table, org_name) are empty strings.
[22 Nov 2006 13:59] Tomash Brechko
Queued to 4.1-runtime, 5.0-runtime, 5.1-runtime.
[30 Nov 2006 1:34] Konstantin Osipov
Pushed into 4.1.23, 5.0.32 and 5.1.13
[4 Dec 2006 14:09] MC Brown
A note has been added to the 4.1.23, 5.0.30 and 5.1.14 changelogs.
[20 Jan 2007 17:12] TOM DONOVAN
The mysql_client_test for this bug continues to fail on 5.0.33.  
Executed on Windows 2000 sp4, built from 5.0.33 Windows community source.
Appears not to be fixed.

#####################################
201 of (1/1): test_bug21635
#####################################
MIN(i) -> MIN(i) ... .\mysql_client_test.c:15447: check failed: 'field->db[0] ==
 0 && field->org_table[0] == 0 && field->table[0] == 0 && field->org_name[0] ==
0'
This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.
[15 Apr 2007 17:03] TOM DONOVAN
Remains unfixed in 5.0.38 - Built with VC8 on Win2ks-p4:

#####################################
202 of (1/1): test_bug21635
#####################################
MIN(i) -> MIN(i) ... .\mysql_client_test.c:15521: check failed: 'field->db[0] ==
 0 && field->org_table[0] == 0 && field->table[0] == 0 && field->org_name[0] ==
0'

This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.