Bug #85533 Incorrect sorting result while substring_index function
Submitted: 20 Mar 2017 9:17 Modified: 20 Mar 2017 11:41
Reporter: Vardan Shahinyan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.16-log, 5.5.54, 5.6.35, 5.7.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: order by, SUBSTRING_INDEX

[20 Mar 2017 9:17] Vardan Shahinyan 
Description:
Correct execution result of below query should have `workbench.mysql.com` and  `dev.mysql.com` exchanged by places.

How to repeat:
CREATE TABLE `hostnames` (
  `id` int(11) NOT NULL,
  `hostname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `hostnames` VALUES (1,'workbench.mysql.com'),(2,'codefights.slack.com'),(3,'codefights.com'),(4,'snarknews.info'),(5,'sololearn.com'),(6,'dev.mysql.com');

SELECT id,hostname 
  FROM hostnames 
  ORDER BY SUBSTRING_INDEX(concat('...',hostname),'.',-1), SUBSTRING_INDEX(concat('...',hostname),'.',-2), hostname;
[20 Mar 2017 11:41] MySQL Verification Team 
Hello Vardan,

Thank you for the report and test case.

Thanks,
Umesh