Bug #15457 full join not work
Submitted: 3 Dec 2005 14:19 Modified: 5 Dec 2005 21:49
Reporter: Nikolay Shestakov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.17-BK, 5.1.3 OS:Linux (Linux)
Assigned to: Igor Babaev CPU Architecture:Any

[3 Dec 2005 14:19] Nikolay Shestakov 
Description:
full join not work correctly

How to repeat:
CREATE TABLE `t1` (
  `ID` varchar(36) collate utf8_unicode_ci NOT NULL,
  `Num` decimal(15,2) NOT NULL,
  `time` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `t1` VALUES ('1', '13.00', '2005-09-18 21:54:48');
INSERT INTO `t1` VALUES ('2', '44.00', '2005-12-03 19:01:30');

CREATE TABLE `t2` (
  `ID` varchar(36) collate utf8_unicode_ci NOT NULL,
  `Num` decimal(15,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `t2` VALUES ('1', '2.00');

select * from t1 full join t2 on t2.ID = t1.ID
#1054 - Unknown column 't1.ID' in 'on clause'

SELECT * FROM t1 FULL JOIN t2 USING ( ID )
return only one row
1	13.00	2005-09-18 21:54:48	2.00
but require two rows
1	13.00	2005-09-18 21:54:48	2.00
2	44	2005-12-03 19:01:30 NULL
[3 Dec 2005 17:32] Valeriy Kravchuk 
Thank you for a problem report. The same results I've got with 5.0.17-BK (ChangeSet@1.2041, 2005-12-01 15:10:35-08:00) on Linux. 

But the real problem is: FULL JOIN is not documented at all in the manual, even latest (http://dev.mysql.com/doc/refman/5.1/en/join.html). So, why do you thing it is supported at all (and what is the right behavior - nothing documented)?
[4 Dec 2005 7:03] Nikolay Shestakov 
because http://dev.mysql.com/doc/refman/5.1/en/roadmap.html
[4 Dec 2005 12:59] Valeriy Kravchuk 
Yes, FULL join support is planned, but a right place to look for real additions made is http://dev.mysql.com/doc/refman/5.1/en/news-5-1-3.html. It is not implemented yet.

Nevertheless, I think, the error message you got (and even more - the same error message in 5.0.17, where FULL joins may be never implemented) is misleading, and is a bug.
[5 Dec 2005 21:49] Igor Babaev 
In the query
select * from t1 full join t2 on t2.ID = t1.ID
'full' as considered as an alias.
It happens because there is no such keyword in MySQL grammar.
So the message
#1054 - Unknown column 't1.ID' in 'on clause'
is quite valid for the above query.