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