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

Invalid query generated after series of filters/mutates #1496

Closed
seibs opened this Issue Jun 20, 2018 · 3 comments

Comments

Projects
2 participants
@seibs

seibs commented Jun 20, 2018

When a series of mutates/filters are moved to "WITH" statements, the compiled statement sometimes includes incorrect table references.

Expression

t = ibis.table([('file_date', 'timestamp'), ('PARTITIONTIME', 'date'), ('val', 'int64')])
def test(table):
    table = table[table.PARTITIONTIME < ibis.date('2017-01-01')]
    table = table.mutate(file_date=table.file_date.cast('date'))
    table = table[table.file_date < ibis.date('2017-01-01')]
    table[table]
    table = table.mutate(XYZ=table.val * 2)
    return table
x = test(t)
print(ibis.bigquery.compile(x.join(x.view())[x]))

Compiled

WITH t0 AS (
  SELECT *
  FROM t4
  WHERE `PARTITIONTIME` < '2017-01-01'
),
t1 AS (
  SELECT CAST(`file_date` AS DATE) AS `file_date`, `PARTITIONTIME`, `val`
  FROM t0
),
t2 AS (
  SELECT t1.*, t0.`val` * 2 AS `XYZ`
  FROM t1
  WHERE t1.`file_date` < '2017-01-01'
)
SELECT t2.*
FROM t2
  CROSS JOIN t2 t3

@cpcloud cpcloud added this to the 0.14 milestone Jun 21, 2018

@cpcloud

This comment has been minimized.

Member

cpcloud commented Jun 21, 2018

This isn't actually a bug, but t4 is appearing because the way we generate names for tables created with ibis.table is the same way we generate aliases for subqueries. If you pass name='foobar' to ibis.table, for example, then you'll get this output:

WITH t0 AS (
  SELECT *
  FROM foobar
  WHERE `PARTITIONTIME` < DATE '2017-01-01'
),
t1 AS (
  SELECT CAST(`file_date` AS DATE) AS `file_date`, `PARTITIONTIME`, `val`
  FROM t0
),
t2 AS (
  SELECT t1.*, t0.`val` * 2 AS `XYZ`
  FROM t1
  WHERE t1.`file_date` < DATE '2017-01-01'
)
SELECT t2.*
FROM t2
  CROSS JOIN t2 t3

This is potentially a duplicate of #1460.

@seibs Is there another issue that manifested this way with non-generated table names?

@seibs

This comment has been minimized.

seibs commented Jun 21, 2018

@cpcloud Sorry, the issue is a little hidden. In the t2 definition, the t0 in the select should be t1.

@cpcloud cpcloud self-assigned this Jun 21, 2018

@cpcloud cpcloud added this to To do in BigQuery via automation Jun 21, 2018

@cpcloud

This comment has been minimized.

Member

cpcloud commented Jul 6, 2018

@seibs Finally got around to fixing this. The issue here is that we're fusing across projections without considering the immediate parent table. What happens is that when we're looking for fusion opportunities in a project we look for common expressions in the deepest base table in the tree and assume that we can substitute in the matching columns from that in whatever query we're projecting. This is invalid because there may be other non-fused relations in between. For now, the fix is to only allow fusing with the immediate parent.

@cpcloud cpcloud closed this in ca618b8 Jul 6, 2018

BigQuery automation moved this from To do to Done Jul 6, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment