Eperbab
Eperbab

Reputation: 378

MySQL limit clause in having - is it a bug?

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

Answers (0)

Related Questions