Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20409

With ONLY_FULL_GROUP_BY work don't correct.

    XMLWordPrintable

Details

    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

          Activity

            People

              Unassigned Unassigned
              ilya Илья Индиго
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.