You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
MonetDB version:
MonetDB 5 server v11.21.5 "Jul2015"
Serving database 'demo', using 4 threads
Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked
Found 15.916 GiB available main-memory.
Copyright (c) 1993-July 2008 CWI.
Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Listening for connection requests on mapi:monetdb://127.0.0.1:50000/
MonetDB/GIS module loaded
Start processing logs sql/sql_logs version 52200
Start reading the write-ahead log 'sql_logs\sql\log.12'
Finished reading the write-ahead log 'sql_logs\sql\log.12'
Finished processing logs sql/sql_logs
MonetDB/SQL module loaded
This is not a bug, but a missing feature. A.f.a.I.k., MonetDB doesn't support expressions in GROUP BY yet. It only support references to columns. Changed this report into "enhancement".
What a pity, so I have to pervert queries. Hopefully this feature will be added in the next release.
Comment 21462
Date: 2015-11-05 12:53:49 +0100
From: Martin van Dinther <<martin.van.dinther>>
There are some workarounds for this:
-- use a subquery in the FROM-clause:
SELECT "State3", SUM("Sales") AS "Sales"
FROM (SELECT LEFT("State", 3) AS "State3", "Sales" FROM test) AS test
GROUP BY "State3";
-- use a view to extend the table with the expression colum(s) once
CREATE VIEW test_vw AS
SELECT LEFT("State", 3) AS "State3", "State", "Sales" FROM test;
-- and next query the view instead of the table
SELECT "State3", SUM("Sales") AS "Sales"
FROM test_vw
GROUP BY "State3";
Hope this helps.
Comment 22035
Date: 2016-04-14 12:11:36 +0200
From: Martin van Dinther <<martin.van.dinther>>
You can also use the column alias in the GROUP BY (and also in HAVING).
So a simpler workaround is:
SELECT LEFT("State", 3) AS "StateFirst3chars", SUM("Sales") AS "Sales"
FROM test
GROUP BY "StateFirst3chars";
Date: 2015-10-22 09:13:12 +0200
From: Sherzod Mutalov <>
To: SQL devs <>
Version: 11.21.5 (Jul2015)
CC: g.ozolins, jspeis, martin.van.dinther, @PedroTadim, @yzchang
Last updated: 2019-04-30 12:36:03 +0200
Comment 21378
Date: 2015-10-22 09:13:12 +0200
From: Sherzod Mutalov <>
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:39.0) Gecko/20100101 Firefox/39.0
Build Identifier:
Cannot use SQL expressions with GROUP BY clause, which causes error. Similar query works on SQL Server, PostgreSQL and MySQL
Reproducible: Always
Steps to Reproduce:
Execute next statements:
SELECT LEFT("State", 3) AS "State",
SUM("Sales") AS "Sales"
FROM test
GROUP BY LEFT("State", 3);
Actual Results:
syntax error, unexpected LEFT in: "select left("State", 3) as "State",
sum("Sales") as "Sales"
from test
group by l"
Expected Results:
+-------+--------------------------+
| State | Sales |
+=======+==========================+
| Tex | 450 |
+-------+--------------------------+
MonetDB version:
MonetDB 5 server v11.21.5 "Jul2015"
Serving database 'demo', using 4 threads
Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked
Found 15.916 GiB available main-memory.
Copyright (c) 1993-July 2008 CWI.
Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Listening for connection requests on mapi:monetdb://127.0.0.1:50000/
MonetDB/GIS module loaded
Start processing logs sql/sql_logs version 52200
Start reading the write-ahead log 'sql_logs\sql\log.12'
Finished reading the write-ahead log 'sql_logs\sql\log.12'
Finished processing logs sql/sql_logs
MonetDB/SQL module loaded
Comment 21379
Date: 2015-10-22 09:16:20 +0200
From: Sherzod Mutalov <>
Read steps to reproduce as below:
Execute next statements:
SELECT LEFT("State", 3) AS "State",
SUM("Sales") AS "Sales"
FROM test
GROUP BY LEFT("State", 3);
Comment 21380
Date: 2015-10-22 11:56:42 +0200
From: @yzchang
This is not a bug, but a missing feature. A.f.a.I.k., MonetDB doesn't support expressions in GROUP BY yet. It only support references to columns. Changed this report into "enhancement".
Comment 21381
Date: 2015-10-22 13:03:42 +0200
From: Sherzod Mutalov <>
What a pity, so I have to pervert queries. Hopefully this feature will be added in the next release.
Comment 21462
Date: 2015-11-05 12:53:49 +0100
From: Martin van Dinther <<martin.van.dinther>>
There are some workarounds for this:
-- use a subquery in the FROM-clause:
SELECT "State3", SUM("Sales") AS "Sales"
FROM (SELECT LEFT("State", 3) AS "State3", "Sales" FROM test) AS test
GROUP BY "State3";
-- use a view to extend the table with the expression colum(s) once
CREATE VIEW test_vw AS
SELECT LEFT("State", 3) AS "State3", "State", "Sales" FROM test;
-- and next query the view instead of the table
SELECT "State3", SUM("Sales") AS "Sales"
FROM test_vw
GROUP BY "State3";
Hope this helps.
Comment 22035
Date: 2016-04-14 12:11:36 +0200
From: Martin van Dinther <<martin.van.dinther>>
You can also use the column alias in the GROUP BY (and also in HAVING).
So a simpler workaround is:
SELECT LEFT("State", 3) AS "StateFirst3chars", SUM("Sales") AS "Sales"
FROM test
GROUP BY "StateFirst3chars";
Comment 22191
Date: 2016-06-02 15:28:03 +0200
From: Gatis Ozolins <<g.ozolins>>
This is also issue for us, where BI tools are generating SQL
Comment 25081
Date: 2017-03-02 16:15:26 +0100
From: MonetDB Mercurial Repository <>
Changeset 3d3a3776b749 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=3d3a3776b749
Changeset description:
Comment 26404
Date: 2018-04-23 22:03:11 +0200
From: jspeis
would be great to see this feature added!
Comment 26741
Date: 2018-12-20 16:35:53 +0100
From: @PedroTadim
This feature will be available in the next feature release.
The text was updated successfully, but these errors were encountered: