Union except and intersect

[saschahofmann]

A lot (all?) backends support some kind of except/intersect UNION

E.g postgres https://www.postgresqltutorial.com/postgresql-union/
or bigquery https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#intersect

Does it make sense to implement these in the main repo? Do you have a feature freeze until the 2.0 release?

I could have a look at this myself if you agree this should go into ibis directly!

[datapythonista]

Maybe not in the docs, but UNION is implemented in Ibis already.

[saschahofmann]

Ahhh I see you implemented intersect and Except separately.! Yeah just discovered it in the code?

Sorry! I think this might be broken in Bigquery then but will test and write there separately

[datapythonista]

Bigquery needs to be adapted to the last changes in Ibis, but other than that, that part should be working.

Should we close this issue, or is there anything else that is missing from what you requested here?

[saschahofmann]

Yes sorry closing this. Needs to be addressed in BigQuery ibis-project/ibis-bigquery#87

[saschahofmann]

Hey sorry,
I was just playing around with difference in bigquery and postgres. I can do things like this

table.difference(table2)

But if I try to create a projection like this

table.difference(table2).projection(['column1', 'column2']).compile()

I get a NotImplementedError: <class 'ibis.expr.operations.Difference'>

Originating from here https://github.com/ibis-project/ibis/blob/1.4.0/ibis/backends/base_sqlalchemy/compiler.py#L401

It works for Union any ideas how I can work around this e.g. with what if I want to run a filter on a difference query, @datapythonista ?

[saschahofmann]

Note this is for ibis 1.4.0. since main looks very different this could be fixed there alread?

[datapythonista]

Not so familiar with this part, from the code looks like it should be implemented, not sure what is missing. I don't think there has been any change in that part recently, other than moving code from one place to another. Happy to take PRs for this, before or after the release.

[saschahofmann]

Ok I'll have a look

[saschahofmann]

Will probably create a PR for this tomorrow just for completeness. I am monkey-patching 1.4.0 like this in our project right now

from ibis.backends.base_sqlalchemy import compiler

def _collect_Difference(self, expr, toplevel=False):
    if toplevel:
        raise NotImplementedError()


compiler.SelectBuilder._collect_Difference = _collect_Difference