DATEDIFF always returns null if using a date field that is the result of a LAG window function in a subquery / view

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Create a simple table with one column of different dates (2016-01-01 
00:00:00, 2016-02-01 00:00:00, etc.)
2. Using the lag window function, get the previous date for each date
3. Using the query for step 2. as a subquery, compute the datediff between the 
date and the lagged previousDate. 

What is the expected output? What do you see instead?
I expected to have the datediff function return sensible numbers. Instead it 
returned null for all rows. Consequently, if the results of step 2 are saved as 
a table, and that table used in step 3, then it returns actual numbers instead 
of null. If you save the query of step 2 as a view and use that in step 3, 
you'll get null values.

I'm using the web ui for BigQuery on 2016-04-05. 

Original issue reported on code.google.com by joel.the...@poweredbygrow.com on 5 Apr 2016 at 11:55

• Merged into: #374

Attachments:

• wrong_results.png
• testing.csv

[GoogleCodeExporter]

Unfortunately some analytic functions convert their results into integers 
incorrectly. See issue 374 for a similar bug.

We're reluctant to make changes that might break current users, but the new SQL 
dialect (issue 448) will address this.

In the meantime, you can work around the issue by explicitly casting the 
analytic function output to a timestamp before using it.

Original comment by jcon...@google.com on 6 Apr 2016 at 4:27

• Changed state: Duplicate