Description:
havent been able to extract problem, I mean, created separated test tables and it works as expected.
Quote from wiki:
The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).
So my left table contains 6 records, but join only returns 4, the problem is seems with combination of ENUM field and '' values.
select * from test t left outer join test1 t1
on t.id_gender = t1.id_gender
where t1.l2 = 'sl'
where g.l2 = 'sl'
How to repeat:
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id_gender` enum('','1','2','3') CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci ROW_FORMAT=DYNAMIC;
--
-- Dumping data for table `test`
--
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` (`id_gender`) VALUES
('2'),
('2'),
('2'),
('1'),
(''),
('');
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
--
-- Definition of table `test1`
--
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
`id_gender` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '0',
`l2` varchar(2) COLLATE utf8_slovenian_ci NOT NULL DEFAULT '',
`term` varchar(45) COLLATE utf8_slovenian_ci DEFAULT NULL,
PRIMARY KEY (`id_gender`,`l2`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;
--
-- Dumping data for table `test1`
--
/*!40000 ALTER TABLE `test1` DISABLE KEYS */;
INSERT INTO `test1` (`id_gender`,`l2`,`term`) VALUES
('1','sl','moški'),
('2','sl','ženski'),
('3','sl','kastrat'),
('1','en','male'),
('2','en','female'),
('3','en','castrated');
/*!40000 ALTER TABLE `test1` ENABLE KEYS */;
Description: havent been able to extract problem, I mean, created separated test tables and it works as expected. Quote from wiki: The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). So my left table contains 6 records, but join only returns 4, the problem is seems with combination of ENUM field and '' values. select * from test t left outer join test1 t1 on t.id_gender = t1.id_gender where t1.l2 = 'sl' where g.l2 = 'sl' How to repeat: DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `id_gender` enum('','1','2','3') CHARACTER SET utf8 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci ROW_FORMAT=DYNAMIC; -- -- Dumping data for table `test` -- /*!40000 ALTER TABLE `test` DISABLE KEYS */; INSERT INTO `test` (`id_gender`) VALUES ('2'), ('2'), ('2'), ('1'), (''), (''); /*!40000 ALTER TABLE `test` ENABLE KEYS */; -- -- Definition of table `test1` -- DROP TABLE IF EXISTS `test1`; CREATE TABLE `test1` ( `id_gender` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '0', `l2` varchar(2) COLLATE utf8_slovenian_ci NOT NULL DEFAULT '', `term` varchar(45) COLLATE utf8_slovenian_ci DEFAULT NULL, PRIMARY KEY (`id_gender`,`l2`) USING BTREE ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci; -- -- Dumping data for table `test1` -- /*!40000 ALTER TABLE `test1` DISABLE KEYS */; INSERT INTO `test1` (`id_gender`,`l2`,`term`) VALUES ('1','sl','moški'), ('2','sl','ženski'), ('3','sl','kastrat'), ('1','en','male'), ('2','en','female'), ('3','en','castrated'); /*!40000 ALTER TABLE `test1` ENABLE KEYS */;