Bug #28898 mysql_fetch_field gives empty table name by a view
Submitted: 5 Jun 2007 14:18 Modified: 4 Jul 2007 1:07
Reporter: Mathijs Savenije Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.44-BK, 5.0.40 OS:Linux (Gentoo)
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: mysql_fetch_field, VIEW

[5 Jun 2007 14:18] Mathijs Savenije
Description:

When query a join with a view and a table, the table name is empty for all fields with mysql_fetch_field.

Without the join mysql_fetch_field works fine for the views

==========================================================
Expect:

Information for column 0:
blob:         0
max_length:   3
multiple_key: 0
name:         id
not_null:     1
numeric:      1
primary_key:  0
table:        Tags
type:         int
default:      
unique_key:   0
unsigned:     0
zerofill:     0

==========================================================
Result:

Information for column 0:
blob:         0
max_length:   3
multiple_key: 0
name:         id
not_null:     1
numeric:      1
primary_key:  0
table:        
type:         int
default:      
unique_key:   0
unsigned:     0
zerofill:     0

How to repeat:

Where Tags is the view. The other tables are InnoDB

SELECT `Tags`.`id`, `Tags`.`name`, `Tags`.`value`, `Tags`.`quantity`, `Tags`.`tag_priority`, `Tags`.`language`, `Tags`.`group_id`, `Tags`.`group_name`, `Tags`.`group_type`, `Tags`.`group_priority` FROM `tags` AS `Tags` JOIN `tag_value_pe_product` ON (`tag_value_pe_product`.`product_id` = 2 AND `tag_value_pe_product`.`tag_value_id` = `Tags`.`id`) WHERE `Tags`.`language` = 'ENG' ORDER BY `Tags`.`group_priority` ASC, `Tags`.`group_name` ASC, `Tags`.`tag_priority` ASC, `Tags`.`name` ASC

Table layout and testcase in php is attached.
[5 Jun 2007 14:19] Mathijs Savenije
Table layout

Attachment: tables.sql (application/octet-stream, text), 2.60 KiB.

[5 Jun 2007 14:20] Mathijs Savenije
Testcase in php

Attachment: testcase.php (application/octet-stream, text), 1.37 KiB.

[5 Jun 2007 17:20] Andrey Hristov
The server is not faulty. Just dumped the communication between the client and the server and the table name is being sent

0000   00 00 00 00 00 00 00 00 00 00 00 00 08 00 45 08  ..............E.
0010   02 51 fd 98 40 00 40 06 3d 04 7f 00 00 01 7f 00  .Q..@.@.=.......
0020   00 01 0c ea dd 3b 8d 83 8d 8a 8d 8c 14 cb 80 18  .....;..........
0030   20 00 00 46 00 00 01 01 08 0a 10 11 70 d0 10 11   ..F........p...
0040   70 cd 01 00 00 01 0a 24 00 00 02 03 64 65 66 00  p......$....def.
0050   00 0a 74 61 67 5f 76 61 6c 75 65 73 02 69 64 02  ..tag_values.id.
0060   69 64 0c 3f 00 14 00 00 00 08 01 00 00 00 00 27  id.?...........'
0070   00 00 03 03 64 65 66 00 00 09 74 61 67 5f 68 65  ....def...tag_he
0080   61 64 73 04 6e 61 6d 65 04 6e 61 6d 65 0c 08 00  ads.name.name...
0090   ff 00 00 00 fd 01 10 00 00 00 2a 00 00 04 03 64  ..........*....d
00a0   65 66 00 00 0a 74 61 67 5f 76 61 6c 75 65 73 05  ef...tag_values.
00b0   76 61 6c 75 65 05 76 61 6c 75 65 0c 08 00 ff 00  value.value.....
00c0   00 00 fd 01 10 00 00 00 2f 00 00 05 03 64 65 66  ......../....def
00d0   00 00 09 74 61 67 5f 68 65 61 64 73 08 71 75 61  ...tag_heads.qua
00e0   6e 74 69 74 79 08 71 75 61 6e 74 69 74 79 0c 08  ntity.quantity..
00f0   00 0a 00 00 00 fd 01 10 00 00 00 37 00 00 06 03  ...........7....
0100   64 65 66 00 00 09 74 61 67 5f 68 65 61 64 73 0c  def...tag_heads.
0110   74 61 67 5f 70 72 69 6f 72 69 74 79 0c 74 61 67  tag_priority.tag
0120   5f 70 72 69 6f 72 69 74 79 0c 3f 00 0b 00 00 00  _priority.?.....
0130   03 01 10 00 00 00 30 00 00 07 03 64 65 66 00 00  ......0....def..
0140   0a 74 61 67 5f 76 61 6c 75 65 73 08 6c 61 6e 67  .tag_values.lang
0150   75 61 67 65 08 6c 61 6e 67 75 61 67 65 0c 08 00  uage.language...
0160   03 00 00 00 fe 01 10 00 00 00 2f 00 00 08 03 64  ........../....d
0170   65 66 00 00 09 74 61 67 5f 68 65 61 64 73 08 67  ef...tag_heads.g
0180   72 6f 75 70 5f 69 64 08 67 72 6f 75 70 5f 69 64  roup_id.group_id
0190   0c 3f 00 0b 00 00 00 03 01 10 00 00 00 34 00 00  .?...........4..
01a0   09 03 64 65 66 00 00 0a 74 61 67 5f 67 72 6f 75  ..def...tag_grou
01b0   70 73 0a 67 72 6f 75 70 5f 6e 61 6d 65 0a 67 72  ps.group_name.gr
01c0   6f 75 70 5f 6e 61 6d 65 0c 08 00 ff 00 00 00 fd  oup_name........
01d0   01 10 00 00 00 34 00 00 0a 03 64 65 66 00 00 0a  .....4....def...
01e0   74 61 67 5f 67 72 6f 75 70 73 0a 67 72 6f 75 70  tag_groups.group
01f0   5f 74 79 70 65 0a 67 72 6f 75 70 5f 74 79 70 65  _type.group_type
0200   0c 08 00 08 00 00 00 fe 01 11 00 00 00 3c 00 00  .............<..
0210   0b 03 64 65 66 00 00 0a 74 61 67 5f 67 72 6f 75  ..def...tag_grou
0220   70 73 0e 67 72 6f 75 70 5f 70 72 69 6f 72 69 74  ps.group_priorit
0230   79 0e 67 72 6f 75 70 5f 70 72 69 6f 72 69 74 79  y.group_priority
0240   0c 3f 00 0b 00 00 00 03 01 10 00 00 00 05 00 00  .?..............
0250   0c fe 00 00 22 00 05 00 00 0d fe 00 00 22 00     ...."........".
[5 Jun 2007 17:46] Valeriy Kravchuk
I think, it is a bug, still. Checked with mysql -T on latest 5.0.44-BK:

mysql> SELECT `Tags`.`id`, `Tags`.`name`, `Tags`.`value`, `Tags`.`quantity`,
    -> `Tags`.`tag_priority`, `Tags`.`language`, `Tags`.`group_id`, `Tags`.`group_name`,
    -> `Tags`.`group_type`, `Tags`.`group_priority` FROM `tags` AS `Tags` JOIN
    -> `tag_value_pe_product` ON (`tag_value_pe_product`.`product_id` = 2 AND
    -> `tag_value_pe_product`.`tag_value_id` = `Tags`.`id`) WHERE `Tags`.`language` = 'ENG' ORDER
    -> BY `Tags`.`group_priority` ASC, `Tags`.`group_name` ASC, `Tags`.`tag_pri
ority` ASC,
    -> `Tags`.`name` ASC;
Field   1:  `id`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  `tag_values`
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 0
Decimals:   0
Flags:      NOT_NULL NUM

Field   2:  `name`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  `tag_heads`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     255
Max_length: 0
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE

...

Note that Table is empty, while Org_table is correct. While for simple SELECT from view:

mysql> select * from tags;
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `tags`
Org_table:  `tag_values`
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 0
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

Field   2:  `name`
Catalog:    `def`
Database:   `test`
Table:      `tags`
Org_table:  `tag_heads`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     255
Max_length: 0
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE PART_KEY

...

both Table and Org_table are correct. It is a bug, as results are inconsistent.
[5 Jun 2007 20:11] Andrey Hristov
Sorry, my fault, I misread the protocol dump. It has 2 0x0 after def, which are for DB and TABLE. Which means that even DB is empty, not only table.
[19 Jun 2007 13:33] 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/29110

ChangeSet@1.2522, 2007-06-19 19:01:47+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #28898.
  Table alias and database name of VIEW columns was empty
  in the metadata of sorted SELECT statement where VIEW was
  joined with other table.
[19 Jun 2007 23:32] 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/29137

ChangeSet@1.2522, 2007-06-20 05:01:15+05:00, gshchepa@gleb.loc +5 -0
  Fixed bug #28898.
  Table alias and database name of VIEW columns was empty
  in the metadata of sorted SELECT statement where VIEW was
  joined with other table.
[20 Jun 2007 6:56] 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/29153

ChangeSet@1.2522, 2007-06-20 12:25:07+05:00, gshchepa@gleb.loc +5 -0
  Fixed bug #28898.
  For a join query with GROUP BY and/or ORDER BY and a view reference
  in the FROM list the metadata erroneously showed empty table aliases
  and database names for the view columns.
[20 Jun 2007 8:52] 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/29161

ChangeSet@1.2523, 2007-06-20 14:21:48+05:00, gshchepa@gleb.loc +2 -0
  metadata.test, metadata.result:
    Updated test case for bug #28898. Additional cleanup.
[25 Jun 2007 21:49] Bugs System
Pushed into 5.1.21-beta
[25 Jun 2007 21:51] Bugs System
Pushed into 5.0.46
[4 Jul 2007 1:07] Paul Dubois
Noted in 5.0.46, 5.1.21 changelogs.

For a join with GROUP BY and/or ORDER BY and a view reference in the
FROM list, the query metadata erroneously showed empty table aliases
and database names for the view columns.
[9 Oct 2007 10:03] Sergey Vojtovich
BUG#25874 was marked as duplicate.