How HAVING should be handled seems to be a moving target, as can be seen
with this MS post for MSQL
https://msdn.microsoft.com/en-us/library/ms180199.aspx
"When GROUP BY is not used, HAVING behaves like a WHERE clause"
I know Oracle and PostgreSQL will accept certain HAVING expressions without
a "GROUP BY", but as you have shown for PostgreSQL not all the time.
The issue is based around the idea that if you have no "GROUP BY" within
you statement "GROUP BY ()" is implied or at least this is how I have seen
the question answered in the past. A good thread can be found here
http://glennpaulley.ca/conestoga/2015/05/why-the-sql-standard-does-not-need-another-way-to-do-group-by/
As often seems the case, the thread comes about due to one of Lukas Eder's
blog entries, and he then replies with a lot of background detail. For this
he details the exact SQL standards text.
Roger
On Wednesday, October 21, 2015 at 5:52:13 PM UTC+2, Thomas Mueller wrote:
>
> Hi,
>
> Your example works with H2 (unfortunately), but this one fails (it throws
> an exception):
>
> drop table table_test;
> CREATE TABLE TABLE_TEST(account_id BIGINT, time BIGINT);
> INSERT INTO TABLE_TEST (account_id,time) VALUES (10,2);
> INSERT INTO TABLE_TEST (account_id,time) VALUES (10,3);
> SELECT account_id, time FROM TABLE_TEST HAVING account_id=10;
>
> It also fails with PostgreSQL, and SQLite, and Apache Derby, and HSQLDB.
> So: nope, this will not be supported in H2.
>
> Regards,
> Thomas
>
>
> On Wednesday, October 21, 2015, Taras Fedkiv <[email protected]
> <javascript:>> wrote:
>
>> Hi,
>>
>> H2(v1.4.190) throws error if HAVING is used without GROUP BY.
>> Ex:
>> CREATE MEMORY TABLE IF NOT EXISTS TABLE_TEST(account_id BIGINT, time
>> BIGINT) NOT PERSISTENT
>> INSERT INTO TABLE_TEST (account_id,time) VALUES (10,2)
>> SELECT account_id, time FROM TABLE_TEST HAVING account_id=10 //throws
>> org.h2.jdbc.JdbcSQLException: Column """account_id""" must be in the GROUP
>> BY list;
>>
>> Please add support of HAVING without GROUP BY. MySQL, PostgreSQL support
>> this feature.
>>
>> Thank you,
>> Taras
>>
>>
>> The information in this message may be confidential. It is intended
>> solely for
>> the addressee(s). If you are not the intended recipient, any disclosure,
>> copying or distribution of the message, or any action or omission taken
>> by you
>> in reliance on it, is prohibited and may be unlawful. Please immediately
>> contact the sender if you have received this message in error.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at http://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
