| 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: | |
| 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: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.
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.