Re: Reverting SQL Standard to SQL-92

[Peter Brawley]

DROP TABLE IF EXISTS `name`;
CREATE TABLE `name` (
  `sno` smallint(6) DEFAULT NULL,
  `name` char(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `name` VALUES (1,'foo'),(1,'bar'),(2,'fubar'),(2,'foobar');

DROP TABLE IF EXISTS `location`;
CREATE TABLE `location` (
  `sno` smallint(6) DEFAULT NULL,
  `location` char(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `location` VALUES (1,'foo'),(1,'bar'),(2,'foobar');

set sql_mode="only_full_group_by";

-- QUERY REJECTED UNDER ONLY_FULL_GROUP_BY
SELECT @@sql_mode, a.sno,b.sno,a.name,b.location 
FROM NAME AS a 
JOIN LOCATION AS b ON a.sno=b.sno 
GROUP BY a.sno,b.location ;

set sql_mode="";

-- QUERY REJECTED UNDER ONLY_FULL_GROUP_BY
SELECT @@sql_mode, a.sno,b.sno,a.name,b.location 
FROM NAME AS a 
JOIN LOCATION AS b ON a.sno=b.sno 
GROUP BY a.sno,b.location ;

Again, I ran this script in 5.6, 5.7 and 8.0. In each case the query elicited a group by error when only_full_group_by was set.