Group by HAVING gets an exception

[supabad72]

Hello -

I've been using pg-mem for a while and just now run into something confusing. I'm trying to do simple GROUP BY/HAVING query, which works fine in my real DB, but is failing in pg-mem:

const rows = db.public.many( select o.order_id, count(*) from orders o join order_run r on o.order_id= r.order_id group by o.order_id having count(*) > 1)`

It gets this error:

2 select o.order_id, count(*) from orders o join order_run r on o.order_id= r.order_id 3 group by o.order_id 4 having count(*) > 1; ^ Unexpected kw_having token: "having". Instead, I was expecting to see one of the following:....

I am using 2.6.4 but rolled back to the version I was previously using, 2.3.5, and I got the same result. The query works if I remove the HAVING but I need it for what I'm trying to do. Is there something particular about how pg-mem handles this type of query that I'm missing?

Also, I got a similar error when trying to use a LIMIT clause - is that supported?

Thanks!

[abenhamdine]

FWIW the parser now supports having since version 11.0.1 : see the related commit https://github.com/oguimbal/pgsql-ast-parser/commit/f11a428366a8ab1378f5952edf9cbacc9936f976
but current version of pg-mem use parser version ^10.5.2

[bneigher]

oooh in that case my ticket is also solved by updating to 11.0.1 https://github.com/oguimbal/pg-mem/issues/294

@oguimbal any way we can get a new release with an update to pgsql-ast-parser?

[abenhamdine]

@bneigher perhaps you could send a PR adding a test for HAVING and updating the parser version ? It would be helpful.

[bneigher]

@abenhamdine gave it the ole college try: https://github.com/oguimbal/pg-mem/pull/298

I was getting a large number of failed tests (even without installing the updated npm packages...) node: 16.17.1

furthermore, the errors with the known keys still shows up when running the commends in the new tests. is the anything extra that must be done to get the libaray to pick up on the new keywords HAVING and OVERLAPS cc @supabad72 ?

[bneigher]

@abenhamdine do you have any recommendations for how to take this to the finish line?

[bneigher]

@abenhamdine hey... just giving this one last bump before I throw in the towel on it

[oguimbal]

Hi, sorry, I'm drowning in work. Not following the issues much these days.

The parser has been updated recently (a couple of days ago).

If that doesnt fix it, do you have a full reproduction which could be usable to build a unit test ? (i.e. a sql script which reproduces the issue, with associated create table statements)

[bneigher]

the update didn't work. I have done so, https://github.com/oguimbal/pg-mem/pull/298, in this pull request

[gayapedro]

hey guys, any news on that?

[bneigher]

I spent the time to create a branch and a MR but was really hoping to get some help from maintainer