Doubling WITH statements when doing a complex EXCEPT

[saschahofmann]

For some reason ibis_bigquery doubles the with statements when executing a set of projections combined with a join and except.

As example

import ibis
import ibis_bigquery as iq
t = ibis.table([('id', 'int32'), ('athlete', 'string'), ('birthday', 'date')])
m = ibis.table([('i', 'int32'), ('medals', 'int32')])

s = t.projection(['id', 'athlete'])

j = s.join(m, s['id']==m['i']).materialize()

f = j[j['athlete']=='Usain']

e = j.difference(f).projection(['id', 'athlete', 'medals'])

print(iq.compile(e))

compiles to

WITH t0 AS (
  SELECT `id`, `athlete`
  FROM unbound_table_2
),
t1 AS (
  SELECT *
  FROM t0
    INNER JOIN unbound_table_3 t4
      ON `id` = t4.`i`
)
SELECT t2.`id`, t2.`athlete`, t2.`medals`
FROM (
  WITH t0 AS (
    SELECT `id`, `athlete`
    FROM unbound_table_2
  ),
  t1 AS (
    SELECT *
    FROM t0
      INNER JOIN unbound_table_3 t4
        ON `id` = t4.`i`
  )
  SELECT *
  FROM t1
  EXCEPT
  SELECT t1.*
  FROM t1
  WHERE t1.`athlete` = 'Usain'
) t2

As you can see the global WITH statement is the same as the one in the global FROM.

Besides the point but due to #87 this will also fail because EXCEPT needs to be EXCEPT DISTINCT. Now that everything migrated to ibis 2.0. I'll take another look at doing that.