Description
The SQL grammar of Manticore is often unspecified.
Some SQL queries are rejected by Manticore while they would be fine with other engines.
This is expected, but the problem is that the only way to know if a query is possible is to try several variants and hope one of them will be accepted.
This lack of documentation is mentioned in other issues, e.g. #507 #904 #2788 #3208.
But these issues ask for fixes for undocumented restrictions.
I think a first step would be to document these restrictions.
Reproducible examples, mostly with GROUP BY:
create table junks(grpid int, a int, b int);
insert into junks values (1, 1, 1, 1), (2, 2, 4, 2), (3, 1, 2, 3);
-- OK
select grpid from junks group by grpid;
-- ERROR 1 (undocumented)
-- The condition after HAVING does not allow function calls or filters?
select grpid from junks group by grpid having max(a) > 2; -- KO: unexpected '('
select grpid from junks group by grpid having any(a) > 2; -- KO: attribute not related to GROUP BY
-- workaround (trick)
-- For a HAVING where the computation uses a single column, create a virtual column
select grpid, max(a) as x from junks group by grpid having x > 2; -- OK
-- ERROR 2 (undocumented)
-- The condition after HAVING does not allow operators?
select grpid, max(a) as x, max(b) as y from junks group by grpid having x + y > 2; -- KO
-- ERROR 3 (undocumented)
-- A column cannot use more than one aggregation functions?
select grpid, max(a) + max(b) as x from junks group by grpid having x > 2; -- KO
-- OK (trick for error 3 in some cases)
-- Using more columns, it's possible to work around the previous restriction
select grpid, max(a) as x, max(b) as y, x + y as z from junks group by grpid; -- OK
-- ERROR 4 (undocumented)
-- The syntax of column z is accepted, but Manticore fails with:
-- "can not use HAVING with attribute not related to GROUP BY"
select grpid, max(a) as x, max(b) as y, x + y as z from junks group by grpid having z > 2; -- KO
-- ERROR 5 (undocumented) ERROR 1064 (42000): P01: syntax error, unexpected IN near 'in (1,4)'
-- SELECT max(a,b) is OK. SELECT a in (1,4) is OK. But not both operators in the same column.
select max(a,b) in (1,4) from junks; -- KO
-- OK with a temp column
select max(a,b) as x, x in (1,4) from junks; -- OK
-- ERROR 6 (undocumented)
-- Parenthesis are often prohibited
select grpid, (max(a)) as x from junks group by grpid; -- KO
select grpid, max(a) as x from junks group by grpid; -- OK
From my experiments, unless I'm mistaken, HAVING can only use one column, and requires a trick if the condition on that column is more than a plain comparison. The actual documentation of HAVING is misleading.