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

[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