| Bug #41899 | "orgtable" field info changed in MySQL 5.1 | ||
|---|---|---|---|
| Submitted: | 6 Jan 2009 17:45 | Modified: | 11 Jan 2009 15:19 |
| Reporter: | Andrew Hanna | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Views | Severity: | S2 (Serious) |
| Version: | 5.1.30 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | field info, orgtable, VIEW | ||
[7 Jan 2009 0:02]
Davi Arnaut
I guess that org_table intention was to hold the original name of the (virtual or not) table if a alias is defined. Need to check which one is the documented behavior. Otherwise looks similar to Bug#41788.
[7 Jan 2009 3:24]
Andrew Hanna
If it turns out it's a documentation issue, then I'd request that it be a feature request to add another field info item that would report the original physical table of the field. Not sure if I would need to open a new bug or if this one could simply be converted if that is the case.
[11 Jan 2009 15:19]
Sveta Smirnova
Thank you for the report. According to the documentation at http://dev.mysql.com/doc/refman/5.1/en/c-api-datatypes.html: ----<Q>---- # char * org_name The name of the field, as a null-terminated string. Aliases are ignored. # char * table The name of the table containing this field, if it isn't a calculated field. For calculated fields, the table value is an empty string. If the column is selected from a view, table names the view. If the table or view was given an alias with an AS clause, the value of table is the alias. For a UNION, the value is the empty string. ----</Q>---- So current behavior is wrong, but repeats bug #41788. So I marked this bug as duplicate of bug #41788.
Description: In PHP, I used the field info functionality to create certain logic about views. The basic idea was to do this: SELECT * FROM `viewname` WHERE 0; Then, I would get the field information from the query and use it in my logic. In MySQL 5.0.x, orgtable returned the original table that the field in the view was from. In 5.1.30, orgtable is the name of the view. So table and orgtable are both the same value, making orgtable completely useless. My assumption is that this bug is a problem caused by MySQL and not by PHP, therefore I am posting in here. How to repeat: #!/usr/local/php5/bin/php <?php $oDB = new mysqli('localhost', 'user', 'password', 'test'); // create table $oDB->query("CREATE TABLE `test`.`fieldinfo` (`a` VARCHAR(50) NOT NULL, `b` ENUM('a', 'b', 'c') NOT NULL) ENGINE = MYISAM"); // create view $oDB->query('CREATE VIEW `testview` AS SELECT * FROM `fieldinfo`'); // select $oResult = $oDB->query('SELECT * FROM `testview` WHERE 0'); // fields $aFields = $oResult->fetch_fields(); // debug print_r($aFields); ?> Suggested fix: Expected Result: Array ( [0] => stdClass Object ( [name] => a [orgname] => a [table] => testview [orgtable] => fieldinfo [def] => [max_length] => 0 [length] => 50 [charsetnr] => 8 [flags] => 4097 [type] => 253 [decimals] => 0 ) [1] => stdClass Object ( [name] => b [orgname] => b [table] => testview [orgtable] => fieldinfo [def] => [max_length] => 0 [length] => 1 [charsetnr] => 8 [flags] => 4353 [type] => 254 [decimals] => 0 ) ) Actual Result: Array ( [0] => stdClass Object ( [name] => a [orgname] => a [table] => testview [orgtable] => testview [def] => [max_length] => 0 [length] => 50 [charsetnr] => 8 [flags] => 4097 [type] => 253 [decimals] => 0 ) [1] => stdClass Object ( [name] => b [orgname] => b [table] => testview [orgtable] => testview [def] => [max_length] => 0 [length] => 1 [charsetnr] => 8 [flags] => 4353 [type] => 254 [decimals] => 0 ) )