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:42]
Shlomi Noach
[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.