Bug #49846 Valid dependent views result with ERROR 1055 (42000): 'xxx' isn't in GROUP BY
Submitted: 21 Dec 2009 12:42 Modified: 7 Feb 2018 22:16
Reporter: Shlomi Noach (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.90, 5.1.34-log, 5.1.41 OS:Any
Assigned to: CPU Architecture:Any
Tags: GROUP BY, Views

[21 Dec 2009 12:42] Shlomi Noach
Description:
A chain of views is defined one on top of the other.
Each is legal and valid.
Once GROUP BY is introduced, and although TEMPTABLE algorithm is specified where appropriate, MySQL server fails with underlying MERGE operations and yields with so called invalid GROUP BY statement.

How to repeat:
USE test;

DROP TABLE IF EXISTS t;
CREATE TABLE t (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ts TIMESTAMP,
  data INT
);

INSERT INTO t (id, ts, data) VALUES
  (1, '2009-12-10 10:30:00', 50),
  (2, '2009-12-10 10:45:00', 77),
  (3, '2009-12-10 11:10:00', 99)
;

CREATE OR REPLACE
  ALGORITHM=MERGE
  VIEW tv AS 
    SELECT t2.id, t2.ts, TIMESTAMPDIFF(SECOND, t1.ts, t2.ts) AS ts_diff, t2.data-t1.data AS data_diff
    FROM t t2 INNER JOIN t t1 ON (t1.id = t2.id-1)
;

CREATE OR REPLACE 
  ALGORITHM=MERGE
  VIEW tv2 AS 
    SELECT id, ts, data_diff, data_diff/ts_diff AS data_diff_psec
    FROM tv
;

CREATE OR REPLACE 
  ALGORITHM=TEMPTABLE
  VIEW tv3 AS 
    SELECT MIN(id) AS id, DATE(ts), SUM(data_diff) AS data_diff
    FROM tv2
    GROUP BY DATE(ts)
;

SELECT * FROM tv3;

====================>>>>
ERROR 1055 (42000): 'test.t2.ts' isn't in GROUP BY

Suggested fix:
Interestingly, when changing tv2's ALGORITHM to TEMPTABLE, it works well.
But tv2 is valid as it is.
[21 Dec 2009 12:44] Shlomi Noach
Naturally, sql_mode has to be:
SET sql_mode='ONLY_FULL_GROUP_BY';

Forgot to mention above.
[21 Dec 2009 13:02] Valeriy Kravchuk
Please, check with a newer version, 5.1.41. It works for me:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.1.41-community MySQL Community Server (GPL)

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

mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected, 1 warning (0.23 sec)

mysql> CREATE TABLE t (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   ts TIMESTAMP,
    ->   data INT
    -> );
Query OK, 0 rows affected (0.72 sec)

mysql>
mysql> INSERT INTO t (id, ts, data) VALUES
    ->   (1, '2009-12-10 10:30:00', 50),
    ->   (2, '2009-12-10 10:45:00', 77),
    ->   (3, '2009-12-10 11:10:00', 99)
    -> ;
Query OK, 3 rows affected (0.19 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE OR REPLACE
    ->   ALGORITHM=MERGE
    ->   VIEW tv AS
    ->     SELECT t2.id, t2.ts, TIMESTAMPDIFF(SECOND, t1.ts, t2.ts) AS ts_diff,
t2.data-t1.data
    -> AS data_diff
    ->     FROM t t2 INNER JOIN t t1 ON (t1.id = t2.id-1)
    -> ;
Query OK, 0 rows affected (0.14 sec)

mysql>
mysql> CREATE OR REPLACE
    ->   ALGORITHM=MERGE
    ->   VIEW tv2 AS
    ->     SELECT id, ts, data_diff, data_diff/ts_diff AS data_diff_psec
    ->     FROM tv
    -> ;
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> CREATE OR REPLACE
    ->   ALGORITHM=TEMPTABLE
    ->   VIEW tv3 AS
    ->     SELECT MIN(id) AS id, DATE(ts), SUM(data_diff) AS data_diff
    ->     FROM tv2
    ->     GROUP BY DATE(ts)
    -> ;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> SELECT * FROM tv3;
+------+------------+-----------+
| id   | DATE(ts)   | data_diff |
+------+------------+-----------+
|    2 | 2009-12-10 |        49 |
+------+------------+-----------+
1 row in set (0.03 sec)
[21 Dec 2009 13:10] MySQL Verification Team
mysql 5.1 >SELECT * FROM tv3;
ERROR 1055 (42000): 'test.t2.ts' isn't in GROUP BY

mysql 5.1 >CREATE OR REPLACE
    ->   ALGORITHM=TEMPTABLE
    ->   VIEW tv AS
    ->     SELECT t2.id, t2.ts, TIMESTAMPDIFF(SECOND, t1.ts, t2.ts) AS ts_diff, t2.data-t1.data
    -> AS data_diff
    ->     FROM t t2 INNER JOIN t t1 ON (t1.id = t2.id-1)
    -> ;
Query OK, 0 rows affected (0.04 sec)

mysql 5.1 >
mysql 5.1 >CREATE OR REPLACE
    ->   ALGORITHM=TEMPTABLE
    ->   VIEW tv2 AS
    ->     SELECT id, ts, data_diff, data_diff/ts_diff AS data_diff_psec
    ->     FROM tv
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql 5.1 >SELECT * FROM tv3;
+------+------------+-----------+
| id   | DATE(ts)   | data_diff |
+------+------------+-----------+
|    2 | 2009-12-10 |        49 |
+------+------------+-----------+
1 row in set (0.00 sec)

mysql 5.1 >show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 1.0.6               |
| protocol_version        | 10                  |
| version                 | 5.1.43-Win X64-log  |
| version_comment         | Source distribution |
| version_compile_machine | unknown             |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
6 rows in set (0.00 sec)

mysql 5.1 >
[21 Dec 2009 13:12] MySQL Verification Team
C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.90-Win X64-log Source distribution

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

mysql 5.0 > SET sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.02 sec)

mysql 5.0 > USE test;
Database changed
mysql 5.0 >
mysql 5.0 > DROP TABLE IF EXISTS t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.0 > CREATE TABLE t (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   ts TIMESTAMP,
    ->   data INT
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql 5.0 >
mysql 5.0 > INSERT INTO t (id, ts, data) VALUES
    ->   (1, '2009-12-10 10:30:00', 50),
    ->   (2, '2009-12-10 10:45:00', 77),
    ->   (3, '2009-12-10 11:10:00', 99)
    -> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.0 >
mysql 5.0 > CREATE OR REPLACE
    ->   ALGORITHM=MERGE
    ->   VIEW tv AS
    ->     SELECT t2.id, t2.ts, TIMESTAMPDIFF(SECOND, t1.ts, t2.ts) AS ts_diff, t2.data-t1.data
    -> AS data_diff
    ->     FROM t t2 INNER JOIN t t1 ON (t1.id = t2.id-1)
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql 5.0 >
mysql 5.0 > CREATE OR REPLACE
    ->   ALGORITHM=MERGE
    ->   VIEW tv2 AS
    ->     SELECT id, ts, data_diff, data_diff/ts_diff AS data_diff_psec
    ->     FROM tv
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql 5.0 >
mysql 5.0 > CREATE OR REPLACE
    ->   ALGORITHM=TEMPTABLE
    ->   VIEW tv3 AS
    ->     SELECT MIN(id) AS id, DATE(ts), SUM(data_diff) AS data_diff
    ->     FROM tv2
    ->     GROUP BY DATE(ts)
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql 5.0 >
mysql 5.0 > SELECT * FROM tv3;
ERROR 1055 (42000): 'test.t2.ts' isn't in GROUP BY
mysql 5.0 >
[21 Dec 2009 13:14] MySQL Verification Team
Thank you for the bug report.
[21 Dec 2009 13:28] Valeriy Kravchuk
Then it is a recent regression in 5.0.90, as with 5.0.86 I've got:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.86-community-nt-log MySQL Community Edition (GPL)

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

mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.58 sec)

mysql> CREATE TABLE t (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   ts TIMESTAMP,
    ->   data INT
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO t (id, ts, data) VALUES
    ->   (1, '2009-12-10 10:30:00', 50),
    ->   (2, '2009-12-10 10:45:00', 77),
    ->   (3, '2009-12-10 11:10:00', 99)
    -> ;
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE OR REPLACE
    ->   ALGORITHM=MERGE
    ->   VIEW tv AS
    ->     SELECT t2.id, t2.ts, TIMESTAMPDIFF(SECOND, t1.ts, t2.ts) AS ts_diff,
t2.data-t1.data
    -> AS data_diff
    ->     FROM t t2 INNER JOIN t t1 ON (t1.id = t2.id-1)
    -> ;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE OR REPLACE
    ->   ALGORITHM=MERGE
    ->   VIEW tv2 AS
    ->     SELECT id, ts, data_diff, data_diff/ts_diff AS data_diff_psec
    ->     FROM tv
    -> ;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE OR REPLACE
    ->   ALGORITHM=TEMPTABLE
    ->   VIEW tv3 AS
    ->     SELECT MIN(id) AS id, DATE(ts), SUM(data_diff) AS data_diff
    ->     FROM tv2
    ->     GROUP BY DATE(ts)
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM tv3;
+------+------------+-----------+
| id   | DATE(ts)   | data_diff |
+------+------------+-----------+
|    2 | 2009-12-10 |        49 |
+------+------------+-----------+
1 row in set (0.08 sec)
[21 Dec 2009 13:37] Shlomi Noach
If this is indeed a regression, it may be related to http://bugs.mysql.com/bug.php?id=33133
[21 Dec 2009 13:39] Shlomi Noach
Valeriy, did you perform
SET sql_mode='ONLY_FULL_GROUP_BY';
?

I didn't see it in your output.
[21 Dec 2009 13:59] Valeriy Kravchuk
I thought I have ti set by default. I was wrong:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.1.41-community MySQL Community Server (GPL)

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

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> set sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.22 sec)

mysql> CREATE TABLE t (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   ts TIMESTAMP,
    ->   data INT
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> INSERT INTO t (id, ts, data) VALUES
    ->   (1, '2009-12-10 10:30:00', 50),
    ->   (2, '2009-12-10 10:45:00', 77),
    ->   (3, '2009-12-10 11:10:00', 99)
    -> ;
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE OR REPLACE
    ->   ALGORITHM=MERGE
    ->   VIEW tv AS
    ->     SELECT t2.id, t2.ts, TIMESTAMPDIFF(SECOND, t1.ts, t2.ts) AS ts_diff,
t2.data-t1.data
    -> AS data_diff
    ->     FROM t t2 INNER JOIN t t1 ON (t1.id = t2.id-1)
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> CREATE OR REPLACE
    ->   ALGORITHM=MERGE
    ->   VIEW tv2 AS
    ->     SELECT id, ts, data_diff, data_diff/ts_diff AS data_diff_psec
    ->     FROM tv
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> CREATE OR REPLACE
    ->   ALGORITHM=TEMPTABLE
    ->   VIEW tv3 AS
    ->     SELECT MIN(id) AS id, DATE(ts), SUM(data_diff) AS data_diff
    ->     FROM tv2
    ->     GROUP BY DATE(ts)
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> SELECT * FROM tv3;
ERROR 1055 (42000): 'test.t2.ts' isn't in GROUP BY

So, this is repeatable with recent 5.1 also and is NOT a (recent) regression. Sorry for misleading comments.
[6 Feb 2018 19:32] Sveta Smirnova
Seems to be fixed in 5.7:

mysql> select @@sql_mode;
+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tv3;
+------+------------+-----------+
| id   | DATE(ts)   | data_diff |
+------+------------+-----------+
|    2 | 2009-12-10 |        49 |
+------+------------+-----------+
1 row in set (0.01 sec)
[7 Feb 2018 22:16] Roy Lyseng
Posted by developer:
 
Fixed in 5.7 and up.