MySQL Bugs: #78424: w/ c1&&c2 appears in select list, group by clause and order by clause, SQL fails

Bug #78424	w/ c1&&c2 appears in select list, group by clause and order by clause, SQL fails
Submitted:	14 Sep 2015 2:29	Modified:	14 Sep 2015 10:50
Reporter:	Su Dylan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:

Output:
=======
mysql> drop table if exists t1; create table t1(c1 int, c2 int);
Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.09 sec)

mysql> select c1+c2 from t1 group by c1+c2 order by c1+c2;
Empty set (0.01 sec)

mysql> select c1&&c2 from t1 group by c1&&c2 order by c1&&c2;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t1.c1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.7.7-rc-log |
+--------------+
1 row in set (0.06 sec) 

Problem:
========
When c1&&c2 appears in select list, group by clause and order by clause, the SQL fails.

How to repeat:
drop table if exists t1; create table t1(c1 int, c2 int);
select c1+c2 from t1 group by c1+c2 order by c1+c2;
select c1&&c2 from t1 group by c1&&c2 order by c1&&c2;

Suggested fix:
The following sql succeeds on mysql 5.7.7:
select c1&&c2 from t1 group by c1&&c2 order by c1&&c2;

Thank you for the bug report. Please use 5.7.8 released version:

c:\mysql-5.7.8-rc-winx64>bin\mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> drop table if exists t1; create table t1(c1 int, c2 int);
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.31 sec)

mysql> select c1+c2 from t1 group by c1+c2 order by c1+c2;
Empty set (0.01 sec)

mysql> select c1&&c2 from t1 group by c1&&c2 order by c1&&c2;
Empty set (0.06 sec)

************************************************************************************
c:\mysql-5.7.7-rc-winx64>bin\mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.7-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE test
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> USE test
Database changed
mysql> drop table if exists t1; create table t1(c1 int, c2 int);
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.22 sec)

mysql> select c1+c2 from t1 group by c1+c2 order by c1+c2;
Empty set (0.02 sec)

mysql> select c1&&c2 from t1 group by c1&&c2 order by c1&&c2;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t1.c1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql>