Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.17
-
openSUSE Tumbleweed x86_64
Description
CREATE TABLE `t1` |
(
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, |
`title` VARCHAR(7) NOT NULL |
)
|
ENGINE=InnoDB;
|
INSERT INTO `t1` (`id`,`title`) VALUES (1,'Title 1'),(2,'Title 2'),(3,'Title 3'); |
|
CREATE TABLE `t2` |
(
|
`cid` INT UNSIGNED NOT NULL, |
`access` TINYINT UNSIGNED NOT NULL, |
PRIMARY KEY(`cid`,`access`) |
)
|
ENGINE=InnoDB;
|
INSERT INTO `t2` (`cid`,`access`) VALUES (1,1),(1,2),(1,3),(2,4),(2,5),(2,6),(3,7),(3,8),(3,9); |
|
SET sql_mode='ONLY_FULL_GROUP_BY'; |
SELECT `t1`.*,GROUP_CONCAT(`t2`.access SEPARATOR ',')`access` FROM `t1` JOIN `t2` ON `t1`.`id`=`t2`.`cid` WHERE `t1`.`id`=2 GROUP BY `t1`.`id`; |
t1.title' isn't in GROUP BY QMYSQL3: Unable to prepare statement
https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
SELECT name, address, MAX(age) FROM t GROUP BY name; |
The query is valid if name is a primary key of t or is a unique NOT NULL column. In such cases, MySQL recognizes that the selected column is functionally dependent on a grouping column. For example, if name is a primary key, its value determines the value of address because each group has only one value of the primary key and thus only one row. As a result, there is no randomness in the choice of address value in a group and no need to reject the query.
In my query, `t1`.`id` is the primary key, and all values after it in` t1`. * will be unique all!
MySQL allows such a request in ONLY_FULL_GROUP_BY mode, MariaDB does not.
In the table `t1` there may be more than a hundred fields and they may change. There is no point in registering each in GROUP BY, it should be enough to register only the primary key!
I consider this behavior a serious error!
Attachments
Issue Links
- relates to
-
MDEV-11588 Support for ONLY_FULL_GROUP_BY functional dependency
- Stalled