MySQL Bugs: #44660: MYSQLI_PRI_KEY_FLAG is not set when ORDER BY is used on LEFT JOINED table field

Bug #44660	MYSQLI_PRI_KEY_FLAG is not set when ORDER BY is used on LEFT JOINED table field
Submitted:	5 May 2009 10:06	Modified:	13 May 2009 9:38
Reporter:	Rok Meglič	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connectors: mysqlnd ( PHP )	Severity:	S2 (Serious)
Version:	5.1.30	OS:	Any (The result is the same regardless of OS)
Assigned to:		CPU Architecture:	Any
Tags:	fetch_field, left join, MYSQLI_PRI_KEY_FLAG, order by

Description:
Description:
------------
After we LEFT JOIN two tables and use ORDER BY on foreign table field
the MySQLI doesn't return the primary key flag correctly trough
fetch_field operation. The result is the same regardless of OS. 

PHP MySQL library version 5.0.51a
MySQL server version 5.1.30

(also tried on other 5.x versions on Linux & Windows and I get same result)

SQL EXPORT:
------------
CREATE TABLE `c_news` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `lang` varchar(2) character set utf8 collate utf8_slovenian_ci NOT
NULL default '',
  `title` varchar(200) character set utf8 collate utf8_slovenian_ci NOT
NULL default '',
  `summary` text character set utf8 collate utf8_slovenian_ci,
  `content` text character set utf8 collate utf8_slovenian_ci,
  `picture` varchar(255) character set utf8 collate utf8_slovenian_ci
default NULL,
  `picture_small` varchar(255) character set utf8 collate
utf8_slovenian_ci default NULL,
  `video_pic` varchar(255) character set utf8 collate utf8_slovenian_ci
default NULL,
  `video_clip` varchar(255) character set utf8 collate utf8_slovenian_ci
default NULL,
  `valid_from` date default NULL,
  `valid_till` datetime default NULL,
  `deleted` tinyint(1) default '0',
  `news_date` date default NULL,
  `na_naslovnici` tinyint(1) default NULL,
  `area` int(11) default NULL,
  `sub_area` int(11) default NULL,
  `sub_area1` int(11) default NULL,
  `type` varchar(255) character set utf8 collate utf8_slovenian_ci
default NULL,
  `featured` tinyint(1) default '0',
  `news_gallery_id` varchar(11) default NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `search_fields` (`title`,`summary`,`content`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=966 ;

CREATE TABLE `c_news_categories` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) collate utf8_slovenian_ci NOT NULL,
  `parent_id` int(11) default NULL,
  `sub_parent_id` int(11) default NULL,
  `order_num` int(11) default NULL,
  `deleted` tinyint(1) default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci
CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=59 ;

How to repeat:
Reproduce code:
---------------
$query = "select cn.id from c_news cn LEFT JOIN c_news_categories cc ON
cc.id = cn.area ORDER BY cc.name"; // if we dont use ORDER BY cc.name,
the primary flag is set correctly 
if ($result = $mysqli->query($query)) {
    while ($finfo = $result->fetch_field()) {
        printf("Flags: %d\n", $finfo->flags);
        printf("Primary key: %d\n\n", (bool) ($finfo->flags &
MYSQLI_PRI_KEY_FLAG));
    }
    $result->close();
}

Expected result:
----------------
Flags: 49699 
Primary key: true 

Actual result:
--------------
Flags: 32801
Primary key: 0

Please, check with a newer version of server, 5.1.34, and inform about the results. I suspect some optimizer-related problem here that may be already fixed.

You are right, this is working with 5.1.34 version.

Thank you for the feedback.

Closed as "Can't repeat"

I only have one question. Will this be fix with 5.0.x branch or 5.0.x should be updated to 5.1.x?

Thank you for the feedback.

If you say about client library it should work with existant PHP MySQL library version 5.0.51a

Thank you for the feedback.

No, I'm asking about MySQL server version 5.0.x branch. In my case this bug is also present in 5.0.51a-log (SELECT @@version).