MySQL Bugs: #26894: Use of C api to fetch field name returned by a select using a derived table.

Bug #26894	Use of C api to fetch field name returned by a select using a derived table.
Submitted:	6 Mar 2007 21:06	Modified:	3 Apr 2007 16:46
Reporter:	gildas cadin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S2 (Serious)
Version:	5.0.27, 5.0.37	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any
Tags:	derived table

Description:
One has to be carefull using derived table and the c API.

The name for the field inculde the name of the table used for the SELECT, not the name of the ALIAS used to reference the table.

How to repeat:
Use a query like:

TABLE A( id INT);
TABLE B( id INT);

SELECT A.*,new_B.* FROM A LEFT JOIN (SELECT * FROM B) AS new_B ON A.id=new_B.id;

The C-API returns the fileds A.id and B.id (but not new_B.id)

can you check the values of Org_table and Table ?  They should provide the information.  Also, post a small piece of code that shows the problem.

I got this from "mysql --debug test"

mysql> SELECT A.*,new_B.* FROM A LEFT JOIN (SELECT * FROM B) AS new_B ON
    -> A.id=new_B.id;
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `A`
Org_table:  `a`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NUM

Field   2:  `id`
Catalog:    `def`
Database:   ``
Table:      `new_B`
Org_table:  `b`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NUM

here are 2 tables I have in one database:

CREATE TABLE mtTable
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  ,type CHAR(8)
)
CREATE TABLE mtTable_record
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  ,id_mtTable INT
  ,tag
)

I do a query and look at the content of the field info returned by the c-api and get the following:

SELECT * FROM mtTable LEFT JOIN mtTable_record AS new_table ON .....

XXXXXXXXX: j=0 field_table=[mtTable] field_org_table=[mtTable] field_name=[id] field_org_name=[id] def=[<null>]
XXXXXXXXX: j=1 field_table=[mtTable] field_org_table=[mtTable] field_name=[type] field_org_name=[type] def=[<null>]
XXXXXXXXX: j=2 field_table=[new_table] field_org_table=[mtTable_record] field_name=[id] field_org_name=[id] def=[<null>]
XXXXXXXXX: j=3 field_table=[new_table] field_org_table=[mtTable_record] field_name=[id_mtTable] field_org_name=[id_mtTable] def=[<null>]
XXXXXXXXX: j=4 field_table=[new_table] field_org_table=[mtTable_record] field_name=[tag] field_org_name=[tag] def=[<null>]

Another query returns:

SELECT * FROM mtTable LEFT JOIN (SELECT * FROM mtTable_record) AS new_table ON .....

XXXXXXXXX: j=0 field_table=[mtTable] field_org_table=[mtTable] field_name=[id] field_org_name=[id] def=[<null>]
XXXXXXXXX: j=1 field_table=[mtTable] field_org_table=[mtTable] field_name=[type] field_org_name=[type] def=[<null>]
XXXXXXXXX: j=2 field_table=[mtTable_record] field_org_table=[mtTable_record] field_name=[id] field_org_name=[id] def=[<null>]
XXXXXXXXX: j=3 field_table=[mtTable_record] field_org_table=[mtTable_record] field_name=[id_mtTable] field_org_name=[id_mtTable] def=[<null>]
XXXXXXXXX: j=4 field_table=[mtTable_record] field_org_table=[mtTable_record] field_name=[tag] field_org_name=[tag] def=[<null>]

For the first query the result is what i was expected but for the second one both field_table and field_org_table have value mtTable_record where I was expected field_table=new_table like in the first case...

Thank you for the feedback. Please provide the complete C code test
case? Thanks in advance.

Test code

Attachment: sql.c (text/plain), 1.41 KiB.

I have uploaded some c code.

I complie it using the command line:

gcc sql.c -o sql -I/home/users/aleks/current/SQL/mysql-standard-5.0.27-linux-i686/include -L/usr/local/lib -L/home/users/aleks/current/SQL/mysql-standard-5.0.27-linux-i686/lib -lz -lpthread -lcrypt -lnsl -lm -lpthread -lc -lnss_files -lnss_dns -lresolv -lc -lnss_files -lnss_dns -lresolv -lrt /hub/laleks/current/SQL/mysql-standard-4.1.10-pc-linux-gnu-i686/lib/libmysqlclient.a

When I launch the cprogram I get the following result:

j=0 field_table=[mtTable] field_org_table=[mtTable] field_name=[id] field_org_name=[id] def=[<null>]
j=1 field_table=[mtTable] field_org_table=[mtTable] field_name=[type] field_org_name=[type] def=[<null>]
j=2 field_table=[mtTable_record] field_org_table=[mtTable_record] field_name=[id] field_org_name=[id] def=[<null>]
j=3 field_table=[mtTable_record] field_org_table=[mtTable_record] field_name=[id_mtTable] field_org_name=[id_mtTable] def=[<null>]
j=4 field_table=[mtTable_record] field_org_table=[mtTable_record] field_name=[tag] field_org_name=[tag] def=[<null>]

For information here are the description of the tables:

mysql> explain mtTable;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment |
| type  | varchar(8) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> explain mtTable_record;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| id_mtTable | int(11)     | YES  | MUL | NULL    |                |
| tag        | varchar(12) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

I made a mistake in my command line to compile the binary (I liked to my old sql), here is the correct compile line:

gcc sql.c -o sql -I/home/users/aleks/current/SQL/mysql-standard-5.0.27-linux-i686/include -L/usr/local/lib -L/home/users/aleks/current/SQL/mysql-standard-5.0.27-linux-i686/lib -lz -lpthread -lcrypt -lnsl -lm -lpthread -lc -lnss_files -lnss_dns -lresolv -lc -lnss_files -lnss_dns -lresolv -lrt /hub/laleks/current/SQL/mysql-standard-5.0.27-linux-i686-glibc23/lib/libmysqlclient.a

Remark: If I try another query like the following one the api is working fine:

SELECT * FROM mtTable LEFT JOIN mtTable_record AS new_table ON mtTable.id=new_table.id_mtTable

Result:

j=0 field_table=[mtTable] field_org_table=[mtTable] field_name=[id] field_org_name=[id] def=[<null>]
j=1 field_table=[mtTable] field_org_table=[mtTable] field_name=[type] field_org_name=[type] def=[<null>]
j=2 field_table=[new_table] field_org_table=[mtTable_record] field_name=[id] field_org_name=[id] def=[<null>]
j=3 field_table=[new_table] field_org_table=[mtTable_record] field_name=[id_mtTable] field_org_name=[id_mtTable] def=[<null>]
j=4 field_table=[new_table] field_org_table=[mtTable_record] field_name=[tag] field_org_name=[tag] def=[<null>]

Please provide accurate version of server libraries you use

Using mysqld --version and mysql --version I get the following info:

./bin/mysqld  Ver 5.0.27-standard for pc-linux-gnu on i686 (MySQL Community Edition - Standard (GPL))
./bin/mysql  Ver 14.12 Distrib 5.0.27, for pc-linux-gnu (i686) using readline 5.0

I can not repeat it with last development sources.

Please try with current 5.0.37 version and say us result.

If you can repeat, please, remove libmysqld code and check without it. Also, please, say us result.

I tried with mysql-5.1.16-beta-linux-i686-glibc23 and same result:

I compile using the command line:

gcc sql.c -o sql -I/home/users/aleks/current/SQL/mysql-5.1.16-beta-linux-i686-glibc23/include -L/usr/local/lib -L/home/users/aleks/current/SQL/mysql-5.1.16-beta-linux-i686-glibc23/lib -lmysqlclient -lz -lcrypt -lnsl -lm -lc -lnss_files -lnss_dns -lresolv -lc -lnss_files -lnss_dns -lresolv

I get:

aleks 117 lanip10 [ test ]./sql
j=0 field_table=[mtTable] field_org_table=[mtTable] field_name=[id] field_org_name=[id] def=[<null>]
j=1 field_table=[mtTable] field_org_table=[mtTable] field_name=[type] field_org_name=[type] def=[<null>]
j=2 field_table=[mtTable_record] field_org_table=[mtTable_record] field_name=[id] field_org_name=[id] def=[<null>]
j=3 field_table=[mtTable_record] field_org_table=[mtTable_record] field_name=[id_mtTable] field_org_name=[id_mtTable] def=[<null>]
j=4 field_table=[mtTable_record] field_org_table=[mtTable_record] field_name=[tag] field_org_name=[tag] def=[<null>]

If I try with mysql-5.0.37-linux-i686 I get the following error when I compile if I have -lmysqlclient in the compile line:

gcc sql.c -o sql -I/home/users/aleks/current/SQL/mysql-5.0.37-linux-i686/include -L/usr/local/lib -L/home/users/aleks/current/SQL/mysql-5.0.37-linux-i686/lib -lmysqlclient -lz -lcrypt -lnsl -lm -lc -lnss_files -lnss_dns -lresolv -lc -lnss_files -lnss_dns -lresolv
/home/users/aleks/current/SQL/mysql-5.0.37-linux-i686/lib/libmysqlclient.a(ssl.o)(.gnu.linkonce.d.__vt_Q25yaSSL7Message+0x8): undefined reference to `__pure_virtual'
/home/users/aleks/current/SQL/mysql-5.0.37-linux-i686/lib/libmysqlclient.a(ssl.o)(.gnu.linkonce.d.__vt_Q25yaSSL7Message+0xc): undefined reference to `__pure_virtual'
/home/users/aleks/current/SQL/mysql-5.0.37-linux-i686/lib/libmysqlclient.a(ssl.o)(.gnu.linkonce.d.__vt_Q25yaSSL7Message+0x10): undefined reference to `__pure_virtual'
/home/users/aleks/current/SQL/mysql-5.0.37-linux-i686/lib/libmysqlclient.a(ssl.o)(.gnu.linkonce.d.__vt_Q25yaSSL7Message+0x14): undefined reference to `__pure_virtual'
/home/users/aleks/current/SQL/mysql-5.0.37-linux-i686/lib/libmysqlclient.a(ssl.o)(.gnu.linkonce.d.__vt_Q25yaSSL7Message+0x18): undefined reference to `__pure_virtual'
/home/users/aleks/current/SQL/mysql-5.0.37-linux-i686/lib/libmysqlclient.a(cert_wrapper.o)(.text+0x76): In function `yaSSL::x509::~x509(void)':
cert_wrapper.cpp: undefined reference to `__builtin_delete'
/home/users/aleks/current/SQL/mysql-5.0.37-linux-i686/lib/libmysqlclient.a(cert_wrapper.o)(.text+0x2e6): In function `yaSSL::CertManager::~CertManager(void)':
cert_wrapper.cpp: undefined reference to `__builtin_delete'
/home/users/aleks/current/SQL/mysql-5.0.37-linux-i686/lib/libmysqlclient.a(template_instnt.o)(.gnu.linkonce.t._._Q25mySTLt4list1ZPQ25yaSSL11SSL_SESSION+0x42): In function `mySTL::list<yaSSL::SSL_SESSION *>::~list(void)':
template_instnt.cpp: undefined reference to `__builtin_delete'
/home/users/aleks/current/SQL/mysql-5.0.37-linux-i686/lib/libmysqlclient.a(template_instnt.o)(.gnu.linkonce.t._._Q25mySTLt4list1ZQ25yaSSL11ThreadError+0x42): In function `mySTL::list<yaSSL::ThreadError>::~list(void)':
template_instnt.cpp: undefined reference to `__builtin_delete'
/home/users/aleks/current/SQL/mysql-5.0.37-linux-i686/lib/libmysqlclient.a(template_instnt.o)(.gnu.linkonce.t._._Q25mySTLt4list1ZPUc+0x42): In function `mySTL::list<unsigned char *>::~list(void)':
.... 
And more

If instead I use /hub/laleks/current/SQL/mysql-standard-5.0.27-linux-i686-glibc23/lib/libmysqlclient.a in the compile line, it compile well, but the result is still the same...

Thank you for the feedback.

I steel can not repeat described behaviour.

Please provide output of `ldd sql` command

You asked: 
Please provide output of `ldd sql` command

Here is the result:

aleks 193 lanip10 [ test ]ldd sql
        linux-gate.so.1 =>  (0x00d6c000)
        libz.so.1 => /usr/lib/libz.so.1 (0x00926000)
        libcrypt.so.1 => /lib/libcrypt.so.1 (0x006bc000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x006a4000)
        libm.so.6 => /lib/libm.so.6 (0x008fa000)
        libc.so.6 => /lib/libc.so.6 (0x007ce000)
        libnss_files.so.2 => /lib/libnss_files.so.2 (0x003d7000)
        libnss_dns.so.2 => /lib/libnss_dns.so.2 (0x00fc3000)
        libresolv.so.2 => /lib/libresolv.so.2 (0x00ac1000)
        /lib/ld-linux.so.2 (0x007ac000)

Thank you for the feedback.

There is not any link to libmysqlclient in the ldd output. Please check problems with your linker.

ldd output should looks like:

linux-gate.so.1 =>  (0x002ce000)
        libmysqlclient.so.15 => /Users/sveta/build/mysql-5.1/lib/mysql/libmysqlclient.so.15 (0x002eb000)
        libz.so.1 => /usr/lib/libz.so.1 (0x00111000)
        libcrypt.so.1 => /lib/libcrypt.so.1 (0x00b77000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x00b2c000)
        libm.so.6 => /lib/libm.so.6 (0x00124000)
        libc.so.6 => /lib/libc.so.6 (0x00149000)
        libnss_files.so.2 => /lib/libnss_files.so.2 (0x00d93000)
        libnss_dns.so.2 => /lib/libnss_dns.so.2 (0x0089d000)
        libresolv.so.2 => /lib/libresolv.so.2 (0x00b62000)
        libpthread.so.0 => /lib/libpthread.so.0 (0x00272000)
        /lib/ld-linux.so.2 (0x002cf000)

Actually libmysqlclient does not appear in the output of ldd since it is linked statically in the binary .sql 

The command I used to compile is:

gcc -v sql.c -o sql -I/home/users/aleks/current/SQL/mysql-standard-5.0.27-linux-i686-glibc23/include -L/home/users/aleks/current/SQL/mysql-standard-5.0.27-linux-i686-glibc23/lib -lmysqlclient -lz -lcrypt -lnsl -lm -lc -lnss_files -lnss_dns -lresolv -lc -lnss_files -lnss_dns -lresolv

The library folder contains only the following files (there is no dynamic library in the lot):

aleks 214 lanip10 [ test ]ls -la /home/users/aleks/current/SQL/mysql-standard-5.0.27-linux-i686-glibc23/lib
total 6312
drwxr-xr-x   2 aleks soft    4096 Oct 21 01:25 ./
drwxr-xr-x  14 aleks soft    4096 Oct 21 01:25 ../
-rw-r--r--   1 aleks soft   12968 Oct 21 01:19 libdbug.a
-rw-r--r--   1 aleks soft   57872 Feb 28  2005 libmygcc.a
-rw-r--r--   1 aleks soft 2361102 Oct 21 01:25 libmysqlclient.a
-rw-r--r--   1 aleks soft 2370342 Oct 21 01:24 libmysqlclient_r.a
-rw-r--r--   1 aleks soft    1685 Oct 20 17:22 libmysql.imp
-rw-r--r--   1 aleks soft 1241050 Oct 21 01:19 libmystrings.a
-rw-r--r--   1 aleks soft  286278 Oct 21 01:19 libmysys.a
-rw-r--r--   1 aleks soft   83044 Oct 21 01:19 libz.a

Please indicate server version you connect to.

I connect to server version: 5.0.27-standard-log

I downloaded the source of SQL version 5.0.37 and rebuils mysql with the option --debug allowed and launched it with this option.

aleks 123 lanip10 [ client ]./mysql --debug -u XYZ --port ABC --host XXYYZZ home_debug
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 95336
Server version: 5.0.27-standard-log MySQL Community Edition - Standard (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM mtTable LEFT JOIN (SELECT * FROM mtTable_record) AS new_table ON mtTable.id=new_table.id_mtTable;
Field   1:  `id`
Catalog:    `def`
Database:   `home_debug`
Table:      `mtTable`
Org_table:  `mtTable`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 3
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

Field   2:  `type`
Catalog:    `def`
Database:   `home_debug`
Table:      `mtTable`
Org_table:  `mtTable`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     8
Max_length: 5
Decimals:   0
Flags:

Field   3:  `id`
Catalog:    `def`
Database:   `home_debug`
Table:      `mtTable_record`
Org_table:  `mtTable_record`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 3
Decimals:   0
Flags:      NUM

Field   4:  `id_mtTable`
Catalog:    `def`
Database:   `home_debug`
Table:      `mtTable_record`
Org_table:  `mtTable_record`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 3
Decimals:   0
Flags:      NUM

Field   5:  `tag`
Catalog:    `def`
Database:   `home_debug`
Table:      `mtTable_record`
Org_table:  `mtTable_record`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     12
Max_length: 10
Decimals:   0
Flags:

It looks like the problem is at the level of the server, not at the level of the client, no ???

I still can not repeat the error:

$bin/mysql --debug --socket=/tmp/mysql_ssmirnova.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.37-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table mtTable(id int not null primary key auto_increment, type varchar(8));
Query OK, 0 rows affected (0.08 sec)

mysql> create table mtTable_record(id int not null primary key auto_increment, id_mtTable int, tag varchar(12), unique(id, id_mtTable));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into mtTable values(1, 'type1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into mtTable_record values(1,1,'tag1');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mtTable LEFT JOIN (SELECT * FROM mtTable_record) AS
    -> new_table ON mtTable.id=new_table.id_mtTable;
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `mtTable`
Org_table:  `mtTable`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY 

Field   2:  `type`
Catalog:    `def`
Database:   `test`
Table:      `mtTable`
Org_table:  `mtTable`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     8
Max_length: 5
Decimals:   0
Flags:      

Field   3:  `id`
Catalog:    `def`
Database:   ``
Table:      `new_table`
Org_table:  `mtTable_record`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NOT_NULL NUM 

Field   4:  `id_mtTable`
Catalog:    `def`
Database:   ``
Table:      `new_table`
Org_table:  `mtTable_record`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NUM 

Field   5:  `tag`
Catalog:    `def`
Database:   ``
Table:      `new_table`
Org_table:  `mtTable_record`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     12
Max_length: 4
Decimals:   0
Flags:      

+----+-------+----+------------+------+
| id | type  | id | id_mtTable | tag  |
+----+-------+----+------------+------+
|  1 | type1 |  1 |          1 | tag1 | 
+----+-------+----+------------+------+
1 row in set (0.00 sec)

You said you compiled 5.0.37 server yourself. Please provide configure string you used.

I recompile mysql 5.0.37 myself using the comand:

./configure --with-debug
make

I then used the new mysql client binary to connect to the server already running using 5.0.27 using the option --debug

./mysql --debug -u XYZ --port ABC --host
XXYYZZ home_debug

This makes me think that the problem may be at the level of the server, not the client ?
Could it be the case ?

I've just tried with 5.0.27 server and got wrong results.

So I'll close this report as "Can't repeat", because it has fixed in 5.0.37.

I confirm that switching to a server version 5.0.37 fix the problem.

Thanks.