By einer Default-Installation von MySQL kann es zu folgender Fehlermeldung bei bestimmten GROUP BY geben:
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #8 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dbname.short.long_column_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
In der my.cnf wird folgendes stehen:
sql_mode = 'ONLY_FULL_GROUP_BY'
ONLY_FULL_GROUP_BY muss raus.
Guckst du auch stackoverflow
und hier dev.mysql.com
Oder
Gegeben sind folgende Tabellen:
CREATE TABLE `shop_article_option_article_relation` (
`shop_article_option_article_relation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`shop_article_id` int(10) unsigned NOT NULL,
`shop_article_option_def_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`shop_article_option_article_relation_id`),
KEY `shop_article_option_article_relation_shop_article_FK` (`shop_article_id`),
KEY `shop_article_option_article_relation_shop_article_option_def_FK` (`shop_article_option_def_id`),
CONSTRAINT `shop_article_option_article_relation_shop_article_FK` FOREIGN KEY (`shop_article_id`) REFERENCES `shop_article` (`shop_article_id`),
CONSTRAINT `shop_article_option_article_relation_shop_article_option_def_FK` FOREIGN KEY (`shop_article_option_def_id`) REFERENCES `shop_article_option_def` (`shop_article_option_def_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
CREATE TABLE `shop_article_option_def` (
`shop_article_option_def_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`shop_article_option_def_type` enum('input','select','sizedef','multiselect','checkbox','radio','file') NOT NULL DEFAULT 'select',
`shop_article_option_def_name` varchar(200) NOT NULL,
`shop_article_option_def_desc` text,
`shop_article_option_def_view_name` varchar(200) NOT NULL,
`shop_article_option_def_view_desc` text,
`shop_article_option_def_priority` int(11) NOT NULL DEFAULT '0' COMMENT 'um die Optionen im Frontend zu sortieren. Z.B. erst die Größe, dann erscheint FarbeSelect mit Bildern hinter jedem OptionItem',
PRIMARY KEY (`shop_article_option_def_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
CREATE TABLE `shop_article_option_item` (
`shop_article_option_item_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`shop_article_option_def_id` int(10) unsigned NOT NULL,
`shop_article_option_item_name` varchar(200) NOT NULL COMMENT 'bei shop_article_option_def_type = sizedef, ist name = shop_article_size_def_key',
`shop_article_option_item_value` varchar(200) NOT NULL COMMENT 'bei shop_article_option_def_type = sizedef, ist value = shop_article_size_def_id',
`shop_article_option_item_priority` int(11) NOT NULL DEFAULT '0',
`shop_article_option_item_view_name` varchar(200) NOT NULL,
`shop_article_option_item_view_value` varchar(200) NOT NULL,
PRIMARY KEY (`shop_article_option_item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
CREATE TABLE `shop_article_option_item_article_image_relation` (
`shop_article_option_item_article_image_relation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`shop_article_option_item_id` int(10) unsigned NOT NULL,
`shop_article_id` int(10) unsigned NOT NULL,
`bk_images_image_id` int(10) unsigned NOT NULL,
`shop_article_option_item_article_image_relation_priority` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`shop_article_option_item_article_image_relation_id`),
KEY `shop_article_option_item_article_image_relation__option_item_FK` (`shop_article_option_item_id`),
KEY `shop_article_option_item_article_image_relation_shop_article_FK` (`shop_article_id`),
KEY `shop_article_option_item_article_image_relation__images_image_FK` (`bk_images_image_id`),
CONSTRAINT `shop_article_option_item_article_image_relation__images_image_FK` FOREIGN KEY (`bk_images_image_id`) REFERENCES `bk_images_image` (`bk_images_image_id`),
CONSTRAINT `shop_article_option_item_article_image_relation__option_item_FK` FOREIGN KEY (`shop_article_option_item_id`) REFERENCES `shop_article_option_item` (`shop_article_option_item_id`),
CONSTRAINT `shop_article_option_item_article_image_relation_shop_article_FK` FOREIGN KEY (`shop_article_id`) REFERENCES `shop_article` (`shop_article_id`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8;
CREATE TABLE `shop_article_option_item_article_pricediff` (
`shop_article_option_item_article_pricediff_id` int(11) NOT NULL AUTO_INCREMENT,
`shop_article_option_item_id` int(10) unsigned NOT NULL,
`shop_article_id` int(10) unsigned NOT NULL,
`shop_article_option_item_article_pricediff_value` float NOT NULL DEFAULT '0',
PRIMARY KEY (`shop_article_option_item_article_pricediff_id`),
KEY `fk_shop_article_option_item_article_pricediff_optionitem_idx` (`shop_article_option_item_id`),
KEY `fk_shop_article_option_item_article_pricediff_article_idx` (`shop_article_id`),
CONSTRAINT `fk_shop_article_option_item_article_pricediff_article` FOREIGN KEY (`shop_article_id`) REFERENCES `shop_article` (`shop_article_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_shop_article_option_item_article_pricediff_optionitem` FOREIGN KEY (`shop_article_option_item_id`) REFERENCES `shop_article_option_item` (`shop_article_option_item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
Folgende SQL Query verursacht aus zwei Gründen diesen GROUP BY Error.
SELECT
DISTINCT saoi.*,
saoar.*,
saod.*,
saoiap.shop_article_option_item_article_pricediff_value AS pricediff,
COUNT( DISTINCT saoiai.shop_article_option_item_article_image_relation_id ) as item_image_count
FROM
shop_article_option_item saoi
LEFT JOIN shop_article_option_article_relation saoar
USING(shop_article_option_def_id)
LEFT JOIN shop_article_option_def saod -- to display in frontend
USING(shop_article_option_def_id)
LEFT JOIN shop_article_option_item_article_pricediff saoiap
ON saoiap.shop_article_option_item_id = saoi.shop_article_option_item_id
AND saoiap.shop_article_id = saoar.shop_article_id
LEFT JOIN shop_article_option_item_article_image_relation saoiai ON
saoiai.shop_article_id = ?
AND
saoiai.shop_article_option_item_id = saoi.shop_article_option_item_id
WHERE
saoar.shop_article_id = ?
GROUP BY
saoi.shop_article_option_item_id
ORDER by
saod.shop_article_option_def_priority DESC';
Ergibt
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #8 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'systemgurt.saoar.shop_article_option_article_relation_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Um diesen Fehler zu beseitigen fügt man ein zusätzliches GROUP BY (mit Kommas getrennt) in die Abfrage.
Dann meckert es wieder rum:
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #18 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'systemgurt.saoiap.shop_article_option_item_article_pricediff_value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
…und wieder beseitigt ein zusätzliches GROUP BY diesen Fehler.
Damit sieht die Abfrage so aus:
SELECT
DISTINCT saoi.*,
saoar.*,
saod.*,
saoiap.shop_article_option_item_article_pricediff_value AS pricediff,
COUNT( DISTINCT saoiai.shop_article_option_item_article_image_relation_id ) as item_image_count
FROM
shop_article_option_item saoi
LEFT JOIN shop_article_option_article_relation saoar
USING(shop_article_option_def_id)
LEFT JOIN shop_article_option_def saod -- to display in frontend
USING(shop_article_option_def_id)
LEFT JOIN shop_article_option_item_article_pricediff saoiap
ON saoiap.shop_article_option_item_id = saoi.shop_article_option_item_id
AND saoiap.shop_article_id = saoar.shop_article_id
LEFT JOIN shop_article_option_item_article_image_relation saoiai ON
saoiai.shop_article_id = ?
AND
saoiai.shop_article_option_item_id = saoi.shop_article_option_item_id
WHERE
saoar.shop_article_id = ?
GROUP BY
saoi.shop_article_option_item_id,
saoar.shop_article_option_article_relation_id,
saoiap.shop_article_option_item_article_pricediff_id
ORDER by
saod.shop_article_option_def_priority DESC';