Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Partition by failure on postgres backend #1757

Closed
FedericoV opened this issue Apr 12, 2019 · 4 comments

Comments

Projects
None yet
2 participants
@FedericoV
Copy link

commented Apr 12, 2019

I am working with Ibis version: '1.0.0+5.g80db366'

Trying to execute the following code:

seller_dimensions_table = monster.seller_dimension
last_snapshot_gb = seller_dimensions_table.groupby(['merchant_customer_id', 'snapshot_date'])
sorted_snapshot = last_snapshot_gb.order_by(seller_dimensions_table.snapshot_date).mutate(rank=ibis.row_number())
sorted_snapshot.execute(limit=10)

Gives this long traceback:

-----------------------------------------------------------------------
ProgrammingError                      Traceback (most recent call last)
~/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1181                         parameters,
-> 1182                         context)
   1183         except BaseException as e:

~/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    469     def do_execute(self, cursor, statement, parameters, context=None):
--> 470         cursor.execute(statement, parameters)
    471 

ProgrammingError: Frame clause should not be specified for ranking window functions

The generated SQL looks like this:

SELECT ..., row_number() OVER (PARTITION BY t0.merchant_customer_id, t0.snapshot_date ORDER BY t0.snapshot_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - %(param_1)s AS rank 
FROM monster.seller_dimension AS t0

@cpcloud cpcloud self-assigned this Apr 16, 2019

@cpcloud cpcloud added this to the Next Feature Release milestone Apr 16, 2019

@cpcloud

This comment has been minimized.

Copy link
Member

commented Apr 16, 2019

@FedericoV Thanks for the report. I'm going to release 1.1.0 in the next couple of weeks which will contain a fix for this issue.

@cpcloud

This comment has been minimized.

Copy link
Member

commented Jun 7, 2019

@FedericoV What version of postgres are you using?

@FedericoV

This comment has been minimized.

Copy link
Author

commented Jun 7, 2019

@cpcloud

This comment has been minimized.

Copy link
Member

commented Jun 7, 2019

Interesting, using postgres proper (not redshift) doesn't raise an error when specifying a frame clause for ranking functions. We should be able to remove them with no effect except to allow redshift to work.

@cpcloud cpcloud closed this in #1817 Jun 9, 2019

cpcloud added a commit that referenced this issue Jun 9, 2019

BUG: Do not give a frame clause where not necessary in window functions
Closes #1757
Author: Phillip Cloud <cpcloud@gmail.com>

Closes #1817 from cpcloud/remove-frame-clause-from-alchemy and squashes the following commits:

1fded47 [Phillip Cloud] Remove custom Over implementation and require sqlalchemy>=1.1
5e642bc [Phillip Cloud] Proper links to pypi and codecov
28767bb [Phillip Cloud] BUG: Do not give a frame clause where not necessary in window functions
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.