Description:
Version 5.0.41-community-nt gives unexpected results in complex query, please check given example.
How to repeat:
To reproduce problem please create test db and tables + insert demo data:
DROP TABLE IF EXISTS `_testf2r`;
CREATE TABLE IF NOT EXISTS `_testf2r` (
`TESTFID` char(255) character set latin1 collate latin1_general_ci NOT NULL,
`TESTRID` char(32) character set latin1 collate latin1_general_ci NOT NULL,
PRIMARY KEY (`TESTFID`,`TESTRID`),
KEY `TESTRID` (`TESTRID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `_testf2r` (`TESTFID`, `TESTRID`) VALUES
('42b44bc9934bdb406.85935627', 'ecad80dcbb828a6d5db095cf1ffa9455'),
('42b44bc9941a46fd3.13180499', 'ecad80dcbb828a6d5db095cf1ffa9455'),
('42b44bc99488c66b1.94059993', 'ecad80dcbb828a6d5db095cf1ffa9455');
DROP TABLE IF EXISTS `_testor`;
CREATE TABLE IF NOT EXISTS `_testor` (
`TESTID` char(32) character set latin1 collate latin1_general_ci NOT NULL,
`TESTOID` char(32) character set latin1 collate latin1_general_ci NOT NULL,
PRIMARY KEY (`TESTOID`),
KEY `TESTOID` (`TESTOID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `_testor` (`TESTID`, `TESTOID`) VALUES
('eca8a63b05cdf10b7487bb9f11a1ec88', 'ecad80dcbb828a6d5db095cf1ffa9455');
DROP TABLE IF EXISTS `_testrf`;
CREATE TABLE IF NOT EXISTS `_testrf` (
`TESTID` char(32) character set latin1 collate latin1_general_ci NOT NULL,
`TESTNAME` char(255) NOT NULL,
PRIMARY KEY (`TESTID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `_testrf` (`TESTID`, `TESTNAME`) VALUES
('42b44bc9934bdb406.85935627', 'NAME1'),
('42b44bc9941a46fd3.13180499', 'NAME2'),
('42b44bc99488c66b1.94059993', 'NAME3'),
('42b44bc9950334951.12393781', 'NAME4');
DROP TABLE IF EXISTS `_testr`;
CREATE TABLE IF NOT EXISTS `_testr` (
`TESTID` char(32) character set latin1 collate latin1_general_ci NOT NULL,
`TESTTITLE` char(255) NOT NULL,
PRIMARY KEY (`TESTID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `_testr` (`TESTID`, `TESTTITLE`) VALUES
('ecad80dcbb828a6d5db095cf1ffa9455', 'testrolle');
Now execute following query:
SELECT
_testrf.TESTNAME
FROM
_testr
LEFT JOIN _testf2r ON _testr.TESTID = _testf2r.TESTRID
LEFT JOIN _testrf ON _testrf.TESTID = _testf2r.TESTFID
LEFT JOIN _testor ON _testor.TESTOID = _testr.TESTID
WHERE
_testor.TESTOID IS NOT NULL AND
(
SELECT 1
FROM
_testr
LEFT JOIN _testf2r ON _testr.TESTID = _testf2r.TESTRID
LEFT JOIN _testor ON _testor.TESTOID = _testr.TESTID
WHERE
_testrf.TESTID = _testf2r.TESTFID AND
_testor.TESTID IS NOT NULL LIMIT 1
) IS NULL
GROUP BY _testrf.TESTID;
5.0.41-community-nt version returns "NAME1", 5.0.32-Debian_7etch3-log returns nothing - expected behaviour.
Suggested fix:
Newer versions has no such problem, but in version changelog i saw nothing related to this problem.
Description: Version 5.0.41-community-nt gives unexpected results in complex query, please check given example. How to repeat: To reproduce problem please create test db and tables + insert demo data: DROP TABLE IF EXISTS `_testf2r`; CREATE TABLE IF NOT EXISTS `_testf2r` ( `TESTFID` char(255) character set latin1 collate latin1_general_ci NOT NULL, `TESTRID` char(32) character set latin1 collate latin1_general_ci NOT NULL, PRIMARY KEY (`TESTFID`,`TESTRID`), KEY `TESTRID` (`TESTRID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `_testf2r` (`TESTFID`, `TESTRID`) VALUES ('42b44bc9934bdb406.85935627', 'ecad80dcbb828a6d5db095cf1ffa9455'), ('42b44bc9941a46fd3.13180499', 'ecad80dcbb828a6d5db095cf1ffa9455'), ('42b44bc99488c66b1.94059993', 'ecad80dcbb828a6d5db095cf1ffa9455'); DROP TABLE IF EXISTS `_testor`; CREATE TABLE IF NOT EXISTS `_testor` ( `TESTID` char(32) character set latin1 collate latin1_general_ci NOT NULL, `TESTOID` char(32) character set latin1 collate latin1_general_ci NOT NULL, PRIMARY KEY (`TESTOID`), KEY `TESTOID` (`TESTOID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `_testor` (`TESTID`, `TESTOID`) VALUES ('eca8a63b05cdf10b7487bb9f11a1ec88', 'ecad80dcbb828a6d5db095cf1ffa9455'); DROP TABLE IF EXISTS `_testrf`; CREATE TABLE IF NOT EXISTS `_testrf` ( `TESTID` char(32) character set latin1 collate latin1_general_ci NOT NULL, `TESTNAME` char(255) NOT NULL, PRIMARY KEY (`TESTID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `_testrf` (`TESTID`, `TESTNAME`) VALUES ('42b44bc9934bdb406.85935627', 'NAME1'), ('42b44bc9941a46fd3.13180499', 'NAME2'), ('42b44bc99488c66b1.94059993', 'NAME3'), ('42b44bc9950334951.12393781', 'NAME4'); DROP TABLE IF EXISTS `_testr`; CREATE TABLE IF NOT EXISTS `_testr` ( `TESTID` char(32) character set latin1 collate latin1_general_ci NOT NULL, `TESTTITLE` char(255) NOT NULL, PRIMARY KEY (`TESTID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `_testr` (`TESTID`, `TESTTITLE`) VALUES ('ecad80dcbb828a6d5db095cf1ffa9455', 'testrolle'); Now execute following query: SELECT _testrf.TESTNAME FROM _testr LEFT JOIN _testf2r ON _testr.TESTID = _testf2r.TESTRID LEFT JOIN _testrf ON _testrf.TESTID = _testf2r.TESTFID LEFT JOIN _testor ON _testor.TESTOID = _testr.TESTID WHERE _testor.TESTOID IS NOT NULL AND ( SELECT 1 FROM _testr LEFT JOIN _testf2r ON _testr.TESTID = _testf2r.TESTRID LEFT JOIN _testor ON _testor.TESTOID = _testr.TESTID WHERE _testrf.TESTID = _testf2r.TESTFID AND _testor.TESTID IS NOT NULL LIMIT 1 ) IS NULL GROUP BY _testrf.TESTID; 5.0.41-community-nt version returns "NAME1", 5.0.32-Debian_7etch3-log returns nothing - expected behaviour. Suggested fix: Newer versions has no such problem, but in version changelog i saw nothing related to this problem.