Bug #53897 cross join in MySQL v5.5.2, succeded in MySQL v4.1.22

• Submitted: 21 May 2010 18:28
• Modified: 22 Jun 2010 9:22
• Reporter: Ådne Hetlelid
• Status: No Feedback
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: MySQL v5.5.2
• OS: Windows
• CPU Architecture: Any

[21 May 2010 18:28] Ådne Hetlelid

Description:
SQL test
MySQL v4.1.22 sucess
MySQL v5.5.2 failed

How to repeat:
DROP TABLE IF EXISTS `node_object`;
CREATE TABLE `node_object` (
  `node_id` int(22) NOT NULL default '0',
  `class_id` int(22) NOT NULL default '0',
  `master_id` int(22) NOT NULL default '0',
  `reference_id` int(22) NOT NULL default '0',
  PRIMARY KEY  (`node_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

select	o.node_id
from
	node_object o
cross join node_object o_6
on	o_6.class_id='6'
and	o_6.node_id in (o_7.master_id,o_9.master_id)
left join node_object o_7
on	o_7.class_id='7'
and	o_7.master_id=o_6.node_id
left join node_object o_9
on	o_9.class_id='9'
group by o.node_id

[22 May 2010 9:22] Valeriy Kravchuk

This looks related to changes in JOIN processing since 5.0.12. Read http://dev.mysql.com/doc/refman/5.0/en/join.html:

"Previously, the ON clause could refer to columns in tables named to its right. Now an ON clause can refer only to its operands."

So, query should rewritten to something like:

mysql> select o.node_id from node_object o cross join node_object o_6 left join node_object o_7 on o_7.class_id='7' and o_7.master_id=o_6.node_id left join node_object o_9 on o_9.class_id='9' and o_6.node_id in (o_7.master_id,o_9.master_id) group by o.node_id;
Empty set (0.00 sec)

[22 Jun 2010 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".