The problem happens only with ONLY_FULL_GROUP_BY, MySQL

Language: Layout:

SET SESSION SQL_MODE = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
SHOW VARIABLES LIKE 'version';

-- Now create some tables to query
DROP TABLE IF EXISTS t_products, t_barcodes;

CREATE TABLE t_products (
  skuid INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  sku VARCHAR(40) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL UNIQUE,
  title VARCHAR(80) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  INDEX (title(10))
);
CREATE TABLE t_barcodes (
  barcode VARCHAR(15) CHARACTER SET ascii COLLATE ascii_general_ci PRIMARY KEY,
  skuid INTEGER UNSIGNED NOT NULL,
  INDEX (skuid)  -- in production this is a FOREIGN KEY
);

INSERT INTO t_products (skuid, sku, title) VALUES
(1, 'SKU1', 'title 1'),
(2, 'SKU2', 'title 2'),
(3, 'SKU3', 'title 3');

INSERT INTO t_barcodes (barcode, skuid) VALUES
('BC1FORSKU1', 1),
('BC2FORSKU1', 1),
('BC1FORSKU2', 2),
('BC2FORSKU2', 2),
('BC1FORSKU3', 3),
('BC2FORSKU3', 3);

-- WORKING: Get product ID, SKU, title, and barcodes where the SKU
-- prefix or title prefix matches.  In the production code, each
-- 'SK%' or the like is a PDO placeholder.
SELECT pr.skuid, pr.sku, pr.title,
GROUP_CONCAT(bc.barcode SEPARATOR '
') AS barcodes
FROM (
(SELECT DISTINCT skuid, sku, title
FROM t_products
WHERE sku LIKE 'SK%' OR title LIKE 'SK%')
) AS pr
LEFT JOIN t_barcodes AS bc ON bc.skuid = pr.skuid
GROUP BY pr.skuid;

-- WORKING: Look up all barcodes of products where one barcode
-- matches the query string
SELECT pr.skuid, pr.sku, pr.title,
GROUP_CONCAT(bc.barcode SEPARATOR '
') AS barcodes
FROM (
(SELECT DISTINCT t_products.skuid, t_products.sku, t_products.title
FROM t_products
INNER JOIN t_barcodes ON t_products.skuid = t_barcodes.skuid
WHERE t_barcodes.barcode = 'BC1FORSKU1')
) AS pr
LEFT JOIN t_barcodes AS bc ON bc.skuid = pr.skuid
GROUP BY pr.skuid;

-- But this statement gives error 1055:
-- Expression #2 of SELECT list is not in GROUP BY clause and
-- contains nonaggregated column 'pr.sku' which is not functionally
-- dependent on columns in GROUP BY clause; this is incompatible
-- with sql_mode=only_full_group_by
SELECT pr.skuid, pr.sku, pr.title,
GROUP_CONCAT(bc.barcode SEPARATOR '
') AS barcodes
FROM (
(SELECT DISTINCT skuid, sku, title
FROM t_products
WHERE sku LIKE 'BC1FORSKU1%' OR title LIKE 'BC1FORSKU1%')
UNION
(SELECT DISTINCT t_products.skuid, t_products.sku, t_products.title
FROM t_products
INNER JOIN t_barcodes ON t_products.skuid = t_barcodes.skuid
WHERE t_barcodes.barcode = 'BC1FORSKU1')
) AS pr
LEFT JOIN t_barcodes AS bc ON bc.skuid = pr.skuid
GROUP BY pr.skuid


Absolute service time: 0,5 sec, absolute service time: 0,5 sec