google bigquery - Different Aliases produce different Result for exactly same Query -

below 2 versions of same query

version 1 (uses k alias in inner select):

select k, w_vol,    row_number() on (order k desc) rank1,   row_number() on (order w_vol desc) rank2 (   select w_vol, c_date k      (select 1590 c_date, 1 w_vol),     (select 1599 c_date, 1 w_vol),     (select 1602 c_date, 1 w_vol),     (select 1609 c_date, 2 w_vol),     (select 1610 c_date, 1 w_vol), ) order 1 

version 2 (uses l alias in inner select):

select l, w_vol,    row_number() on (order l desc) rank1,   row_number() on (order w_vol desc) rank2 (   select w_vol, c_date l      (select 1590 c_date, 1 w_vol),     (select 1599 c_date, 1 w_vol),     (select 1602 c_date, 1 w_vol),     (select 1609 c_date, 2 w_vol),     (select 1610 c_date, 1 w_vol), ) order 1  

below output consistently getting both queries (note no cached results used)

i don't have problem understanding why or result generated (it relatively trivial) - - expected result same no matter alias used - alias @ all!

question: why consistently getting 1 result version 1 , version 2?

note: not interested in recommendations on how re-write query differently "hide" problem! please not bother answers in direction!

forgot mention: 

most of aliases produce same result 'l', few with'k'. example of such 'x'. wanted make clear - not 1 particular alias problematic. , more - problematic alias depends on name of fileds.

so think hashing issue involves fields/aliases names - wild guess!

i don't have specific explanation behavior, note both results still correct: difference between results different order chosen when sorting several rows have same value (1) column being sorted (w_vol).

generally speaking, bigquery not promise particular ordering when applying order by equal values, , ordering may vary across different executions of same query reason.

i agree it's interesting ordering seems depend on alias, see plenty of plausible explanations why happens--for example, engine might iterate through list of fields use analytic functions, , changing alias might change order in analytic functions evaluated, in turn changes ordering of rows equal values when sorting 1 column.