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.
Comments
Post a Comment