Reputation: 378
I have tested two queries on two servers, and identical datasets. I got the same results in 3 cases, and wrong result in one case.
Servers: MySQL 5.1.73 (running on RHEL Linux) and MySQL 5.7.11 (running on windows 7)
My queries are below.
On the 5.1.73 server plan A or plan B returns 854 rows.
On the 5.7.11 server plan A returns 854 rows. Plan B returns only 1 row!
Remark: docchange.HID is unique (unsigned integer).
Is this a bug? Is it fixed in newer versions?
Plan B is faster on the production server, but gives wrong results on the development server, and the production server may be upgraded to 5.7.x in the future, which would break functionality for users ..
The two queries:
-- plan A
select r1.doknr, r1.dokcm , r1.HID
from
(SELECT * FROM docdistro WHERE ( (docdistro.tdate_begin) Between '2016-01-01 00:00:00' and '2016-12-01 00:00:00' )) AS d1
INNER JOIN (
select DID, HID, doktype, doknr, dokcm
from docchange as docchange_1
where ( (`docchange_1`.HID)
= ( Select d2.hid from docchange as d2
where d2.did = `docchange_1`.did order by HID asc
limit 1))
) AS r1 ON d1.DID = r1.DID
GROUP BY r1.doknr, r1.dokcm , r1.HID, r1.DID ;
-- plan B
select r1.doknr, r1.dokcm , r1.HID
from
(SELECT * FROM docdistro WHERE ( (docdistro.tdate_begin) Between '2016-01-01 00:00:00' and '2016-12-01 00:00:00' )) AS d1
INNER JOIN (
select DID, HID, doktype, doknr, dokcm
from docchange) AS r1 ON d1.DID = r1.DID
GROUP BY r1.doknr, r1.dokcm , r1.HID, r1.DID
HAVING (
r1.HID = (
Select docchange.hid
from docchange
where docchange.did = r1.DID
order by HID asc
limit 1 )
) ;
Update: Test database & test queries added. Query A1, A2, A4, B4 works properly on both servers. Query B1, B2 works on MySQL 5.1.73. They return no records on MySQL 5.7.11. Query A3, B3 works on MySQL 5.1.73, and gives an error message on 5.7.11. They work properly on 5.7.11 if sql_mode=only_full_group_by" is turned off. I think that query B1, B2 should work the same way on both servers, but they don't. Is this a bug?
Expected query output:
cfgID | mid | did | HID | DID | docname |
---|---|---|---|---|---|
1 | 1 | 1 | 1 | 1 | doc1v1 |
2 | 1 | 2 | 3 | 2 | doc2v1 |
3 | 2 | 2 | 3 | 2 | doc2v1 |
Test database:
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`cfgID` int(11) NOT NULL AUTO_INCREMENT,
`mid` int(11) NOT NULL,
`did` int(11) NOT NULL,
PRIMARY KEY (`cfgID`),
UNIQUE KEY `cfgID_UNIQUE` (`cfgID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,1,1),(2,1,2),(3,2,2);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`HID` int(11) NOT NULL AUTO_INCREMENT,
`DID` int(11) NOT NULL,
`docname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`HID`),
UNIQUE KEY `HID_UNIQUE` (`HID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
INSERT INTO `t2` VALUES (1,1,'doc1v1'),(2,1,'doc1v2'),(3,2,'doc2v1'), (4,3,'doc3v1');
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
UNLOCK TABLES;
Test queries:
-- plan A1
SELECT * FROM (SELECT * FROM t1) as d1 INNER JOIN ( SELECT t2.* FROM t2
INNER JOIN (
select did as mdid, min(hid) as mhid from t2 group by t2.did) as xmin ON t2.did = xmin.mdid and t2.HID = xmin.mhid
) as d2
ON d1.did = d2.did
GROUP BY d1.cfgID, d1.mid, d1.did, d2.hid, d2.did, d2.docname;
-- plan A2
SELECT * FROM (SELECT * FROM t1) as d1 INNER JOIN ( SELECT t2.* FROM t2
where t2.hid = (
select hid as mhid from t2 as xmin where t2.did = xmin.did order by hid asc limit 1
)
) as d2
ON d1.did = d2.did
GROUP BY d1.cfgID, d1.mid, d1.did, d2.hid, d2.did, d2.docname;
-- plan A3 ; switch off "ONLY_FULL_GROUP_BY"
SELECT *
FROM
(SELECT * FROM t1) as d1 INNER JOIN ( SELECT t2.* FROM t2
where t2.hid = (select hid as mhid from t2 as xmin group by xmin.did having xmin.did = t2.did order by xmin.hid asc limit 1)
) as d2 ON d1.did = d2.did
GROUP BY d1.cfgID, d1.mid, d1.did, d2.hid, d2.did, d2.docname;
-- plan A4
SELECT *
FROM
(SELECT * FROM t1) as d1 INNER JOIN ( SELECT t2.* FROM t2
where t2.hid = (select min(hid) as mhid from t2 as xmin group by xmin.did having xmin.did = t2.did)
) as d2 ON d1.did = d2.did
GROUP BY d1.cfgID, d1.mid, d1.did, d2.hid, d2.did, d2.docname;
-- plan B1
SELECT * FROM (SELECT * FROM t1) as d1 INNER JOIN ( SELECT t2.* FROM t2) as d2
ON d1.did = d2.did
GROUP BY d1.cfgID, d1.mid, d1.did, d2.hid, d2.did, d2.docname
HAVING d2.hid = (select min(hid) as mhid from t2 where t2.did = d2.did group by t2.did)
;
-- plan B2
SELECT * FROM (SELECT * FROM t1) as d1 INNER JOIN ( SELECT t2.* FROM t2) as d2
ON d1.did = d2.did
GROUP BY d1.cfgID, d1.mid, d1.did, d2.hid, d2.did, d2.docname
HAVING d2.hid = (select hid as mhid from t2 where t2.did = d2.did order by hid asc limit 1);
-- plan B3:
SELECT d1.*, d2.* FROM (SELECT * FROM t1) as d1 INNER JOIN
(SELECT t2.* FROM t2) as d2
ON d1.did = d2.did
GROUP BY d1.cfgID, d1.mid, d1.did, d2.hid, d2.did, d2.docname
HAVING d2.hid = (select hid as mhid from t2 as tx group by tx.did having tx.did = d2.did order by tx.hid asc limit 1)
;
-- plan B4
SELECT *
FROM
(SELECT * FROM t1) as d1 INNER JOIN ( SELECT t2.* FROM t2
) as d2 ON d1.did = d2.did
GROUP BY d1.cfgID, d1.mid, d1.did, d2.hid, d2.did, d2.docname
HAVING d2.hid = (select min(hid) as mhid from t2 group by t2.did having t2.did = d2.did);
Upvotes: 0
Views: 1275