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

fix: implement modulus operator #1048

Merged
merged 6 commits into from
Jul 10, 2024
Merged

fix: implement modulus operator #1048

merged 6 commits into from
Jul 10, 2024

Conversation

aholyoke
Copy link
Contributor

Thank you for opening a Pull Request! Before submitting your PR, there are a few things you can do to make sure it goes smoothly:

  • Make sure to open an issue as a bug/issue before writing your code! That way we can discuss the change, evaluate designs, and agree on the general idea
  • Ensure the tests and linter pass
  • Code coverage does not decrease (if any source code was changed)
  • Appropriate docs were updated (if necessary)

Fixes #<issue_number_goes_here> 🦕


Adds implementation of mod operator %

Currently if I try to use % in python code, it will fall back to the SA default compiler implementation which renders it as a percent sign, which is not syntactically valid in BQ. I could workaround this by using func.mod, but this isn't portable across dialects (eg. SQL Server has support for %, but not MOD)

@aholyoke aholyoke requested review from a team as code owners March 24, 2024 15:28
@aholyoke aholyoke requested a review from PhongChuong March 24, 2024 15:28
@product-auto-label product-auto-label bot added size: s Pull request size is small. api: bigquery Issues related to the googleapis/python-bigquery-sqlalchemy API. labels Mar 24, 2024
@aholyoke aholyoke changed the title Implement modulus operator fix: implement modulus operator May 25, 2024
@Linchin Linchin added the kokoro:force-run Add this label to force Kokoro to re-run the tests. label May 29, 2024
@Linchin Linchin assigned Linchin and unassigned mrfaizal May 29, 2024
@yoshi-kokoro yoshi-kokoro removed the kokoro:force-run Add this label to force Kokoro to re-run the tests. label May 29, 2024
@aholyoke
Copy link
Contributor Author

@Linchin This seems like a pretty safe change. Any chance you can review it?

@chalmerlowe
Copy link
Collaborator

@aholyoke

Can you do me a favor?
Can you provide me with a example of what the outcome would look like if used in situ (not the fake version generated by the test code) but a sample that includes the calculation of a modulo compared to zero for example:

The sqlalchemy commands that would generate a statement that includes something akin to blah, blah, blah MOD(X, Y) == 0

I want to ensure that I am picturing this the way you are.

Thanks.

@aholyoke
Copy link
Contributor Author

aholyoke commented Jul 1, 2024

@chalmerlowe Sure, this is how it behaves before the change

In [1]: import sqlalchemy as sa; from sqlalchemy_bigquery import BigQueryDialect

In [2]: meta = sa.MetaData(); table = sa.Table("table_name", meta, sa.Column("col_name", sa.Integer()))

In [3]: print(str(sa.select(table.c.col_name).where(table.c.col_name % 2 == 0).compile(dialect=BigQueryDialect())))
SELECT `table_name`.`col_name`
FROM `table_name`
WHERE `table_name`.`col_name` % :col_name_1 = :param_1

Which the following screenshots confirm is not the correct syntax in BQ:
Screenshot 2024-06-30 at 9 58 36 PM
Screenshot 2024-06-30 at 9 58 53 PM

and this is how it behaves after the change

In [1]: import sqlalchemy as sa; from sqlalchemy_bigquery import BigQueryDialect

In [2]: meta = sa.MetaData(); table = sa.Table("table_name", meta, sa.Column("col_name", sa.Integer()))

In [3]: print(str(sa.select(table.c.col_name).where(table.c.col_name % 2 == 0).compile(dialect=BigQueryDialect())))
SELECT `table_name`.`col_name`
FROM `table_name`
WHERE MOD(`table_name`.`col_name`, :col_name_1) = :param_1

In [4]: sa.select(table.c.col_name).where(table.c.col_name % 2 == 0).compile(dialect=BigQueryDialect()).params
Out[4]: {'col_name_1': 2, 'param_1': 0}

@chalmerlowe chalmerlowe self-assigned this Jul 8, 2024
@chalmerlowe chalmerlowe added the kokoro:force-run Add this label to force Kokoro to re-run the tests. label Jul 8, 2024
@yoshi-kokoro yoshi-kokoro removed the kokoro:force-run Add this label to force Kokoro to re-run the tests. label Jul 8, 2024
@chalmerlowe chalmerlowe added the owlbot:run Add this label to trigger the Owlbot post processor. label Jul 8, 2024
@gcf-owl-bot gcf-owl-bot bot removed the owlbot:run Add this label to trigger the Owlbot post processor. label Jul 8, 2024
@chalmerlowe chalmerlowe added the kokoro:force-run Add this label to force Kokoro to re-run the tests. label Jul 8, 2024
@yoshi-kokoro yoshi-kokoro removed the kokoro:force-run Add this label to force Kokoro to re-run the tests. label Jul 8, 2024
@chalmerlowe chalmerlowe added kokoro:force-run Add this label to force Kokoro to re-run the tests. owlbot:run Add this label to trigger the Owlbot post processor. labels Jul 9, 2024
@gcf-owl-bot gcf-owl-bot bot removed the owlbot:run Add this label to trigger the Owlbot post processor. label Jul 9, 2024
@yoshi-kokoro yoshi-kokoro removed the kokoro:force-run Add this label to force Kokoro to re-run the tests. label Jul 9, 2024
Copy link
Collaborator

@chalmerlowe chalmerlowe left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM.

@chalmerlowe chalmerlowe merged commit f5fb1a2 into googleapis:main Jul 10, 2024
15 checks passed
@chalmerlowe
Copy link
Collaborator

@aholyoke

Thanks for pushing this PR. We appreciate the effort and your support in better understanding the nature of the issue this solves.

Have a great day! 💜

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
api: bigquery Issues related to the googleapis/python-bigquery-sqlalchemy API. size: s Pull request size is small.
Projects
None yet
Development

Successfully merging this pull request may close these issues.

5 participants