| 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: | |
| 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: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.
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!