[MDEV-24409] NTILE example throws: ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause Created: 2020-12-15  Updated: 2020-12-15  Resolved: 2020-12-15

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.5.8
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Vassilis Virvilis Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Debian SID (unstable)

Issue Links:
Duplicate
duplicates MDEV-17785 Window functions not working in ONLY_... Closed

 Description   

Hi following the example of NTILE in https://mariadb.com/kb/en/ntile/

create table t1 (
 
    pk int primary key,
 
    a int,
 
    b int
 
  );
 
 
 
insert into t1 values
 
    (11 , 0, 10),
 
    (12 , 0, 10),
 
    (13 , 1, 10),
 
    (14 , 1, 10),
 
    (18 , 2, 10),
 
    (15 , 2, 20),
 
    (16 , 2, 20),
 
    (17 , 2, 20),
 
    (19 , 4, 20),
 
    (20 , 4, 20);
 
 
 
select pk, a, b,
 
    ntile(1) over (order by pk)
 
  from t1;

I get

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

This is clearly a bug in the documentation or in the mariadb server.

 Comments   
Comment by Vassilis Virvilis [ 2020-12-15 ]

ok sorry for this. I searched a lot before creating this but I failed to find it in time.

Looks like a duplicate of MDEV-17785

I turned off ONLY_FULL_GROUP_BY with 

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

and the example works.

Sorry for the trouble.

Generated at Thu Feb 08 09:29:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.