Bug #69310 Valid SQL statement rejected in ONLY_FULL_GROUP_BY

• Submitted: 23 May 2013 11:47
• Modified: 23 May 2013 12:25
• Reporter: Shlomi Noach (OCA)
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6, 5.0.97, 5.1.70, 5.5.32, 5.6.12, 5.7.2
• OS: Any
• CPU Architecture: Any
• Tags: GROUP BY, ONLY_FULL_GROUP_BY, SQL_MODE

[23 May 2013 11:47] Shlomi Noach

Description:
A valid GROUP BY query is rejected with:

ERROR 1055 (42000): '###########' isn't in GROUP BY

while in 'ONLY_FULL_GROUP_BY' sql_mode.

How to repeat:
set @@sql_mode :='ONLY_FULL_GROUP_BY';
drop table if exists test.t;
create table test.t (
  id int unsigned auto_increment primary key,
  ts timestamp
);

insert into test.t values (null,null);
insert into test.t values (null,null);
insert into test.t values (null,null);

 SELECT
    MIN(id) AS id,
    DATE(ts) + INTERVAL HOUR(ts) HOUR AS ts,
    DATE(ts) + INTERVAL (HOUR(ts) + 1) HOUR AS end_ts
  FROM
    test.t
  GROUP BY DATE(ts), HOUR(ts)
;

---
Results with:
ERROR 1055 (42000): 'test.t.ts' isn't in GROUP BY

[23 May 2013 12:25] Sveta Smirnova

Thank you for the report.

Verified as described.

[4 Dec 2013 16:47] Guilhem Bichot

Sveta: your Postgres query is really different, because it uses MIN() which is an aggregate function. I suggest testing:
SELECT
    MIN(id) AS id,
    round(ts) + 1
  FROM
    t
  GROUP BY round(ts), round(ts);

[5 Dec 2013 18:53] Sveta Smirnova

Guilhem,

it still works:

test=# create table t(id int, ts int);
CREATE TABLE
test=#  insert into t values(1,1);
INSERT 0 1
test=#  insert into t values(2,2);
INSERT 0 1
test=#  insert into t values(3,3);
INSERT 0 1
test=# SELECT
test-#     MIN(id) AS id,
test-#     round(ts) + 1
test-#   FROM
test-#     t
test-#   GROUP BY round(ts), round(ts);
 id | ?column? 
----+----------
  1 |        2
  2 |        3
  3 |        4
(3 rows)

[14 Mar 2018 20:45] Roy Lyseng

Posted by developer:
 
Notice that the specified query here is an extension above the SQL standard.

A more standard-compliant query can be obtained by using derived tables:

SELECT
    MIN(id) AS id,
    d + INTERVAL h HOUR AS ts,
    d + INTERVAL (h + 1) HOUR AS end_ts
  FROM
   (SELECT id, DATE(d) AS d, HOUR(ts) AS h
    FROM test.t) AS dt
  GROUP BY d, h
;