Bug #14915 mysql_result(..., "tablename.fieldname") + Upgrade 4.1 -> 5.0
Submitted: 14 Nov 2005 9:36 Modified: 10 Sep 2008 14:07
Reporter: Mark Plomer Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.15 OS:Linux (Linux (not relevant))
Assigned to: Assigned Account CPU Architecture:Any

[14 Nov 2005 9:36] Mark Plomer
Description:
One of my customers has experienced the following problem:
He uses mysql_result() (in PHP) with "tablename.rowname" to access the fields, if he selects (joins) from more than one table. This worked with MySQL 4.1.14.
After upgrading to 5.0.15 it doesn't work at first attempt, (but accessing the fields only with "rowname" always works).

After some testing, i found that the following conditions must be true to reproduce the problem:
- Table must be created with 4.1.x !!!
- Then upgrading to 5.0.x without touching the binary-table-files
- selecting from more than one table
- And (which i really don't understand): The field which should be accessed by mysql_result() by "tablename.rowname" must be in ORDER BY-Clause in the SELECT-Query (either in form "tablename.fieldname" or only "fieldname", thats irrelevant)

After i found out, that mysqldump and reimport the databases fixes the problem, i can live with that, but i think, you should know about this ;-)

How to repeat:
PHP-Script:

// ... Connecting, Selecting DB ...
$result = mysql_query("SELECT * FROM animal LEFT JOIN user ON animal.user_id=user.id ORDER BY user.name");
$name = mysql_result($result, 0, "user.name");
echo $name;

Sample-DB (only to understand the query):

CREATE TABLE `user` (
`id` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
`name` varchar( 255 ) NOT NULL default '',
PRIMARY KEY ( `id` )
) ENGINE = MYISAM;
INSERT INTO `user` VALUES ( 1, 'andy' ) ;
INSERT INTO `user` VALUES ( 2, 'chris' ) ;

CREATE TABLE `animal` (
`id` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
`user_id` int( 11 ) NOT NULL default '0',
`name` varchar( 255 ) NOT NULL default '',
PRIMARY KEY ( `id` )
) ENGINE = MYISAM;
INSERT INTO `animal` VALUES ( 1, 1, 'cat' );
INSERT INTO `animal` VALUES ( 2, 1, 'dog' );
INSERT INTO `animal` VALUES ( 3, 2, 'fish' );
INSERT INTO `animal` VALUES ( 4, 2, 'mouse' );
[14 Nov 2005 9:42] Mark Plomer
When i wrote "rowname", i meant "fieldname" ...
[14 Nov 2005 9:44] Mark Plomer
[i would not change the Subject before ...]
[14 Nov 2005 10:32] Mark Plomer
I forgot to mention the error-message, that is displayed, when calling the mysql_result(..., "tablename.fieldname"):
"tablename.fieldname not found in MySQL result index 8 in ....php"
[23 Nov 2005 21:15] Thomas Keller
Hi there!

I can confirm this behaviour, though I updated also from 4.0.25 to 5.0.16 on a FC2 machine. The same behaviour popped up on a 4.1.9 to 5.0.16 upgrade on my local FC4 installation. In both cases the binary tables weren't touched during upgrade.

I used PHP's mysql_fetch_field to retrieve info about each field in my query. The query contains one join and uses aliases. Like the post author already told, the table name isn't set for each returned field, unless the ORDER BY part is ommitted. Here is a small PHP code snippet:

<?php

error_reporting(E_ALL);

include "../admin/globals.inc.php";
include "../lib.inc.php";

// does a simple mysql_connect and mysql_select_db for our db
$link = mysqlConnect();

$sql = 'SELECT SQL_CALC_FOUND_ROWS tb.*, t0.* FROM
users as tb 
LEFT JOIN users_online AS t0 ON 1 AND t0.user_id=tb.user_id 
ORDER BY `actphrase` asc';
$res = mysql_query($sql, $link) or die(mysql_error());

$fieldCount = mysql_num_fields($res);

for ($i=0; $i<$fieldCount; $i++)
{
    echo $i," ",mysql_field_table($res, $i),"<br>";
}

?>

The code above works flawlessly in 4.0.x and 4.1.x, but breaks in 5.0.16 as it only partially returns the table, not always. The fieldname after which is ordered is irrelevant.

I'm now trying to dump and restore my db and see if this helps to fix the issue.
[23 Nov 2005 22:03] Thomas Keller
Indeed, after doing

CREATE TABLE tmp SELECT * FROM table;
DROP TABLE table;
ALTER TABLE tmp RENAME table;

for all included tables in my query via commandline client, the problem vanished.
[5 Apr 2006 19:06] Devin Butts
This problem still exists for me.  The TableName.FieldName doesn't work.  I'm using MySQL 5.0.18-Debian_1.   I tried the above, recreating the table with no success.  I'm having to create an array of field references vs field index to reference fields with the same name from different tables in one result because of an inner join.  The problem exists for me when using both PHP4 and PHP5. 

(Other key words for search: Table dot Field Table Name dot Field Name)
[25 Jul 2006 7:43] Hartmut Holzgraefe
Yet another condition: 

- only the last column for each table is affected

I tracked things down to this difference in the 4.1 and 5.0 .frm files defining the user table:

-00002170  05 4a ff 00 05 00 00 00  00 00 00 00 fd 08 00 00  |.J..............|
+00002170  05 4a ff 00 05 00 00 00  00 00 00 00 0f 08 00 00  |.J..............|
                                                ^^

Once i change this byte from 0xFD to 0xFF in the user.frm file taken from 4.1 the problem with the last column of the user tabel goes away but still persists for the animals table
[25 Jul 2006 11:06] Hartmut Holzgraefe
C test case and data files (SQL and MyISAM)

Attachment: bug_14915-1.0.tar.bz2 (application/x-tar, text), 188.43 KiB.

[25 Jul 2006 11:09] Hartmut Holzgraefe
Added a C example to show that:
- it is not a PHP specific problem
- it is mysql_fetch_field() which is to blame 
  (although the real problem is on the server side)
[30 Aug 2006 10:24] Konstantin Osipov
This is a duplicate of Bug#14897 "ResultSet.getString("table.column") sometimes doesn't find the column", which is fixed in 5.0-runtime and will hopefully make it into 5.0.25.

I tested the bug against 4.1, 5.0 and 5.0 runtime, and in 5.0 I get the following metadata:
| animal.id | animal.user_id | .name  | user.id   | .name  |
whereas in 5.0-runtime (with the fix for Bug#14897) and 4.1 the result is correct:
| animal.id | animal.user_id | animal.name  | user.id   | user.name  |
[10 Sep 2008 13:50] Richard Bagnall
The following is failing for me. I seem unable to reference the field value using the field name 'firstname', though using the index '0' works? Can anyone advise this perplexed newbie please?

	$strSQL = "SELECT firstname FROM users WHERE id = ".$_GET["id"];

	//echo($strSQL);
	$result = mysql_query( $strSQL ) or die(strSQLError($strSQL)); 
	//echo($strSQL);
	if ($result){
		$nrows = mysql_num_rows($result);
		$row = mysql_fetch_row($result);
		//echo( $row[0] );
		$strFirstName = $row['firstname'];
	}
[10 Sep 2008 14:07] Mark Plomer
This is absolutely the wrong place to ask such questions. But have a look at mysql_fetch_assoc(), instead of mysql_fetch_row().
[10 Sep 2008 14:10] Richard Bagnall
Sorry & Thanks.
[24 Dec 2008 10:52] max ali
maxhex