Allow expressions/constants in join predicates

[GoogleCodeExporter]

Currently join predicates must be qualified field names.
Expressions or constants are not allowed.

E.g. this query does not work:

SELECT *
FROM
(SELECT year,
  COUNT(*) AS cnt
 FROM publicdata:samples.natality
 GROUP BY year) cur

 LEFT OUTER JOIN (SELECT year,
  COUNT(*) AS cnt
 FROM publicdata:samples.natality
 GROUP BY year) prev

 ON cur.year - 1 = prev.year

and fails with:  "Error: ON clause must be AND of = comparisons of one field 
name from each table, with all field names prefixed with table name."

Using "ON prev.year = 2000" also fails as constants are not allowed.

The workaround is to use nested subqueries with the expression/constant as a 
column, which can then then joined on in the outer scope. However this 
increases query complexity and slows down ad hoc query development.

Please allow expressions and constants in join predicates.

Original issue reported on code.google.com by nick.pre...@servian.com.au on 4 Oct 2013 at 7:02

• Merged into: BigQuery SQL improvements #448

[GoogleCodeExporter]

Agreed, this behavior is less than ideal.  I'll look in to what we can do about 
this, but adding this feature will likely be a few months out, at least.  I 
would recommend continuing to use the workaround for the time being.  Thanks 
for the request!

Original comment by dk...@google.com on 4 Oct 2013 at 7:40

[GoogleCodeExporter]

Original comment by thomasp...@google.com on 22 Aug 2014 at 5:53

• Changed state: Accepted

[GoogleCodeExporter]

any update on this feature?

Original comment by emand...@gmail.com on 17 Nov 2014 at 9:44

[GoogleCodeExporter]

It's still something we'd like to add, but it's low priority, and we don't have 
an ETA right now.

Is this an issue of convenience (i.e., not having to write the subquery), or is 
this a more significant blocker for you?

Original comment by jcon...@google.com on 17 Nov 2014 at 5:50

[GoogleCodeExporter]

It is not a significant block.  My workaround was to do add an expression 
inside a subquery. 

Original comment by emand...@gmail.com on 17 Nov 2014 at 10:57

[GoogleCodeExporter]

Any updates on this? 
Thanks in advance

Original comment by koen.wil...@gmail.com on 15 Jul 2015 at 1:44

[GoogleCodeExporter]

Same status as #4.

Original comment by jcon...@google.com on 15 Jul 2015 at 8:18

[GoogleCodeExporter]

Is this still low priority for the team? Because for me, the restriction on 
join conditions to simple field equalities is the single most frustrating 
aspect of BigQuery.

Original comment by oliver.u...@gmail.com on 5 Nov 2015 at 2:18

[GoogleCodeExporter]

Update: Priority of this feature has been raised, and implementation work is 
under way. Current plan is to target it with next BigQuery release.

Original comment by mos...@google.com on 5 Nov 2015 at 3:02

[GoogleCodeExporter]

Delighted to hear that!

Original comment by oliver.u...@gmail.com on 5 Nov 2015 at 3:06

[GoogleCodeExporter]

looking forward to seeing this

Original comment by jason...@gmail.com on 22 Feb 2016 at 12:52

[GoogleCodeExporter]

I also have a few queries for which this functionality would be very useful! 
Hope it's coming soon.

Original comment by wouter.c...@vente-exclusive.com on 26 Feb 2016 at 8:44

[GoogleCodeExporter]

Duplicating against the SQL dialect bug.

Original comment by jcon...@google.com on 26 Feb 2016 at 4:35

• Changed state: Duplicate