Joining ExtractValue fails
DATA:
CREATE TABLE `test` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`parent` tinyint(3) unsigned NOT NULL,
`title` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `test` (`id`, `parent`, `title`) VALUES
(1, 0, '<root><title>root entry</title></root>'),
(2, 1, '<root><title>child entry</title></root>');
QUERY 1:
SELECT tb0.id, tb0.parent, tb0.title as title, tj.title as parent_title FROM `test` tb0 left join `test` tj on(tj.id = tb0.parent) having parent_title = '' or parent_title is null
RESULT 1 (OK):
id | parent | title | parent_title
1 | 0 | <root><title>root entry</title></root> | NULL
QUERY 2:
SELECT tb0.id, tb0.parent, extractvalue(tb0.title,'/root/title') as title, extractvalue(tj.title,'/root/title') as parent_title FROM `test` tb0 left join `test` tj on(tj.id = tb0.parent) having parent_title = '' or parent_title is null
RESULT 2 (JOIN FAILS):
id | parent | title | parent_title
1 | 0 | root entry | NULL
2 | 1 | child entry | root entry
Join fails only while we join the same table; joins to other tables with ExtractValue work fine.
Is it me? Or is it a bug? I'm using the 5.1.22-rc-community server on WinXP.
Could anyone chceck the above error in the latest version of MySQL?
Reagrds,
Jakub